From 03d27b5bdaea6253e674cdf2779465873b657900 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Felix=20D=C3=B6rre?= Date: Mon, 14 Sep 2015 23:11:07 +0200 Subject: [PATCH] upd: convert to PostgreSQL --- .../gigi/database/DatabaseConnection.java | 78 +- .../cacert/gigi/database/SQLFileManager.java | 8 +- .../cacert/gigi/database/tableStructure.sql | 680 ++++++++++-------- .../cacert/gigi/dbObjects/CACertificate.java | 8 +- .../cacert/gigi/dbObjects/Certificate.java | 16 +- .../gigi/dbObjects/CertificateOwner.java | 8 +- .../gigi/dbObjects/CertificateProfile.java | 6 +- src/org/cacert/gigi/dbObjects/Domain.java | 17 +- .../dbObjects/DomainPingConfiguration.java | 2 +- .../cacert/gigi/dbObjects/EmailAddress.java | 2 +- src/org/cacert/gigi/dbObjects/Group.java | 2 +- .../cacert/gigi/dbObjects/Organisation.java | 10 +- src/org/cacert/gigi/dbObjects/User.java | 25 +- src/org/cacert/gigi/email/EmailProvider.java | 6 +- src/org/cacert/gigi/pages/LoginPage.java | 7 +- src/org/cacert/gigi/pages/main/Signup.java | 16 +- src/org/cacert/gigi/pages/wot/AssurePage.java | 13 +- src/org/cacert/gigi/ping/PingerDaemon.java | 4 +- src/org/cacert/gigi/util/Job.java | 4 +- src/org/cacert/gigi/util/Notary.java | 4 +- src/org/cacert/gigi/util/PasswordHash.java | 2 +- .../cacert/gigi/TestUserGroupMembership.java | 2 +- .../gigi/pages/orga/TestOrgaManagement.java | 2 +- .../cacert/gigi/testUtils/ManagedTest.java | 2 +- tests/org/cacert/gigi/util/TestNotary.java | 2 +- .../gigi/util/TestPasswordMigration.java | 4 +- .../org/cacert/gigi/DevelLauncher.java | 5 + .../org/cacert/gigi/util/SimpleSigner.java | 12 +- 28 files changed, 526 insertions(+), 421 deletions(-) diff --git a/src/org/cacert/gigi/database/DatabaseConnection.java b/src/org/cacert/gigi/database/DatabaseConnection.java index 8e20f2a7..9a8b10ea 100644 --- a/src/org/cacert/gigi/database/DatabaseConnection.java +++ b/src/org/cacert/gigi/database/DatabaseConnection.java @@ -4,12 +4,14 @@ import java.io.IOException; import java.io.InputStream; 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.Properties; +import java.util.StringJoiner; +import java.util.regex.Matcher; +import java.util.regex.Pattern; import org.cacert.gigi.database.SQLFileManager.ImportType; @@ -39,15 +41,8 @@ public class DatabaseConnection { private void tryConnect() { try { - c = DriverManager.getConnection(credentials.getProperty("sql.url") + "?zeroDateTimeBehavior=convertToNull", credentials.getProperty("sql.user"), credentials.getProperty("sql.password")); - PreparedStatement ps = c.prepareStatement("SET SESSION wait_timeout=?, time_zone='+0:00';"); - try { - ps.setInt(1, CONNECTION_TIMEOUT); - ps.execute(); - adHoc = c.createStatement(); - } finally { - ps.close(); - } + c = DriverManager.getConnection(credentials.getProperty("sql.url") + "?socketTimeout=" + CONNECTION_TIMEOUT, credentials.getProperty("sql.user"), credentials.getProperty("sql.password")); + adHoc = c.createStatement(); } catch (SQLException e) { e.printStackTrace(); } @@ -55,10 +50,26 @@ public class DatabaseConnection { public GigiPreparedStatement prepare(String query) { ensureOpen(); + query = preprocessQuery(query); GigiPreparedStatement statement = statements.get(query); if (statement == null) { try { - statement = new GigiPreparedStatement(c.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)); + statement = new GigiPreparedStatement(c.prepareStatement(query, query.startsWith("SELECT ") ? Statement.NO_GENERATED_KEYS : Statement.RETURN_GENERATED_KEYS)); + } catch (SQLException e) { + throw new Error(e); + } + statements.put(query, statement); + } + return statement; + } + + public GigiPreparedStatement prepareScrollable(String query) { + 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); } @@ -105,7 +116,7 @@ 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(); + 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); @@ -119,6 +130,10 @@ public class DatabaseConnection { upgrade(version); } + public void beginTransaction() throws SQLException { + c.setAutoCommit(false); + } + private static void upgrade(int version) { try { Statement s = getInstance().c.createStatement(); @@ -146,10 +161,6 @@ public class DatabaseConnection { } } - public void beginTransaction() throws SQLException { - c.setAutoCommit(false); - } - public void commitTransaction() throws SQLException { c.commit(); c.setAutoCommit(true); @@ -165,4 +176,39 @@ public class DatabaseConnection { e.printStackTrace(); } } + + public static final String preprocessQuery(String originalQuery) { + originalQuery = originalQuery.replace('`', '"'); + if (originalQuery.matches("^INSERT INTO [^ ]+ SET .*")) { + Pattern p = Pattern.compile("INSERT INTO ([^ ]+) SET (.*)"); + Matcher m = p.matcher(originalQuery); + if (m.matches()) { + String replacement = "INSERT INTO " + toIdentifier(m.group(1)); + String[] parts = m.group(2).split(","); + StringJoiner columns = new StringJoiner(", "); + StringJoiner values = new StringJoiner(", "); + for (int i = 0; i < parts.length; i++) { + String[] split = parts[i].split("=", 2); + columns.add(toIdentifier(split[0])); + values.add(split[1]); + } + replacement += "(" + columns.toString() + ") VALUES(" + values.toString() + ")"; + return replacement; + } + } + + // + return originalQuery; + } + + private static CharSequence toIdentifier(String ident) { + ident = ident.trim(); + if ( !ident.startsWith("\"")) { + ident = "\"" + ident; + } + if ( !ident.endsWith("\"")) { + ident = ident + "\""; + } + return ident; + } } diff --git a/src/org/cacert/gigi/database/SQLFileManager.java b/src/org/cacert/gigi/database/SQLFileManager.java index 9aaebfae..400884af 100644 --- a/src/org/cacert/gigi/database/SQLFileManager.java +++ b/src/org/cacert/gigi/database/SQLFileManager.java @@ -27,9 +27,10 @@ public class SQLFileManager { public static void addFile(Statement stmt, InputStream f, ImportType type) throws IOException, SQLException { String sql = readFile(f); - sql = sql.replaceAll("--[^\n]+\n", "\n"); + sql = sql.replaceAll("--[^\n]*\n", "\n"); + sql = sql.replaceAll("#[^\n]*\n", "\n"); String[] stmts = sql.split(";"); - Pattern p = Pattern.compile("\\s*DROP TABLE IF EXISTS `([^`]+)`"); + Pattern p = Pattern.compile("\\s*DROP TABLE IF EXISTS \"([^\"]+)\""); for (String string : stmts) { Matcher m = p.matcher(string); string = string.trim(); @@ -39,8 +40,9 @@ public class SQLFileManager { if ((string.contains("profiles") || string.contains("cacerts")) && type != ImportType.PRODUCTION) { continue; } + string = DatabaseConnection.preprocessQuery(string); if (m.matches() && type == ImportType.TRUNCATE) { - String sql2 = "TRUNCATE `" + m.group(1) + "`"; + String sql2 = "DELETE FROM \"" + m.group(1) + "\""; stmt.addBatch(sql2); continue; } diff --git a/src/org/cacert/gigi/database/tableStructure.sql b/src/org/cacert/gigi/database/tableStructure.sql index 98133297..f451ede2 100644 --- a/src/org/cacert/gigi/database/tableStructure.sql +++ b/src/org/cacert/gigi/database/tableStructure.sql @@ -1,332 +1,376 @@ -DROP TABLE IF EXISTS `certOwners`; -CREATE TABLE `certOwners` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `modified` timestamp NULL DEFAULT NULL, - `deleted` timestamp NULL DEFAULT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `users`; -CREATE TABLE `users` ( - `id` int(11) NOT NULL, - `email` varchar(255) NOT NULL DEFAULT '', - `password` varchar(255) NOT NULL DEFAULT '', - `fname` varchar(255) NOT NULL DEFAULT '', - `mname` varchar(255) NOT NULL DEFAULT '', - `lname` varchar(255) NOT NULL DEFAULT '', - `suffix` varchar(50) NOT NULL DEFAULT '', - `dob` date NOT NULL DEFAULT '0000-00-00', - `verified` int(1) NOT NULL DEFAULT '0', - `ccid` int(3) NOT NULL DEFAULT '0', - `regid` int(5) NOT NULL DEFAULT '0', - `locid` int(7) NOT NULL DEFAULT '0', - `listme` int(1) NOT NULL DEFAULT '0', - `contactinfo` varchar(255) NOT NULL DEFAULT '', - `language` varchar(5) NOT NULL DEFAULT '', - PRIMARY KEY (`id`), - KEY `ccid` (`ccid`), - KEY `regid` (`regid`), - KEY `locid` (`locid`), - KEY `email` (`email`), - KEY `stats_users_verified` (`verified`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - - -DROP TABLE IF EXISTS `organisations`; -CREATE TABLE IF NOT EXISTS `organisations` ( - `id` int(11) NOT NULL, - `name` varchar(100) NOT NULL, - `state` varchar(2) NOT NULL, - `province` varchar(100) NOT NULL, - `city` varchar(100) NOT NULL, - `contactEmail` varchar(100) NOT NULL, - `creator` int(11) NOT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `domains`; -CREATE TABLE `domains` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `memid` int(11) NOT NULL, - `domain` varchar(255) NOT NULL, - `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `modified` datetime NULL DEFAULT NULL, - `deleted` datetime NULL DEFAULT NULL, - PRIMARY KEY (`id`), - KEY `memid` (`memid`), - KEY `domain` (`domain`), - KEY `stats_domains_deleted` (`deleted`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `emails`; -CREATE TABLE `emails` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `memid` int(11) NOT NULL DEFAULT '0', - `email` varchar(255) NOT NULL DEFAULT '', - `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `modified` datetime NULL DEFAULT NULL, - `deleted` datetime NULL DEFAULT NULL, - `hash` varchar(50) NOT NULL DEFAULT '', - `attempts` int(1) NOT NULL DEFAULT '0', - PRIMARY KEY (`id`), - KEY `memid` (`memid`), - KEY `stats_email_hash` (`hash`), - KEY `stats_email_deleted` (`deleted`), - KEY `email` (`email`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `emailPinglog`; -CREATE TABLE `emailPinglog` ( - `when` datetime NOT NULL, - `uid` int(11) NOT NULL, - `email` varchar(255) NOT NULL, - `type` enum('fast', 'active') NOT NULL, - `status` enum('open', 'success', 'failed') NOT NULL, - `result` varchar(255) NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `pingconfig`; -CREATE TABLE `pingconfig` ( - `id` int(13) NOT NULL AUTO_INCREMENT, - `domainid` int(11) NOT NULL, - `type` enum('email', 'ssl', 'http', 'dns') NOT NULL, - `info` varchar(255) NOT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - - -DROP TABLE IF EXISTS `domainPinglog`; -CREATE TABLE `domainPinglog` ( - `when` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, - `configId` int(13) NOT NULL, - `state` enum('open', 'success', 'failed') NOT NULL, - `challenge` varchar(16), - `result` varchar(255) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `baddomains`; -CREATE TABLE `baddomains` ( - `domain` varchar(255) NOT NULL DEFAULT '' -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - - -DROP TABLE IF EXISTS `alerts`; -CREATE TABLE `alerts` ( - `memid` int(11) NOT NULL DEFAULT '0', - `general` tinyint(1) NOT NULL DEFAULT '0', - `country` tinyint(1) NOT NULL DEFAULT '0', - `regional` tinyint(1) NOT NULL DEFAULT '0', - `radius` tinyint(1) NOT NULL DEFAULT '0', - PRIMARY KEY (`memid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `user_agreements`; -CREATE TABLE `user_agreements` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `memid` int(11) NOT NULL, - `secmemid` int(11) DEFAULT NULL, - `document` varchar(50) DEFAULT NULL, - `date` datetime DEFAULT NULL, - `active` int(1) NOT NULL, - `method` varchar(100) NOT NULL, - `comment` varchar(100) DEFAULT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `certs`; -CREATE TABLE `certs` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `memid` int(11) NOT NULL DEFAULT '0', - `serial` varchar(50) NOT NULL DEFAULT '', - `keytype` char(2) NOT NULL DEFAULT 'NS', - `codesign` tinyint(1) NOT NULL DEFAULT '0', - `md` enum('md5','sha1','sha256','sha512') NOT NULL DEFAULT 'sha512', - `profile` int(3) NOT NULL, - `caid` int(3) NULL DEFAULT NULL, - - `csr_name` varchar(255) NOT NULL DEFAULT '', - `csr_type` enum('CSR', 'SPKAC') NOT NULL, - `crt_name` varchar(255) NOT NULL DEFAULT '', - `created` timestamp NULL DEFAULT NULL, - `modified` datetime NULL DEFAULT NULL, - `revoked` datetime NULL DEFAULT NULL, - `expire` datetime NULL DEFAULT NULL, - `renewed` tinyint(1) NOT NULL DEFAULT '0', - `disablelogin` int(1) NOT NULL DEFAULT '0', - `pkhash` char(40) DEFAULT NULL, - `certhash` char(40) DEFAULT NULL, - `description` varchar(100) NOT NULL DEFAULT '', - PRIMARY KEY (`id`), - KEY `emailcerts_pkhash` (`pkhash`), - KEY `revoked` (`revoked`), - KEY `created` (`created`), - KEY `memid` (`memid`), - KEY `serial` (`serial`), - KEY `stats_emailcerts_expire` (`expire`), - KEY `emailcrt` (`crt_name`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - - -DROP TABLE IF EXISTS `certAvas`; -CREATE TABLE `certAvas` ( - `certid` int(11) NOT NULL, - `name` varchar(20) NOT NULL, - `value` varchar(255) NOT NULL, - - PRIMARY KEY (`certid`, `name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `clientcerts`; -CREATE TABLE `clientcerts` ( - `id` int(11) NOT NULL, - `disablelogin` int(1) NOT NULL DEFAULT '0', - - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `profiles`; -CREATE TABLE `profiles` ( - `id` int(3) NOT NULL AUTO_INCREMENT, - `keyname` varchar(60) NOT NULL, - `include` varchar(200) NOT NULL, - `requires` varchar(200) NOT NULL, - `name` varchar(100) NOT NULL, - PRIMARY KEY (`id`), - UNIQUE (`keyname`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `subjectAlternativeNames`; -CREATE TABLE `subjectAlternativeNames` ( - `certId` int(11) NOT NULL, - `contents` varchar(50) NOT NULL, - `type` enum('email','DNS') NOT NULL -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `cacerts`; -CREATE TABLE `cacerts` ( - `id` int(3) NOT NULL AUTO_INCREMENT, - `keyname` varchar(60) NOT NULL, - `link` varchar(160) NOT NULL, - `parentRoot` int(3) NOT NULL, - `validFrom` datetime NULL DEFAULT NULL, - `validTo` datetime NULL DEFAULT NULL, - PRIMARY KEY (`id`), - UNIQUE (`keyname`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - - -DROP TABLE IF EXISTS `jobs`; -CREATE TABLE `jobs` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `targetId` int(11) NOT NULL, - `task` enum('sign','revoke') NOT NULL, - `state` enum('open', 'done', 'error') NOT NULL DEFAULT 'open', - `warning` int(2) NOT NULL DEFAULT '0', - `executeFrom` DATE, - `executeTo` VARCHAR(11), - PRIMARY KEY (`id`), - KEY `state` (`state`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - - -DROP TABLE IF EXISTS `notary`; -CREATE TABLE `notary` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `from` int(11) NOT NULL DEFAULT '0', - `to` int(11) NOT NULL DEFAULT '0', +DROP TABLE IF EXISTS "certOwners"; +CREATE TABLE "certOwners" ( + "id" serial NOT NULL, + "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + "modified" timestamp NULL DEFAULT NULL, + "deleted" timestamp NULL DEFAULT NULL, + PRIMARY KEY ("id") +); + +DROP TABLE IF EXISTS "users"; +CREATE TABLE "users" ( + "id" int NOT NULL, + "email" varchar(255) NOT NULL DEFAULT '', + "password" varchar(255) NOT NULL DEFAULT '', + "fname" varchar(255) NOT NULL DEFAULT '', + "mname" varchar(255) NOT NULL DEFAULT '', + "lname" varchar(255) NOT NULL DEFAULT '', + "suffix" varchar(50) NOT NULL DEFAULT '', + "dob" date NOT NULL, + "verified" boolean NOT NULL DEFAULT 'false', + "ccid" int NOT NULL DEFAULT '0', + "regid" int NOT NULL DEFAULT '0', + "locid" int NOT NULL DEFAULT '0', + "listme" boolean NOT NULL DEFAULT 'false', + "contactinfo" varchar(255) NOT NULL DEFAULT '', + "language" varchar(5) NOT NULL DEFAULT '', + PRIMARY KEY ("id") +); +CREATE INDEX ON "users" ("ccid"); +CREATE INDEX ON "users" ("regid"); +CREATE INDEX ON "users" ("locid"); +CREATE INDEX ON "users" ("email"); +CREATE INDEX ON "users" ("verified"); + + +DROP TABLE IF EXISTS "organisations"; +CREATE TABLE IF NOT EXISTS "organisations" ( + "id" int NOT NULL, + "name" varchar(100) NOT NULL, + "state" varchar(2) NOT NULL, + "province" varchar(100) NOT NULL, + "city" varchar(100) NOT NULL, + "contactEmail" varchar(100) NOT NULL, + "creator" int NOT NULL, + PRIMARY KEY ("id") +); + +DROP TABLE IF EXISTS "domains"; +CREATE TABLE "domains" ( + "id" serial NOT NULL, + "memid" int NOT NULL, + "domain" varchar(255) NOT NULL, + "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + "modified" timestamp NULL DEFAULT NULL, + "deleted" timestamp NULL DEFAULT NULL, + PRIMARY KEY ("id") +); +CREATE INDEX ON "domains" ("memid"); +CREATE INDEX ON "domains" ("domain"); +CREATE INDEX ON "domains" ("deleted"); + +DROP TABLE IF EXISTS "emails"; +CREATE TABLE "emails" ( + "id" serial NOT NULL, + "memid" int NOT NULL DEFAULT '0', + "email" varchar(255) NOT NULL DEFAULT '', + "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + "modified" timestamp NULL DEFAULT NULL, + "deleted" timestamp NULL DEFAULT NULL, + "hash" varchar(50) NOT NULL DEFAULT '', + "attempts" smallint NOT NULL DEFAULT '0', + PRIMARY KEY ("id") +); +CREATE INDEX ON "emails" ("memid"); +CREATE INDEX ON "emails" ("hash"); +CREATE INDEX ON "emails" ("deleted"); +CREATE INDEX ON "emails" ("email"); + +DROP TABLE IF EXISTS "emailPinglog"; +DROP TABLE IF EXISTS "domainPinglog"; + +DROP TYPE IF EXISTS "emailPingType"; +CREATE TYPE "emailPingType" AS ENUM ('fast', 'active'); +DROP TYPE IF EXISTS "pingState"; +CREATE TYPE "pingState" AS ENUM ('open', 'success', 'failed'); + +CREATE TABLE "emailPinglog" ( + "when" timestamp NOT NULL, + "uid" int NOT NULL, + "email" varchar(255) NOT NULL, + "type" "emailPingType" NOT NULL, + "status" "pingState" NOT NULL, + "result" varchar(255) NOT NULL +); + +DROP TABLE IF EXISTS "pingconfig"; + +DROP TYPE IF EXISTS "pingType"; +CREATE TYPE "pingType" AS ENUM ('email', 'ssl', 'http', 'dns'); + +CREATE TABLE "pingconfig" ( + "id" serial NOT NULL, + "domainid" int NOT NULL, + "type" "pingType" NOT NULL, + "info" varchar(255) NOT NULL, + PRIMARY KEY ("id") +); + + +CREATE TABLE "domainPinglog" ( + "when" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + "configId" int NOT NULL, + "state" "pingState" NOT NULL, + "challenge" varchar(16), + "result" varchar(255) +); + +DROP TABLE IF EXISTS "baddomains"; +CREATE TABLE "baddomains" ( + "domain" varchar(255) NOT NULL DEFAULT '' +); + + +DROP TABLE IF EXISTS "alerts"; +CREATE TABLE "alerts" ( + "memid" int NOT NULL DEFAULT '0', + "general" boolean NOT NULL DEFAULT 'false', + "country" boolean NOT NULL DEFAULT 'false', + "regional" boolean NOT NULL DEFAULT 'false', + "radius" boolean NOT NULL DEFAULT 'false', + PRIMARY KEY ("memid") +); + +DROP TABLE IF EXISTS "user_agreements"; +CREATE TABLE "user_agreements" ( + "id" serial NOT NULL, + "memid" int NOT NULL, + "secmemid" int DEFAULT NULL, + "document" varchar(50) DEFAULT NULL, + "date" timestamp DEFAULT NULL, + "active" boolean NOT NULL, + "method" varchar(100) NOT NULL, + "comment" varchar(100) DEFAULT NULL, + PRIMARY KEY ("id") +); + +DROP TABLE IF EXISTS "certs"; + +DROP TYPE IF EXISTS "mdType"; +CREATE TYPE "mdType" AS ENUM('md5','sha1','sha256','sha512'); + +DROP TYPE IF EXISTS "csrType"; +CREATE TYPE "csrType" AS ENUM ('CSR', 'SPKAC'); + +CREATE TABLE "certs" ( + "id" serial NOT NULL, + "memid" int NOT NULL DEFAULT '0', + "serial" varchar(50) NOT NULL DEFAULT '', + "keytype" char(2) NOT NULL DEFAULT 'NS', + "codesign" boolean NOT NULL DEFAULT 'false', + "md" "mdType" NOT NULL DEFAULT 'sha512', + "profile" int NOT NULL, + "caid" int NULL DEFAULT NULL, + + "csr_name" varchar(255) NOT NULL DEFAULT '', + "csr_type" "csrType" NOT NULL, + "crt_name" varchar(255) NOT NULL DEFAULT '', + "created" timestamp NULL DEFAULT NULL, + "modified" timestamp NULL DEFAULT NULL, + "revoked" timestamp NULL DEFAULT NULL, + "expire" timestamp NULL DEFAULT NULL, + "renewed" boolean NOT NULL DEFAULT 'false', + "disablelogin" boolean NOT NULL DEFAULT 'false', + "pkhash" char(40) DEFAULT NULL, + "certhash" char(40) DEFAULT NULL, + "description" varchar(100) NOT NULL DEFAULT '', + PRIMARY KEY ("id") +); +CREATE INDEX ON "certs" ("pkhash"); +CREATE INDEX ON "certs" ("revoked"); +CREATE INDEX ON "certs" ("created"); +CREATE INDEX ON "certs" ("memid"); +CREATE INDEX ON "certs" ("serial"); +CREATE INDEX ON "certs" ("expire"); +CREATE INDEX ON "certs" ("crt_name"); + + + +DROP TABLE IF EXISTS "certAvas"; +CREATE TABLE "certAvas" ( + "certid" int NOT NULL, + "name" varchar(20) NOT NULL, + "value" varchar(255) NOT NULL, + + PRIMARY KEY ("certid", "name") +); + +DROP TABLE IF EXISTS "clientcerts"; +CREATE TABLE "clientcerts" ( + "id" int NOT NULL, + "disablelogin" boolean NOT NULL DEFAULT 'false', + + PRIMARY KEY ("id") +); + +DROP TABLE IF EXISTS "profiles"; +CREATE TABLE "profiles" ( + "id" serial NOT NULL, + "keyname" varchar(60) NOT NULL, + "include" varchar(200) NOT NULL, + "requires" varchar(200) NOT NULL, + "name" varchar(100) NOT NULL, + PRIMARY KEY ("id"), + UNIQUE ("keyname") +); + +DROP TABLE IF EXISTS "subjectAlternativeNames"; + +DROP TYPE IF EXISTS "SANType"; +CREATE TYPE "SANType" AS ENUM ('email', 'DNS'); + +CREATE TABLE "subjectAlternativeNames" ( + "certId" int NOT NULL, + "contents" varchar(50) NOT NULL, + "type" "SANType" NOT NULL +); + +DROP TABLE IF EXISTS "cacerts"; +CREATE TABLE "cacerts" ( + "id" serial NOT NULL, + "keyname" varchar(60) NOT NULL, + "link" varchar(160) NOT NULL, + "parentRoot" int NOT NULL, + "validFrom" timestamp NULL DEFAULT NULL, + "validTo" timestamp NULL DEFAULT NULL, + PRIMARY KEY ("id"), + UNIQUE ("keyname") +); + +DROP TABLE IF EXISTS "jobs"; + +DROP TYPE IF EXISTS "jobType"; +CREATE TYPE "jobType" AS ENUM ('sign', 'revoke'); +DROP TYPE IF EXISTS "jobState"; +CREATE TYPE "jobState" AS ENUM ('open', 'done', 'error'); + + +CREATE TABLE "jobs" ( + "id" serial NOT NULL, + "targetId" int NOT NULL, + "task" "jobType" NOT NULL, + "state" "jobState" NOT NULL DEFAULT 'open', + "warning" smallint NOT NULL DEFAULT '0', + "executeFrom" DATE, + "executeTo" VARCHAR(11), + PRIMARY KEY ("id") +); + +CREATE INDEX ON "jobs" ("state"); + +DROP TABLE IF EXISTS "notary"; + +DROP TYPE IF EXISTS "notaryType"; +CREATE TYPE "notaryType" AS enum('Face to Face Meeting', 'TOPUP', 'TTP-Assisted'); + +CREATE TABLE "notary" ( + "id" serial NOT NULL, + "from" int NOT NULL DEFAULT '0', + "to" int NOT NULL DEFAULT '0', # total points that have been entered - `points` int(3) NOT NULL DEFAULT '0', + "points" int NOT NULL DEFAULT '0', # awarded and the "experience points" are calculated virtually # Face to Face is default, TOPUP is for the remaining 30Points after two TTP # TTP is default ttp assurance - `method` enum('Face to Face Meeting', 'TOPUP', 'TTP-Assisted') NOT NULL DEFAULT 'Face to Face Meeting', - `location` varchar(255) NOT NULL DEFAULT '', - `date` varchar(255) NOT NULL DEFAULT '', + "method" "notaryType" NOT NULL DEFAULT 'Face to Face Meeting', + "location" varchar(255) NOT NULL DEFAULT '', + "date" varchar(255) NOT NULL DEFAULT '', # date when assurance was entered - `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, #? - `expire` datetime NULL DEFAULT NULL, + "expire" timestamp NULL DEFAULT NULL, #????????????????? - `sponsor` int(11) NOT NULL DEFAULT '0', + "sponsor" int NOT NULL DEFAULT '0', # date when assurance was deleted (or 0) - `deleted` datetime NULL DEFAULT NULL, - PRIMARY KEY (`id`), - KEY `from` (`from`), - KEY `to` (`to`), - KEY `stats_notary_when` (`when`), - KEY `stats_notary_method` (`method`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - - -DROP TABLE IF EXISTS `cats_passed`; -CREATE TABLE `cats_passed` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `user_id` int(11) NOT NULL, - `variant_id` int(11) NOT NULL, - `pass_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (`id`), - UNIQUE KEY `test_passed` (`user_id`,`variant_id`,`pass_date`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; + "deleted" timestamp NULL DEFAULT NULL, + PRIMARY KEY ("id") +); + +CREATE INDEX ON "notary"("from"); +CREATE INDEX ON "notary"("to"); +CREATE INDEX ON "notary"("when"); +CREATE INDEX ON "notary"("method"); + + +DROP TABLE IF EXISTS "cats_passed"; +CREATE TABLE "cats_passed" ( + "id" serial NOT NULL, + "user_id" int NOT NULL, + "variant_id" int NOT NULL, + "pass_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY ("id"), + UNIQUE ("user_id","variant_id","pass_date") +); # -------------------------------------------------------- # -# Table structure for table `cats_type` +# Table structure for table "cats_type" # -DROP TABLE IF EXISTS `cats_type`; -CREATE TABLE `cats_type` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `type_text` varchar(255) NOT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `type_text` (`type_text`) -) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `arbitrations`; -CREATE TABLE IF NOT EXISTS `arbitrations` ( - `user` int(11) NOT NULL, - `arbitration` varchar(20) NOT NULL, - PRIMARY KEY (`user`,`arbitration`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `user_groups`; -CREATE TABLE IF NOT EXISTS `user_groups` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `user` int(11) NOT NULL, - `permission` enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer') NOT NULL, - `granted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `deleted` timestamp NULL DEFAULT NULL, - `grantedby` int(11) NOT NULL, - `revokedby` int(11) DEFAULT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `org_admin`; -CREATE TABLE IF NOT EXISTS `org_admin` ( - `orgid` int(11) NOT NULL, - `memid` int(11) NOT NULL, - `master` enum('y', 'n') NOT NULL, - `creator` int(11) NOT NULL, - `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `deleter` int(11) NULL DEFAULT NULL, - `deleted` timestamp NULL DEFAULT NULL, - KEY (`orgid`, `memid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `adminLog`; -CREATE TABLE `adminLog` ( - `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `uid` int(11) unsigned NOT NULL, - `admin` int(11) unsigned NOT NULL, - `type` varchar(100) NOT NULL DEFAULT '', - `information` varchar(50) NOT NULL DEFAULT '', - KEY (`when`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -DROP TABLE IF EXISTS `schemeVersion`; -CREATE TABLE `schemeVersion` ( - `version` int(5) NOT NULL, - PRIMARY KEY (`version`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; -INSERT INTO schemeVersion(version) VALUES(4); +DROP TABLE IF EXISTS "cats_type"; +CREATE TABLE "cats_type" ( + "id" serial NOT NULL, + "type_text" varchar(255) NOT NULL, + PRIMARY KEY ("id"), + UNIQUE ("type_text") +); + +DROP TABLE IF EXISTS "arbitrations"; +CREATE TABLE IF NOT EXISTS "arbitrations" ( + "user" int NOT NULL, + "arbitration" varchar(20) NOT NULL, + PRIMARY KEY ("user","arbitration") +); + +DROP TABLE IF EXISTS "user_groups"; + +DROP TYPE IF EXISTS "userGroup"; +CREATE TYPE "userGroup" AS enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer'); + +CREATE TABLE IF NOT EXISTS "user_groups" ( + "id" serial NOT NULL, + "user" int NOT NULL, + "permission" "userGroup" NOT NULL, + "granted" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + "deleted" timestamp NULL DEFAULT NULL, + "grantedby" int NOT NULL, + "revokedby" int DEFAULT NULL, + PRIMARY KEY ("id") +); + +DROP TABLE IF EXISTS "org_admin"; + +DROP TYPE IF EXISTS "yesno"; +CREATE TYPE "yesno" AS enum('y', 'n'); + + +CREATE TABLE IF NOT EXISTS "org_admin" ( + "orgid" int NOT NULL, + "memid" int NOT NULL, + "master" "yesno" NOT NULL, + "creator" int NOT NULL, + "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + "deleter" int NULL DEFAULT NULL, + "deleted" timestamp NULL DEFAULT NULL +); +CREATE INDEX ON "org_admin"("orgid", "memid"); + + +DROP TABLE IF EXISTS "adminLog"; +CREATE TABLE "adminLog" ( + "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + "uid" int NOT NULL, + "admin" int NOT NULL, + "type" varchar(100) NOT NULL DEFAULT '', + "information" varchar(50) NOT NULL DEFAULT '' +); +CREATE INDEX ON "adminLog"("when"); + + +DROP TABLE IF EXISTS "schemeVersion"; +CREATE TABLE "schemeVersion" ( + "version" smallint NOT NULL, + PRIMARY KEY ("version") +); +INSERT INTO "schemeVersion" (version) VALUES(4); diff --git a/src/org/cacert/gigi/dbObjects/CACertificate.java b/src/org/cacert/gigi/dbObjects/CACertificate.java index b6ede830..bdfc6531 100644 --- a/src/org/cacert/gigi/dbObjects/CACertificate.java +++ b/src/org/cacert/gigi/dbObjects/CACertificate.java @@ -31,7 +31,7 @@ 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 = ?"); + GigiPreparedStatement conn = DatabaseConnection.getInstance().prepare("SELECT `keyname`, `parentRoot`, `link` FROM `cacerts` WHERE `id`=?"); conn.setInt(1, id); GigiResultSet res = conn.executeQuery(); if ( !res.next()) { @@ -109,7 +109,7 @@ 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=?"); + GigiPreparedStatement q = DatabaseConnection.getInstance().prepare("SELECT `id`, `parentRoot` FROM `cacerts` WHERE `keyname`=?"); q.setString(1, names.get(subj)); GigiResultSet res = q.executeQuery(); int id; @@ -128,14 +128,14 @@ public class CACertificate implements IdCachable { link = "http://g2.crt.cacert.org/g2/" + parts[1] + "/" + parts[0] + "-" + parts[2] + ".crt"; } - GigiPreparedStatement q2 = DatabaseConnection.getInstance().prepare("INSERT INTO cacerts SET parentRoot=?, keyname=?, link=?"); + 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=?"); + GigiPreparedStatement q3 = DatabaseConnection.getInstance().prepare("UPDATE `cacerts` SET `parentRoot`=?, `id`=?"); q3.setInt(1, id); q3.setInt(2, id); q3.execute(); diff --git a/src/org/cacert/gigi/dbObjects/Certificate.java b/src/org/cacert/gigi/dbObjects/Certificate.java index 85ba0346..fc92b309 100644 --- a/src/org/cacert/gigi/dbObjects/Certificate.java +++ b/src/org/cacert/gigi/dbObjects/Certificate.java @@ -165,7 +165,7 @@ public class Certificate { profile = CertificateProfile.getById(rs.getInt("profile")); this.serial = rs.getString("serial"); - GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("SELECT contents, type FROM `subjectAlternativeNames` WHERE certId=?"); + GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("SELECT `contents`, `type` FROM `subjectAlternativeNames` WHERE `certId`=?"); ps2.setInt(1, id); GigiResultSet rs2 = ps2.executeQuery(); sans = new LinkedList<>(); @@ -247,15 +247,15 @@ public class Certificate { } Notary.writeUserAgreement(owner, "CCA", "issue certificate", "", true, 0); - GigiPreparedStatement inserter = DatabaseConnection.getInstance().prepare("INSERT INTO certs SET md=?, csr_type=?, crt_name='', memid=?, profile=?"); - inserter.setString(1, md); + GigiPreparedStatement inserter = DatabaseConnection.getInstance().prepare("INSERT INTO certs SET md=?::`mdType`, csr_type=?::`csrType`, crt_name='', memid=?, profile=?"); + inserter.setString(1, md.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=?"); + 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()); @@ -263,7 +263,7 @@ public class Certificate { san.execute(); } - GigiPreparedStatement insertAVA = DatabaseConnection.getInstance().prepare("INSERT certAvas SET certid=?, name=?, value=?"); + 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()); @@ -276,7 +276,7 @@ public class Certificate { fos.write(csr.getBytes("UTF-8")); } - GigiPreparedStatement updater = DatabaseConnection.getInstance().prepare("UPDATE certs SET csr_name=? WHERE id=?"); + GigiPreparedStatement updater = DatabaseConnection.getInstance().prepare("UPDATE `certs` SET `csr_name`=? WHERE id=?"); updater.setString(1, csrName); updater.setInt(2, id); updater.execute(); @@ -359,8 +359,8 @@ public class Certificate { } // TODO caching? try { - String concat = "group_concat(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"); + 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(); return new Certificate(rs); diff --git a/src/org/cacert/gigi/dbObjects/CertificateOwner.java b/src/org/cacert/gigi/dbObjects/CertificateOwner.java index 5be24fc8..9560224e 100644 --- a/src/org/cacert/gigi/dbObjects/CertificateOwner.java +++ b/src/org/cacert/gigi/dbObjects/CertificateOwner.java @@ -25,7 +25,7 @@ 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"); + 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()) { @@ -48,7 +48,7 @@ public abstract class CertificateOwner implements IdCachable { if (id != 0) { throw new Error("refusing to insert"); } - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO certOwners() VALUES()"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `certOwners` DEFAULT VALUES"); ps.execute(); id = ps.lastInsertId(); myCache.put(this); @@ -58,7 +58,7 @@ public abstract class CertificateOwner implements IdCachable { } public Domain[] getDomains() { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM domains WHERE memid=? AND deleted IS NULL"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id` FROM `domains` WHERE `memid`=? AND `deleted` IS NULL"); ps.setInt(1, getId()); try (GigiResultSet rs = ps.executeQuery()) { @@ -106,7 +106,7 @@ 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=?"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `certOwners` SET `deleted`=NOW() WHERE `id`=?"); ps.setInt(1, getId()); ps.execute(); myCache.remove(this); diff --git a/src/org/cacert/gigi/dbObjects/CertificateProfile.java b/src/org/cacert/gigi/dbObjects/CertificateProfile.java index 5ac1f761..3a040903 100644 --- a/src/org/cacert/gigi/dbObjects/CertificateProfile.java +++ b/src/org/cacert/gigi/dbObjects/CertificateProfile.java @@ -188,7 +188,7 @@ 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=?"); + 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(); @@ -203,7 +203,7 @@ public class CertificateProfile implements IdCachable { throw new Error("Config error. Certificate Profile mismatch"); } } else { - GigiPreparedStatement insert = DatabaseConnection.getInstance().prepare("INSERT INTO `profiles` SET keyname=?, include=?, requires=?, name=?, id=?"); + 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", "")); @@ -213,7 +213,7 @@ public class CertificateProfile implements IdCachable { } } - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id, keyname, name, requires, include FROM `profiles`"); + 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")); diff --git a/src/org/cacert/gigi/dbObjects/Domain.java b/src/org/cacert/gigi/dbObjects/Domain.java index 7e35225f..283efdae 100644 --- a/src/org/cacert/gigi/dbObjects/Domain.java +++ b/src/org/cacert/gigi/dbObjects/Domain.java @@ -81,7 +81,7 @@ 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"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid`, `domain` FROM `domains` WHERE `id`=? AND `deleted` IS NULL"); ps.setInt(1, id); GigiResultSet rs = ps.executeQuery(); @@ -156,7 +156,7 @@ 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"); + 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); @@ -188,7 +188,7 @@ 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=?"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `domains` SET `deleted`=CURRENT_TIMESTAMP WHERE `id`=?"); ps.setInt(1, id); ps.execute(); } @@ -226,7 +226,7 @@ public class Domain implements IdCachable, Verifyable { } public void addPing(PingType type, String config) throws GigiApiException { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO pingconfig SET domainid=?, type=?, info=?"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `pingconfig` `SET` `domainid`=?, `type`=?, `info`=?"); ps.setInt(1, id); ps.setString(2, type.toString().toLowerCase()); ps.setString(3, config); @@ -235,21 +235,21 @@ public class Domain implements IdCachable, Verifyable { } public synchronized void verify(String hash) throws GigiApiException { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE domainPinglog SET state='success' WHERE challenge=? AND configId IN (SELECT id FROM pingconfig WHERE domainId=?)"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `domainPinglog` `SET` `state`='success' WHERE `challenge`=? AND `configId` `IN` (SELECT `id` FROM `pingconfig` WHERE `domainId`=?)"); 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'"); + 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(); } public DomainPingExecution[] getPings() throws GigiApiException { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT state, type, info, result, configId FROM domainPinglog INNER JOIN pingconfig ON pingconfig.id=domainPinglog.configid WHERE pingconfig.domainid=? ORDER BY `when` DESC;"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `state`, `type`, `info`, `result`, `configId` 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(); @@ -273,10 +273,9 @@ public class Domain implements IdCachable, Verifyable { } public static int searchUserIdByDomain(String domain) { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT memid FROM domains WHERE domain = ?"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `domains` WHERE `domain` = ?"); ps.setString(1, domain); GigiResultSet res = ps.executeQuery(); - res.beforeFirst(); if (res.next()) { return res.getInt(1); } else { diff --git a/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java b/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java index c6d053af..47e07dd3 100644 --- a/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java +++ b/src/org/cacert/gigi/dbObjects/DomainPingConfiguration.java @@ -28,7 +28,7 @@ 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=?"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id`, `domainid`, `type`, `info` FROM `pingconfig` WHERE `id`=?"); ps.setInt(1, id); GigiResultSet rs = ps.executeQuery(); diff --git a/src/org/cacert/gigi/dbObjects/EmailAddress.java b/src/org/cacert/gigi/dbObjects/EmailAddress.java index c499b1a9..fbd7057a 100644 --- a/src/org/cacert/gigi/dbObjects/EmailAddress.java +++ b/src/org/cacert/gigi/dbObjects/EmailAddress.java @@ -22,7 +22,7 @@ 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"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid`, `email`, `hash` FROM `emails` WHERE `id`=? AND `deleted` IS NULL"); ps.setInt(1, id); GigiResultSet rs = ps.executeQuery(); diff --git a/src/org/cacert/gigi/dbObjects/Group.java b/src/org/cacert/gigi/dbObjects/Group.java index a40403e8..8a2c1de4 100644 --- a/src/org/cacert/gigi/dbObjects/Group.java +++ b/src/org/cacert/gigi/dbObjects/Group.java @@ -22,7 +22,7 @@ public enum Group { } public User[] getMembers(int offset, int count) { - GigiPreparedStatement gps = DatabaseConnection.getInstance().prepare("SELECT user FROM user_groups WHERE permission=? AND deleted is NULL LIMIT ?,?"); + 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); diff --git a/src/org/cacert/gigi/dbObjects/Organisation.java b/src/org/cacert/gigi/dbObjects/Organisation.java index 9beb0f57..79c088c8 100644 --- a/src/org/cacert/gigi/dbObjects/Organisation.java +++ b/src/org/cacert/gigi/dbObjects/Organisation.java @@ -120,14 +120,14 @@ 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"); + 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; } - GigiPreparedStatement ps2 = DatabaseConnection.getInstance().prepare("INSERT INTO org_admin SET orgid=?, memid=?, creator=?, master=?"); + 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()); @@ -147,7 +147,7 @@ public class Organisation extends CertificateOwner { } public List getAllAdmins() { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT memid, master FROM org_admin WHERE orgid=? AND deleted is null"); + 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(); @@ -160,7 +160,7 @@ public class Organisation extends CertificateOwner { } public static Organisation[] getOrganisations(int offset, int count) { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT certOwners.id FROM organisations inner join certOwners on certOwners.id=organisations.id where certOwners.deleted is null LIMIT ?,?"); + 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(); @@ -180,7 +180,7 @@ public class Organisation extends CertificateOwner { cert.revoke(); } } - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE organisations SET name=?, state=?, province=?, city=?, contactEmail=?"); + 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); diff --git a/src/org/cacert/gigi/dbObjects/User.java b/src/org/cacert/gigi/dbObjects/User.java index 911d98b7..69a65487 100644 --- a/src/org/cacert/gigi/dbObjects/User.java +++ b/src/org/cacert/gigi/dbObjects/User.java @@ -52,7 +52,7 @@ 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"); + GigiPreparedStatement psg = DatabaseConnection.getInstance().prepare("SELECT `permission` FROM `user_groups` WHERE `user`=? AND `deleted` is NULL"); psg.setInt(1, rs.getInt("id")); try (GigiResultSet rs2 = psg.executeQuery()) { @@ -86,7 +86,7 @@ public class User extends CertificateOwner { public void insert(String password) { int id = super.insert(); - GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("insert into `users` set `email`=?, `password`=?, " + "`fname`=?, `mname`=?, `lname`=?, " + "`suffix`=?, `dob`=?, `language`=?, id=?"); + 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()); @@ -100,7 +100,7 @@ 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=?"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `password` FROM `users` WHERE `id`=?"); ps.setInt(1, getId()); try (GigiResultSet rs = ps.executeQuery()) { if ( !rs.next()) { @@ -251,9 +251,8 @@ public class User extends CertificateOwner { for (EmailAddress email : getEmails()) { if (email.getId() == delMail.getId()) { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE emails SET deleted=? WHERE id=?"); - ps.setDate(1, new Date(System.currentTimeMillis())); - ps.setInt(2, delMail.getId()); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `emails` SET `deleted`=CURRENT_TIMESTAMP WHERE `id`=?"); + ps.setInt(1, delMail.getId()); ps.execute(); return; } @@ -263,7 +262,7 @@ 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"); + GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT * FROM `notary` WHERE `to`=? AND `deleted` IS NULL"); query.setInt(1, getId()); try (GigiResultSet res = query.executeQuery()) { @@ -379,7 +378,7 @@ 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=?, grantedby=?"); + 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()); @@ -388,7 +387,7 @@ public class User extends CertificateOwner { 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=? AND user=?"); + 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()); @@ -397,7 +396,7 @@ public class User extends CertificateOwner { public List getOrganisations() { List orgas = new ArrayList<>(); - GigiPreparedStatement query = DatabaseConnection.getInstance().prepare("SELECT orgid FROM org_admin WHERE `memid`=? AND deleted is NULL"); + 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()) { @@ -418,7 +417,7 @@ 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"); + 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()) { if ( !rs.next()) { @@ -431,7 +430,7 @@ 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 ASC LIMIT 100"); + 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()) { while (rs.next()) { @@ -442,7 +441,7 @@ public class User extends CertificateOwner { } public EmailAddress[] getEmails() { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM emails WHERE memid=? AND deleted is NULL"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id` FROM `emails` WHERE `memid`=? AND `deleted` IS NULL"); ps.setInt(1, getId()); try (GigiResultSet rs = ps.executeQuery()) { diff --git a/src/org/cacert/gigi/email/EmailProvider.java b/src/org/cacert/gigi/email/EmailProvider.java index 6494bf8d..a625e401 100644 --- a/src/org/cacert/gigi/email/EmailProvider.java +++ b/src/org/cacert/gigi/email/EmailProvider.java @@ -145,10 +145,11 @@ public abstract class EmailProvider { continue; } - GigiPreparedStatement statmt = DatabaseConnection.getInstance().prepare("insert into `emailPinglog` set `when`=NOW(), `email`=?, `result`=?, `uid`=?"); + 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(); if (line == null || !line.startsWith("250")) { @@ -160,10 +161,11 @@ public abstract class EmailProvider { } } - GigiPreparedStatement statmt = DatabaseConnection.getInstance().prepare("insert into `emailPinglog` set `when`=NOW(), `email`=?, `result`=?, `uid`=?"); + 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(); return FAIL; } diff --git a/src/org/cacert/gigi/pages/LoginPage.java b/src/org/cacert/gigi/pages/LoginPage.java index 91b6b1b7..06006cf2 100644 --- a/src/org/cacert/gigi/pages/LoginPage.java +++ b/src/org/cacert/gigi/pages/LoginPage.java @@ -134,12 +134,17 @@ public class LoginPage extends Page { } public static User fetchUserBySerial(String serial) { + if ( !serial.matches("[A-Fa-f0-9]+")) { + throw new Error("serial malformed."); + } GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `memid` FROM `certs` WHERE `serial`=? AND `disablelogin`='0' AND `revoked` is NULL"); - ps.setString(1, serial); + ps.setString(1, serial.toLowerCase()); GigiResultSet rs = ps.executeQuery(); User user = null; if (rs.next()) { user = User.getById(rs.getInt(1)); + } else { + System.out.println("User with serial " + serial + " not found."); } rs.close(); return user; diff --git a/src/org/cacert/gigi/pages/main/Signup.java b/src/org/cacert/gigi/pages/main/Signup.java index 66665af2..a637f83d 100644 --- a/src/org/cacert/gigi/pages/main/Signup.java +++ b/src/org/cacert/gigi/pages/main/Signup.java @@ -123,8 +123,8 @@ 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"); + 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, buildup.getEmail()); q2.setString(1, buildup.getEmail()); GigiResultSet r1 = q1.executeQuery(); @@ -134,7 +134,7 @@ public class Signup extends Form { } r1.close(); r2.close(); - GigiPreparedStatement q3 = DatabaseConnection.getInstance().prepare("select `domain` from `baddomains` where `domain`=RIGHT(?, LENGTH(`domain`))"); + GigiPreparedStatement q3 = DatabaseConnection.getInstance().prepare("SELECT `domain` FROM `baddomains` WHERE `domain`=RIGHT(?, LENGTH(`domain`))"); q3.setString(1, buildup.getEmail()); GigiResultSet r3 = q3.executeQuery(); @@ -185,12 +185,12 @@ public class Signup extends Form { EmailAddress ea = new EmailAddress(buildup, buildup.getEmail()); ea.insert(Page.getLanguage(req)); - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("insert into `alerts` set `memid`=?," + " `general`=?, `country`=?, `regional`=?, `radius`=?"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `alerts` SET `memid`=?," + " `general`=?, `country`=?, `regional`=?, `radius`=?"); ps.setInt(1, memid); - ps.setString(2, general ? "1" : "0"); - ps.setString(3, country ? "1" : "0"); - ps.setString(4, regional ? "1" : "0"); - ps.setString(5, radius ? "1" : "0"); + ps.setBoolean(2, general); + ps.setBoolean(3, country); + ps.setBoolean(4, regional); + ps.setBoolean(5, radius); ps.execute(); Notary.writeUserAgreement(buildup, "CCA", "account creation", "", true, 0); diff --git a/src/org/cacert/gigi/pages/wot/AssurePage.java b/src/org/cacert/gigi/pages/wot/AssurePage.java index cc20c4d7..924cc953 100644 --- a/src/org/cacert/gigi/pages/wot/AssurePage.java +++ b/src/org/cacert/gigi/pages/wot/AssurePage.java @@ -2,6 +2,8 @@ package org.cacert.gigi.pages.wot; import java.io.IOException; import java.io.PrintWriter; +import java.sql.Date; +import java.util.Calendar; import java.util.HashMap; import javax.servlet.http.HttpServletRequest; @@ -75,19 +77,20 @@ public class AssurePage extends Page { 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"); + 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"); ps.setString(1, req.getParameter("email")); - String day = req.getParameter("year") + "-" + req.getParameter("month") + "-" + req.getParameter("day"); - ps.setString(2, day); + 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(); int id = 0; if (rs.next()) { id = rs.getInt(1); - int verified = rs.getInt(2); + boolean verified = rs.getBoolean(2); if (rs.next()) { out.println("Error, ambigous user. Please contact support@cacert.org."); } else { - if (verified == 0) { + if ( !verified) { out.println(translate(req, "User is not yet verified. Please try again in 24 hours!")); } else if (getUser(req).getId() == id) { diff --git a/src/org/cacert/gigi/ping/PingerDaemon.java b/src/org/cacert/gigi/ping/PingerDaemon.java index ceb88e9a..bb6dc5af 100644 --- a/src/org/cacert/gigi/ping/PingerDaemon.java +++ b/src/org/cacert/gigi/ping/PingerDaemon.java @@ -31,8 +31,8 @@ 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"); - enterPingResult = DatabaseConnection.getInstance().prepare("INSERT INTO domainPinglog SET configId=?, state=?, result=?, challenge=?"); + 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`"); + enterPingResult = DatabaseConnection.getInstance().prepare("INSERT INTO `domainPinglog` SET `configId`=?, `state`=?, `result`=?, `challenge`=?"); pingers.put(PingType.EMAIL, new EmailPinger()); pingers.put(PingType.SSL, new SSLPinger(truststore)); pingers.put(PingType.HTTP, new HTTPFetch()); diff --git a/src/org/cacert/gigi/util/Job.java b/src/org/cacert/gigi/util/Job.java index f5287717..e6f50140 100644 --- a/src/org/cacert/gigi/util/Job.java +++ b/src/org/cacert/gigi/util/Job.java @@ -33,7 +33,7 @@ public class Job { public 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=?, executeFrom=?, executeTo=?"); + 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); @@ -44,7 +44,7 @@ public class Job { public static Job revoke(Certificate targetId) { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `jobs` SET targetId=?, task=?"); + 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(); diff --git a/src/org/cacert/gigi/util/Notary.java b/src/org/cacert/gigi/util/Notary.java index 767230b0..a2dfe0f8 100644 --- a/src/org/cacert/gigi/util/Notary.java +++ b/src/org/cacert/gigi/util/Notary.java @@ -15,11 +15,11 @@ 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`=?"); + 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.setInt(4, active ? 1 : 0); + q.setBoolean(4, active); q.setString(5, method); q.setString(6, comment); q.execute(); diff --git a/src/org/cacert/gigi/util/PasswordHash.java b/src/org/cacert/gigi/util/PasswordHash.java index ed0d4f3c..04e68618 100644 --- a/src/org/cacert/gigi/util/PasswordHash.java +++ b/src/org/cacert/gigi/util/PasswordHash.java @@ -46,7 +46,7 @@ public class PasswordHash { } } - private static String sha1(String password) { + public static String sha1(String password) { try { MessageDigest md = MessageDigest.getInstance("SHA1"); byte[] digest = md.digest(password.getBytes("UTF-8")); diff --git a/tests/org/cacert/gigi/TestUserGroupMembership.java b/tests/org/cacert/gigi/TestUserGroupMembership.java index c1834acd..3808df5d 100644 --- a/tests/org/cacert/gigi/TestUserGroupMembership.java +++ b/tests/org/cacert/gigi/TestUserGroupMembership.java @@ -82,7 +82,7 @@ public class TestUserGroupMembership extends ManagedTest { } private GigiResultSet fetchGroupRowsFor(User u) throws SQLException { - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT * FROM user_groups WHERE user=?"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT * FROM `user_groups` WHERE `user`=?"); ps.setInt(1, u.getId()); GigiResultSet rs = ps.executeQuery(); return rs; diff --git a/tests/org/cacert/gigi/pages/orga/TestOrgaManagement.java b/tests/org/cacert/gigi/pages/orga/TestOrgaManagement.java index c8a6c139..3960cba0 100644 --- a/tests/org/cacert/gigi/pages/orga/TestOrgaManagement.java +++ b/tests/org/cacert/gigi/pages/orga/TestOrgaManagement.java @@ -53,7 +53,7 @@ public class TestOrgaManagement extends ClientTest { if (affiliation2.getTarget().getId() == u2.getId()) { affiliation2 = allAdmins.get(1); } - assertSame(u.getId(), affiliation2.getTarget().getId()); + assertEquals(u.getId(), affiliation2.getTarget().getId()); assertFalse(affiliation2.isMaster()); executeBasicWebInteraction(cookie, ViewOrgPage.DEFAULT_PATH + "/" + orgs[0].getId(), "del=" + URLEncoder.encode(u.getEmail(), "UTF-8") + "&email=&do_affiliate=y", 1); diff --git a/tests/org/cacert/gigi/testUtils/ManagedTest.java b/tests/org/cacert/gigi/testUtils/ManagedTest.java index a21079c4..86868354 100644 --- a/tests/org/cacert/gigi/testUtils/ManagedTest.java +++ b/tests/org/cacert/gigi/testUtils/ManagedTest.java @@ -273,7 +273,7 @@ public class ManagedTest extends ConfiguredTest { try { ter.receive().verify(); - GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT id FROM users where email=?"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT `id` FROM `users` WHERE `email`=?"); ps.setString(1, email); try (GigiResultSet rs = ps.executeQuery()) { diff --git a/tests/org/cacert/gigi/util/TestNotary.java b/tests/org/cacert/gigi/util/TestNotary.java index 4bfb26d6..03f157f3 100644 --- a/tests/org/cacert/gigi/util/TestNotary.java +++ b/tests/org/cacert/gigi/util/TestNotary.java @@ -64,7 +64,7 @@ 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=TIMESTAMPADD(YEAR,-15,NOW()) WHERE id=?"); + GigiPreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `users` SET dob=NOW() - interval '15 years' WHERE id=?"); ps.setInt(1, id); ps.execute(); User assurer = User.getById(id); diff --git a/tests/org/cacert/gigi/util/TestPasswordMigration.java b/tests/org/cacert/gigi/util/TestPasswordMigration.java index 030794cb..2992e05c 100644 --- a/tests/org/cacert/gigi/util/TestPasswordMigration.java +++ b/tests/org/cacert/gigi/util/TestPasswordMigration.java @@ -20,8 +20,8 @@ public class TestPasswordMigration extends ManagedTest { @Test public void testPasswordMigration() throws IOException { - GigiPreparedStatement stmt = DatabaseConnection.getInstance().prepare("UPDATE users SET `password`=SHA1(?) WHERE id=?"); - stmt.setString(1, "a"); + 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(); String cookie = login(ru.getUser().getEmail(), "a"); diff --git a/util-testing/org/cacert/gigi/DevelLauncher.java b/util-testing/org/cacert/gigi/DevelLauncher.java index 4137aa5d..9fab71c9 100644 --- a/util-testing/org/cacert/gigi/DevelLauncher.java +++ b/util-testing/org/cacert/gigi/DevelLauncher.java @@ -188,6 +188,11 @@ public class DevelLauncher { if ( !ticketUsed) { HttpSession sess = req.getSession(); User user = User.getById(1); + if (user == null) { + resp.getWriter().println("ticket consumed but no user available for that action"); + ticketUsed = true; + return true; + } sess.setAttribute(LOGGEDIN, true); sess.setAttribute(Language.SESSION_ATTRIB_NAME, user.getPreferredLocale()); sess.setAttribute(USER, user); diff --git a/util-testing/org/cacert/gigi/util/SimpleSigner.java b/util-testing/org/cacert/gigi/util/SimpleSigner.java index 9cae722d..764faf05 100644 --- a/util-testing/org/cacert/gigi/util/SimpleSigner.java +++ b/util-testing/org/cacert/gigi/util/SimpleSigner.java @@ -134,19 +134,19 @@ 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 " + // - "INNER JOIN certs ON certs.id=jobs.targetId " + // + readyCerts = DatabaseConnection.getInstance().prepare("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 " + // - "WHERE certId=?"); + getSANSs = DatabaseConnection.getInstance().prepare("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=?"); - 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'"); + 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=?"); finishJob = DatabaseConnection.getInstance().prepare("UPDATE jobs SET state='done' WHERE id=?"); @@ -297,7 +297,7 @@ 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=?"); + 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()) { -- 2.39.2