Certificate digests are always lower case.

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

7 files changed, 9 insertions, 8 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)"