fix: SQL change database call pattern
authorFelix Dörre <felix@dogcraft.de>
Thu, 19 Nov 2015 13:24:44 +0000 (14:24 +0100)
committerFelix Dörre <felix@dogcraft.de>
Thu, 19 Nov 2015 13:33:54 +0000 (14:33 +0100)
30 files changed:
src/org/cacert/gigi/Gigi.java
src/org/cacert/gigi/database/DatabaseConnection.java
src/org/cacert/gigi/database/GigiPreparedStatement.java
src/org/cacert/gigi/dbObjects/CACertificate.java
src/org/cacert/gigi/dbObjects/CATS.java
src/org/cacert/gigi/dbObjects/Certificate.java
src/org/cacert/gigi/dbObjects/CertificateOwner.java
src/org/cacert/gigi/dbObjects/CertificateProfile.java
src/org/cacert/gigi/dbObjects/Domain.java
src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java
src/org/cacert/gigi/dbObjects/EmailAddress.java
src/org/cacert/gigi/dbObjects/Group.java
src/org/cacert/gigi/dbObjects/Job.java
src/org/cacert/gigi/dbObjects/Organisation.java
src/org/cacert/gigi/dbObjects/SupportedUser.java
src/org/cacert/gigi/dbObjects/User.java
src/org/cacert/gigi/email/EmailProvider.java
src/org/cacert/gigi/pages/LoginPage.java
src/org/cacert/gigi/pages/main/Signup.java
src/org/cacert/gigi/pages/wot/AssurePage.java
src/org/cacert/gigi/ping/DomainPinger.java
src/org/cacert/gigi/ping/PingerDaemon.java
src/org/cacert/gigi/util/Notary.java
tests/org/cacert/gigi/TestUserGroupMembership.java
tests/org/cacert/gigi/testUtils/ManagedTest.java
tests/org/cacert/gigi/testUtils/PingTest.java
tests/org/cacert/gigi/util/TestNotary.java
tests/org/cacert/gigi/util/TestPasswordMigration.java
util-testing/org/cacert/gigi/pages/Manager.java
util-testing/org/cacert/gigi/util/SimpleSigner.java

