From 0b86fb147b4a61f315770fa5bba4466ca18ddfa8 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Felix=20D=C3=B6rre?= <felix@dogcraft.de>
Date: Sat, 3 Sep 2016 17:07:57 +0200
Subject: [PATCH] upd: cleanup SQL statements to make them statically
 verifiable.

Change-Id: I4e7b773bf13a1c5a9b979a995bf72fe5ba45f9d0
---
 src/org/cacert/gigi/database/DBEnum.java      |  6 +++++
 .../gigi/database/GigiPreparedStatement.java  | 17 +++++++++++++
 src/org/cacert/gigi/dbObjects/Assurance.java  |  8 +++++-
 .../cacert/gigi/dbObjects/Certificate.java    | 10 ++++++--
 src/org/cacert/gigi/dbObjects/Domain.java     |  2 +-
 .../cacert/gigi/dbObjects/DomainPingType.java |  9 ++++++-
 .../cacert/gigi/dbObjects/EmailAddress.java   |  8 +++---
 src/org/cacert/gigi/dbObjects/Group.java      | 16 ++++++------
 src/org/cacert/gigi/dbObjects/Job.java        | 12 +++++----
 src/org/cacert/gigi/dbObjects/Name.java       | 25 +++++++++++++------
 src/org/cacert/gigi/dbObjects/NamePart.java   |  5 ++--
 src/org/cacert/gigi/dbObjects/User.java       |  9 ++++---
 src/org/cacert/gigi/email/EmailProvider.java  |  6 ++---
 src/org/cacert/gigi/output/GroupSelector.java |  2 +-
 src/org/cacert/gigi/ping/DomainPinger.java    | 13 +++++++++-
 src/org/cacert/gigi/util/Notary.java          |  2 +-
 .../cacert/gigi/TestUserGroupMembership.java  |  4 +--
 .../gigi/pages/account/TestMyDetailsEdit.java |  6 ++---
 .../admin/TestSEAdminNotificationMail.java    |  8 +++---
 .../cacert/gigi/testUtils/BusinessTest.java   |  2 +-
 .../cacert/gigi/testUtils/ManagedTest.java    |  2 +-
 .../org/cacert/gigi/pages/Manager.java        |  2 +-
 22 files changed, 122 insertions(+), 52 deletions(-)
 create mode 100644 src/org/cacert/gigi/database/DBEnum.java

diff --git a/src/org/cacert/gigi/database/DBEnum.java b/src/org/cacert/gigi/database/DBEnum.java
new file mode 100644
index 00000000..72c45a16
--- /dev/null
+++ b/src/org/cacert/gigi/database/DBEnum.java
@@ -0,0 +1,6 @@
+package org.cacert.gigi.database;
+
+public interface DBEnum {
+
+    public String getDBName();
+}
diff --git a/src/org/cacert/gigi/database/GigiPreparedStatement.java b/src/org/cacert/gigi/database/GigiPreparedStatement.java
index eae804b8..a779f965 100644
--- a/src/org/cacert/gigi/database/GigiPreparedStatement.java
+++ b/src/org/cacert/gigi/database/GigiPreparedStatement.java
@@ -76,6 +76,15 @@ public class GigiPreparedStatement implements AutoCloseable {
         }
     }
 
