From a0232b6e40e7e09767f0444d24e18bf12dafc362 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Felix=20D=C3=B6rre?= Date: Thu, 19 Nov 2015 14:24:44 +0100 Subject: [PATCH] fix: SQL change database call pattern --- src/org/cacert/gigi/Gigi.java | 7 + .../gigi/database/DatabaseConnection.java | 95 ++++-- .../gigi/database/GigiPreparedStatement.java | 36 ++- .../cacert/gigi/dbObjects/CACertificate.java | 88 +++--- src/org/cacert/gigi/dbObjects/CATS.java | 29 +- .../cacert/gigi/dbObjects/Certificate.java | 158 +++++----- .../gigi/dbObjects/CertificateOwner.java | 97 +++--- .../gigi/dbObjects/CertificateProfile.java | 59 ++-- src/org/cacert/gigi/dbObjects/Domain.java | 133 ++++---- .../dbObjects/DomainPingConfiguration.java | 48 +-- .../cacert/gigi/dbObjects/EmailAddress.java | 59 ++-- src/org/cacert/gigi/dbObjects/Group.java | 26 +- src/org/cacert/gigi/dbObjects/Job.java | 64 ++-- .../cacert/gigi/dbObjects/Organisation.java | 117 +++---- .../cacert/gigi/dbObjects/SupportedUser.java | 14 +- src/org/cacert/gigi/dbObjects/User.java | 290 +++++++++--------- src/org/cacert/gigi/email/EmailProvider.java | 27 +- src/org/cacert/gigi/pages/LoginPage.java | 32 +- src/org/cacert/gigi/pages/main/Signup.java | 53 ++-- src/org/cacert/gigi/pages/wot/AssurePage.java | 12 +- src/org/cacert/gigi/ping/DomainPinger.java | 27 +- src/org/cacert/gigi/ping/PingerDaemon.java | 3 +- src/org/cacert/gigi/util/Notary.java | 49 +-- .../cacert/gigi/TestUserGroupMembership.java | 57 ++-- .../cacert/gigi/testUtils/ManagedTest.java | 27 +- tests/org/cacert/gigi/testUtils/PingTest.java | 18 +- tests/org/cacert/gigi/util/TestNotary.java | 8 +- .../gigi/util/TestPasswordMigration.java | 23 +- .../org/cacert/gigi/pages/Manager.java | 43 +-- .../org/cacert/gigi/util/SimpleSigner.java | 84 ++--- 30 files changed, 936 insertions(+), 847 deletions(-) diff --git a/src/org/cacert/gigi/Gigi.java b/src/org/cacert/gigi/Gigi.java index b7605e87..209175ab 100644 --- a/src/org/cacert/gigi/Gigi.java +++ b/src/org/cacert/gigi/Gigi.java @@ -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 { diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java index b8c09e5a..bccae86f 100644 --- a/src/org/cacert/gigi/database/DatabaseConnection.java +++ b/src/org/cacert/gigi/database/DatabaseConnection.java @@ -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 statements = new HashMap(); + private HashMap statements = new HashMap(); + + HashSet 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 instances = new ThreadLocal() { - - @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 e : statements.entrySet()) { + if (e.getValue() == ps) { + writer.println("
"); + writer.println(e.getKey()); + } + } + } + } } diff --git a/src/org/cacert/gigi/database/GigiPreparedStatement.java b/src/org/cacert/gigi/database/GigiPreparedStatement.java index 6d967648..411b1fd5 100644 --- a/src/org/cacert/gigi/database/GigiPreparedStatement.java +++ b/src/org/cacert/gigi/database/GigiPreparedStatement.java @@ -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); + + } + } diff --git a/src/org/cacert/gigi/dbObjects/CACertificate.java b/src/org/cacert/gigi/dbObjects/CACertificate.java index cc5bcd89..41401b6d 100644 --- a/src/org/cacert/gigi/dbObjects/CACertificate.java +++ b/src/org/cacert/gigi/dbObjects/CACertificate.java @@ -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); } } } diff --git a/src/org/cacert/gigi/dbObjects/CATS.java b/src/org/cacert/gigi/dbObjects/CATS.java index fd715ca5..0bda2e67 100644 --- a/src/org/cacert/gigi/dbObjects/CATS.java +++ b/src/org/cacert/gigi/dbObjects/CATS.java @@ -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(); + } } } diff --git a/src/org/cacert/gigi/dbObjects/Certificate.java b/src/org/cacert/gigi/dbObjects/Certificate.java index f355e67a..8e66c7f3 100644 --- a/src/org/cacert/gigi/dbObjects/Certificate.java +++ b/src/org/cacert/gigi/dbObjects/Certificate.java @@ -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 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 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 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; diff --git a/src/org/cacert/gigi/dbObjects/CertificateOwner.java b/src/org/cacert/gigi/dbObjects/CertificateOwner.java index 2adda5ee..8ba04fee 100644 --- a/src/org/cacert/gigi/dbObjects/CertificateOwner.java +++ b/src/org/cacert/gigi/dbObjects/CertificateOwner.java @@ -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 data = new LinkedList(); + try (GigiResultSet rs = ps.executeQuery()) { + LinkedList data = new LinkedList(); - 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 data = new LinkedList(); 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 entries = new LinkedList(); + 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 entries = new LinkedList(); - 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; } } diff --git a/src/org/cacert/gigi/dbObjects/CertificateProfile.java b/src/org/cacert/gigi/dbObjects/CertificateProfile.java index 87f55f74..6f57b3ec 100644 --- a/src/org/cacert/gigi/dbObjects/CertificateProfile.java +++ b/src/org/cacert/gigi/dbObjects/CertificateProfile.java @@ -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); } diff --git a/src/org/cacert/gigi/dbObjects/Domain.java b/src/org/cacert/gigi/dbObjects/Domain.java index 6093f6d8..1d31db61 100644 --- a/src/org/cacert/gigi/dbObjects/Domain.java +++ b/src/org/cacert/gigi/dbObjects/Domain.java @@ -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 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; + } } } diff --git a/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java b/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java index 145473fd..baa7b7b0 100644 --- a/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java +++ b/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java @@ -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 { diff --git a/src/org/cacert/gigi/dbObjects/EmailAddress.java b/src/org/cacert/gigi/dbObjects/EmailAddress.java index b7bb080f..cf007312 100644 --- a/src/org/cacert/gigi/dbObjects/EmailAddress.java +++ b/src/org/cacert/gigi/dbObjects/EmailAddress.java @@ -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 { diff --git a/src/org/cacert/gigi/dbObjects/Group.java b/src/org/cacert/gigi/dbObjects/Group.java index 8a2c1de4..07d3c11f 100644 --- a/src/org/cacert/gigi/dbObjects/Group.java +++ b/src/org/cacert/gigi/dbObjects/Group.java @@ -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; } } diff --git a/src/org/cacert/gigi/dbObjects/Job.java b/src/org/cacert/gigi/dbObjects/Job.java index 9104c131..bb357a8d 100644 --- a/src/org/cacert/gigi/dbObjects/Job.java +++ b/src/org/cacert/gigi/dbObjects/Job.java @@ -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; } } diff --git a/src/org/cacert/gigi/dbObjects/Organisation.java b/src/org/cacert/gigi/dbObjects/Organisation.java index b51d2838..6b5b28f4 100644 --- a/src/org/cacert/gigi/dbObjects/Organisation.java +++ b/src/org/cacert/gigi/dbObjects/Organisation.java @@ -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 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 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 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; diff --git a/src/org/cacert/gigi/dbObjects/SupportedUser.java b/src/org/cacert/gigi/dbObjects/SupportedUser.java index decf5527..e957aa08 100644 --- a/src/org/cacert/gigi/dbObjects/SupportedUser.java +++ b/src/org/cacert/gigi/dbObjects/SupportedUser.java @@ -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() { diff --git a/src/org/cacert/gigi/dbObjects/User.java b/src/org/cacert/gigi/dbObjects/User.java index e6afc796..55e567f5 100644 --- a/src/org/cacert/gigi/dbObjects/User.java +++ b/src/org/cacert/gigi/dbObjects/User.java @@ -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 assurances = new LinkedList(); 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 assurances = new LinkedList(); + try (GigiResultSet res = query.executeQuery()) { + List assurances = new LinkedList(); - 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 getOrganisations() { List 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 results = new LinkedList(); - 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 data = new LinkedList(); 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 entries = new LinkedList(); + 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 entries = new LinkedList(); + + 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(); } } - } diff --git a/src/org/cacert/gigi/email/EmailProvider.java b/src/org/cacert/gigi/email/EmailProvider.java index ff9fde7a..e2c4d5d6 100644 --- a/src/org/cacert/gigi/email/EmailProvider.java +++ b/src/org/cacert/gigi/email/EmailProvider.java @@ -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; } diff --git a/src/org/cacert/gigi/pages/LoginPage.java b/src/org/cacert/gigi/pages/LoginPage.java index 58adcda2..ba6e0eec 100644 --- a/src/org/cacert/gigi/pages/LoginPage.java +++ b/src/org/cacert/gigi/pages/LoginPage.java @@ -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) { diff --git a/src/org/cacert/gigi/pages/main/Signup.java b/src/org/cacert/gigi/pages/main/Signup.java index 50059818..bb72a9cd 100644 --- a/src/org/cacert/gigi/pages/main/Signup.java +++ b/src/org/cacert/gigi/pages/main/Signup.java @@ -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(); diff --git a/src/org/cacert/gigi/pages/wot/AssurePage.java b/src/org/cacert/gigi/pages/wot/AssurePage.java index efb246b6..39a5aa90 100644 --- a/src/org/cacert/gigi/pages/wot/AssurePage.java +++ b/src/org/cacert/gigi/pages/wot/AssurePage.java @@ -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(""); } - rs.close(); - } finally { - if (rs != null) { - rs.close(); - } } } } diff --git a/src/org/cacert/gigi/ping/DomainPinger.java b/src/org/cacert/gigi/ping/DomainPinger.java index 7676bcdb..94233492 100644 --- a/src/org/cacert/gigi/ping/DomainPinger.java +++ b/src/org/cacert/gigi/ping/DomainPinger.java @@ -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(); + } } } diff --git a/src/org/cacert/gigi/ping/PingerDaemon.java b/src/org/cacert/gigi/ping/PingerDaemon.java index 71d7140a..4936bfc1 100644 --- a/src/org/cacert/gigi/ping/PingerDaemon.java +++ b/src/org/cacert/gigi/ping/PingerDaemon.java @@ -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()); diff --git a/src/org/cacert/gigi/util/Notary.java b/src/org/cacert/gigi/util/Notary.java index 3823cf7d..f229bdd4 100644 --- a/src/org/cacert/gigi/util/Notary.java +++ b/src/org/cacert/gigi/util/Notary.java @@ -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(); } diff --git a/tests/org/cacert/gigi/TestUserGroupMembership.java b/tests/org/cacert/gigi/TestUserGroupMembership.java index 3808df5d..7ac06ff3 100644 --- a/tests/org/cacert/gigi/TestUserGroupMembership.java +++ b/tests/org/cacert/gigi/TestUserGroupMembership.java @@ -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); } diff --git a/tests/org/cacert/gigi/testUtils/ManagedTest.java b/tests/org/cacert/gigi/testUtils/ManagedTest.java index bd35e821..96b4e61b 100644 --- a/tests/org/cacert/gigi/testUtils/ManagedTest.java +++ b/tests/org/cacert/gigi/testUtils/ManagedTest.java @@ -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) { diff --git a/tests/org/cacert/gigi/testUtils/PingTest.java b/tests/org/cacert/gigi/testUtils/PingTest.java index 14e00214..fd5de700 100644 --- a/tests/org/cacert/gigi/testUtils/PingTest.java +++ b/tests/org/cacert/gigi/testUtils/PingTest.java @@ -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); } } diff --git a/tests/org/cacert/gigi/util/TestNotary.java b/tests/org/cacert/gigi/util/TestNotary.java index 03f157f3..740c12c6 100644 --- a/tests/org/cacert/gigi/util/TestNotary.java +++ b/tests/org/cacert/gigi/util/TestNotary.java @@ -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"); diff --git a/tests/org/cacert/gigi/util/TestPasswordMigration.java b/tests/org/cacert/gigi/util/TestPasswordMigration.java index 2992e05c..7ff4ea6d 100644 --- a/tests/org/cacert/gigi/util/TestPasswordMigration.java +++ b/tests/org/cacert/gigi/util/TestPasswordMigration.java @@ -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("$")); + } } } diff --git a/util-testing/org/cacert/gigi/pages/Manager.java b/util-testing/org/cacert/gigi/pages/Manager.java index da506a6a..711f48e2 100644 --- a/util-testing/org/cacert/gigi/pages/Manager.java +++ b/util-testing/org/cacert/gigi/pages/Manager.java @@ -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; diff --git a/util-testing/org/cacert/gigi/util/SimpleSigner.java b/util-testing/org/cacert/gigi/util/SimpleSigner.java index d7bfee5f..a0c38983 100644 --- a/util-testing/org/cacert/gigi/util/SimpleSigner.java +++ b/util-testing/org/cacert/gigi/util/SimpleSigner.java @@ -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.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 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", ""); @@ -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; } } -- 2.39.2