index b7605e876373d4d1637375a29acda736559da3ed..209175ab157fe4b8b3ed569f7c246579423341c8 100644 (file)
@@ -156,6 +156,13 @@ public class Gigi extends HttpServlet {
             putPage(UserTrainings.SUPPORT_PATH, new UserTrainings(true), null);
 
             putPage(PasswordResetPage.PATH, new PasswordResetPage(), null);
+            putPage("/dbs", new Page("Database set") {
+
+                @Override
+                public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
+                    DatabaseConnection.getInstance().lockedStatements(resp.getWriter());
+                }
+            }, "Database set");
 
             if (testing) {
                 try {
index b8c09e5ab76a7d37a30b0a1ea9db171b24786abd..bccae86faebb950f1ea82da64aca0e90db8d58b2 100644 (file)
@@ -2,12 +2,16 @@ package org.cacert.gigi.database;
 
 import java.io.IOException;
 import java.io.InputStream;
+import java.io.PrintWriter;
 import java.sql.Connection;
 import java.sql.DriverManager;
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.HashMap;
+import java.util.HashSet;
+import java.util.Map.Entry;
 import java.util.Properties;
 import java.util.StringJoiner;
 import java.util.regex.Matcher;
@@ -23,7 +27,9 @@ public class DatabaseConnection {
 
     private Connection c;
 
-    private HashMap<String, GigiPreparedStatement> statements = new HashMap<String, GigiPreparedStatement>();
+    private HashMap<String, PreparedStatement> statements = new HashMap<String, PreparedStatement>();
+
+    HashSet<PreparedStatement> underUse = new HashSet<>();
 
     private static Properties credentials;
 
@@ -48,34 +54,44 @@ public class DatabaseConnection {
         }
     }
 
-    public GigiPreparedStatement prepare(String query) {
+    protected synchronized PreparedStatement prepareInternal(String query) throws SQLException {
         ensureOpen();
         query = preprocessQuery(query);
-        GigiPreparedStatement statement = statements.get(query);
-        if (statement == null) {
-            try {
-                statement = new GigiPreparedStatement(c.prepareStatement(query, query.startsWith("SELECT ") ? Statement.NO_GENERATED_KEYS : Statement.RETURN_GENERATED_KEYS));
-            } catch (SQLException e) {
-                throw new Error(e);
+        PreparedStatement statement = statements.get(query);
+        if (statement != null) {
+            if (underUse.add(statement)) {
+                return statement;
+            } else {
+                throw new Error("Statement in Use");
             }
-            statements.put(query, statement);
         }
-        return statement;
+        statement = c.prepareStatement(query, query.startsWith("SELECT ") ? Statement.NO_GENERATED_KEYS : Statement.RETURN_GENERATED_KEYS);
+        statements.put(query, statement);
+        if (underUse.add(statement)) {
+            return statement;
+        } else {
+            throw new Error("Statement in Use");
+        }
     }
 
-    public GigiPreparedStatement prepareScrollable(String query) {
+    protected synchronized PreparedStatement prepareInternalScrollable(String query) throws SQLException {
         ensureOpen();
         query = preprocessQuery(query);
-        GigiPreparedStatement statement = statements.get(query);
-        if (statement == null) {
-            try {
-                statement = new GigiPreparedStatement(c.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
-            } catch (SQLException e) {
-                throw new Error(e);
+        PreparedStatement statement = statements.get("__SCROLLABLE__! " + query);
+        if (statement != null) {
+            if (underUse.add(statement)) {
+                return statement;
+            } else {
+                throw new Error("Statement in Use");
             }
-            statements.put(query, statement);
         }
-        return statement;
+        statement = c.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
+        statements.put("__SCROLLABLE__! " + query, statement);
+        if (underUse.add(statement)) {
+            return statement;
+        } else {
+            throw new Error("Statement in Use");
+        }
     }
 
     private long lastAction = System.currentTimeMillis();
@@ -95,16 +111,17 @@ public class DatabaseConnection {
         lastAction = System.currentTimeMillis();
     }
 
-    private static ThreadLocal<DatabaseConnection> instances = new ThreadLocal<DatabaseConnection>() {
-
-        @Override
-        protected DatabaseConnection initialValue() {
-            return new DatabaseConnection();
-        }
-    };
+    private static DatabaseConnection instance;
 
     public static DatabaseConnection getInstance() {
-        return instances.get();
+        if (instance == null) {
+            synchronized (DatabaseConnection.class) {
+                if (instance == null) {
+                    instance = new DatabaseConnection();
+                }
+            }
+        }
+        return instance;
     }
 
     public static boolean isInited() {
@@ -116,10 +133,12 @@ public class DatabaseConnection {
             throw new Error("Re-initiaizing is forbidden.");
         }
         credentials = conf;
-        GigiResultSet rs = getInstance().prepare("SELECT version FROM \"schemeVersion\" ORDER BY version DESC LIMIT 1;").executeQuery();
         int version = 0;
-        if (rs.next()) {
-            version = rs.getInt(1);
+        try (GigiPreparedStatement gigiPreparedStatement = new GigiPreparedStatement("SELECT version FROM \"schemeVersion\" ORDER BY version DESC LIMIT 1;")) {
+            GigiResultSet rs = gigiPreparedStatement.executeQuery();
+            if (rs.next()) {
+                version = rs.getInt(1);
+            }
         }
         if (version == CURRENT_SCHEMA_VERSION) {
             return; // Good to go
@@ -211,4 +230,20 @@ public class DatabaseConnection {
         }
         return ident;
     }
+
+    protected synchronized void returnStatement(PreparedStatement target) {
+        underUse.remove(target);
+    }
+
+    public void lockedStatements(PrintWriter writer) {
+        writer.println(underUse.size());
+        for (PreparedStatement ps : underUse) {
+            for (Entry<String, PreparedStatement> e : statements.entrySet()) {
+                if (e.getValue() == ps) {
+                    writer.println("<br/>");
+                    writer.println(e.getKey());
+                }
+            }
+        }
+    }
 }
index 6d967648e9695a0c0e8781c95888b571904a4bed..411b1fd5fb840056fa5000acc58d1d8be7a164aa 100644 (file)
@@ -6,17 +6,35 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Timestamp;
 
-public class GigiPreparedStatement {
+public class GigiPreparedStatement implements AutoCloseable {
 
     private PreparedStatement target;
 
-    public GigiPreparedStatement(PreparedStatement preparedStatement) {
+    private GigiResultSet rs;
+
+    protected GigiPreparedStatement(PreparedStatement preparedStatement) {
         target = preparedStatement;
     }
 
+    public GigiPreparedStatement(String stmt) {
+        this(stmt, false);
+    }
+
+    public GigiPreparedStatement(String stmt, boolean scroll) {
+        try {
+            if (scroll) {
+                target = DatabaseConnection.getInstance().prepareInternalScrollable(stmt);
+            } else {
+                target = DatabaseConnection.getInstance().prepareInternal(stmt);
+            }
+        } catch (SQLException e) {
+            throw new Error(e);
+        }
+    }
+
     public GigiResultSet executeQuery() {
         try {
-            return new GigiResultSet(target.executeQuery());
+            return rs = new GigiResultSet(target.executeQuery());
         } catch (SQLException e) {
             handleSQL(e);
             throw new Error(e);
@@ -107,4 +125,16 @@ public class GigiPreparedStatement {
 
     }
 
+    @Override
+    public void close() {
+        GigiResultSet r = rs;
+        if (r != null) {
+            r.close();
+        }
+        PreparedStatement tg = target;
+        target = null;
+        DatabaseConnection.getInstance().returnStatement(tg);
+
+    }
+
 }
index cc5bcd8935c43fdcf795a8649839503e71fcc0d9..41401b6ddab883b84e10180f766bf1492882d212 100644 (file)
@@ -13,7 +13,6 @@ import java.util.HashMap;
 
 import javax.security.auth.x500.X500Principal;
 
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.util.ServerConstants;
@@ -32,17 +31,19 @@ public class CACertificate implements IdCachable {
 
     private CACertificate(int id) {
         this.id = id;
-        GigiPreparedStatement conn = DatabaseConnection.getInstance().prepare("SELECT `keyname`, `parentRoot`, `link` FROM `cacerts` WHERE `id`=?");
-        conn.setInt(1, id);
-        GigiResultSet res = conn.executeQuery();
-        if ( !res.next()) {
-            throw new IllegalArgumentException();
-        }
-        keyname = res.getString("keyname");
-        link = res.getString("link");
-        int parentRoot = res.getInt("parentRoot");
-        if (res.next()) {
-            throw new RuntimeException("DB is broken");
+        int parentRoot;
+        try (GigiPreparedStatement conn = new GigiPreparedStatement("SELECT `keyname`, `parentRoot`, `link` FROM `cacerts` WHERE `id`=?")) {
+            conn.setInt(1, id);
+            GigiResultSet res = conn.executeQuery();
+            if ( !res.next()) {
+                throw new IllegalArgumentException();
+            }
+            keyname = res.getString("keyname");
+            link = res.getString("link");
+            parentRoot = res.getInt("parentRoot");
+            if (res.next()) {
+                throw new RuntimeException("DB is broken");
+            }
         }
         if (parentRoot == id) {
             parent = this;
@@ -110,39 +111,42 @@ public class CACertificate implements IdCachable {
 
                 X500Principal subj = toInsert.getSubjectX500Principal();
                 boolean self = toInsert.getIssuerX500Principal().equals(subj);
-                GigiPreparedStatement q = DatabaseConnection.getInstance().prepare("SELECT `id`, `parentRoot` FROM `cacerts` WHERE `keyname`=?");
-                q.setString(1, names.get(subj));
-                GigiResultSet res = q.executeQuery();
-                int id;
-                if (res.next()) {
-                    id = res.getInt("id");
-                    if (res.getInt("parentRoot") != (self ? id : inserted.get(toInsert.getIssuerX500Principal()))) {
-                        throw new Error("Invalid DB structure: " + subj + "->" + inserted.get(toInsert.getIssuerX500Principal()) + " vs " + res.getInt("parentRoot"));
-                    }
-                } else {
-                    String link;
-                    String keyname = names.get(subj);
-                    if ( !keyname.contains("_")) {
-                        link = "https://g2.crt." + ServerConstants.getSuffix() + "/g2/" + keyname + ".crt";
+                try (GigiPreparedStatement q = new GigiPreparedStatement("SELECT `id`, `parentRoot` FROM `cacerts` WHERE `keyname`=?")) {
+                    q.setString(1, names.get(subj));
+                    GigiResultSet res = q.executeQuery();
+                    int id;
+                    if (res.next()) {
+                        id = res.getInt("id");
+                        if (res.getInt("parentRoot") != (self ? id : inserted.get(toInsert.getIssuerX500Principal()))) {
+                            throw new Error("Invalid DB structure: " + subj + "->" + inserted.get(toInsert.getIssuerX500Principal()) + " vs " + res.getInt("parentRoot"));
+                        }
                     } else {
-                        String[] parts = keyname.split("_");
-                        link = "https://g2.crt." + ServerConstants.getSuffix() + "/g2/" + parts[1] + "/" + parts[0] + "-" + parts[2] + ".crt";
-
-                    }
-                    GigiPreparedStatement q2 = DatabaseConnection.getInstance().prepare("INSERT INTO `cacerts` SET `parentRoot`=?, `keyname`=?, `link`=?");
-                    q2.setInt(1, self ? 0 : inserted.get(toInsert.getIssuerX500Principal()));
-                    q2.setString(2, keyname);
-                    q2.setString(3, link);
-                    q2.execute();
-                    id = q2.lastInsertId();
-                    if (self) {
-                        GigiPreparedStatement q3 = DatabaseConnection.getInstance().prepare("UPDATE `cacerts` SET `parentRoot`=?, `id`=?");
-                        q3.setInt(1, id);
-                        q3.setInt(2, id);
-                        q3.execute();
+                        String link;
+                        String keyname = names.get(subj);
+                        if ( !keyname.contains("_")) {
+                            link = "https://g2.crt." + ServerConstants.getSuffix() + "/g2/" + keyname + ".crt";
+                        } else {
+                            String[] parts = keyname.split("_");
+                            link = "https://g2.crt." + ServerConstants.getSuffix() + "/g2/" + parts[1] + "/" + parts[0] + "-" + parts[2] + ".crt";
+
+                        }
+                        try (GigiPreparedStatement q2 = new GigiPreparedStatement("INSERT INTO `cacerts` SET `parentRoot`=?, `keyname`=?, `link`=?")) {
+                            q2.setInt(1, self ? 0 : inserted.get(toInsert.getIssuerX500Principal()));
+                            q2.setString(2, keyname);
+                            q2.setString(3, link);
+                            q2.execute();
+                            id = q2.lastInsertId();
+                        }
+                        if (self) {
+                            try (GigiPreparedStatement q3 = new GigiPreparedStatement("UPDATE `cacerts` SET `parentRoot`=?, `id`=?")) {
+                                q3.setInt(1, id);
+                                q3.setInt(2, id);
+                                q3.execute();
+                            }
+                        }
                     }
+                    inserted.put(subj, id);
                 }
-                inserted.put(subj, id);
             }
         }
     }
index fd715ca513ddc2fa56078adb7210dfa9863277ee..0bda2e6770205e0cd5bde09f7974356c913fff25 100644 (file)
@@ -4,7 +4,6 @@ import java.sql.Timestamp;
 import java.util.Date;
 import java.util.HashMap;
 
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 
@@ -21,9 +20,11 @@ public class CATS {
     }
 
     static {
-        GigiResultSet res = DatabaseConnection.getInstance().prepare("SELECT `id`, `type_text` FROM `cats_type`").executeQuery();
-        while (res.next()) {
-            names.put(res.getString(2), res.getInt(1));
+        try (GigiPreparedStatement st = new GigiPreparedStatement("SELECT `id`, `type_text` FROM `cats_type`")) {
+            GigiResultSet res = st.executeQuery();
+            while (res.next()) {
+                names.put(res.getString(2), res.getInt(1));
+            }
         }
         ASSURER_CHALLANGE_ID = getID(ASSURER_CHALLANGE_NAME);
     }
@@ -31,20 +32,22 @@ public class CATS {
     public static synchronized int getID(String name) {
         Integer i = names.get(name);
         if (i == null) {
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `cats_type` SET `type_text`=?");
-            ps.setString(1, name);
-            ps.execute();
-            i = ps.lastInsertId();
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `cats_type` SET `type_text`=?")) {
+                ps.setString(1, name);
+                ps.execute();
+                i = ps.lastInsertId();
+            }
             names.put(name, i);
         }
         return i;
     }
 
     public static void enterResult(User user, String testType, Date passDate) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `cats_passed` SET `user_id`=?, `variant_id`=?, `pass_date`=?");
-        ps.setInt(1, user.getId());
-        ps.setInt(2, getID(testType));
-        ps.setTimestamp(3, new Timestamp(passDate.getTime()));
-        ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `cats_passed` SET `user_id`=?, `variant_id`=?, `pass_date`=?")) {
+            ps.setInt(1, user.getId());
+            ps.setInt(2, getID(testType));
+            ps.setTimestamp(3, new Timestamp(passDate.getTime()));
+            ps.execute();
+        }
     }
 }
index f355e67abe7cc72e3e1b6f678e0ebdef2baa0dbc..8e66c7f30c3d98c3b123ef9f6f5c73114aa95905 100644 (file)
@@ -17,7 +17,6 @@ import java.util.List;
 import java.util.Map.Entry;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.util.KeyStorage;
@@ -150,39 +149,42 @@ public class Certificate implements IdCachable {
         this.sans = Arrays.asList(sans);
         synchronized (Certificate.class) {
 
-            GigiPreparedStatement inserter = DatabaseConnection.getInstance().prepare("INSERT INTO certs SET md=?::`mdType`, csr_type=?::`csrType`, crt_name='', memid=?, profile=?");
-            inserter.setString(1, md.toString().toLowerCase());
-            inserter.setString(2, csrType.toString());
-            inserter.setInt(3, owner.getId());
-            inserter.setInt(4, profile.getId());
-            inserter.execute();
-            id = inserter.lastInsertId();
-
-            GigiPreparedStatement san = DatabaseConnection.getInstance().prepare("INSERT INTO `subjectAlternativeNames` SET `certId`=?, contents=?, type=?::`SANType`");
-            for (SubjectAlternateName subjectAlternateName : sans) {
-                san.setInt(1, id);
-                san.setString(2, subjectAlternateName.getName());
-                san.setString(3, subjectAlternateName.getType().getOpensslName());
-                san.execute();
+            try (GigiPreparedStatement inserter = new GigiPreparedStatement("INSERT INTO certs SET md=?::`mdType`, csr_type=?::`csrType`, crt_name='', memid=?, profile=?")) {
+                inserter.setString(1, md.toString().toLowerCase());
+                inserter.setString(2, csrType.toString());
+                inserter.setInt(3, owner.getId());
+                inserter.setInt(4, profile.getId());
+                inserter.execute();
+                id = inserter.lastInsertId();
             }
 
-            GigiPreparedStatement insertAVA = DatabaseConnection.getInstance().prepare("INSERT INTO `certAvas` SET `certId`=?, name=?, value=?");
-            insertAVA.setInt(1, id);
-            for (Entry<String, String> e : dn.entrySet()) {
-                insertAVA.setString(2, e.getKey());
-                insertAVA.setString(3, e.getValue());
-                insertAVA.execute();
+            try (GigiPreparedStatement san = new GigiPreparedStatement("INSERT INTO `subjectAlternativeNames` SET `certId`=?, contents=?, type=?::`SANType`")) {
+                for (SubjectAlternateName subjectAlternateName : sans) {
+                    san.setInt(1, id);
+                    san.setString(2, subjectAlternateName.getName());
+                    san.setString(3, subjectAlternateName.getType().getOpensslName());
+                    san.execute();
+                }
+            }
+
+            try (GigiPreparedStatement insertAVA = new GigiPreparedStatement("INSERT INTO `certAvas` SET `certId`=?, name=?, value=?")) {
+                insertAVA.setInt(1, id);
+                for (Entry<String, String> e : dn.entrySet()) {
+                    insertAVA.setString(2, e.getKey());
+                    insertAVA.setString(3, e.getValue());
+                    insertAVA.execute();
+                }
             }
             File csrFile = KeyStorage.locateCsr(id);
             csrName = csrFile.getPath();
             try (FileOutputStream fos = new FileOutputStream(csrFile)) {
                 fos.write(csr.getBytes("UTF-8"));
             }
-
-            GigiPreparedStatement updater = DatabaseConnection.getInstance().prepare("UPDATE `certs` SET `csr_name`=? WHERE id=?");
-            updater.setString(1, csrName);
-            updater.setInt(2, id);
-            updater.execute();
+            try (GigiPreparedStatement updater = new GigiPreparedStatement("UPDATE `certs` SET `csr_name`=? WHERE id=?")) {
+                updater.setString(1, csrName);
+                updater.setInt(2, id);
+                updater.execute();
+            }
 
             cache.put(this);
         }
@@ -198,16 +200,14 @@ public class Certificate implements IdCachable {
         profile = CertificateProfile.getById(rs.getInt("profile"));
         this.serial = rs.getString("serial");
 
-        GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("SELECT `contents`, `type` FROM `subjectAlternativeNames` WHERE `certId`=?");
-        ps2.setInt(1, id);
-        GigiResultSet rs2 = ps2.executeQuery();
-        sans = new LinkedList<>();
-        while (rs2.next()) {
-            sans.add(new SubjectAlternateName(SANType.valueOf(rs2.getString("type").toUpperCase()), rs2.getString("contents")));
+        try (GigiPreparedStatement ps2 = new GigiPreparedStatement("SELECT `contents`, `type` FROM `subjectAlternativeNames` WHERE `certId`=?")) {
+            ps2.setInt(1, id);
+            GigiResultSet rs2 = ps2.executeQuery();
+            sans = new LinkedList<>();
+            while (rs2.next()) {
+                sans.add(new SubjectAlternateName(SANType.valueOf(rs2.getString("type").toUpperCase()), rs2.getString("contents")));
+            }
         }
-        rs2.close();
-
-        rs.close();
     }
 
     public enum CertificateStatus {
@@ -238,23 +238,24 @@ public class Certificate implements IdCachable {
     }
 
     public synchronized CertificateStatus getStatus() {
-        GigiPreparedStatement searcher = DatabaseConnection.getInstance().prepare("SELECT crt_name, created, revoked, serial, caid FROM certs WHERE id=?");
-        searcher.setInt(1, id);
-        GigiResultSet rs = searcher.executeQuery();
-        if ( !rs.next()) {
-            throw new IllegalStateException("Certificate not in Database");
-        }
+        try (GigiPreparedStatement searcher = new GigiPreparedStatement("SELECT crt_name, created, revoked, serial, caid FROM certs WHERE id=?")) {
+            searcher.setInt(1, id);
+            GigiResultSet rs = searcher.executeQuery();
+            if ( !rs.next()) {
+                throw new IllegalStateException("Certificate not in Database");
+            }
 
-        crtName = rs.getString(1);
-        serial = rs.getString(4);
-        if (rs.getTimestamp(2) == null) {
-            return CertificateStatus.DRAFT;
-        }
-        ca = CACertificate.getById(rs.getInt("caid"));
-        if (rs.getTimestamp(2) != null && rs.getTimestamp(3) == null) {
-            return CertificateStatus.ISSUED;
+            crtName = rs.getString(1);
+            serial = rs.getString(4);
+            if (rs.getTimestamp(2) == null) {
+                return CertificateStatus.DRAFT;
+            }
+            ca = CACertificate.getById(rs.getInt("caid"));
+            if (rs.getTimestamp(2) != null && rs.getTimestamp(3) == null) {
+                return CertificateStatus.ISSUED;
+            }
+            return CertificateStatus.REVOKED;
         }
-        return CertificateStatus.REVOKED;
     }
 
     /**
@@ -355,20 +356,21 @@ public class Certificate implements IdCachable {
             return null;
         }
         String concat = "string_agg(concat('/', `name`, '=', REPLACE(REPLACE(value, '\\\\', '\\\\\\\\'), '/', '\\\\/')), '')";
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT certs.id, " + concat + " as `subject`, `md`, `csr_name`, `crt_name`,`memid`, `profile`, `certs`.`serial` FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=`certs`.`id` WHERE `serial`=? GROUP BY `certs`.`id`");
-        ps.setString(1, serial);
-        GigiResultSet rs = ps.executeQuery();
-        if ( !rs.next()) {
-            return null;
-        }
-        int id = rs.getInt(1);
-        Certificate c1 = cache.get(id);
-        if (c1 != null) {
-            return c1;
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + concat + " as `subject`, `md`, `csr_name`, `crt_name`,`memid`, `profile`, `certs`.`serial` FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=`certs`.`id` WHERE `serial`=? GROUP BY `certs`.`id`")) {
+            ps.setString(1, serial);
+            GigiResultSet rs = ps.executeQuery();
+            if ( !rs.next()) {
+                return null;
+            }
+            int id = rs.getInt(1);
+            Certificate c1 = cache.get(id);
+            if (c1 != null) {
+                return c1;
+            }
+            Certificate certificate = new Certificate(rs);
+            cache.put(certificate);
+            return certificate;
         }
-        Certificate certificate = new Certificate(rs);
-        cache.put(certificate);
-        return certificate;
     }
 
     private static ObjectCache<Certificate> cache = new ObjectCache<>();
@@ -381,16 +383,17 @@ public class Certificate implements IdCachable {
 
         try {
             String concat = "string_agg(concat('/', `name`, '=', REPLACE(REPLACE(value, '\\\\', '\\\\\\\\'), '/', '\\\\/')), '')";
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT certs.id, " + concat + " as subject, md, csr_name, crt_name,memid, profile, certs.serial FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=certs.id WHERE certs.id=? GROUP BY certs.id");
-            ps.setInt(1, id);
-            GigiResultSet rs = ps.executeQuery();
-            if ( !rs.next()) {
-                return null;
-            }
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + concat + " as subject, md, csr_name, crt_name,memid, profile, certs.serial FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=certs.id WHERE certs.id=? GROUP BY certs.id")) {
+                ps.setInt(1, id);
+                GigiResultSet rs = ps.executeQuery();
+                if ( !rs.next()) {
+                    return null;
+                }
 
-            Certificate c = new Certificate(rs);
-            cache.put(c);
-            return c;
+                Certificate c = new Certificate(rs);
+                cache.put(c);
+                return c;
+            }
         } catch (IllegalArgumentException e) {
 
         }
@@ -421,11 +424,12 @@ public class Certificate implements IdCachable {
 
     public java.util.Date getRevocationDate() {
         if (getStatus() == CertificateStatus.REVOKED) {
-            GigiPreparedStatement prep = DatabaseConnection.getInstance().prepare("SELECT revoked FROM certs WHERE id=?");
-            prep.setInt(1, getId());
-            GigiResultSet res = prep.executeQuery();
-            if (res.next()) {
-                return new java.util.Date(res.getDate("revoked").getTime());
+            try (GigiPreparedStatement prep = new GigiPreparedStatement("SELECT revoked FROM certs WHERE id=?")) {
+                prep.setInt(1, getId());
+                GigiResultSet res = prep.executeQuery();
+                if (res.next()) {
+                    return new java.util.Date(res.getDate("revoked").getTime());
+                }
             }
         }
         return null;
index 2adda5ee4fea22130a37503464ad44e69c9c71c2..8ba04feece6b42e7bfb4445b761be1469208876d 100644 (file)
@@ -3,7 +3,6 @@ package org.cacert.gigi.dbObjects;
 import java.util.LinkedList;
 import java.util.List;
 
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 
@@ -18,9 +17,10 @@ public abstract class CertificateOwner implements IdCachable {
     }
 
     protected CertificateOwner() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `certOwners` DEFAULT VALUES");
-        ps.execute();
-        id = ps.lastInsertId();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `certOwners` DEFAULT VALUES")) {
+            ps.execute();
+            id = ps.lastInsertId();
+        }
         myCache.put(this);
     }
 
@@ -31,18 +31,19 @@ public abstract class CertificateOwner implements IdCachable {
     public static synchronized CertificateOwner getById(int id) {
         CertificateOwner u = myCache.get(id);
         if (u == null) {
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT *, `users`.`id` AS uid, `organisations`.`id` AS oid FROM `certOwners` LEFT JOIN `users` ON `users`.`id`=`certOwners`.`id` LEFT JOIN `organisations` ON `organisations`.`id` = `certOwners`.`id` WHERE `certOwners`.`id`=? AND `deleted` is null");
-            ps.setInt(1, id);
-            try (GigiResultSet rs = ps.executeQuery()) {
-                if ( !rs.next()) {
-                    return null;
-                }
-                if (rs.getString("uid") != null) {
-                    myCache.put(u = new User(rs));
-                } else if (rs.getString("oid") != null) {
-                    myCache.put(u = new Organisation(rs));
-                } else {
-                    System.err.print("Malformed cert owner: " + id);
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT *, `users`.`id` AS uid, `organisations`.`id` AS oid FROM `certOwners` LEFT JOIN `users` ON `users`.`id`=`certOwners`.`id` LEFT JOIN `organisations` ON `organisations`.`id` = `certOwners`.`id` WHERE `certOwners`.`id`=? AND `deleted` is null")) {
+                ps.setInt(1, id);
+                try (GigiResultSet rs = ps.executeQuery()) {
+                    if ( !rs.next()) {
+                        return null;
+                    }
+                    if (rs.getString("uid") != null) {
+                        myCache.put(u = new User(rs));
+                    } else if (rs.getString("oid") != null) {
+                        myCache.put(u = new Organisation(rs));
+                    } else {
+                        System.err.print("Malformed cert owner: " + id);
+                    }
                 }
             }
         }
@@ -50,30 +51,26 @@ public abstract class CertificateOwner implements IdCachable {
     }
 
     public Domain[] getDomains() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id` FROM `domains` WHERE `memid`=? AND `deleted` IS NULL");
-        ps.setInt(1, getId());
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `id` FROM `domains` WHERE `memid`=? AND `deleted` IS NULL")) {
+            ps.setInt(1, getId());
 
-        try (GigiResultSet rs = ps.executeQuery()) {
-            LinkedList<Domain> data = new LinkedList<Domain>();
+            try (GigiResultSet rs = ps.executeQuery()) {
+                LinkedList<Domain> data = new LinkedList<Domain>();
 
-            while (rs.next()) {
-                data.add(Domain.getById(rs.getInt(1)));
-            }
+                while (rs.next()) {
+                    data.add(Domain.getById(rs.getInt(1)));
+                }
 
-            return data.toArray(new Domain[0]);
+                return data.toArray(new Domain[0]);
+            }
         }
     }
 
     public Certificate[] getCertificates(boolean includeRevoked) {
-        GigiPreparedStatement ps;
-        if (includeRevoked) {
-            ps = DatabaseConnection.getInstance().prepare("SELECT id FROM certs WHERE memid=?");
-        } else {
-            ps = DatabaseConnection.getInstance().prepare("SELECT id FROM certs WHERE memid=? AND revoked IS NULL");
-        }
-        ps.setInt(1, getId());
+        try (GigiPreparedStatement ps = new GigiPreparedStatement(includeRevoked ? "SELECT id FROM certs WHERE memid=?" : "SELECT id FROM certs WHERE memid=? AND revoked IS NULL")) {
+            ps.setInt(1, getId());
 
-        try (GigiResultSet rs = ps.executeQuery()) {
+            GigiResultSet rs = ps.executeQuery();
             LinkedList<Certificate> data = new LinkedList<Certificate>();
 
             while (rs.next()) {
@@ -98,32 +95,34 @@ public abstract class CertificateOwner implements IdCachable {
     public abstract boolean isValidEmail(String email);
 
     public void delete() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `certOwners` SET `deleted`=NOW() WHERE `id`=?");
-        ps.setInt(1, getId());
-        ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `certOwners` SET `deleted`=NOW() WHERE `id`=?")) {
+            ps.setInt(1, getId());
+            ps.execute();
+        }
         myCache.remove(this);
     }
 
     public String[] getAdminLog() {
-        GigiPreparedStatement prep = DatabaseConnection.getInstance().prepare("SELECT `when`, type, information FROM `adminLog` WHERE uid=? ORDER BY `when` ASC");
-        prep.setInt(1, getId());
-        GigiResultSet res = prep.executeQuery();
-        List<String> entries = new LinkedList<String>();
+        try (GigiPreparedStatement prep = new GigiPreparedStatement("SELECT `when`, type, information FROM `adminLog` WHERE uid=? ORDER BY `when` ASC")) {
+            prep.setInt(1, getId());
+            GigiResultSet res = prep.executeQuery();
+            List<String> entries = new LinkedList<String>();
 
-        while (res.next()) {
-            entries.add(res.getString(2) + " (" + res.getString(3) + ")");
+            while (res.next()) {
+                entries.add(res.getString(2) + " (" + res.getString(3) + ")");
+            }
+            return entries.toArray(new String[0]);
         }
-
-        return entries.toArray(new String[0]);
     }
 
     public static CertificateOwner getByEnabledSerial(String serial) {
-        GigiPreparedStatement prep = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `certs` WHERE serial=? AND `disablelogin`='0' AND `revoked` is NULL");
-        prep.setString(1, serial.toLowerCase());
-        GigiResultSet res = prep.executeQuery();
-        if (res.next()) {
-            return getById(res.getInt(1));
+        try (GigiPreparedStatement prep = new GigiPreparedStatement("SELECT `memid` FROM `certs` WHERE serial=? AND `disablelogin`='0' AND `revoked` is NULL")) {
+            prep.setString(1, serial.toLowerCase());
+            GigiResultSet res = prep.executeQuery();
+            if (res.next()) {
+                return getById(res.getInt(1));
+            }
+            return null;
         }
-        return null;
     }
 }
index 87f55f7441bc6eaa9ce000ffd95152585741ea89..6f57b3ec0a7d1a848841e60b1c099d425ef50552 100644 (file)
@@ -10,7 +10,6 @@ import java.util.List;
 import java.util.Map;
 import java.util.Properties;
 
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 
@@ -190,39 +189,41 @@ public class CertificateProfile implements IdCachable {
             }
 
             String[] parts = f.getName().split("\\.")[0].split("-", 2);
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `keyname`, `include`, `requires`, `name` FROM `profiles` WHERE `id`=?");
-            ps.setInt(1, Integer.parseInt(parts[0]));
-            GigiResultSet rs = ps.executeQuery();
-
-            if (rs.next()) {
-                if ( !rs.getString("keyname").equals(parts[1])) {
-                    throw new Error("Config error. Certificate Profile mismatch");
-                }
-                if ( !rs.getString("include").equals(p.getProperty("include"))) {
-                    throw new Error("Config error. Certificate Profile mismatch");
-                }
-                if ( !rs.getString("requires").equals(p.getProperty("requires", ""))) {
-                    throw new Error("Config error. Certificate Profile mismatch");
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `keyname`, `include`, `requires`, `name` FROM `profiles` WHERE `id`=?")) {
+                ps.setInt(1, Integer.parseInt(parts[0]));
+                GigiResultSet rs = ps.executeQuery();
+
+                if (rs.next()) {
+                    if ( !rs.getString("keyname").equals(parts[1])) {
+                        throw new Error("Config error. Certificate Profile mismatch");
+                    }
+                    if ( !rs.getString("include").equals(p.getProperty("include"))) {
+                        throw new Error("Config error. Certificate Profile mismatch");
+                    }
+                    if ( !rs.getString("requires").equals(p.getProperty("requires", ""))) {
+                        throw new Error("Config error. Certificate Profile mismatch");
+                    }
+                } else {
+                    try (GigiPreparedStatement insert = new GigiPreparedStatement("INSERT INTO `profiles` SET `keyname`=?, `include`=?, `requires`=?, `name`=?, `id`=?")) {
+                        insert.setString(1, parts[1]);
+                        insert.setString(2, p.getProperty("include"));
+                        insert.setString(3, p.getProperty("requires", ""));
+                        insert.setString(4, p.getProperty("name"));
+                        insert.setInt(5, Integer.parseInt(parts[0]));
+                        insert.execute();
+                    }
                 }
-            } else {
-                GigiPreparedStatement insert = DatabaseConnection.getInstance().prepare("INSERT INTO `profiles` SET `keyname`=?, `include`=?, `requires`=?, `name`=?, `id`=?");
-                insert.setString(1, parts[1]);
-                insert.setString(2, p.getProperty("include"));
-                insert.setString(3, p.getProperty("requires", ""));
-                insert.setString(4, p.getProperty("name"));
-                insert.setInt(5, Integer.parseInt(parts[0]));
-                insert.execute();
             }
         }
 
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id`, `keyname`, `name`, `requires`, `include` FROM `profiles`");
-        GigiResultSet rs = ps.executeQuery();
-        while (rs.next()) {
-            CertificateProfile cp = new CertificateProfile(rs.getInt("id"), rs.getString("keyName"), rs.getString("name"), rs.getString("requires"), rs.getString("include"));
-            myId.put(cp.getId(), cp);
-            myName.put(cp.getKeyName(), cp);
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `id`, `keyname`, `name`, `requires`, `include` FROM `profiles`")) {
+            GigiResultSet rs = ps.executeQuery();
+            while (rs.next()) {
+                CertificateProfile cp = new CertificateProfile(rs.getInt("id"), rs.getString("keyName"), rs.getString("name"), rs.getString("requires"), rs.getString("include"));
+                myId.put(cp.getId(), cp);
+                myName.put(cp.getKeyName(), cp);
+            }
         }
-
         byName = Collections.unmodifiableMap(myName);
         byId = Collections.unmodifiableMap(myId);
     }
index 6093f6d85b74b82ce64083cbdadc2563f4ec3371..1d31db61c2dee3dcf95dc122d74f4e5694344271 100644 (file)
@@ -12,7 +12,6 @@ import java.util.Properties;
 import java.util.Set;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.util.PublicSuffixes;
@@ -38,17 +37,17 @@ public class Domain implements IdCachable, Verifyable {
     }
 
     private Domain(int id) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid`, `domain` FROM `domains` WHERE `id`=? AND `deleted` IS NULL");
-        ps.setInt(1, id);
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `memid`, `domain` FROM `domains` WHERE `id`=? AND `deleted` IS NULL")) {
+            ps.setInt(1, id);
 
-        GigiResultSet rs = ps.executeQuery();
-        if ( !rs.next()) {
-            throw new IllegalArgumentException("Invalid domain id " + id);
+            GigiResultSet rs = ps.executeQuery();
+            if ( !rs.next()) {
+                throw new IllegalArgumentException("Invalid domain id " + id);
+            }
+            this.id = id;
+            owner = CertificateOwner.getById(rs.getInt(1));
+            suffix = rs.getString(2);
         }
-        this.id = id;
-        owner = CertificateOwner.getById(rs.getInt(1));
-        suffix = rs.getString(2);
-        rs.close();
     }
 
     public Domain(User actor, CertificateOwner owner, String suffix) throws GigiApiException {
@@ -115,16 +114,17 @@ public class Domain implements IdCachable, Verifyable {
     }
 
     private static void checkInsert(String suffix) throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `domains` WHERE (`domain`=? OR (CONCAT('.', `domain`)=RIGHT(?,LENGTH(`domain`)+1)  OR RIGHT(`domain`,LENGTH(?)+1)=CONCAT('.',?))) AND `deleted` IS NULL");
-        ps.setString(1, suffix);
-        ps.setString(2, suffix);
-        ps.setString(3, suffix);
-        ps.setString(4, suffix);
-        GigiResultSet rs = ps.executeQuery();
-        boolean existed = rs.next();
-        rs.close();
-        if (existed) {
-            throw new GigiApiException("Domain could not be inserted. Domain is already valid.");
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `domains` WHERE (`domain`=? OR (CONCAT('.', `domain`)=RIGHT(?,LENGTH(`domain`)+1)  OR RIGHT(`domain`,LENGTH(?)+1)=CONCAT('.',?))) AND `deleted` IS NULL")) {
+            ps.setString(1, suffix);
+            ps.setString(2, suffix);
+            ps.setString(3, suffix);
+            ps.setString(4, suffix);
+            GigiResultSet rs = ps.executeQuery();
+            boolean existed = rs.next();
+            rs.close();
+            if (existed) {
+                throw new GigiApiException("Domain could not be inserted. Domain is already valid.");
+            }
         }
     }
 
@@ -133,11 +133,12 @@ public class Domain implements IdCachable, Verifyable {
             throw new GigiApiException("already inserted.");
         }
         checkInsert(suffix);
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `domains` SET memid=?, domain=?");
-        ps.setInt(1, owner.getId());
-        ps.setString(2, suffix);
-        ps.execute();
-        id = ps.lastInsertId();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `domains` SET memid=?, domain=?")) {
+            ps.setInt(1, owner.getId());
+            ps.setString(2, suffix);
+            ps.execute();
+            id = ps.lastInsertId();
+        }
         myCache.put(this);
     }
 
@@ -145,9 +146,10 @@ public class Domain implements IdCachable, Verifyable {
         if (id == 0) {
             throw new GigiApiException("not inserted.");
         }
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `domains` SET `deleted`=CURRENT_TIMESTAMP WHERE `id`=?");
-        ps.setInt(1, id);
-        ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `domains` SET `deleted`=CURRENT_TIMESTAMP WHERE `id`=?")) {
+            ps.setInt(1, id);
+            ps.execute();
+        }
     }
 
     public CertificateOwner getOwner() {
@@ -169,13 +171,13 @@ public class Domain implements IdCachable, Verifyable {
         LinkedList<DomainPingConfiguration> configs = this.configs;
         if (configs == null) {
             configs = new LinkedList<>();
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM pingconfig WHERE domainid=?");
-            ps.setInt(1, id);
-            GigiResultSet rs = ps.executeQuery();
-            while (rs.next()) {
-                configs.add(DomainPingConfiguration.getById(rs.getInt(1)));
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT id FROM pingconfig WHERE domainid=?")) {
+                ps.setInt(1, id);
+                GigiResultSet rs = ps.executeQuery();
+                while (rs.next()) {
+                    configs.add(DomainPingConfiguration.getById(rs.getInt(1)));
+                }
             }
-            rs.close();
             this.configs = configs;
 
         }
@@ -183,39 +185,43 @@ public class Domain implements IdCachable, Verifyable {
     }
 
     public void addPing(DomainPingType type, String config) throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `pingconfig` SET `domainid`=?, `type`=?::`pingType`, `info`=?");
-        ps.setInt(1, id);
-        ps.setString(2, type.toString().toLowerCase());
-        ps.setString(3, config);
-        ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `pingconfig` SET `domainid`=?, `type`=?::`pingType`, `info`=?")) {
+            ps.setInt(1, id);
+            ps.setString(2, type.toString().toLowerCase());
+            ps.setString(3, config);
+            ps.execute();
+        }
         configs = null;
     }
 
     public synchronized void verify(String hash) throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `domainPinglog` SET `state`='success' WHERE `challenge`=? AND `state`='open' AND `configId` IN (SELECT `id` FROM `pingconfig` WHERE `domainid`=? AND `type`='email')");
-        ps.setString(1, hash);
-        ps.setInt(2, id);
-        ps.executeUpdate();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `domainPinglog` SET `state`='success' WHERE `challenge`=? AND `state`='open' AND `configId` IN (SELECT `id` FROM `pingconfig` WHERE `domainid`=? AND `type`='email')")) {
+            ps.setString(1, hash);
+            ps.setInt(2, id);
+            ps.executeUpdate();
+        }
     }
 
     public boolean isVerified() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `domainPinglog` INNER JOIN `pingconfig` ON `pingconfig`.`id`=`domainPinglog`.`configId` WHERE `domainid`=? AND `state`='success'");
-        ps.setInt(1, id);
-        GigiResultSet rs = ps.executeQuery();
-        return rs.next();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `domainPinglog` INNER JOIN `pingconfig` ON `pingconfig`.`id`=`domainPinglog`.`configId` WHERE `domainid`=? AND `state`='success'")) {
+            ps.setInt(1, id);
+            GigiResultSet rs = ps.executeQuery();
+            return rs.next();
+        }
     }
 
     public DomainPingExecution[] getPings() throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepareScrollable("SELECT `state`, `type`, `info`, `result`, `configId`, `when` FROM `domainPinglog` INNER JOIN `pingconfig` ON `pingconfig`.`id`=`domainPinglog`.`configId` WHERE `pingconfig`.`domainid`=? ORDER BY `when` DESC;");
-        ps.setInt(1, id);
-        GigiResultSet rs = ps.executeQuery();
-        rs.last();
-        DomainPingExecution[] contents = new DomainPingExecution[rs.getRow()];
-        rs.beforeFirst();
-        for (int i = 0; i < contents.length && rs.next(); i++) {
-            contents[i] = new DomainPingExecution(rs);
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `state`, `type`, `info`, `result`, `configId`, `when` FROM `domainPinglog` INNER JOIN `pingconfig` ON `pingconfig`.`id`=`domainPinglog`.`configId` WHERE `pingconfig`.`domainid`=? ORDER BY `when` DESC;", true)) {
+            ps.setInt(1, id);
+            GigiResultSet rs = ps.executeQuery();
+            rs.last();
+            DomainPingExecution[] contents = new DomainPingExecution[rs.getRow()];
+            rs.beforeFirst();
+            for (int i = 0; i < contents.length && rs.next(); i++) {
+                contents[i] = new DomainPingExecution(rs);
+            }
+            return contents;
         }
-        return contents;
 
     }
 
@@ -230,13 +236,14 @@ public class Domain implements IdCachable, Verifyable {
     }
 
     public static int searchUserIdByDomain(String domain) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `domains` WHERE `domain` = ?");
-        ps.setString(1, domain);
-        GigiResultSet res = ps.executeQuery();
-        if (res.next()) {
-            return res.getInt(1);
-        } else {
-            return -1;
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `memid` FROM `domains` WHERE `domain` = ?")) {
+            ps.setString(1, domain);
+            GigiResultSet res = ps.executeQuery();
+            if (res.next()) {
+                return res.getInt(1);
+            } else {
+                return -1;
+            }
         }
     }
 
index 145473fdb9a871c892f8f42ad87caa89a9c4ee5d..baa7b7b00b69c1affad4e21391f67e425f08b01a 100644 (file)
@@ -7,7 +7,6 @@ import java.util.Map;
 
 import org.cacert.gigi.Gigi;
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.output.template.Scope;
@@ -24,17 +23,18 @@ public class DomainPingConfiguration implements IdCachable {
     private String info;
 
     private DomainPingConfiguration(int id) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id`, `domainid`, `type`, `info` FROM `pingconfig` WHERE `id`=?");
-        ps.setInt(1, id);
-
-        GigiResultSet rs = ps.executeQuery();
-        if ( !rs.next()) {
-            throw new IllegalArgumentException("Invalid pingconfig id " + id);
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `id`, `domainid`, `type`, `info` FROM `pingconfig` WHERE `id`=?")) {
+            ps.setInt(1, id);
+
+            GigiResultSet rs = ps.executeQuery();
+            if ( !rs.next()) {
+                throw new IllegalArgumentException("Invalid pingconfig id " + id);
+            }
+            this.id = rs.getInt("id");
+            target = Domain.getById(rs.getInt("domainid"));
+            type = DomainPingType.valueOf(rs.getString("type").toUpperCase());
+            info = rs.getString("info");
         }
-        this.id = rs.getInt("id");
-        target = Domain.getById(rs.getInt("domainid"));
-        type = DomainPingType.valueOf(rs.getString("type").toUpperCase());
-        info = rs.getString("info");
     }
 
     @Override
@@ -65,23 +65,25 @@ public class DomainPingConfiguration implements IdCachable {
     }
 
     public Date getLastExecution() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `when` AS stamp from `domainPinglog` WHERE `configId`=? ORDER BY `when` DESC LIMIT 1");
-        ps.setInt(1, id);
-        GigiResultSet rs = ps.executeQuery();
-        if (rs.next()) {
-            return new Date(rs.getTimestamp("stamp").getTime());
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `when` AS stamp from `domainPinglog` WHERE `configId`=? ORDER BY `when` DESC LIMIT 1")) {
+            ps.setInt(1, id);
+            GigiResultSet rs = ps.executeQuery();
+            if (rs.next()) {
+                return new Date(rs.getTimestamp("stamp").getTime());
+            }
+            return new Date(0);
         }
-        return new Date(0);
     }
 
     public Date getLastSuccess() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `when` AS stamp from `domainPinglog` WHERE `configId`=? AND state='success' ORDER BY `when` DESC LIMIT 1");
-        ps.setInt(1, id);
-        GigiResultSet rs = ps.executeQuery();
-        if (rs.next()) {
-            return new Date(rs.getTimestamp("stamp").getTime());
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `when` AS stamp from `domainPinglog` WHERE `configId`=? AND state='success' ORDER BY `when` DESC LIMIT 1")) {
+            ps.setInt(1, id);
+            GigiResultSet rs = ps.executeQuery();
+            if (rs.next()) {
+                return new Date(rs.getTimestamp("stamp").getTime());
+            }
+            return new Date(0);
         }
-        return new Date(0);
     }
 
     public synchronized void requestReping() throws GigiApiException {
index b7bb080fa16adf67bc676e6de6b0edf94fbb74c6..cf007312a8067310b33e00c1b6a9ce84efdc6eb7 100644 (file)
@@ -4,7 +4,6 @@ import java.io.IOException;
 import java.util.Locale;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.email.EmailProvider;
@@ -23,18 +22,18 @@ public class EmailAddress implements IdCachable, Verifyable {
     private String hash = null;
 
     private EmailAddress(int id) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid`, `email`, `hash` FROM `emails` WHERE `id`=? AND `deleted` IS NULL");
-        ps.setInt(1, id);
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `memid`, `email`, `hash` FROM `emails` WHERE `id`=? AND `deleted` IS NULL")) {
+            ps.setInt(1, id);
 
-        GigiResultSet rs = ps.executeQuery();
-        if ( !rs.next()) {
-            throw new IllegalArgumentException("Invalid email id " + id);
+            GigiResultSet rs = ps.executeQuery();
+            if ( !rs.next()) {
+                throw new IllegalArgumentException("Invalid email id " + id);
+            }
+            this.id = id;
+            owner = User.getById(rs.getInt(1));
+            address = rs.getString(2);
+            hash = rs.getString(3);
         }
-        this.id = id;
-        owner = User.getById(rs.getInt(1));
-        address = rs.getString(2);
-        hash = rs.getString(3);
-        rs.close();
     }
 
     public EmailAddress(User owner, String address, Locale mailLocale) throws GigiApiException {
@@ -53,18 +52,18 @@ public class EmailAddress implements IdCachable, Verifyable {
                 if (id != 0) {
                     throw new IllegalStateException("already inserted.");
                 }
-                GigiPreparedStatement psCheck = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `emails` WHERE email=? AND deleted is NULL");
-                GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `emails` SET memid=?, hash=?, email=?");
-                ps.setInt(1, owner.getId());
-                ps.setString(2, hash);
-                ps.setString(3, address);
-                psCheck.setString(1, address);
-                GigiResultSet res = psCheck.executeQuery();
-                if (res.next()) {
-                    throw new GigiApiException("The email is currently valid");
+                try (GigiPreparedStatement psCheck = new GigiPreparedStatement("SELECT 1 FROM `emails` WHERE email=? AND deleted is NULL"); GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `emails` SET memid=?, hash=?, email=?")) {
+                    ps.setInt(1, owner.getId());
+                    ps.setString(2, hash);
+                    ps.setString(3, address);
+                    psCheck.setString(1, address);
+                    GigiResultSet res = psCheck.executeQuery();
+                    if (res.next()) {
+                        throw new GigiApiException("The email is currently valid");
+                    }
+                    ps.execute();
+                    id = ps.lastInsertId();
                 }
-                ps.execute();
-                id = ps.lastInsertId();
                 myCache.put(this);
             }
             MailProbe.sendMailProbe(l, "email", id, hash, address);
@@ -83,16 +82,18 @@ public class EmailAddress implements IdCachable, Verifyable {
 
     public synchronized void verify(String hash) throws GigiApiException {
         if (this.hash.equals(hash)) {
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `emails` SET hash='' WHERE id=?");
-            ps.setInt(1, id);
-            ps.execute();
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `emails` SET hash='' WHERE id=?")) {
+                ps.setInt(1, id);
+                ps.execute();
+            }
             hash = "";
 
             // Verify user with that primary email
-            GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("update `users` set `verified`='1' where `id`=? and `email`=? and `verified`='0'");
-            ps2.setInt(1, owner.getId());
-            ps2.setString(2, address);
-            ps2.execute();
+            try (GigiPreparedStatement ps2 = new GigiPreparedStatement("update `users` set `verified`='1' where `id`=? and `email`=? and `verified`='0'")) {
+                ps2.setInt(1, owner.getId());
+                ps2.setString(2, address);
+                ps2.execute();
+            }
             this.hash = "";
 
         } else {
index 8a2c1de479755ed0bce9d529dfffd014e93f1295..07d3c11f1971519414a774250f2faa9375cd20c6 100644 (file)
@@ -1,6 +1,5 @@
 package org.cacert.gigi.dbObjects;
 
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 
@@ -22,18 +21,19 @@ public enum Group {
     }
 
     public User[] getMembers(int offset, int count) {
-        GigiPreparedStatement gps = DatabaseConnection.getInstance().prepareScrollable("SELECT `user` FROM `user_groups` WHERE `permission`=?::`userGroup` AND `deleted` IS NULL OFFSET ? LIMIT ?");
-        gps.setString(1, dbName);
-        gps.setInt(2, offset);
-        gps.setInt(3, count);
-        GigiResultSet grs = gps.executeQuery();
-        grs.last();
-        User[] users = new User[grs.getRow()];
-        int i = 0;
-        grs.beforeFirst();
-        while (grs.next()) {
-            users[i++] = User.getById(grs.getInt(1));
+        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.setInt(2, offset);
+            gps.setInt(3, count);
+            GigiResultSet grs = gps.executeQuery();
+            grs.last();
+            User[] users = new User[grs.getRow()];
+            int i = 0;
+            grs.beforeFirst();
+            while (grs.next()) {
+                users[i++] = User.getById(grs.getInt(1));
+            }
+            return users;
         }
-        return users;
     }
 }
index 9104c1315a5d18537893149a342c326be6888c92..bb357a8d07b742fddda040031abf4a8ca0dd8d83 100644 (file)
@@ -3,7 +3,6 @@ package org.cacert.gigi.dbObjects;
 import java.sql.Date;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.output.CertificateValiditySelector;
@@ -32,38 +31,40 @@ public class Job implements IdCachable {
 
     public synchronized static Job sign(Certificate targetId, Date start, String period) throws GigiApiException {
         CertificateValiditySelector.checkValidityLength(period);
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `jobs` SET targetId=?, task=?::`jobType`, executeFrom=?, executeTo=?");
-        ps.setInt(1, targetId.getId());
-        ps.setString(2, JobType.SIGN.getName());
-        ps.setDate(3, start);
-        ps.setString(4, period);
-        ps.execute();
-        return cache.put(new Job(ps.lastInsertId()));
+        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.setDate(3, start);
+            ps.setString(4, period);
+            ps.execute();
+            return cache.put(new Job(ps.lastInsertId()));
+        }
     }
 
     public synchronized static Job revoke(Certificate targetId) {
 
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `jobs` SET targetId=?, task=?::`jobType`");
-        ps.setInt(1, targetId.getId());
-        ps.setString(2, JobType.REVOKE.getName());
-        ps.execute();
-        return cache.put(new Job(ps.lastInsertId()));
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `jobs` SET targetId=?, task=?::`jobType`")) {
+            ps.setInt(1, targetId.getId());
+            ps.setString(2, JobType.REVOKE.getName());
+            ps.execute();
+            return cache.put(new Job(ps.lastInsertId()));
+        }
     }
 
     public synchronized boolean waitFor(int max) throws InterruptedException {
         long start = System.currentTimeMillis();
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `jobs` WHERE id=? AND state='open'");
-        ps.setInt(1, id);
-        GigiResultSet rs = ps.executeQuery();
-        while (rs.next()) {
-            rs.close();
-            if (max != 0 && System.currentTimeMillis() - start > max) {
-                return false;
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `jobs` WHERE id=? AND state='open'")) {
+            ps.setInt(1, id);
+            GigiResultSet rs = ps.executeQuery();
+            while (rs.next()) {
+                rs.close();
+                if (max != 0 && System.currentTimeMillis() - start > max) {
+                    return false;
+                }
+                Thread.sleep((long) (2000 + Math.random() * 2000));
+                rs = ps.executeQuery();
             }
-            Thread.sleep((long) (2000 + Math.random() * 2000));
-            rs = ps.executeQuery();
         }
-        rs.close();
         return true;
     }
 
@@ -79,15 +80,16 @@ public class Job implements IdCachable {
         if (i != null) {
             return i;
         }
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `jobs` WHERE id=?'");
-        ps.setInt(1, id);
-        GigiResultSet rs = ps.executeQuery();
-        if (rs.next()) {
-            Job j = new Job(id);
-            cache.put(j);
-            return j;
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `jobs` WHERE id=?'")) {
+            ps.setInt(1, id);
+            GigiResultSet rs = ps.executeQuery();
+            if (rs.next()) {
+                Job j = new Job(id);
+                cache.put(j);
+                return j;
+            }
+            return null;
         }
-        return null;
 
     }
 }
index b51d2838e4e045eb40296ed5c2dd301266a41f2e..6b5b28f44234dd006ab6f3f8d3bef29edcc542e8 100644 (file)
@@ -4,7 +4,6 @@ import java.util.ArrayList;
 import java.util.List;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.dbObjects.Certificate.CertificateStatus;
@@ -62,18 +61,18 @@ public class Organisation extends CertificateOwner {
         this.city = city;
         this.email = email;
         int id = getId();
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO organisations SET id=?, name=?, state=?, province=?, city=?, contactEmail=?, creator=?");
-        ps.setInt(1, id);
-        ps.setString(2, name);
-        ps.setString(3, state);
-        ps.setString(4, province);
-        ps.setString(5, city);
-        ps.setString(6, email);
-        ps.setInt(7, creator.getId());
-        synchronized (Organisation.class) {
-            ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO organisations SET id=?, name=?, state=?, province=?, city=?, contactEmail=?, creator=?")) {
+            ps.setInt(1, id);
+            ps.setString(2, name);
+            ps.setString(3, state);
+            ps.setString(4, province);
+            ps.setString(5, city);
+            ps.setString(6, email);
+            ps.setInt(7, creator.getId());
+            synchronized (Organisation.class) {
+                ps.execute();
+            }
         }
-
     }
 
     protected Organisation(GigiResultSet rs) {
@@ -120,58 +119,63 @@ public class Organisation extends CertificateOwner {
         if ( !actor.isInGroup(Group.ORGASSURER) && !isMaster(actor)) {
             throw new GigiApiException("Only org assurer or master-admin may add admins to an organisation.");
         }
-        GigiPreparedStatement ps1 = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `org_admin` WHERE `orgid`=? AND `memid`=? AND `deleted` IS NULL");
-        ps1.setInt(1, getId());
-        ps1.setInt(2, admin.getId());
-        GigiResultSet result = ps1.executeQuery();
-        if (result.next()) {
-            return;
+        try (GigiPreparedStatement ps1 = new GigiPreparedStatement("SELECT 1 FROM `org_admin` WHERE `orgid`=? AND `memid`=? AND `deleted` IS NULL")) {
+            ps1.setInt(1, getId());
+            ps1.setInt(2, admin.getId());
+            GigiResultSet result = ps1.executeQuery();
+            if (result.next()) {
+                return;
+            }
+        }
+        try (GigiPreparedStatement ps2 = new GigiPreparedStatement("INSERT INTO `org_admin` SET `orgid`=?, `memid`=?, `creator`=?, `master`=?::`yesno`")) {
+            ps2.setInt(1, getId());
+            ps2.setInt(2, admin.getId());
+            ps2.setInt(3, actor.getId());
+            ps2.setString(4, master ? "y" : "n");
+            ps2.execute();
         }
-        GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("INSERT INTO `org_admin` SET `orgid`=?, `memid`=?, `creator`=?, `master`=?::`yesno`");
-        ps2.setInt(1, getId());
-        ps2.setInt(2, admin.getId());
-        ps2.setInt(3, actor.getId());
-        ps2.setString(4, master ? "y" : "n");
-        ps2.execute();
     }
 
     public void removeAdmin(User admin, User actor) throws GigiApiException {
         if ( !actor.isInGroup(Group.ORGASSURER) && !isMaster(actor)) {
             throw new GigiApiException("Only org assurer or master-admin may delete admins from an organisation.");
         }
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE org_admin SET deleter=?, deleted=NOW() WHERE orgid=? AND memid=?");
-        ps.setInt(1, actor.getId());
-        ps.setInt(2, getId());
-        ps.setInt(3, admin.getId());
-        ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE org_admin SET deleter=?, deleted=NOW() WHERE orgid=? AND memid=?")) {
+            ps.setInt(1, actor.getId());
+            ps.setInt(2, getId());
+            ps.setInt(3, admin.getId());
+            ps.execute();
+        }
     }
 
     public List<Affiliation> getAllAdmins() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepareScrollable("SELECT `memid`, `master` FROM `org_admin` WHERE `orgid`=? AND `deleted` IS NULL");
-        ps.setInt(1, getId());
-        GigiResultSet rs = ps.executeQuery();
-        rs.last();
-        ArrayList<Affiliation> al = new ArrayList<>(rs.getRow());
-        rs.beforeFirst();
-        while (rs.next()) {
-            al.add(new Affiliation(User.getById(rs.getInt(1)), rs.getString(2).equals("y"), null));
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `memid`, `master` FROM `org_admin` WHERE `orgid`=? AND `deleted` IS NULL", true)) {
+            ps.setInt(1, getId());
+            GigiResultSet rs = ps.executeQuery();
+            rs.last();
+            ArrayList<Affiliation> al = new ArrayList<>(rs.getRow());
+            rs.beforeFirst();
+            while (rs.next()) {
+                al.add(new Affiliation(User.getById(rs.getInt(1)), rs.getString(2).equals("y"), null));
+            }
+            return al;
         }
-        return al;
     }
 
     public static Organisation[] getOrganisations(int offset, int count) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepareScrollable("SELECT `certOwners`.`id` FROM `organisations` INNER JOIN `certOwners` ON `certOwners`.`id`=`organisations`.`id` WHERE `certOwners`.`deleted` IS NULL OFFSET ? LIMIT ?");
-        ps.setInt(1, offset);
-        ps.setInt(2, count);
-        GigiResultSet res = ps.executeQuery();
-        res.last();
-        Organisation[] resu = new Organisation[res.getRow()];
-        res.beforeFirst();
-        int i = 0;
-        while (res.next()) {
-            resu[i++] = getById(res.getInt(1));
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `certOwners`.`id` FROM `organisations` INNER JOIN `certOwners` ON `certOwners`.`id`=`organisations`.`id` WHERE `certOwners`.`deleted` IS NULL OFFSET ? LIMIT ?", true)) {
+            ps.setInt(1, offset);
+            ps.setInt(2, count);
+            GigiResultSet res = ps.executeQuery();
+            res.last();
+            Organisation[] resu = new Organisation[res.getRow()];
+            res.beforeFirst();
+            int i = 0;
+            while (res.next()) {
+                resu[i++] = getById(res.getInt(1));
+            }
+            return resu;
         }
-        return resu;
     }
 
     public void update(String o, String c, String st, String l, String mail) {
@@ -180,13 +184,14 @@ public class Organisation extends CertificateOwner {
                 cert.revoke();
             }
         }
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `organisations` SET `name`=?, `state`=?, `province`=?, `city`=?, `contactEmail`=?");
-        ps.setString(1, o);
-        ps.setString(2, c);
-        ps.setString(3, st);
-        ps.setString(4, l);
-        ps.setString(5, mail);
-        ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `organisations` SET `name`=?, `state`=?, `province`=?, `city`=?, `contactEmail`=?")) {
+            ps.setString(1, o);
+            ps.setString(2, c);
+            ps.setString(3, st);
+            ps.setString(4, l);
+            ps.setString(5, mail);
+            ps.execute();
+        }
         email = mail;
         name = o;
         state = c;
index decf55274047b5eab4e13400c7f2c3530cf66742..e957aa082faef1a5b1af048e8dea69cb0d73ad50 100644 (file)
@@ -3,7 +3,6 @@ package org.cacert.gigi.dbObjects;
 import java.sql.Date;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.dbObjects.Certificate.CertificateStatus;
 
@@ -54,12 +53,13 @@ public class SupportedUser {
         if (ticket == null) {
             throw new GigiApiException("No ticket set!");
         }
-        GigiPreparedStatement prep = DatabaseConnection.getInstance().prepare("INSERT INTO `adminLog` SET uid=?, admin=?, type=?, information=?");
-        prep.setInt(1, target.getId());
-        prep.setInt(2, supporter.getId());
-        prep.setString(3, type);
-        prep.setString(4, ticket);
-        prep.executeUpdate();
+        try (GigiPreparedStatement prep = new GigiPreparedStatement("INSERT INTO `adminLog` SET uid=?, admin=?, type=?, information=?")) {
+            prep.setInt(1, target.getId());
+            prep.setInt(2, supporter.getId());
+            prep.setString(3, type);
+            prep.setString(4, ticket);
+            prep.executeUpdate();
+        }
     }
 
     public int getId() {
index e6afc79666c60f1cf07540b893f08d309a3a4b3a..55e567f5dd6a93a793a9c358cde85c7242360748 100644 (file)
@@ -11,7 +11,6 @@ import java.util.Locale;
 import java.util.Set;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.localisation.Language;
@@ -53,12 +52,13 @@ public class User extends CertificateOwner {
             locale = Language.getLocaleFromString(localeStr);
         }
 
-        GigiPreparedStatement psg = DatabaseConnection.getInstance().prepare("SELECT `permission` FROM `user_groups` WHERE `user`=? AND `deleted` is NULL");
-        psg.setInt(1, rs.getInt("id"));
+        try (GigiPreparedStatement psg = new GigiPreparedStatement("SELECT `permission` FROM `user_groups` WHERE `user`=? AND `deleted` is NULL")) {
+            psg.setInt(1, rs.getInt("id"));
 
-        try (GigiResultSet rs2 = psg.executeQuery()) {
-            while (rs2.next()) {
-                groups.add(Group.getByString(rs2.getString(1)));
+            try (GigiResultSet rs2 = psg.executeQuery()) {
+                while (rs2.next()) {
+                    groups.add(Group.getByString(rs2.getString(1)));
+                }
             }
         }
     }
@@ -68,17 +68,18 @@ public class User extends CertificateOwner {
         this.dob = dob;
         this.name = name;
         this.locale = locale;
-        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("INSERT INTO `users` SET `email`=?, `password`=?, " + "`fname`=?, `mname`=?, `lname`=?, " + "`suffix`=?, `dob`=?, `language`=?, id=?");
-        query.setString(1, email);
-        query.setString(2, PasswordHash.hash(password));
-        query.setString(3, name.getFname());
-        query.setString(4, name.getMname());
-        query.setString(5, name.getLname());
-        query.setString(6, name.getSuffix());
-        query.setDate(7, dob);
-        query.setString(8, locale.toString());
-        query.setInt(9, getId());
-        query.execute();
+        try (GigiPreparedStatement query = new GigiPreparedStatement("INSERT INTO `users` SET `email`=?, `password`=?, " + "`fname`=?, `mname`=?, `lname`=?, " + "`suffix`=?, `dob`=?, `language`=?, id=?")) {
+            query.setString(1, email);
+            query.setString(2, PasswordHash.hash(password));
+            query.setString(3, name.getFname());
+            query.setString(4, name.getMname());
+            query.setString(5, name.getLname());
+            query.setString(6, name.getSuffix());
+            query.setDate(7, dob);
+            query.setString(8, locale.toString());
+            query.setInt(9, getId());
+            query.execute();
+        }
         new EmailAddress(this, email, locale);
     }
 
@@ -99,26 +100,27 @@ public class User extends CertificateOwner {
     }
 
     public void changePassword(String oldPass, String newPass) throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `password` FROM `users` WHERE `id`=?");
-        ps.setInt(1, getId());
-        try (GigiResultSet rs = ps.executeQuery()) {
-            if ( !rs.next()) {
-                throw new GigiApiException("User not found... very bad.");
-            }
-            if (PasswordHash.verifyHash(oldPass, rs.getString(1)) == null) {
-                throw new GigiApiException("Old password does not match.");
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `password` FROM `users` WHERE `id`=?")) {
+            ps.setInt(1, getId());
+            try (GigiResultSet rs = ps.executeQuery()) {
+                if ( !rs.next()) {
+                    throw new GigiApiException("User not found... very bad.");
+                }
+                if (PasswordHash.verifyHash(oldPass, rs.getString(1)) == null) {
+                    throw new GigiApiException("Old password does not match.");
+                }
             }
         }
         setPassword(newPass);
     }
 
     private void setPassword(String newPass) throws GigiApiException {
-        GigiPreparedStatement ps;
         PasswordStrengthChecker.assertStrongPassword(newPass, getName(), getEmail());
-        ps = DatabaseConnection.getInstance().prepare("UPDATE users SET `password`=? WHERE id=?");
-        ps.setString(1, PasswordHash.hash(newPass));
-        ps.setInt(2, getId());
-        ps.executeUpdate();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE users SET `password`=? WHERE id=?")) {
+            ps.setString(1, PasswordHash.hash(newPass));
+            ps.setInt(2, getId());
+            ps.executeUpdate();
+        }
     }
 
     public void setName(Name name) {
@@ -138,23 +140,24 @@ public class User extends CertificateOwner {
     }
 
     public boolean hasPassedCATS() {
-        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `cats_passed` where `user_id`=? AND `variant_id`=?");
-        query.setInt(1, getId());
-        query.setInt(2, CATS.ASSURER_CHALLANGE_ID);
-        try (GigiResultSet rs = query.executeQuery()) {
-            if (rs.next()) {
-                return true;
-            } else {
-                return false;
+        try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT 1 FROM `cats_passed` where `user_id`=? AND `variant_id`=?")) {
+            query.setInt(1, getId());
+            query.setInt(2, CATS.ASSURER_CHALLANGE_ID);
+            try (GigiResultSet rs = query.executeQuery()) {
+                if (rs.next()) {
+                    return true;
+                } else {
+                    return false;
+                }
             }
         }
     }
 
     public int getAssurancePoints() {
-        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT sum(points) FROM `notary` where `to`=? AND `deleted` is NULL");
-        query.setInt(1, getId());
+        try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT sum(points) FROM `notary` where `to`=? AND `deleted` is NULL")) {
+            query.setInt(1, getId());
 
-        try (GigiResultSet rs = query.executeQuery()) {
+            GigiResultSet rs = query.executeQuery();
             int points = 0;
 
             if (rs.next()) {
@@ -166,10 +169,10 @@ public class User extends CertificateOwner {
     }
 
     public int getExperiencePoints() {
-        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT count(*) FROM `notary` where `from`=? AND `deleted` is NULL");
-        query.setInt(1, getId());
+        try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT count(*) FROM `notary` where `from`=? AND `deleted` is NULL")) {
+            query.setInt(1, getId());
 
-        try (GigiResultSet rs = query.executeQuery()) {
+            GigiResultSet rs = query.executeQuery();
             int points = 0;
 
             if (rs.next()) {
@@ -235,10 +238,11 @@ public class User extends CertificateOwner {
                     throw new GigiApiException("Email not verified.");
                 }
 
-                GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE users SET email=? WHERE id=?");
-                ps.setString(1, newMail.getAddress());
-                ps.setInt(2, getId());
-                ps.execute();
+                try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE users SET email=? WHERE id=?")) {
+                    ps.setString(1, newMail.getAddress());
+                    ps.setInt(2, getId());
+                    ps.execute();
+                }
 
                 this.email = newMail.getAddress();
                 return;
@@ -255,9 +259,10 @@ public class User extends CertificateOwner {
 
         for (EmailAddress email : getEmails()) {
             if (email.getId() == delMail.getId()) {
-                GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `emails` SET `deleted`=CURRENT_TIMESTAMP WHERE `id`=?");
-                ps.setInt(1, delMail.getId());
-                ps.execute();
+                try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `emails` SET `deleted`=CURRENT_TIMESTAMP WHERE `id`=?")) {
+                    ps.setInt(1, delMail.getId());
+                    ps.execute();
+                }
                 return;
             }
         }
@@ -266,10 +271,10 @@ public class User extends CertificateOwner {
 
     public synchronized Assurance[] getReceivedAssurances() {
         if (receivedAssurances == null) {
-            GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT * FROM `notary` WHERE `to`=? AND `deleted` IS NULL");
-            query.setInt(1, getId());
+            try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT * FROM `notary` WHERE `to`=? AND `deleted` IS NULL")) {
+                query.setInt(1, getId());
 
-            try (GigiResultSet res = query.executeQuery()) {
+                GigiResultSet res = query.executeQuery();
                 List<Assurance> assurances = new LinkedList<Assurance>();
 
                 while (res.next()) {
@@ -285,17 +290,18 @@ public class User extends CertificateOwner {
 
     public synchronized Assurance[] getMadeAssurances() {
         if (madeAssurances == null) {
-            GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT * FROM notary WHERE `from`=? AND deleted is NULL");
-            query.setInt(1, getId());
+            try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT * FROM notary WHERE `from`=? AND deleted is NULL")) {
+                query.setInt(1, getId());
 
-            try (GigiResultSet res = query.executeQuery()) {
-                List<Assurance> assurances = new LinkedList<Assurance>();
+                try (GigiResultSet res = query.executeQuery()) {
+                    List<Assurance> assurances = new LinkedList<Assurance>();
 
-                while (res.next()) {
-                    assurances.add(new Assurance(res));
-                }
+                    while (res.next()) {
+                        assurances.add(new Assurance(res));
+                    }
 
-                this.madeAssurances = assurances.toArray(new Assurance[0]);
+                    this.madeAssurances = assurances.toArray(new Assurance[0]);
+                }
             }
         }
 
@@ -320,14 +326,15 @@ public class User extends CertificateOwner {
     }
 
     protected void rawUpdateUserData() {
-        GigiPreparedStatement update = DatabaseConnection.getInstance().prepare("UPDATE users SET fname=?, lname=?, mname=?, suffix=?, dob=? WHERE id=?");
-        update.setString(1, name.getFname());
-        update.setString(2, name.getLname());
-        update.setString(3, name.getMname());
-        update.setString(4, name.getSuffix());
-        update.setDate(5, getDoB());
-        update.setInt(6, getId());
-        update.executeUpdate();
+        try (GigiPreparedStatement update = new GigiPreparedStatement("UPDATE users SET fname=?, lname=?, mname=?, suffix=?, dob=? WHERE id=?")) {
+            update.setString(1, name.getFname());
+            update.setString(2, name.getLname());
+            update.setString(3, name.getMname());
+            update.setString(4, name.getSuffix());
+            update.setDate(5, getDoB());
+            update.setInt(6, getId());
+            update.executeUpdate();
+        }
     }
 
     public Locale getPreferredLocale() {
@@ -340,35 +347,37 @@ public class User extends CertificateOwner {
     }
 
     public boolean wantsDirectoryListing() {
-        GigiPreparedStatement get = DatabaseConnection.getInstance().prepare("SELECT listme FROM users WHERE id=?");
-        get.setInt(1, getId());
-        try (GigiResultSet exec = get.executeQuery()) {
+        try (GigiPreparedStatement get = new GigiPreparedStatement("SELECT listme FROM users WHERE id=?")) {
+            get.setInt(1, getId());
+            GigiResultSet exec = get.executeQuery();
             return exec.next() && exec.getBoolean("listme");
         }
     }
 
     public String getContactInformation() {
-        GigiPreparedStatement get = DatabaseConnection.getInstance().prepare("SELECT contactinfo FROM users WHERE id=?");
-        get.setInt(1, getId());
+        try (GigiPreparedStatement get = new GigiPreparedStatement("SELECT contactinfo FROM users WHERE id=?")) {
+            get.setInt(1, getId());
 
-        try (GigiResultSet exec = get.executeQuery()) {
+            GigiResultSet exec = get.executeQuery();
             exec.next();
             return exec.getString("contactinfo");
         }
     }
 
     public void setDirectoryListing(boolean on) {
-        GigiPreparedStatement update = DatabaseConnection.getInstance().prepare("UPDATE users SET listme = ? WHERE id = ?");
-        update.setBoolean(1, on);
-        update.setInt(2, getId());
-        update.executeUpdate();
+        try (GigiPreparedStatement update = new GigiPreparedStatement("UPDATE users SET listme = ? WHERE id = ?")) {
+            update.setBoolean(1, on);
+            update.setInt(2, getId());
+            update.executeUpdate();
+        }
     }
 
     public void setContactInformation(String contactInfo) {
-        GigiPreparedStatement update = DatabaseConnection.getInstance().prepare("UPDATE users SET contactinfo = ? WHERE id = ?");
-        update.setString(1, contactInfo);
-        update.setInt(2, getId());
-        update.executeUpdate();
+        try (GigiPreparedStatement update = new GigiPreparedStatement("UPDATE users SET contactinfo = ? WHERE id = ?")) {
+            update.setString(1, contactInfo);
+            update.setInt(2, getId());
+            update.executeUpdate();
+        }
     }
 
     public boolean isInGroup(Group g) {
@@ -381,32 +390,35 @@ public class User extends CertificateOwner {
 
     public void grantGroup(User granter, Group toGrant) {
         groups.add(toGrant);
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `user_groups` SET `user`=?, `permission`=?::`userGroup`, `grantedby`=?");
-        ps.setInt(1, getId());
-        ps.setString(2, toGrant.getDatabaseName());
-        ps.setInt(3, granter.getId());
-        ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `user_groups` SET `user`=?, `permission`=?::`userGroup`, `grantedby`=?")) {
+            ps.setInt(1, getId());
+            ps.setString(2, toGrant.getDatabaseName());
+            ps.setInt(3, granter.getId());
+            ps.execute();
+        }
     }
 
     public void revokeGroup(User revoker, Group toRevoke) {
         groups.remove(toRevoke);
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("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.setInt(3, getId());
-        ps.execute();
+        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.setInt(3, getId());
+            ps.execute();
+        }
     }
 
     public List<Organisation> getOrganisations() {
         List<Organisation> orgas = new ArrayList<>();
-        GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT `orgid` FROM `org_admin` WHERE `memid`=? AND `deleted` IS NULL");
-        query.setInt(1, getId());
-        try (GigiResultSet res = query.executeQuery()) {
-            while (res.next()) {
-                orgas.add(Organisation.getById(res.getInt(1)));
-            }
+        try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT `orgid` FROM `org_admin` WHERE `memid`=? AND `deleted` IS NULL")) {
+            query.setInt(1, getId());
+            try (GigiResultSet res = query.executeQuery()) {
+                while (res.next()) {
+                    orgas.add(Organisation.getById(res.getInt(1)));
+                }
 
-            return orgas;
+                return orgas;
+            }
         }
     }
 
@@ -420,9 +432,9 @@ public class User extends CertificateOwner {
     }
 
     public static User getByEmail(String mail) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `users`.`id` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id` = `users`.`id` WHERE `email`=? AND `deleted` IS NULL");
-        ps.setString(1, mail);
-        try (GigiResultSet rs = ps.executeQuery()) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `users`.`id` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id` = `users`.`id` WHERE `email`=? AND `deleted` IS NULL")) {
+            ps.setString(1, mail);
+            GigiResultSet rs = ps.executeQuery();
             if ( !rs.next()) {
                 return null;
             }
@@ -433,9 +445,9 @@ public class User extends CertificateOwner {
 
     public static User[] findByEmail(String mail) {
         LinkedList<User> results = new LinkedList<User>();
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `users`.`id` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id` = `users`.`id` WHERE `users`.`email` LIKE ? AND `deleted` IS NULL GROUP BY `users`.`id` LIMIT 100");
-        ps.setString(1, mail);
-        try (GigiResultSet rs = ps.executeQuery()) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `users`.`id` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id` = `users`.`id` WHERE `users`.`email` LIKE ? AND `deleted` IS NULL GROUP BY `users`.`id` LIMIT 100")) {
+            ps.setString(1, mail);
+            GigiResultSet rs = ps.executeQuery();
             while (rs.next()) {
                 results.add(User.getById(rs.getInt(1)));
             }
@@ -444,10 +456,10 @@ public class User extends CertificateOwner {
     }
 
     public EmailAddress[] getEmails() {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id` FROM `emails` WHERE `memid`=? AND `deleted` IS NULL");
-        ps.setInt(1, getId());
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `id` FROM `emails` WHERE `memid`=? AND `deleted` IS NULL")) {
+            ps.setInt(1, getId());
 
-        try (GigiResultSet rs = ps.executeQuery()) {
+            GigiResultSet rs = ps.executeQuery();
             LinkedList<EmailAddress> data = new LinkedList<EmailAddress>();
 
             while (rs.next()) {
@@ -470,45 +482,49 @@ public class User extends CertificateOwner {
     }
 
     public String[] getTrainings() {
-        GigiPreparedStatement prep = DatabaseConnection.getInstance().prepare("SELECT `pass_date`, `type_text` FROM `cats_passed` LEFT JOIN `cats_type` ON `cats_type`.`id`=`cats_passed`.`variant_id`  WHERE `user_id`=? ORDER BY `pass_date` ASC");
-        prep.setInt(1, getId());
-        GigiResultSet res = prep.executeQuery();
-        List<String> entries = new LinkedList<String>();
+        try (GigiPreparedStatement prep = new GigiPreparedStatement("SELECT `pass_date`, `type_text` FROM `cats_passed` LEFT JOIN `cats_type` ON `cats_type`.`id`=`cats_passed`.`variant_id`  WHERE `user_id`=? ORDER BY `pass_date` ASC")) {
+            prep.setInt(1, getId());
+            GigiResultSet res = prep.executeQuery();
+            List<String> entries = new LinkedList<String>();
+
+            while (res.next()) {
 
-        while (res.next()) {
+                entries.add(DateSelector.getDateFormat().format(res.getTimestamp(1)) + " (" + res.getString(2) + ")");
+            }
 
-            entries.add(DateSelector.getDateFormat().format(res.getTimestamp(1)) + " (" + res.getString(2) + ")");
+            return entries.toArray(new String[0]);
         }
 
-        return entries.toArray(new String[0]);
     }
 
     public int generatePasswordResetTicket(User actor, String token, String privateToken) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `passwordResetTickets` SET `memid`=?, `creator`=?, `token`=?, `private_token`=?");
-        ps.setInt(1, getId());
-        ps.setInt(2, getId());
-        ps.setString(3, token);
-        ps.setString(4, PasswordHash.hash(privateToken));
-        ps.execute();
-        return ps.lastInsertId();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `passwordResetTickets` SET `memid`=?, `creator`=?, `token`=?, `private_token`=?")) {
+            ps.setInt(1, getId());
+            ps.setInt(2, getId());
+            ps.setString(3, token);
+            ps.setString(4, PasswordHash.hash(privateToken));
+            ps.execute();
+            return ps.lastInsertId();
+        }
     }
 
     public static User getResetWithToken(int id, String token) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `passwordResetTickets` WHERE `id`=? AND `token`=? AND `used` IS NULL");
-        ps.setInt(1, id);
-        ps.setString(2, token);
-        GigiResultSet res = ps.executeQuery();
-        if ( !res.next()) {
-            return null;
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `memid` FROM `passwordResetTickets` WHERE `id`=? AND `token`=? AND `used` IS NULL")) {
+            ps.setInt(1, id);
+            ps.setString(2, token);
+            GigiResultSet res = ps.executeQuery();
+            if ( !res.next()) {
+                return null;
+            }
+            return User.getById(res.getInt(1));
         }
-        return User.getById(res.getInt(1));
     }
 
     public synchronized void consumePasswordResetTicket(int id, String private_token, String newPassword) throws GigiApiException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `private_token` FROM `passwordResetTickets` WHERE `id`=? AND `memid`=? AND `used` IS NULL");
-        ps.setInt(1, id);
-        ps.setInt(2, getId());
-        try (GigiResultSet rs = ps.executeQuery()) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `private_token` FROM `passwordResetTickets` WHERE `id`=? AND `memid`=? AND `used` IS NULL")) {
+            ps.setInt(1, id);
+            ps.setInt(2, getId());
+            GigiResultSet rs = ps.executeQuery();
             if ( !rs.next()) {
                 throw new GigiApiException("Token not found... very bad.");
             }
@@ -516,10 +532,10 @@ public class User extends CertificateOwner {
                 throw new GigiApiException("Private token does not match.");
             }
             setPassword(newPassword);
-            ps = DatabaseConnection.getInstance().prepare("UPDATE `passwordResetTickets` SET  `used` = CURRENT_TIMESTAMP WHERE `id`=?");
+        }
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `passwordResetTickets` SET  `used` = CURRENT_TIMESTAMP WHERE `id`=?")) {
             ps.setInt(1, id);
             ps.executeUpdate();
         }
     }
-
 }
index ff9fde7a919f9b63adacb0ab5a56412bf295073b..e2c4d5d63cb296b615fea0f9ee44093f4cfcca01 100644 (file)
@@ -20,7 +20,6 @@ import javax.naming.NamingException;
 import javax.net.ssl.SSLSocketFactory;
 
 import org.cacert.gigi.crypto.SMIME;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.util.DNSUtil;
 
@@ -145,12 +144,13 @@ public abstract class EmailProvider {
                         continue;
                     }
 
-                    GigiPreparedStatement statmt = DatabaseConnection.getInstance().prepare("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`=?, `uid`=?, `type`='fast', `status`=?::`pingState`");
-                    statmt.setString(1, address);
-                    statmt.setString(2, line);
-                    statmt.setInt(3, forUid);
-                    statmt.setString(4, "success");
-                    statmt.execute();
+                    try (GigiPreparedStatement statmt = new GigiPreparedStatement("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`=?, `uid`=?, `type`='fast', `status`=?::`pingState`")) {
+                        statmt.setString(1, address);
+                        statmt.setString(2, line);
+                        statmt.setInt(3, forUid);
+                        statmt.setString(4, "success");
+                        statmt.execute();
+                    }
 
                     if (line == null || !line.startsWith("250")) {
                         return line;
@@ -161,12 +161,13 @@ public abstract class EmailProvider {
 
             }
         }
-        GigiPreparedStatement statmt = DatabaseConnection.getInstance().prepare("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`=?, `uid`=?, `type`='fast', `status`=?::`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();
+        try (GigiPreparedStatement statmt = new GigiPreparedStatement("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`=?, `uid`=?, `type`='fast', `status`=?::`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;
     }
 
index 58adcda2dacb1a3ab1aa3be2592f847554f58fde..ba6e0eecd8f7f2055f11b303a111398c356cd1d3 100644 (file)
@@ -13,7 +13,6 @@ import javax.servlet.http.HttpServletResponse;
 import javax.servlet.http.HttpSession;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.dbObjects.CertificateOwner;
@@ -100,24 +99,25 @@ public class LoginPage extends Page {
     private void tryAuthWithUnpw(HttpServletRequest req) {
         String un = req.getParameter("username");
         String pw = req.getParameter("password");
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `password`, `id` FROM `users` WHERE `email`=? AND verified='1'");
-        ps.setString(1, un);
-        GigiResultSet rs = ps.executeQuery();
-        if (rs.next()) {
-            String dbHash = rs.getString(1);
-            String hash = PasswordHash.verifyHash(pw, dbHash);
-            if (hash != null) {
-                if ( !hash.equals(dbHash)) {
-                    GigiPreparedStatement gps = DatabaseConnection.getInstance().prepare("UPDATE `users` SET `password`=? WHERE `email`=?");
-                    gps.setString(1, hash);
-                    gps.setString(2, un);
-                    gps.executeUpdate();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `password`, `id` FROM `users` WHERE `email`=? AND verified='1'")) {
+            ps.setString(1, un);
+            GigiResultSet rs = ps.executeQuery();
+            if (rs.next()) {
+                String dbHash = rs.getString(1);
+                String hash = PasswordHash.verifyHash(pw, dbHash);
+                if (hash != null) {
+                    if ( !hash.equals(dbHash)) {
+                        try (GigiPreparedStatement gps = new GigiPreparedStatement("UPDATE `users` SET `password`=? WHERE `email`=?")) {
+                            gps.setString(1, hash);
+                            gps.setString(2, un);
+                            gps.executeUpdate();
+                        }
+                    }
+                    loginSession(req, User.getById(rs.getInt(2)));
+                    req.getSession().setAttribute(LOGIN_METHOD, "Password");
                 }
-                loginSession(req, User.getById(rs.getInt(2)));
-                req.getSession().setAttribute(LOGIN_METHOD, "Password");
             }
         }
-        rs.close();
     }
 
     public static User getUser(HttpServletRequest req) {
index 500598181b003dcca003e646da46cfa118b28d65..bb72a9cd1a44fa516624823a36b7671c7d62ea58 100644 (file)
@@ -119,26 +119,24 @@ public class Signup extends Form {
         if (isFailed(out)) {
             return false;
         }
-        GigiPreparedStatement q1 = DatabaseConnection.getInstance().prepare("SELECT * FROM `emails` WHERE `email`=? AND `deleted` IS NULL");
-        GigiPreparedStatement q2 = DatabaseConnection.getInstance().prepare("SELECT * FROM `certOwners` INNER JOIN `users` ON `users`.`id`=`certOwners`.`id` WHERE `email`=? AND `deleted` IS NULL");
-        q1.setString(1, email);
-        q2.setString(1, email);
-        GigiResultSet r1 = q1.executeQuery();
-        GigiResultSet r2 = q2.executeQuery();
-        if (r1.next() || r2.next()) {
-            outputError(out, req, "This email address is currently valid in the system.");
-        }
-        r1.close();
-        r2.close();
-        GigiPreparedStatement q3 = DatabaseConnection.getInstance().prepare("SELECT `domain` FROM `baddomains` WHERE `domain`=RIGHT(?, LENGTH(`domain`))");
-        q3.setString(1, email);
-
-        GigiResultSet r3 = q3.executeQuery();
-        if (r3.next()) {
-            String domain = r3.getString(1);
-            outputError(out, req, "We don't allow signups from people using email addresses from %s", domain);
-        }
-        r3.close();
+        try (GigiPreparedStatement q1 = new GigiPreparedStatement("SELECT * FROM `emails` WHERE `email`=? AND `deleted` IS NULL"); GigiPreparedStatement q2 = new GigiPreparedStatement("SELECT * FROM `certOwners` INNER JOIN `users` ON `users`.`id`=`certOwners`.`id` WHERE `email`=? AND `deleted` IS NULL")) {
+            q1.setString(1, email);
+            q2.setString(1, email);
+            GigiResultSet r1 = q1.executeQuery();
+            GigiResultSet r2 = q2.executeQuery();
+            if (r1.next() || r2.next()) {
+                outputError(out, req, "This email address is currently valid in the system.");
+            }
+        }
+        try (GigiPreparedStatement q3 = new GigiPreparedStatement("SELECT `domain` FROM `baddomains` WHERE `domain`=RIGHT(?, LENGTH(`domain`))")) {
+            q3.setString(1, email);
+
+            GigiResultSet r3 = q3.executeQuery();
+            if (r3.next()) {
+                String domain = r3.getString(1);
+                outputError(out, req, "We don't allow signups from people using email addresses from %s", domain);
+            }
+        }
         String mailResult = EmailProvider.FAIL;
         try {
             mailResult = HTMLEncoder.encodeHTML(EmailProvider.getInstance().checkEmailServer(0, email));
@@ -176,13 +174,14 @@ public class Signup extends Form {
             DatabaseConnection.getInstance().beginTransaction();
             User u = new User(email, password, buildupName, myDoB.getDate(), Page.getLanguage(req).getLocale());
 
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `alerts` SET `memid`=?," + " `general`=?, `country`=?, `regional`=?, `radius`=?");
-            ps.setInt(1, u.getId());
-            ps.setBoolean(2, general);
-            ps.setBoolean(3, country);
-            ps.setBoolean(4, regional);
-            ps.setBoolean(5, radius);
-            ps.execute();
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `alerts` SET `memid`=?," + " `general`=?, `country`=?, `regional`=?, `radius`=?")) {
+                ps.setInt(1, u.getId());
+                ps.setBoolean(2, general);
+                ps.setBoolean(3, country);
+                ps.setBoolean(4, regional);
+                ps.setBoolean(5, radius);
+                ps.execute();
+            }
             Notary.writeUserAgreement(u, "CCA", "account creation", "", true, 0);
 
             DatabaseConnection.getInstance().commitTransaction();
index efb246b6cde2f4c1efa595a74f77a0d5ae55211b..39a5aa901b83d6a529dec7c733f04fc352b8ce04 100644 (file)
@@ -10,7 +10,6 @@ import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.dbObjects.User;
@@ -75,14 +74,12 @@ public class AssurePage extends Page {
             return;
         }
 
-        GigiResultSet rs = null;
-        try {
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `users`.`id`, `verified` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id`=`users`.`id` WHERE `email`=? AND `dob`=? AND `deleted` IS NULL");
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `users`.`id`, `verified` FROM `users` INNER JOIN `certOwners` ON `certOwners`.`id`=`users`.`id` WHERE `email`=? AND `dob`=? AND `deleted` IS NULL")) {
             ps.setString(1, req.getParameter("email"));
             Calendar c = Calendar.getInstance();
             c.set(Integer.parseInt(req.getParameter("year")), Integer.parseInt(req.getParameter("month")) - 1, Integer.parseInt(req.getParameter("day")));
             ps.setDate(2, new Date(c.getTimeInMillis()));
-            rs = ps.executeQuery();
+            GigiResultSet rs = ps.executeQuery();
             int id = 0;
             if (rs.next()) {
                 id = rs.getInt(1);
@@ -106,11 +103,6 @@ public class AssurePage extends Page {
                 out.print("</div>");
             }
 
-            rs.close();
-        } finally {
-            if (rs != null) {
-                rs.close();
-            }
         }
     }
 }
index 7676bcdbebe9c4e2c968a65d6857470a4d5d4608..942334920fc0432b434913379b0cb641959046e2 100644 (file)
@@ -1,6 +1,5 @@
 package org.cacert.gigi.ping;
 
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.dbObjects.CertificateOwner;
 import org.cacert.gigi.dbObjects.Domain;
@@ -14,22 +13,24 @@ 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) {
-        GigiPreparedStatement enterPingResult = DatabaseConnection.getInstance().prepare("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.setString(3, result);
-        enterPingResult.setString(4, token);
-        enterPingResult.execute();
+        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.setString(3, result);
+            enterPingResult.setString(4, token);
+            enterPingResult.execute();
+        }
 
     }
 
     protected static void updatePingResult(int configId, String state, String result, String token) {
-        GigiPreparedStatement updatePingResult = DatabaseConnection.getInstance().prepare("UPDATE `domainPinglog` SET `state`=?::`pingState`, `result`=? WHERE `configId`=? AND `challenge`=?");
-        updatePingResult.setString(1, DomainPinger.PING_STILL_PENDING == state ? "open" : DomainPinger.PING_SUCCEDED.equals(state) ? "success" : "failed");
-        updatePingResult.setString(2, result);
-        updatePingResult.setInt(3, configId);
-        updatePingResult.setString(4, token);
-        updatePingResult.execute();
+        try (GigiPreparedStatement updatePingResult = new GigiPreparedStatement("UPDATE `domainPinglog` SET `state`=?::`pingState`, `result`=? WHERE `configId`=? AND `challenge`=?")) {
+            updatePingResult.setString(1, DomainPinger.PING_STILL_PENDING == state ? "open" : DomainPinger.PING_SUCCEDED.equals(state) ? "success" : "failed");
+            updatePingResult.setString(2, result);
+            updatePingResult.setInt(3, configId);
+            updatePingResult.setString(4, token);
+            updatePingResult.execute();
+        }
 
     }
 }
index 71d7140a5cbb1b5196e5d27197ecb31195e18849..4936bfc1c1a66e213cb56826ecede219d99c448e 100644 (file)
@@ -5,7 +5,6 @@ import java.util.HashMap;
 import java.util.LinkedList;
 import java.util.Queue;
 
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.dbObjects.Domain;
@@ -29,7 +28,7 @@ public class PingerDaemon extends Thread {
 
     @Override
     public void run() {
-        searchNeededPings = DatabaseConnection.getInstance().prepare("SELECT `pingconfig`.`id` FROM `pingconfig` LEFT JOIN `domainPinglog` ON `domainPinglog`.`configId` = `pingconfig`.`id` INNER JOIN `domains` ON `domains`.`id` = `pingconfig`.`domainid` WHERE ( `domainPinglog`.`configId` IS NULL) AND `domains`.`deleted` IS NULL GROUP BY `pingconfig`.`id`");
+        searchNeededPings = new GigiPreparedStatement("SELECT `pingconfig`.`id` FROM `pingconfig` LEFT JOIN `domainPinglog` ON `domainPinglog`.`configId` = `pingconfig`.`id` INNER JOIN `domains` ON `domains`.`id` = `pingconfig`.`domainid` WHERE ( `domainPinglog`.`configId` IS NULL) AND `domains`.`deleted` IS NULL GROUP BY `pingconfig`.`id`");
         pingers.put(DomainPingType.EMAIL, new EmailPinger());
         pingers.put(DomainPingType.SSL, new SSLPinger(truststore));
         pingers.put(DomainPingType.HTTP, new HTTPFetch());
index 3823cf7d008c6b390c846cb2d779765720df799a..f229bdd4ac2afd9e2a8f9fc39fded1dab3798109 100644 (file)
@@ -6,7 +6,6 @@ import java.util.Date;
 import java.util.GregorianCalendar;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.dbObjects.Group;
@@ -17,29 +16,30 @@ import org.cacert.gigi.output.DateSelector;
 public class Notary {
 
     public static void writeUserAgreement(User member, String document, String method, String comment, boolean active, int secmemid) {
-        GigiPreparedStatement q = DatabaseConnection.getInstance().prepare("INSERT INTO `user_agreements` SET `memid`=?, `secmemid`=?," + " `document`=?,`date`=NOW(), `active`=?,`method`=?,`comment`=?");
-        q.setInt(1, member.getId());
-        q.setInt(2, secmemid);
-        q.setString(3, document);
-        q.setBoolean(4, active);
-        q.setString(5, method);
-        q.setString(6, comment);
-        q.execute();
+        try (GigiPreparedStatement q = new GigiPreparedStatement("INSERT INTO `user_agreements` SET `memid`=?, `secmemid`=?," + " `document`=?,`date`=NOW(), `active`=?,`method`=?,`comment`=?")) {
+            q.setInt(1, member.getId());
+            q.setInt(2, secmemid);
+            q.setString(3, document);
+            q.setBoolean(4, active);
+            q.setString(5, method);
+            q.setString(6, comment);
+            q.execute();
+        }
     }
 
     public static void checkAssuranceIsPossible(User assurer, User target) throws GigiApiException {
         if (assurer.getId() == target.getId()) {
             throw new GigiApiException("You cannot assure yourself.");
         }
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT 1 FROM `notary` where `to`=? and `from`=? AND `deleted` IS NULL");
-        ps.setInt(1, target.getId());
-        ps.setInt(2, assurer.getId());
-        GigiResultSet rs = ps.executeQuery();
-        if (rs.next()) {
-            rs.close();
-            throw new GigiApiException("You have already assured this member.");
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `notary` where `to`=? and `from`=? AND `deleted` IS NULL")) {
+            ps.setInt(1, target.getId());
+            ps.setInt(2, assurer.getId());
+            GigiResultSet rs = ps.executeQuery();
+            if (rs.next()) {
+                rs.close();
+                throw new GigiApiException("You have already assured this member.");
+            }
         }
-        rs.close();
         if ( !assurer.canAssure()) {
             throw new GigiApiException("You are not an assurer.");
         }
@@ -120,13 +120,14 @@ public class Notary {
             throw gae;
         }
 
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?");
-        ps.setInt(1, assurer.getId());
-        ps.setInt(2, assuree.getId());
-        ps.setInt(3, awarded);
-        ps.setString(4, location);
-        ps.setString(5, date);
-        ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?")) {
+            ps.setInt(1, assurer.getId());
+            ps.setInt(2, assuree.getId());
+            ps.setInt(3, awarded);
+            ps.setString(4, location);
+            ps.setString(5, date);
+            ps.execute();
+        }
         assurer.invalidateMadeAssurances();
         assuree.invalidateReceivedAssurances();
     }
index 3808df5d891cc6f0d696bfeac944b473dea7cc22..7ac06ff36595431101b8766ecd79f7e589e8cadd 100644 (file)
@@ -8,7 +8,6 @@ import java.util.Arrays;
 import java.util.Collections;
 import java.util.HashSet;
 
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.dbObjects.Group;
@@ -39,17 +38,20 @@ public class TestUserGroupMembership extends ManagedTest {
         assertThat(u2, is(not(sameInstance(u))));
         assertBehavesTtpGroup(u2);
 
-        GigiResultSet rs = fetchGroupRowsFor(u);
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT * FROM `user_groups` WHERE `user`=?")) {
+            ps.setInt(1, u.getId());
+            GigiResultSet rs = ps.executeQuery();
 
-        assertTrue(rs.next());
-        assertEquals(0, rs.getInt("revokedby"));
-        assertEquals(granter.getId(), rs.getInt("grantedby"));
-        assertEquals(ttpGroup.getDatabaseName(), rs.getString("permission"));
+            assertTrue(rs.next());
+            assertEquals(0, rs.getInt("revokedby"));
+            assertEquals(granter.getId(), rs.getInt("grantedby"));
+            assertEquals(ttpGroup.getDatabaseName(), rs.getString("permission"));
 
-        assertNull(rs.getDate("deleted"));
-        assertNotNull(rs.getDate("granted"));
+            assertNull(rs.getDate("deleted"));
+            assertNotNull(rs.getDate("granted"));
 
-        assertFalse(rs.next());
+            assertFalse(rs.next());
+        }
     }
 
     @Test
@@ -69,23 +71,19 @@ public class TestUserGroupMembership extends ManagedTest {
         assertThat(u2, is(not(sameInstance(u))));
         assertBehavesEmpty(u);
 
-        GigiResultSet rs = fetchGroupRowsFor(u);
-        assertTrue(rs.next());
-        assertEquals(granter.getId(), rs.getInt("revokedby"));
-        assertEquals(granter.getId(), rs.getInt("grantedby"));
-        assertEquals(ttpGroup.getDatabaseName(), rs.getString("permission"));
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT * FROM `user_groups` WHERE `user`=?")) {
+            ps.setInt(1, u.getId());
+            GigiResultSet rs = ps.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(granter.getId(), rs.getInt("revokedby"));
+            assertEquals(granter.getId(), rs.getInt("grantedby"));
+            assertEquals(ttpGroup.getDatabaseName(), rs.getString("permission"));
 
-        assertNotNull(rs.getDate("deleted"));
-        assertNotNull(rs.getDate("granted"));
+            assertNotNull(rs.getDate("deleted"));
+            assertNotNull(rs.getDate("granted"));
 
-        assertFalse(rs.next());
-    }
-
-    private GigiResultSet fetchGroupRowsFor(User u) throws SQLException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT * FROM `user_groups` WHERE `user`=?");
-        ps.setInt(1, u.getId());
-        GigiResultSet rs = ps.executeQuery();
-        return rs;
+            assertFalse(rs.next());
+        }
     }
 
     private void assertBehavesEmpty(User u) {
@@ -103,17 +101,18 @@ public class TestUserGroupMembership extends ManagedTest {
     @Test
     public void testListGroup() {
         Group g = Group.getByString("supporter");
+        int start = g.getMembers(0, 10).length;
         User ux = User.getById(createVerifiedUser("fn", "ln", createUniqueName() + "@example.org", TEST_PASSWORD));
         User ux2 = User.getById(createVerifiedUser("fn", "ln", createUniqueName() + "@example.org", TEST_PASSWORD));
-        assertEquals(0, g.getMembers(0, 10).length);
+        assertEquals(0, g.getMembers(0, 10).length + start);
         ux.grantGroup(ux, g);
-        assertEquals(1, g.getMembers(0, 10).length);
+        assertEquals(1, g.getMembers(0, 10).length + start);
         ux2.grantGroup(ux, g);
-        assertEquals(2, g.getMembers(0, 10).length);
+        assertEquals(2, g.getMembers(0, 10).length + start);
         ux2.revokeGroup(ux, g);
-        assertEquals(1, g.getMembers(0, 10).length);
+        assertEquals(1, g.getMembers(0, 10).length + start);
         ux.revokeGroup(ux, g);
-        assertEquals(0, g.getMembers(0, 10).length);
+        assertEquals(0, g.getMembers(0, 10).length + start);
 
     }
 
index bd35e821324dc0eff83b2d23be45f422258ac4f1..96b4e61b06886d689ebf3d0b6d867f80005ae2b1 100644 (file)
@@ -35,7 +35,6 @@ import javax.net.ssl.X509KeyManager;
 
 import org.cacert.gigi.DevelLauncher;
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.database.SQLFileManager.ImportType;
@@ -274,10 +273,10 @@ public class ManagedTest extends ConfiguredTest {
         try {
             ter.receive().verify();
 
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id` FROM `users` WHERE `email`=?");
-            ps.setString(1, email);
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `id` FROM `users` WHERE `email`=?")) {
+                ps.setString(1, email);
 
-            try (GigiResultSet rs = ps.executeQuery()) {
+                GigiResultSet rs = ps.executeQuery();
                 if (rs.next()) {
                     return rs.getInt(1);
                 }
@@ -319,15 +318,17 @@ public class ManagedTest extends ConfiguredTest {
     }
 
     public static void makeAssurer(int uid) {
-        GigiPreparedStatement ps1 = DatabaseConnection.getInstance().prepare("INSERT INTO `cats_passed` SET `user_id`=?, `variant_id`=?");
-        ps1.setInt(1, uid);
-        ps1.setInt(2, 1);
-        ps1.execute();
-
-        GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("INSERT INTO `notary` SET `from`=?, `to`=?, points='100'");
-        ps2.setInt(1, uid);
-        ps2.setInt(2, uid);
-        ps2.execute();
+        try (GigiPreparedStatement ps1 = new GigiPreparedStatement("INSERT INTO `cats_passed` SET `user_id`=?, `variant_id`=?")) {
+            ps1.setInt(1, uid);
+            ps1.setInt(2, 1);
+            ps1.execute();
+        }
+
+        try (GigiPreparedStatement ps2 = new GigiPreparedStatement("INSERT INTO `notary` SET `from`=?, `to`=?, points='100'")) {
+            ps2.setInt(1, uid);
+            ps2.setInt(2, uid);
+            ps2.execute();
+        }
     }
 
     protected static String stripCookie(String headerField) {
index 14e002146098561b75d081fcdb22c338f9facc72..fd5de700daa9d0c87a0443cda2c3c097f54cabc4 100644 (file)
@@ -12,7 +12,6 @@ import java.sql.SQLException;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.pages.account.domain.DomainOverview;
@@ -34,15 +33,16 @@ public abstract class PingTest extends ClientTest {
     }
 
     protected void waitForPings(int count) throws SQLException, InterruptedException {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT COUNT(*) FROM `domainPinglog`");
-        long start = System.currentTimeMillis();
-        while (System.currentTimeMillis() - start < 10000) {
-            GigiResultSet rs = ps.executeQuery();
-            rs.next();
-            if (rs.getInt(1) >= count) {
-                break;
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT COUNT(*) FROM `domainPinglog`")) {
+            long start = System.currentTimeMillis();
+            while (System.currentTimeMillis() - start < 10000) {
+                GigiResultSet rs = ps.executeQuery();
+                rs.next();
+                if (rs.getInt(1) >= count) {
+                    break;
+                }
+                Thread.sleep(200);
             }
-            Thread.sleep(200);
         }
     }
 
index 03f157f33b15d8d3de1e6fb08ba5ecd07244336c..740c12c6ae10f947dc853ca3db6d0142d5902fd1 100644 (file)
@@ -6,7 +6,6 @@ import java.sql.SQLException;
 import java.util.Date;
 
 import org.cacert.gigi.GigiApiException;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.dbObjects.User;
 import org.cacert.gigi.output.DateSelector;
@@ -64,9 +63,10 @@ public class TestNotary extends ManagedTest {
             users[i] = User.getById(id);
         }
         int id = createAssuranceUser("fn", "ln", createUniqueName() + "@email.org", TEST_PASSWORD);
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `users` SET dob=NOW() - interval '15 years' WHERE id=?");
-        ps.setInt(1, id);
-        ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("UPDATE `users` SET dob=NOW() - interval '15 years' WHERE id=?")) {
+            ps.setInt(1, id);
+            ps.execute();
+        }
         User assurer = User.getById(id);
         for (int i = 0; i < users.length; i++) {
             assuranceFail(assurer, users[i], -1, "test-notary", "2014-01-01");
index 2992e05c9bf0eb9b70e4cb60194ce5ccf89c0a1f..7ff4ea6dd76176bdc9e577c8610e4eb4c655e557 100644 (file)
@@ -5,7 +5,6 @@ import static org.junit.Assert.*;
 
 import java.io.IOException;
 
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
 import org.cacert.gigi.testUtils.ManagedTest;
@@ -20,18 +19,20 @@ public class TestPasswordMigration extends ManagedTest {
 
     @Test
     public void testPasswordMigration() throws IOException {
-        GigiPreparedStatement stmt = DatabaseConnection.getInstance().prepare("UPDATE users SET `password`=? WHERE id=?");
-        stmt.setString(1, PasswordHash.sha1("a"));
-        stmt.setInt(2, ru.getUser().getId());
-        stmt.execute();
+        try (GigiPreparedStatement stmt = new GigiPreparedStatement("UPDATE users SET `password`=? WHERE id=?")) {
+            stmt.setString(1, PasswordHash.sha1("a"));
+            stmt.setInt(2, ru.getUser().getId());
+            stmt.execute();
+        }
         String cookie = login(ru.getUser().getEmail(), "a");
         assertTrue(isLoggedin(cookie));
 
-        stmt = DatabaseConnection.getInstance().prepare("SELECT `password` FROM users WHERE id=?");
-        stmt.setInt(1, ru.getUser().getId());
-        GigiResultSet res = stmt.executeQuery();
-        assertTrue(res.next());
-        String newHash = res.getString(1);
-        assertThat(newHash, containsString("$"));
+        try (GigiPreparedStatement stmt = new GigiPreparedStatement("SELECT `password` FROM users WHERE id=?")) {
+            stmt.setInt(1, ru.getUser().getId());
+            GigiResultSet res = stmt.executeQuery();
+            assertTrue(res.next());
+            String newHash = res.getString(1);
+            assertThat(newHash, containsString("$"));
+        }
     }
 }
index da506a6ae08d1d717689e2947a32ddfdaea7503f..711f48e21137e1c0df5b60499411ef53bfc5380d 100644 (file)
@@ -25,7 +25,6 @@ import javax.servlet.http.HttpServletResponse;
 import org.cacert.gigi.Gigi;
 import org.cacert.gigi.GigiApiException;
 import org.cacert.gigi.crypto.SPKAC;
-import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.dbObjects.Certificate;
 import org.cacert.gigi.dbObjects.Certificate.CertificateStatus;
@@ -96,24 +95,25 @@ public class Manager extends Page {
         }
         assurers = new User[10];
         try {
-            GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?");
-            for (int i = 0; i < assurers.length; i++) {
-                String mail = "test-assurer" + i + "@example.com";
-                User u = User.getByEmail(mail);
-                if (u == null) {
-                    System.out.println("Creating assurer");
-                    createUser(mail);
-                    u = User.getByEmail(mail);
-                    passCATS(u);
-                    ps.setInt(1, u.getId());
-                    ps.setInt(2, u.getId());
-                    ps.setInt(3, 100);
-                    ps.setString(4, "Manager init code");
-                    ps.setString(5, "1990-01-01");
-                    ps.execute();
-                }
-                assurers[i] = u;
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?")) {
+                for (int i = 0; i < assurers.length; i++) {
+                    String mail = "test-assurer" + i + "@example.com";
+                    User u = User.getByEmail(mail);
+                    if (u == null) {
+                        System.out.println("Creating assurer");
+                        createUser(mail);
+                        u = User.getByEmail(mail);
+                        passCATS(u);
+                        ps.setInt(1, u.getId());
+                        ps.setInt(2, u.getId());
+                        ps.setInt(3, 100);
+                        ps.setString(4, "Manager init code");
+                        ps.setString(5, "1990-01-01");
+                        ps.execute();
+                    }
+                    assurers[i] = u;
 
+                }
             }
         } catch (ReflectiveOperationException | GigiApiException e) {
             e.printStackTrace();
@@ -122,9 +122,10 @@ public class Manager extends Page {
     }
 
     private void passCATS(User u) {
-        GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO cats_passed SET user_id=?, variant_id=1");
-        ps.setInt(1, u.getId());
-        ps.execute();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO cats_passed SET user_id=?, variant_id=1")) {
+            ps.setInt(1, u.getId());
+            ps.execute();
+        }
     }
 
     private static Manager instance;
index d7bfee5fafdab8aab2c8cab356a5a13395d5cff1..a0c3898369391b0a14bc019326373402f140aa8d 100644 (file)
@@ -13,8 +13,6 @@ import java.math.BigInteger;
 import java.nio.file.Paths;
 import java.security.GeneralSecurityException;
 import java.security.KeyFactory;
-import java.security.KeyPair;
-import java.security.KeyPairGenerator;
 import java.security.NoSuchAlgorithmException;
 import java.security.PrivateKey;
 import java.security.PublicKey;
@@ -27,7 +25,6 @@ import java.sql.SQLException;
 import java.sql.Timestamp;
 import java.text.ParseException;
 import java.text.SimpleDateFormat;
-import java.util.Arrays;
 import java.util.Base64;
 import java.util.Calendar;
 import java.util.Date;
@@ -45,7 +42,6 @@ import org.cacert.gigi.crypto.SPKAC;
 import org.cacert.gigi.database.DatabaseConnection;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
-import org.cacert.gigi.dbObjects.Certificate;
 import org.cacert.gigi.dbObjects.Certificate.CSRType;
 import org.cacert.gigi.dbObjects.Certificate.SANType;
 import org.cacert.gigi.dbObjects.Certificate.SubjectAlternateName;
@@ -93,19 +89,6 @@ public class SimpleSigner {
     }
 
     public static void main(String[] args) throws IOException, SQLException, InterruptedException {
-        if (false) {
-            try {
-                KeyPairGenerator kpg = KeyPairGenerator.getInstance("RSA");
-                kpg.initialize(2048);
-                KeyPair kp = kpg.generateKeyPair();
-                X500Principal xp = new X500Principal(genX500Name(Certificate.buildDN("CN", "uiae")).getEncoded());
-                byte[] i = generateCert(kp.getPublic(), kp.getPrivate(), Certificate.buildDN("CN", "uiae"), xp, Arrays.<SubjectAlternateName>asList(), new Date(), new Date(System.currentTimeMillis() + 1000 * 60 * 60), Digest.SHA512, "clientAuth");
-                System.out.println(Base64.getMimeEncoder().encodeToString(i));
-            } catch (GeneralSecurityException e) {
-                e.printStackTrace();
-            }
-            return;
-        }
         Properties p = new Properties();
         try (Reader reader = new InputStreamReader(new FileInputStream("config/gigi.properties"), "UTF-8")) {
             p.load(reader);
@@ -133,24 +116,24 @@ public class SimpleSigner {
             throw new IllegalStateException("already running");
         }
         running = true;
-        readyCerts = DatabaseConnection.getInstance().prepare("SELECT certs.id AS id, certs.csr_name, jobs.id AS jobid, csr_type, md, `executeFrom`, `executeTo`, profile FROM jobs " + //
+        readyCerts = new GigiPreparedStatement("SELECT certs.id AS id, certs.csr_name, jobs.id AS jobid, csr_type, md, `executeFrom`, `executeTo`, profile FROM jobs " + //
                 "INNER JOIN certs ON certs.id=jobs.`targetId` " + //
                 "INNER JOIN profiles ON profiles.id=certs.profile " + //
                 "WHERE jobs.state='open' "//
                 + "AND task='sign'");
 
-        getSANSs = DatabaseConnection.getInstance().prepare("SELECT contents, type FROM `subjectAlternativeNames` " + //
+        getSANSs = new GigiPreparedStatement("SELECT contents, type FROM `subjectAlternativeNames` " + //
                 "WHERE `certId`=?");
 
-        updateMail = DatabaseConnection.getInstance().prepare("UPDATE certs SET crt_name=?," + " created=NOW(), serial=?, caid=? WHERE id=?");
-        warnMail = DatabaseConnection.getInstance().prepare("UPDATE jobs SET warning=warning+1, state=IF(warning<3, 'open','error') WHERE id=?");
+        updateMail = new GigiPreparedStatement("UPDATE certs SET crt_name=?," + " created=NOW(), serial=?, caid=? WHERE id=?");
+        warnMail = new GigiPreparedStatement("UPDATE jobs SET warning=warning+1, state=IF(warning<3, 'open','error') WHERE id=?");
 
-        revoke = DatabaseConnection.getInstance().prepare("SELECT certs.id, certs.csr_name,jobs.id FROM jobs INNER JOIN certs ON jobs.`targetId`=certs.id" + " WHERE jobs.state='open' AND task='revoke'");
-        revokeCompleted = DatabaseConnection.getInstance().prepare("UPDATE certs SET revoked=NOW() WHERE id=?");
+        revoke = new GigiPreparedStatement("SELECT certs.id, certs.csr_name,jobs.id FROM jobs INNER JOIN certs ON jobs.`targetId`=certs.id" + " WHERE jobs.state='open' AND task='revoke'");
+        revokeCompleted = new GigiPreparedStatement("UPDATE certs SET revoked=NOW() WHERE id=?");
 
-        finishJob = DatabaseConnection.getInstance().prepare("UPDATE jobs SET state='done' WHERE id=?");
+        finishJob = new GigiPreparedStatement("UPDATE jobs SET state='done' WHERE id=?");
 
-        locateCA = DatabaseConnection.getInstance().prepare("SELECT id FROM cacerts WHERE keyname=?");
+        locateCA = new GigiPreparedStatement("SELECT id FROM cacerts WHERE keyname=?");
 
         runner = new Thread() {
 
@@ -193,7 +176,6 @@ public class SimpleSigner {
         boolean worked = false;
         while (rs.next()) {
             int id = rs.getInt(1);
-            File crt = KeyStorage.locateCrt(id);
             worked = true;
             System.out.println("Revoke faked: " + id);
             revokeCompleted.setInt(1, id);
@@ -297,15 +279,16 @@ public class SimpleSigner {
                 String ca = caP.getProperty("ca") + "_2015_1";
 
                 HashMap<String, String> subj = new HashMap<>();
-                GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT name, value FROM `certAvas` WHERE `certId`=?");
-                ps.setInt(1, rs.getInt("id"));
-                GigiResultSet rs2 = ps.executeQuery();
-                while (rs2.next()) {
-                    String name = rs2.getString("name");
-                    if (name.equals("EMAIL")) {
-                        name = "emailAddress";
+                try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT name, value FROM `certAvas` WHERE `certId`=?")) {
+                    ps.setInt(1, rs.getInt("id"));
+                    GigiResultSet rs2 = ps.executeQuery();
+                    while (rs2.next()) {
+                        String name = rs2.getString("name");
+                        if (name.equals("EMAIL")) {
+                            name = "emailAddress";
+                        }
+                        subj.put(name, rs2.getString("value"));
                     }
-                    subj.put(name, rs2.getString("value"));
                 }
                 if (subj.size() == 0) {
                     subj.put("CN", "<empty>");
@@ -330,7 +313,6 @@ public class SimpleSigner {
                 }
                 PrivateKey i = loadOpensslKey(new File("signer/ca/" + ca + "/ca.key"));
 
-                String[] call;
                 X509Certificate root = (X509Certificate) CertificateFactory.getInstance("X509").generateCertificate(new FileInputStream("signer/ca/" + ca + "/ca.crt"));
                 byte[] cert = generateCert(pk, i, subj, root.getSubjectX500Principal(), altnames, fromDate, toDate, Digest.valueOf(rs.getString("md").toUpperCase()), caP.getProperty("eku"));
                 PrintWriter out = new PrintWriter(crt);
@@ -338,18 +320,6 @@ public class SimpleSigner {
                 out.println(Base64.getMimeEncoder().encodeToString(cert));
                 out.println("-----END CERTIFICATE-----");
                 out.close();
-                // synchronized (sdf) {
-                /*
-                 * call = new String[] { "openssl", "ca",// "-in", "../../" +
-                 * csrname,// "-cert", "../" + ca + ".crt",// "-keyfile", "../"
-                 * + ca + ".key",// "-out", "../../" + crt.getPath(),// "-utf8",
-                 * "-startdate", sdf.format(fromDate),// "-enddate",
-                 * sdf.format(toDate),// "-batch",// "-md",
-                 * rs.getString("md"),// "-extfile", "../" + f.getName(),//
-                 * "-subj", Certificate.stringifyDN(subj),// "-config",
-                 * "../selfsign.config"// };
-                 */
-                // }
 
                 try (InputStream is = new FileInputStream(crt)) {
                     locateCA.setString(1, ca);
@@ -490,17 +460,20 @@ public class SimpleSigner {
             // ByteArrayInputStream(cert.toByteArray()));
             // c.verify(pk); only for self-signeds
 
-            return cert.toByteArray();
+            byte[] res = cert.toByteArray();
+            cert.close();
+            return res;
         }
 
     }
 
     private static byte[] generateKU() throws IOException {
-        DerOutputStream dos = new DerOutputStream();
-        dos.putBitString(new byte[] {
-            (byte) 0b10101000
-        });
-        return dos.toByteArray();
+        try (DerOutputStream dos = new DerOutputStream()) {
+            dos.putBitString(new byte[] {
+                (byte) 0b10101000
+            });
+            return dos.toByteArray();
+        }
     }
 
     private static byte[] generateEKU(String eku) throws IOException {
@@ -585,9 +558,11 @@ public class SimpleSigner {
             };
             break;
         default:
+            dos.close();
             throw new Error("unknown RDN-type: " + key);
         }
         RDN rdn = new RDN(new AVA(new ObjectIdentifier(oid), new DerValue(dos.toByteArray())));
+        dos.close();
         return rdn;
     }
 
@@ -608,6 +583,7 @@ public class SimpleSigner {
             } else if (san.getType() == SANType.EMAIL) {
                 type = (byte) GeneralNameInterface.NAME_RFC822;
             } else {
+                SANContent.close();
                 throw new Error("" + san.getType());
             }
             SANContent.write(DerValue.createTag(DerValue.TAG_CONTEXT, false, type), san.getName().getBytes("UTF-8"));
@@ -615,6 +591,8 @@ public class SimpleSigner {
         DerOutputStream SANSeqContent = new DerOutputStream();
         SANSeqContent.write(DerValue.tag_Sequence, SANContent);
         byte[] byteArray = SANSeqContent.toByteArray();
+        SANContent.close();
+        SANSeqContent.close();
         return byteArray;
     }
 }