Certificate digests are always lower case.

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

1 files changed, 22 insertions, 17 deletions

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 ) {