Fixed wrong usage of lower(digest) in eDBregister_attempts()

This is a follow up of commit de7a3d88c78cdf400fcee78f71946da8b12ec74f.That
commit introduced an SQL error when eDBregister_attempts() was attempting
to update certificate digest attempt records.  Overhauled and fixed the
complete eDBregister_attempts() function.

1 files changed, 14 insertions, 8 deletions

diff --git a/database/sqlite/edb-sqlite.c b/database/sqlite/edb-sqlite.c
index b9a311b..112153a 100644
--- a/database/sqlite/edb-sqlite.c
+++ b/database/sqlite/edb-sqlite.c
@@ -61,7 +61,8 @@
  * 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 *colname;          /**< Field when doing look up in blacklist and attempts tables */
+        char *colname_where;    /**< Field when using column name in WHERE section of SQL queries */
         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 */
@@ -74,9 +75,9 @@ typedef struct {
  */
 static const eDBattempt_types_t eDBattempt_types[] = {
         {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},
+        {"remoteip\0", "remoteip\0", "allow_ipaddr_attempts\0", "IP Address\0", "10\0", NULL},
+        {"digest\0", "lower(digest)\0", "allow_cert_attempts\0", "Certificate\0", "5\0", "lower\0"},
+        {"username\0", "username\0", "allow_username_attempts\0", "Username\0", "5\0", NULL},
         {NULL, NULL, NULL, NULL}
 };
 
@@ -407,7 +408,7 @@ int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val)
               eDBattempt_types[type].descr, val);
 
         blr  = sqlite_query(ctx, "SELECT blid FROM openvpn_blacklist WHERE %s = %s%s'%q'%s",
-                            eDBattempt_types[type].colname,
+                            eDBattempt_types[type].colname_where,
                             defaultValue(eDBattempt_types[type].value_func, ""),
                             (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""),
                             val,
@@ -436,7 +437,7 @@ int eDBblacklist_check(eurephiaCTX *ctx, const int type, const char *val)
                                     "SELECT atpid, attempts >= %q FROM openvpn_attempts WHERE %s = '%q'",
                                     defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg),
                                                   eDBattempt_types[type].default_value),
-                                    eDBattempt_types[type].colname, val);
+                                    eDBattempt_types[type].colname_where, val);
                 if( atpr != NULL ) {
                         atpid = strdup_nullsafe(sqlite_get_value(atpr, 0, 0));
                         atpexceed = atoi_nullsafe(sqlite_get_value(atpr, 0, 1));
@@ -494,11 +495,16 @@ void eDBregister_attempt(eurephiaCTX *ctx, int type, int mode, const char *value
                            "SELECT atpid, attempts > %s, blid, attempts "
                            "  FROM openvpn_attempts "
                            "  LEFT JOIN openvpn_blacklist USING(%s)"
-                           " WHERE %s = '%q'",
+                           " WHERE %s = %s%s'%q'%s",
                            defaultValue(eGet_value(ctx->dbc->config, eDBattempt_types[type].allow_cfg),
                                         eDBattempt_types[type].default_value),
                            eDBattempt_types[type].colname,
-                           eDBattempt_types[type].colname, value);
+                           eDBattempt_types[type].colname_where,
+                           defaultValue(eDBattempt_types[type].value_func, ""),
+                           (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? "(" : ""),
+                           value,
+                           (strlen_nullsafe(eDBattempt_types[type].value_func) > 0 ? ")" : "")
+                           );
         if( res == NULL ) {
                 eurephia_log(ctx, LOG_FATAL, 0, "Could not look up atpid in openvpn_attempts");
                 return;