+    public void setEnum(int parameterIndex, DBEnum x) {
+        try {
+            target.setString(parameterIndex, x.getDBName());
+        } catch (SQLException e) {
+            handleSQL(e);
+            throw new Error(e);
+        }
+    }
+
     public void setDate(int parameterIndex, Date x) {
         try {
             target.setDate(parameterIndex, x);
@@ -116,6 +125,14 @@ public class GigiPreparedStatement implements AutoCloseable {
         }
     }
 
+    public int getParameterCount() {
+        try {
+            return target.getParameterMetaData().getParameterCount();
+        } catch (SQLException e) {
+            throw new Error(e);
+        }
+    }
+
     private void handleSQL(SQLException e) {
         // TODO Auto-generated method stub
 
diff --git a/src/org/cacert/gigi/dbObjects/Assurance.java b/src/org/cacert/gigi/dbObjects/Assurance.java
index f3940ebb..33dbf1ed 100644
--- a/src/org/cacert/gigi/dbObjects/Assurance.java
+++ b/src/org/cacert/gigi/dbObjects/Assurance.java
@@ -1,11 +1,12 @@
 package org.cacert.gigi.dbObjects;
 
+import org.cacert.gigi.database.DBEnum;
 import org.cacert.gigi.dbObjects.wrappers.DataContainer;
 
 @DataContainer
 public class Assurance {
 
-    public enum AssuranceType {
+    public enum AssuranceType implements DBEnum {
         FACE_TO_FACE("Face to Face Meeting"), TOPUP("TOPUP"), TTP_ASSISTED("TTP-Assisted"), NUCLEUS("Nucleus Bonus");
 
         private final String description;
@@ -17,6 +18,11 @@ public class Assurance {
         public String getDescription() {
             return description;
         }
+
+        @Override
+        public String getDBName() {
+            return description;
+        }
     }
 
     private int id;
diff --git a/src/org/cacert/gigi/dbObjects/Certificate.java b/src/org/cacert/gigi/dbObjects/Certificate.java
index b0c85e96..12aa2993 100644
--- a/src/org/cacert/gigi/dbObjects/Certificate.java
+++ b/src/org/cacert/gigi/dbObjects/Certificate.java
@@ -17,6 +17,7 @@ import java.util.List;
 import java.util.Map.Entry;
 
 import org.cacert.gigi.GigiApiException;
+import org.cacert.gigi.database.DBEnum;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.output.template.Outputable;
@@ -25,7 +26,7 @@ import org.cacert.gigi.util.KeyStorage;
 
 public class Certificate implements IdCachable {
 
-    public enum SANType {
+    public enum SANType implements DBEnum {
         EMAIL("email"), DNS("DNS");
 
         private final String opensslName;
@@ -37,6 +38,11 @@ public class Certificate implements IdCachable {
         public String getOpensslName() {
             return opensslName;
         }
+
+        @Override
+        public String getDBName() {
+            return opensslName;
+        }
     }
 
     public static class SubjectAlternateName implements Comparable<SubjectAlternateName> {
@@ -478,7 +484,7 @@ public class Certificate implements IdCachable {
     public static Certificate[] findBySANPattern(String request, SANType type) {
         try (GigiPreparedStatement prep = new GigiPreparedStatement("SELECT `certId` FROM `subjectAlternativeNames` WHERE `contents` LIKE ? and `type`=?::`SANType` GROUP BY `certId` LIMIT 100", true)) {
             prep.setString(1, request);
-            prep.setString(2, type.getOpensslName());
+            prep.setEnum(2, type);
             return fetchCertsToArray(prep);
         }
     }
diff --git a/src/org/cacert/gigi/dbObjects/Domain.java b/src/org/cacert/gigi/dbObjects/Domain.java
index fcd6709f..36b7dc6f 100644
--- a/src/org/cacert/gigi/dbObjects/Domain.java
+++ b/src/org/cacert/gigi/dbObjects/Domain.java
@@ -110,7 +110,7 @@ public class Domain implements IdCachable, Verifyable {
     public void addPing(DomainPingType type, String config) throws GigiApiException {
         try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `pingconfig` SET `domainid`=?, `type`=?::`pingType`, `info`=?")) {
             ps.setInt(1, id);
-            ps.setString(2, type.toString().toLowerCase());
+            ps.setEnum(2, type);
             ps.setString(3, config);
             ps.execute();
         }
diff --git a/src/org/cacert/gigi/dbObjects/DomainPingType.java b/src/org/cacert/gigi/dbObjects/DomainPingType.java
index fcef3ce1..d86970b7 100644
--- a/src/org/cacert/gigi/dbObjects/DomainPingType.java
+++ b/src/org/cacert/gigi/dbObjects/DomainPingType.java
@@ -1,5 +1,12 @@
 package org.cacert.gigi.dbObjects;
 
-public enum DomainPingType {
+import org.cacert.gigi.database.DBEnum;
+
+public enum DomainPingType implements DBEnum {
     EMAIL, DNS, HTTP, SSL;
+
+    @Override
+    public String getDBName() {
+        return toString().toLowerCase();
+    }
 }
diff --git a/src/org/cacert/gigi/dbObjects/EmailAddress.java b/src/org/cacert/gigi/dbObjects/EmailAddress.java
index 75678539..afd7f2c1 100644
--- a/src/org/cacert/gigi/dbObjects/EmailAddress.java
+++ b/src/org/cacert/gigi/dbObjects/EmailAddress.java
@@ -54,14 +54,16 @@ public class EmailAddress implements IdCachable, Verifyable {
                 if (id != 0) {
                     throw new IllegalStateException("already inserted.");
                 }
-                try (GigiPreparedStatement psCheck = new GigiPreparedStatement("SELECT 1 FROM `emails` WHERE email=? AND deleted is NULL"); GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `emails` SET memid=?, email=?")) {
-                    ps.setInt(1, owner.getId());
-                    ps.setString(2, address);
+                try (GigiPreparedStatement psCheck = new GigiPreparedStatement("SELECT 1 FROM `emails` WHERE email=? AND deleted is NULL")) {
                     psCheck.setString(1, address);
                     GigiResultSet res = psCheck.executeQuery();
                     if (res.next()) {
                         throw new GigiApiException("The email address is already known to the system.");
                     }
+                }
+                try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `emails` SET memid=?, email=?")) {
+                    ps.setInt(1, owner.getId());
+                    ps.setString(2, address);
                     ps.execute();
                     id = ps.lastInsertId();
                 }
diff --git a/src/org/cacert/gigi/dbObjects/Group.java b/src/org/cacert/gigi/dbObjects/Group.java
index 287187a2..d5dae430 100644
--- a/src/org/cacert/gigi/dbObjects/Group.java
+++ b/src/org/cacert/gigi/dbObjects/Group.java
@@ -1,11 +1,12 @@
 package org.cacert.gigi.dbObjects;
 
+import org.cacert.gigi.database.DBEnum;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.output.template.Outputable;
 import org.cacert.gigi.output.template.TranslateCommand;
 
-public enum Group {
+public enum Group implements DBEnum {
     SUPPORTER("supporter", "supporter", true, false, true), //
     ARBITRATOR("arbitrator", "arbitrator", true, false, true), //
     BLOCKEDASSURER("blockedassurer", "may not verify", true, false, false), //
@@ -72,13 +73,9 @@ public enum Group {
         return isSelfViewable;
     }
 
-    public String getDatabaseName() {
-        return dbName;
-    }
-
     public User[] getMembers(int offset, int count) {
         try (GigiPreparedStatement gps = new GigiPreparedStatement("SELECT `user` FROM `user_groups` WHERE `permission`=?::`userGroup` AND `deleted` IS NULL OFFSET ? LIMIT ?", true)) {
-            gps.setString(1, dbName);
+            gps.setEnum(1, this);
             gps.setInt(2, offset);
             gps.setInt(3, count);
             GigiResultSet grs = gps.executeQuery();
@@ -95,7 +92,7 @@ public enum Group {
 
     public int getMemberCount() {
         try (GigiPreparedStatement gps = new GigiPreparedStatement("SELECT COUNT(`user`) FROM `user_groups` WHERE `permission`=?::`userGroup` AND `deleted` IS NULL", true)) {
-            gps.setString(1, dbName);
+            gps.setEnum(1, this);
             GigiResultSet grs = gps.executeQuery();
             if ( !grs.next()) {
                 return 0;
@@ -107,4 +104,9 @@ public enum Group {
     public Outputable getName() {
         return tc;
     }
+
+    @Override
+    public String getDBName() {
+        return dbName;
+    }
 }
diff --git a/src/org/cacert/gigi/dbObjects/Job.java b/src/org/cacert/gigi/dbObjects/Job.java
index a48e44ac..4fa29738 100644
--- a/src/org/cacert/gigi/dbObjects/Job.java
+++ b/src/org/cacert/gigi/dbObjects/Job.java
@@ -3,6 +3,7 @@ package org.cacert.gigi.dbObjects;
 import java.sql.Date;
 
 import org.cacert.gigi.GigiApiException;
+import org.cacert.gigi.database.DBEnum;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.output.CertificateValiditySelector;
@@ -15,7 +16,7 @@ public class Job implements IdCachable {
         this.id = id;
     }
 
-    public static enum JobType {
+    public static enum JobType implements DBEnum {
         SIGN("sign"), REVOKE("revoke");
 
         private final String name;
@@ -24,7 +25,8 @@ public class Job implements IdCachable {
             this.name = name;
         }
 
-        public String getName() {
+        @Override
+        public String getDBName() {
             return name;
         }
     }
@@ -33,7 +35,7 @@ public class Job implements IdCachable {
         CertificateValiditySelector.checkValidityLength(period);
         try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `jobs` SET targetId=?, task=?::`jobType`, executeFrom=?, executeTo=?")) {
             ps.setInt(1, targetId.getId());
-            ps.setString(2, JobType.SIGN.getName());
+            ps.setEnum(2, JobType.SIGN);
             ps.setDate(3, start);
             ps.setString(4, period);
             ps.execute();
@@ -45,7 +47,7 @@ public class Job implements IdCachable {
 
         try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `jobs` SET targetId=?, task=?::`jobType`")) {
             ps.setInt(1, targetId.getId());
-            ps.setString(2, JobType.REVOKE.getName());
+            ps.setEnum(2, JobType.REVOKE);
             ps.execute();
             return cache.put(new Job(ps.lastInsertId()));
         }
@@ -85,7 +87,7 @@ public class Job implements IdCachable {
         if (i != null) {
             return i;
         }
-        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `jobs` WHERE id=?'")) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `jobs` WHERE id=?")) {
             ps.setInt(1, id);
             GigiResultSet rs = ps.executeQuery();
             if (rs.next()) {
diff --git a/src/org/cacert/gigi/dbObjects/Name.java b/src/org/cacert/gigi/dbObjects/Name.java
index eb4c7afb..dc1c0376 100644
--- a/src/org/cacert/gigi/dbObjects/Name.java
+++ b/src/org/cacert/gigi/dbObjects/Name.java
@@ -4,6 +4,7 @@ import java.io.PrintWriter;
 import java.util.Map;
 
 import org.cacert.gigi.GigiApiException;
+import org.cacert.gigi.database.DBEnum;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.dbObjects.NamePart.NamePartType;
@@ -13,6 +14,16 @@ import org.cacert.gigi.util.HTMLEncoder;
 
 public class Name implements Outputable, IdCachable {
 
+    public static enum NameSchemaType implements DBEnum {
+        SINGLE, WESTERN;
+
+        @Override
+        public String getDBName() {
+            return toString().toLowerCase();
+        }
+
+    }
+
     private abstract static class SchemedName {
 
         /**
@@ -27,7 +38,7 @@ public class Name implements Outputable, IdCachable {
          */
         public abstract String toAbbreviatedString();
 
-        public abstract String getSchemeName();
+        public abstract NameSchemaType getSchemeName();
 
         /**
          * @see Name#output(PrintWriter, Language, Map)
@@ -60,8 +71,8 @@ public class Name implements Outputable, IdCachable {
         }
 
         @Override
-        public String getSchemeName() {
-            return "single";
+        public NameSchemaType getSchemeName() {
+            return NameSchemaType.SINGLE;
         }
 
         @Override
@@ -201,8 +212,8 @@ public class Name implements Outputable, IdCachable {
         }
 
         @Override
-        public String getSchemeName() {
-            return "western";
+        public NameSchemaType getSchemeName() {
+            return NameSchemaType.WESTERN;
         }
 
         @Override
@@ -260,7 +271,7 @@ public class Name implements Outputable, IdCachable {
             }
             try (GigiPreparedStatement inserter = new GigiPreparedStatement("INSERT INTO `names` SET `uid`=?, `type`=?::`nameSchemaType`")) {
                 inserter.setInt(1, u.getId());
-                inserter.setString(2, scheme.getSchemeName());
+                inserter.setEnum(2, scheme.getSchemeName());
                 inserter.execute();
                 id = inserter.lastInsertId();
             }
@@ -268,7 +279,7 @@ public class Name implements Outputable, IdCachable {
                 inserter.setInt(1, id);
                 for (int i = 0; i < np.length; i++) {
                     inserter.setInt(2, i);
-                    inserter.setString(3, np[i].getType().getDbValue());
+                    inserter.setEnum(3, np[i].getType());
                     inserter.setString(4, np[i].getValue());
                     inserter.execute();
                 }
diff --git a/src/org/cacert/gigi/dbObjects/NamePart.java b/src/org/cacert/gigi/dbObjects/NamePart.java
index 42464b74..25a0a546 100644
--- a/src/org/cacert/gigi/dbObjects/NamePart.java
+++ b/src/org/cacert/gigi/dbObjects/NamePart.java
@@ -1,15 +1,16 @@
 package org.cacert.gigi.dbObjects;
 
+import org.cacert.gigi.database.DBEnum;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.dbObjects.wrappers.DataContainer;
 
 @DataContainer
 public class NamePart {
 
-    public enum NamePartType {
+    public enum NamePartType implements DBEnum {
         FIRST_NAME, LAST_NAME, SINGLE_NAME, SUFFIX;
 
-        public String getDbValue() {
+        public String getDBName() {
             return name().toLowerCase().replace("_", "-");
         }
     }
diff --git a/src/org/cacert/gigi/dbObjects/User.java b/src/org/cacert/gigi/dbObjects/User.java
index b2599688..e72908be 100644
--- a/src/org/cacert/gigi/dbObjects/User.java
+++ b/src/org/cacert/gigi/dbObjects/User.java
@@ -130,18 +130,19 @@ public class User extends CertificateOwner {
 
     public Name[] getNames() {
         try (GigiPreparedStatement gps = new GigiPreparedStatement("SELECT `id` FROM `names` WHERE `uid`=? AND `deleted` IS NULL", true)) {
+            gps.setInt(1, getId());
             return fetchNamesToArray(gps);
         }
     }
 
     public Name[] getNonDeprecatedNames() {
         try (GigiPreparedStatement gps = new GigiPreparedStatement("SELECT `id` FROM `names` WHERE `uid`=? AND `deleted` IS NULL AND `deprecated` IS NULL", true)) {
+            gps.setInt(1, getId());
             return fetchNamesToArray(gps);
         }
     }
 
     private Name[] fetchNamesToArray(GigiPreparedStatement gps) {
-        gps.setInt(1, getId());
         GigiResultSet rs = gps.executeQuery();
         rs.last();
         Name[] dt = new Name[rs.getRow()];
@@ -261,7 +262,7 @@ public class User extends CertificateOwner {
     public int getExperiencePoints() {
         try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT count(*) FROM ( SELECT `names`.`uid` FROM `notary` INNER JOIN `names` ON `names`.`id` = `to` WHERE `from`=? AND `notary`.`deleted` IS NULL AND `method` = ? ::`notaryType` GROUP BY `names`.`uid`) as p")) {
             query.setInt(1, getId());
-            query.setString(2, AssuranceType.FACE_TO_FACE.getDescription());
+            query.setEnum(2, AssuranceType.FACE_TO_FACE);
 
             GigiResultSet rs = query.executeQuery();
             int points = 0;
@@ -454,7 +455,7 @@ public class User extends CertificateOwner {
         groups.add(toGrant);
         try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `user_groups` SET `user`=?, `permission`=?::`userGroup`, `grantedby`=?")) {
             ps.setInt(1, getId());
-            ps.setString(2, toGrant.getDatabaseName());
+            ps.setEnum(2, toGrant);
             ps.setInt(3, granter.getId());
             ps.execute();
         }
@@ -467,7 +468,7 @@ public class User extends CertificateOwner {
         groups.remove(toRevoke);
         try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `user_groups` SET `deleted`=CURRENT_TIMESTAMP, `revokedby`=? WHERE `deleted` IS NULL AND `permission`=?::`userGroup` AND `user`=?")) {
             ps.setInt(1, revoker.getId());
-            ps.setString(2, toRevoke.getDatabaseName());
+            ps.setEnum(2, toRevoke);
             ps.setInt(3, getId());
             ps.execute();
         }
diff --git a/src/org/cacert/gigi/email/EmailProvider.java b/src/org/cacert/gigi/email/EmailProvider.java
index d82725d1..a55a1c20 100644
--- a/src/org/cacert/gigi/email/EmailProvider.java
+++ b/src/org/cacert/gigi/email/EmailProvider.java
@@ -161,11 +161,10 @@ public abstract class EmailProvider {
                         continue;
                     }
 
-                    try (GigiPreparedStatement statmt = new GigiPreparedStatement("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`=?, `uid`=?, `type`='fast', `status`=?::`pingState`")) {
+                    try (GigiPreparedStatement statmt = new GigiPreparedStatement("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`=?, `uid`=?, `type`='fast', `status`='success'::`pingState`")) {
                         statmt.setString(1, address);
                         statmt.setString(2, line);
                         statmt.setInt(3, forUid);
-                        statmt.setString(4, "success");
                         statmt.execute();
                     }
 
@@ -178,11 +177,10 @@ public abstract class EmailProvider {
 
             }
         }
-        try (GigiPreparedStatement statmt = new GigiPreparedStatement("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`=?, `uid`=?, `type`='fast', `status`=?::`pingState`")) {
+        try (GigiPreparedStatement statmt = new GigiPreparedStatement("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`=?, `uid`=?, `type`='fast'::`emailPingType`, `status`='failed'::`pingState`")) {
             statmt.setString(1, address);
             statmt.setString(2, "Failed to make a connection to the mail server");
             statmt.setInt(3, forUid);
-            statmt.setString(4, "failed");
             statmt.execute();
         }
         return FAIL;
diff --git a/src/org/cacert/gigi/output/GroupSelector.java b/src/org/cacert/gigi/output/GroupSelector.java
index 49db7050..9d3080dd 100644
--- a/src/org/cacert/gigi/output/GroupSelector.java
+++ b/src/org/cacert/gigi/output/GroupSelector.java
@@ -41,7 +41,7 @@ public class GroupSelector implements Outputable {
         out.println("<select name='" + name + "'>");
         for (Group g : Group.values()) {
             if (mayManage(g)) {
-                out.print("<option value='" + g.getDatabaseName());
+                out.print("<option value='" + g.getDBName());
                 if (g.equals(value)) {
                     out.print(" selected");
                 }
diff --git a/src/org/cacert/gigi/ping/DomainPinger.java b/src/org/cacert/gigi/ping/DomainPinger.java
index 15dd0eb9..c5eb3926 100644
--- a/src/org/cacert/gigi/ping/DomainPinger.java
+++ b/src/org/cacert/gigi/ping/DomainPinger.java
@@ -1,11 +1,21 @@
 package org.cacert.gigi.ping;
 
+import org.cacert.gigi.database.DBEnum;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.dbObjects.CertificateOwner;
 import org.cacert.gigi.dbObjects.Domain;
 
 public abstract class DomainPinger {
 
+    public static enum PingState implements DBEnum {
+        OPEN, SUCCESS, FAILED;
+
+        @Override
+        public String getDBName() {
+            return toString().toLowerCase();
+        }
+    }
+
     public static final String PING_STILL_PENDING = null;
 
     public static final String PING_SUCCEDED = "";
@@ -13,9 +23,10 @@ public abstract class DomainPinger {
     public abstract void ping(Domain domain, String configuration, CertificateOwner target, int confId);
 
     protected static void enterPingResult(int configId, String state, String result, String token) {
+        PingState estate = DomainPinger.PING_STILL_PENDING == state ? PingState.OPEN : DomainPinger.PING_SUCCEDED.equals(state) ? PingState.SUCCESS : PingState.FAILED;
         try (GigiPreparedStatement enterPingResult = new GigiPreparedStatement("INSERT INTO `domainPinglog` SET `configId`=?, `state`=?::`pingState`, `result`=?, `challenge`=?")) {
             enterPingResult.setInt(1, configId);
-            enterPingResult.setString(2, DomainPinger.PING_STILL_PENDING == state ? "open" : DomainPinger.PING_SUCCEDED.equals(state) ? "success" : "failed");
+            enterPingResult.setEnum(2, estate);
             enterPingResult.setString(3, result);
             enterPingResult.setString(4, token);
             enterPingResult.execute();
diff --git a/src/org/cacert/gigi/util/Notary.java b/src/org/cacert/gigi/util/Notary.java
index 996ddf59..e853bd62 100644
--- a/src/org/cacert/gigi/util/Notary.java
+++ b/src/org/cacert/gigi/util/Notary.java
@@ -48,7 +48,7 @@ public class Notary {
         try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `notary` where `to`=? and `from`=? and `method` = ? ::`notaryType` AND `deleted` IS NULL AND `when` > (now() - interval '1 days' * ?)")) {
             ps.setInt(1, target.getId());
             ps.setInt(2, assurer.getId());
-            ps.setString(3, AssuranceType.FACE_TO_FACE.getDescription());
+            ps.setEnum(3, AssuranceType.FACE_TO_FACE);
             ps.setInt(4, LIMIT_DAYS_VERIFICATION);
             GigiResultSet rs = ps.executeQuery();
             return !rs.next();
diff --git a/tests/org/cacert/gigi/TestUserGroupMembership.java b/tests/org/cacert/gigi/TestUserGroupMembership.java
index bd1fd4f7..22135574 100644
--- a/tests/org/cacert/gigi/TestUserGroupMembership.java
+++ b/tests/org/cacert/gigi/TestUserGroupMembership.java
@@ -46,7 +46,7 @@ public class TestUserGroupMembership extends BusinessTest {
             assertTrue(rs.next());
             assertEquals(0, rs.getInt("revokedby"));
             assertEquals(granter.getId(), rs.getInt("grantedby"));
-            assertEquals(ttpGroup.getDatabaseName(), rs.getString("permission"));
+            assertEquals(ttpGroup.getDBName(), rs.getString("permission"));
 
             assertNull(rs.getDate("deleted"));
             assertNotNull(rs.getDate("granted"));
@@ -78,7 +78,7 @@ public class TestUserGroupMembership extends BusinessTest {
             assertTrue(rs.next());
             assertEquals(granter.getId(), rs.getInt("revokedby"));
             assertEquals(granter.getId(), rs.getInt("grantedby"));
-            assertEquals(ttpGroup.getDatabaseName(), rs.getString("permission"));
+            assertEquals(ttpGroup.getDBName(), rs.getString("permission"));
 
             assertNotNull(rs.getDate("deleted"));
             assertNotNull(rs.getDate("granted"));
diff --git a/tests/org/cacert/gigi/pages/account/TestMyDetailsEdit.java b/tests/org/cacert/gigi/pages/account/TestMyDetailsEdit.java
index 38f691ab..83597848 100644
--- a/tests/org/cacert/gigi/pages/account/TestMyDetailsEdit.java
+++ b/tests/org/cacert/gigi/pages/account/TestMyDetailsEdit.java
@@ -115,21 +115,21 @@ public class TestMyDetailsEdit extends ManagedTest {
     public void testModifyUserGroup() throws IOException {
         User user = User.getById(id);
         // test add group
-        assertNull(executeBasicWebInteraction(cookie, MyDetails.PATH, "action=addGroup&groupToModify=" + URLEncoder.encode(Group.LOCATE_AGENT.getDatabaseName(), "UTF-8"), 0));
+        assertNull(executeBasicWebInteraction(cookie, MyDetails.PATH, "action=addGroup&groupToModify=" + URLEncoder.encode(Group.LOCATE_AGENT.getDBName(), "UTF-8"), 0));
 
         user = User.getById(id);
         user.refreshGroups();
         assertTrue(user.isInGroup(Group.LOCATE_AGENT));
 
         // test remove group
-        assertNull(executeBasicWebInteraction(cookie, MyDetails.PATH, "action=removeGroup&groupToModify=" + URLEncoder.encode(Group.LOCATE_AGENT.getDatabaseName(), "UTF-8"), 0));
+        assertNull(executeBasicWebInteraction(cookie, MyDetails.PATH, "action=removeGroup&groupToModify=" + URLEncoder.encode(Group.LOCATE_AGENT.getDBName(), "UTF-8"), 0));
 
         user = User.getById(id);
         user.refreshGroups();
         assertFalse(user.isInGroup(Group.LOCATE_AGENT));
 
         // test add group that only support can add
-        assertNotNull(executeBasicWebInteraction(cookie, MyDetails.PATH, "action=addGroup&groupToModify=" + URLEncoder.encode(Group.SUPPORTER.getDatabaseName(), "UTF-8"), 0));
+        assertNotNull(executeBasicWebInteraction(cookie, MyDetails.PATH, "action=addGroup&groupToModify=" + URLEncoder.encode(Group.SUPPORTER.getDBName(), "UTF-8"), 0));
 
         user = User.getById(id);
         user.refreshGroups();
diff --git a/tests/org/cacert/gigi/pages/admin/TestSEAdminNotificationMail.java b/tests/org/cacert/gigi/pages/admin/TestSEAdminNotificationMail.java
index e0ca8289..f025b0f6 100644
--- a/tests/org/cacert/gigi/pages/admin/TestSEAdminNotificationMail.java
+++ b/tests/org/cacert/gigi/pages/admin/TestSEAdminNotificationMail.java
@@ -64,7 +64,7 @@ public class TestSEAdminNotificationMail extends ClientTest {
 
     @Test
     public void testGrantUserGroup() throws MalformedURLException, IOException {
-        executeBasicWebInteraction(cookie, SupportUserDetailsPage.PATH + targetID + "/", "addGroup&groupToModify=" + URLEncoder.encode(Group.CODESIGNING.getDatabaseName(), "UTF-8"), 0);
+        executeBasicWebInteraction(cookie, SupportUserDetailsPage.PATH + targetID + "/", "addGroup&groupToModify=" + URLEncoder.encode(Group.CODESIGNING.getDBName(), "UTF-8"), 0);
 
         StringWriter sw = new StringWriter();
         PrintWriter pw = new PrintWriter(sw);
@@ -80,7 +80,7 @@ public class TestSEAdminNotificationMail extends ClientTest {
 
     @Test
     public void testRemoveUserGroup() throws MalformedURLException, IOException {
-        executeBasicWebInteraction(cookie, SupportUserDetailsPage.PATH + targetID + "/", "removeGroup&groupToModify=" + URLEncoder.encode(Group.CODESIGNING.getDatabaseName(), "UTF-8"), 0);
+        executeBasicWebInteraction(cookie, SupportUserDetailsPage.PATH + targetID + "/", "removeGroup&groupToModify=" + URLEncoder.encode(Group.CODESIGNING.getDBName(), "UTF-8"), 0);
 
         StringWriter sw = new StringWriter();
         PrintWriter pw = new PrintWriter(sw);
@@ -96,7 +96,7 @@ public class TestSEAdminNotificationMail extends ClientTest {
 
     @Test
     public void testGrantSupporterGroup() throws MalformedURLException, IOException {
-        executeBasicWebInteraction(cookie, SupportUserDetailsPage.PATH + targetID + "/", "addGroup&groupToModify=" + URLEncoder.encode(Group.SUPPORTER.getDatabaseName(), "UTF-8"), 0);
+        executeBasicWebInteraction(cookie, SupportUserDetailsPage.PATH + targetID + "/", "addGroup&groupToModify=" + URLEncoder.encode(Group.SUPPORTER.getDBName(), "UTF-8"), 0);
 
         StringWriter sw = new StringWriter();
         PrintWriter pw = new PrintWriter(sw);
@@ -116,7 +116,7 @@ public class TestSEAdminNotificationMail extends ClientTest {
 
     @Test
     public void testRemoveSupporterGroup() throws MalformedURLException, IOException {
-        executeBasicWebInteraction(cookie, SupportUserDetailsPage.PATH + targetID + "/", "removeGroup&groupToModify=" + URLEncoder.encode(Group.SUPPORTER.getDatabaseName(), "UTF-8"), 0);
+        executeBasicWebInteraction(cookie, SupportUserDetailsPage.PATH + targetID + "/", "removeGroup&groupToModify=" + URLEncoder.encode(Group.SUPPORTER.getDBName(), "UTF-8"), 0);
 
         StringWriter sw = new StringWriter();
         PrintWriter pw = new PrintWriter(sw);
diff --git a/tests/org/cacert/gigi/testUtils/BusinessTest.java b/tests/org/cacert/gigi/testUtils/BusinessTest.java
index db888c03..ad0b1e1f 100644
--- a/tests/org/cacert/gigi/testUtils/BusinessTest.java
+++ b/tests/org/cacert/gigi/testUtils/BusinessTest.java
@@ -167,7 +167,7 @@ public abstract class BusinessTest extends ConfiguredTest {
         supporter = createVerifiedUser();
         try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `user_groups` SET `user`=?, `permission`=?::`userGroup`, `grantedby`=?")) {
             ps.setInt(1, supporter.getId());
-            ps.setString(2, Group.SUPPORTER.getDatabaseName());
+            ps.setString(2, Group.SUPPORTER.getDBName());
             ps.setInt(3, supporter.getId());
             ps.execute();
         }
diff --git a/tests/org/cacert/gigi/testUtils/ManagedTest.java b/tests/org/cacert/gigi/testUtils/ManagedTest.java
index 22da1eab..01ee4c59 100644
--- a/tests/org/cacert/gigi/testUtils/ManagedTest.java
+++ b/tests/org/cacert/gigi/testUtils/ManagedTest.java
@@ -492,7 +492,7 @@ public class ManagedTest extends ConfiguredTest {
         int i = createVerifiedUser("fn", "ln", createUniqueName() + "@email.com", TEST_PASSWORD);
         try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `user_groups` SET `user`=?, `permission`=?::`userGroup`, `grantedby`=?")) {
             ps.setInt(1, i);
-            ps.setString(2, Group.SUPPORTER.getDatabaseName());
+            ps.setString(2, Group.SUPPORTER.getDBName());
             ps.setInt(3, i);
             ps.execute();
         }
diff --git a/util-testing/org/cacert/gigi/pages/Manager.java b/util-testing/org/cacert/gigi/pages/Manager.java
index ec709a17..e1af384a 100644
--- a/util-testing/org/cacert/gigi/pages/Manager.java
+++ b/util-testing/org/cacert/gigi/pages/Manager.java
@@ -105,7 +105,7 @@ public class Manager extends Page {
             if ( !u.isInGroup(Group.SUPPORTER)) {
                 try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `user_groups` SET `user`=?, `permission`=?::`userGroup`, `grantedby`=?")) {
                     ps.setInt(1, u.getId());
-                    ps.setString(2, Group.SUPPORTER.getDatabaseName());
+                    ps.setString(2, Group.SUPPORTER.getDBName());
                     ps.setInt(3, u.getId());
                     ps.execute();
                 }
-- 
2.39.2