Certificate digests are always lower case.

This makes sure that all interactions with the database will convert
the digest strings to lower case.

8 files changed, 31 insertions, 25 deletions

diff --git a/database/sqlite/administration/attempts.c b/database/sqlite/administration/attempts.c
index e94878a..b35b386 100644
--- a/database/sqlite/administration/attempts.c
+++ b/database/sqlite/administration/attempts.c
@@ -74,7 +74,7 @@ xmlDoc *attempts_list(eurephiaCTX *ctx, eDBfieldMap *fmap) {
 
         // Query the database for registered attempts
         res = sqlite_query_mapped(ctx, SQL_SELECT,
-                                  "SELECT username, digest, remoteip, attempts,"
+                                  "SELECT username, lower(digest), remoteip, attempts,"
                                   "       registered, last_attempt, atpid"
                                   "  FROM openvpn_attempts",
                                   NULL, fmap, "atpid");
diff --git a/database/sqlite/administration/blacklist.c b/database/sqlite/administration/blacklist.c
index 80c4506..79f4b87 100644
--- a/database/sqlite/administration/blacklist.c
+++ b/database/sqlite/administration/blacklist.c
@@ -75,7 +75,7 @@ xmlDoc *blacklist_list(eurephiaCTX *ctx, eDBfieldMap *fmap) {
 
         // Query the database for registered attempts
         res = sqlite_query_mapped(ctx, SQL_SELECT,
-                                  "SELECT username, digest, remoteip,"
+                                  "SELECT username, lower(digest), remoteip,"
                                   "       registered, last_accessed, blid"
                                   "  FROM openvpn_blacklist",
                                   NULL, fmap, "blid");
diff --git a/database/sqlite/administration/certificates.c b/database/sqlite/administration/certificates.c
index b64aa07..d8b7a23 100644
--- a/database/sqlite/administration/certificates.c
+++ b/database/sqlite/administration/certificates.c
@@ -86,7 +86,8 @@ static xmlDoc *certificate_list(eurephiaCTX *ctx, eDBfieldMap *srch_map, const c
         }
 
         res = sqlite_query_mapped(ctx, SQL_SELECT,
-                                  "SELECT depth, digest, common_name, organisation, email, registered, certid"
+                                  "SELECT depth, lower(digest), common_name, organisation, email, "
+				  "       registered, certid"
                                   "  FROM openvpn_certificates", NULL, srch_map, sortkeys);
         if( res == NULL ) {
                 eurephia_log(ctx, LOG_ERROR, 0, "Could not query the certificate table");
diff --git a/database/sqlite/administration/firewalladmin.c b/database/sqlite/administration/firewalladmin.c
index fdebccb..3ea2f28 100644
--- a/database/sqlite/administration/firewalladmin.c
+++ b/database/sqlite/administration/firewalladmin.c
@@ -89,7 +89,7 @@ xmlDoc *fwadmin_search(eurephiaCTX *ctx, eDBfieldMap *fmap) {
                                   "SELECT access_descr, fw_profile, accessprofile, "
                                   "       uid, username, "
                                   "       uac.certid, common_name, organisation, "
-                                  "       email, digest, c.registered, uicid "
+                                  "       email, lower(digest), c.registered, uicid "
                                   "  FROM openvpn_accesses"
                                   "  LEFT JOIN openvpn_usercerts uac USING (accessprofile)"
                                   "  LEFT JOIN openvpn_users USING (uid)"
diff --git a/database/sqlite/administration/lastlog.c b/database/sqlite/administration/lastlog.c
index e850d94..64adf78 100644
--- a/database/sqlite/administration/lastlog.c
+++ b/database/sqlite/administration/lastlog.c
@@ -96,7 +96,7 @@ xmlDoc *eDBadminGetLastlog(eurephiaCTX *ctx, xmlDoc *srch_xml, const char *sortk
                                   "       vpnipaddr, vpnipmask, sessionstatus, sessionkey,"
                                   "       login, logout, session_duration, session_deleted,"
                                   "       bytes_sent, bytes_received, uicid, accessprofile,"
-                                  "       access_descr, fw_profile, depth, digest,"
+                                  "       access_descr, fw_profile, depth, lower(digest),"
                                   "       common_name, organisation, email, username, ll.uid"
                                   "  FROM openvpn_lastlog ll"
                                   "  LEFT JOIN openvpn_usercerts USING (uid, certid)"
diff --git a/database/sqlite/administration/useraccount.c b/database/sqlite/administration/useraccount.c
index 17b9330..3c2c5d3 100644
--- a/database/sqlite/administration/useraccount.c
+++ b/database/sqlite/administration/useraccount.c
@@ -177,7 +177,7 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType,
                 if( infoType & USERINFO_certs ) {
                         // Extract certificate info
                         qres = sqlite_query(ctx,
-                                            "SELECT depth, digest, common_name, organisation, email, "
+                                            "SELECT depth, lower(digest), common_name, organisation, email, "
                                             "       c.registered, c.certid,  uc.accessprofile, access_descr,"
                                             "       fw_profile"
                                             "  FROM openvpn_certificates c"
@@ -230,7 +230,7 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType,
                                             "       vpnipaddr, vpnipmask, sessionstatus, sessionkey,"
                                             "       login, logout, session_duration, session_deleted,"
                                             "       bytes_sent, bytes_received, uicid, accessprofile,"
-                                            "       access_descr, fw_profile, depth, digest,"
+                                            "       access_descr, fw_profile, depth, lower(digest),"
                                             "       common_name, organisation, email"
                                             "  FROM openvpn_lastlog ll"
                                             "  LEFT JOIN openvpn_usercerts USING (uid, certid)"
diff --git a/database/sqlite/administration/usercerts.c b/database/sqlite/administration/usercerts.c
index 8e235c5..abc0b40 100644
--- a/database/sqlite/administration/usercerts.c
+++ b/database/sqlite/administration/usercerts.c
@@ -92,7 +92,7 @@ xmlDoc *usercerts_search(eurephiaCTX *ctx, eDBfieldMap *where_m, const char *sor
                                   "SELECT uicid, ucs.uid AS uid, certid, ucs.registered AS registered,"
                                   "       ucs.accessprofile AS accessprofile, access_descr,"
                                   "       username, "
-                                  "       common_name, organisation, email, digest, depth "
+                                  "       common_name, organisation, email, lower(digest), depth "
                                   "  FROM openvpn_usercerts ucs"
                                   "  LEFT JOIN openvpn_certificates USING(certid)"
                                   "  LEFT JOIN openvpn_accesses acc ON(ucs.accessprofile = acc.accessprofile)"
diff --git a/database/sqlite/edb-sqlite.c b/database/sqlite/edb-sqlite.c
index 6728f84..b9a311b 100644
--- a/database/sqlite/edb-sqlite.c
+++ b/database/sqlite/edb-sqlite.c
@@ -1,5 +1,5 @@
-/*  eurephia-sqlite.c  --  Main driver for eurephia authentication plugin for OpenVPN
- *                         This is the SQLite database driver
+/*  edb-sqlite.c  --  Main driver for eurephia authentication plugin for OpenVPN
+ *                    This is the SQLite database driver
  *
  *  GPLv2 only - Copyright (C) 2008, 2009
  *               David Sommerseth <dazo@users.sourceforge.net>
@@ -61,10 +61,11 @@
  * to database field names, configuration options (with default values) and description
  */
 typedef struct {
-        char *colname;		/**< Column name when doing look up in blacklist and attempts tables */
-        char *allow_cfg;	/**< Configure parameter for the attempt limits */
-        char *descr;		/**< Description, used to give more readable output for users */
-        char *default_value;	/**< Default value, if config option is not found */
+        char *colname;          /**< Column name when doing look up in blacklist and attempts tables */
+        char *allow_cfg;        /**< Configure parameter for the attempt limits */
+        char *descr;            /**< Description, used to give more readable output for users */
+        char *default_value;    /**< Default value, if config option is not found */
+        char *value_func;       /**< If not NULL, the value will be passed through the given SQL function */
 } eDBattempt_types_t;
                
 
@@ -72,11 +73,11 @@ typedef struct {
  *  Static mapping table with the needed values.  Uses the eDBattempt_types_t struct.
  */
 static const eDBattempt_types_t eDBattempt_types[] = {
-        {NULL, NULL, NULL},
-        {"remoteip\0", "allow_ipaddr_attempts\0", "IP Address\0", "10\0"},
-        {"digest\0", "allow_cert_attempts\0", "Certificate\0", "5\0"},
-        {"username\0", "allow_username_attempts\0", "Username\0", "5\0"},
-        {NULL, NULL, NULL}
+        {NULL, NULL, NULL, NULL},
+        {"remoteip\0", "allow_ipaddr_attempts\0", "IP Address\0", "10\0", NULL},
+        {"lower(digest)\0", "allow_cert_attempts\0", "Certificate\0", "5\0", "lower\0"},
+        {"username\0", "allow_username_attempts\0", "Username\0", "5\0", NULL},
+        {NULL, NULL, NULL, NULL}
 };
 
 
@@ -232,7 +233,7 @@ int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char
                            "  FROM openvpn_certificates cert"
                            "  LEFT JOIN openvpn_blacklist bl USING(digest)"
                            " WHERE organisation='%q' AND common_name='%q' "
-                           "      AND email='%q' AND depth='%i' AND cert.digest='%q'%c",
+                           "      AND email='%q' AND depth='%i' AND lower(cert.digest)=lower('%q')%c",
                            org, cname, email, depth, digest, 0);
 
         if( res != NULL ) {
@@ -253,7 +254,7 @@ int eDBauth_TLS(eurephiaCTX *ctx, const char *org, const char *cname, const char
                 eurephia_log(ctx, LOG_FATAL, 0, "Could not look up certificate information");
         }
 
-        DEBUG(ctx, 20, "Result function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %s) - %i",
+        DEBUG(ctx, 20, "Result function call: eDBauth_TLS(ctx, '%s', '%s', '%s', '%s', %i) - %i",
               org, cname, email, digest, depth, certid);
 
         return certid;
@@ -405,8 +406,12 @@ int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val)
         DEBUG(ctx, 20, "Function call: eDBblacklist_check(ctx, '%s', '%s')",
               eDBattempt_types[type].descr, val);
 
-        blr  = sqlite_query(ctx, "SELECT blid FROM openvpn_blacklist WHERE %s = '%q'",
-                            eDBattempt_types[type].colname, val);
+        blr  = sqlite_query(ctx, "SELECT blid FROM openvpn_blacklist WHERE %s = %s%s'%q'%s",
+                            eDBattempt_types[type].colname,
+                            defaultValue(eDBattempt_types[type].value_func, ""),
+                            (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""),
+                            val,
+                            (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? ")" : ""));
         if( blr != NULL ) {
                 blid = strdup_nullsafe(sqlite_get_value(blr, 0, 0));
                 sqlite_free_results(blr);
@@ -1033,8 +1038,8 @@ eurephiaVALUES *eDBget_blacklisted_ip(eurephiaCTX *ctx) {
         if( res == NULL ) {
                 eurephia_log(ctx, LOG_FATAL, 0,
                              "Could not retrieve blacklisted IP addresses from the database");
-		return NULL;
-	}
+                return NULL;
+        }
         ret = eCreate_value_space(ctx, 21);
         for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
                 if( (ip = sqlite_get_value(res, i, 0)) != NULL ) {