From 0d5ae34e23b50f59e759702b1ebf6f5a3a7137f4 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Felix=20D=C3=B6rre?= Date: Sun, 30 Jul 2017 21:09:00 +0200 Subject: [PATCH] upd: precise SQL statements for verification Change-Id: I921b638d0734b14eb2cd1f9389c20e7fb7b15922 --- src/club/wpia/gigi/dbObjects/CATS.java | 2 +- src/club/wpia/gigi/dbObjects/Domain.java | 2 +- src/club/wpia/gigi/dbObjects/Group.java | 2 +- src/club/wpia/gigi/dbObjects/Organisation.java | 2 +- src/club/wpia/gigi/dbObjects/User.java | 4 ++-- src/club/wpia/gigi/pages/PasswordResetPage.java | 2 +- src/club/wpia/gigi/util/Notary.java | 2 +- 7 files changed, 8 insertions(+), 8 deletions(-) diff --git a/src/club/wpia/gigi/dbObjects/CATS.java b/src/club/wpia/gigi/dbObjects/CATS.java index c9f96d11..0f2b9ff0 100644 --- a/src/club/wpia/gigi/dbObjects/CATS.java +++ b/src/club/wpia/gigi/dbObjects/CATS.java @@ -97,7 +97,7 @@ public class CATS { } public static boolean isInCatsLimit(int uID, int testID) { - try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `cats_passed` WHERE `user_id` = ? AND `variant_id` = ? AND`pass_date` > (now() - interval '1 months' * ?)")) { + try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `cats_passed` WHERE `user_id` = ? AND `variant_id` = ? AND`pass_date` > (now() - interval '1 months' * ?::INTEGER)")) { ps.setInt(1, uID); ps.setInt(2, testID); ps.setInt(3, TEST_MONTHS); diff --git a/src/club/wpia/gigi/dbObjects/Domain.java b/src/club/wpia/gigi/dbObjects/Domain.java index d7de209c..e8accbca 100644 --- a/src/club/wpia/gigi/dbObjects/Domain.java +++ b/src/club/wpia/gigi/dbObjects/Domain.java @@ -34,7 +34,7 @@ public class Domain implements IdCachable, Verifyable { } private static void checkInsert(String suffix) throws GigiApiException { - 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")) { + try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `domains` WHERE (`domain`=? OR (CONCAT('.', `domain`)=RIGHT(?,LENGTH(`domain`)+1) OR RIGHT(`domain`,LENGTH(?)+1)=CONCAT('.',?::VARCHAR))) AND `deleted` IS NULL")) { ps.setString(1, suffix); ps.setString(2, suffix); ps.setString(3, suffix); diff --git a/src/club/wpia/gigi/dbObjects/Group.java b/src/club/wpia/gigi/dbObjects/Group.java index 778c46e0..556395b2 100644 --- a/src/club/wpia/gigi/dbObjects/Group.java +++ b/src/club/wpia/gigi/dbObjects/Group.java @@ -74,7 +74,7 @@ public enum Group implements DBEnum { } public User[] getMembers(int offset, int count) { - try (GigiPreparedStatement gps = new GigiPreparedStatement("SELECT `user` FROM `user_groups` WHERE `permission`=?::`userGroup` AND `deleted` IS NULL OFFSET ? LIMIT ?", true)) { + try (GigiPreparedStatement gps = new GigiPreparedStatement("SELECT `user` FROM `user_groups` WHERE `permission`=?::`userGroup` AND `deleted` IS NULL OFFSET ?::INTEGER LIMIT ?::INTEGER", true)) { gps.setEnum(1, this); gps.setInt(2, offset); gps.setInt(3, count); diff --git a/src/club/wpia/gigi/dbObjects/Organisation.java b/src/club/wpia/gigi/dbObjects/Organisation.java index 3ce83c70..70299293 100644 --- a/src/club/wpia/gigi/dbObjects/Organisation.java +++ b/src/club/wpia/gigi/dbObjects/Organisation.java @@ -198,7 +198,7 @@ public class Organisation extends CertificateOwner { } public static Organisation[] getOrganisations(int offset, int count) { - 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)) { + try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `certOwners`.`id` FROM `organisations` INNER JOIN `certOwners` ON `certOwners`.`id`=`organisations`.`id` WHERE `certOwners`.`deleted` IS NULL OFFSET ?::INTEGER LIMIT ?::INTEGER", true)) { ps.setInt(1, offset); ps.setInt(2, count); GigiResultSet res = ps.executeQuery(); diff --git a/src/club/wpia/gigi/dbObjects/User.java b/src/club/wpia/gigi/dbObjects/User.java index a4c32014..b65dbc5a 100644 --- a/src/club/wpia/gigi/dbObjects/User.java +++ b/src/club/wpia/gigi/dbObjects/User.java @@ -591,7 +591,7 @@ public class User extends CertificateOwner { } public static User getResetWithToken(int id, String token) { - try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `memid` FROM `passwordResetTickets` WHERE `id`=? AND `token`=? AND `used` IS NULL AND `created` > CURRENT_TIMESTAMP - interval '1 hours' * ?")) { + try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `memid` FROM `passwordResetTickets` WHERE `id`=? AND `token`=? AND `used` IS NULL AND `created` > CURRENT_TIMESTAMP - interval '1 hours' * ?::INTEGER")) { ps.setInt(1, id); ps.setString(2, token); ps.setInt(3, PasswordResetPage.HOUR_MAX); @@ -631,7 +631,7 @@ public class User extends CertificateOwner { } public boolean isInVerificationLimit() { - try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `notary` INNER JOIN `names` ON `names`.`id`=`to` WHERE `names`.`uid` = ? AND `when` > (now() - (interval '1 month' * ?)) AND (`expire` IS NULL OR `expire` > now()) AND `notary`.`deleted` IS NULL;")) { + try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `notary` INNER JOIN `names` ON `names`.`id`=`to` WHERE `names`.`uid` = ? AND `when` > (now() - (interval '1 month' * ?::INTEGER)) AND (`expire` IS NULL OR `expire` > now()) AND `notary`.`deleted` IS NULL;")) { ps.setInt(1, getId()); ps.setInt(2, VERIFICATION_MONTHS); diff --git a/src/club/wpia/gigi/pages/PasswordResetPage.java b/src/club/wpia/gigi/pages/PasswordResetPage.java index 92937aea..3bfb894a 100644 --- a/src/club/wpia/gigi/pages/PasswordResetPage.java +++ b/src/club/wpia/gigi/pages/PasswordResetPage.java @@ -61,7 +61,7 @@ public class PasswordResetPage extends Page { @Override public SuccessMessageResult submit(HttpServletRequest req) throws GigiApiException { - try (GigiPreparedStatement passwordReset = new GigiPreparedStatement("UPDATE `passwordResetTickets` SET `used` = CURRENT_TIMESTAMP WHERE `used` IS NULL AND `created` < CURRENT_TIMESTAMP - interval '1 hours' * ?;")) { + try (GigiPreparedStatement passwordReset = new GigiPreparedStatement("UPDATE `passwordResetTickets` SET `used` = CURRENT_TIMESTAMP WHERE `used` IS NULL AND `created` < CURRENT_TIMESTAMP - interval '1 hours' * ?::INTEGER;")) { passwordReset.setInt(1, HOUR_MAX); passwordReset.execute(); } diff --git a/src/club/wpia/gigi/util/Notary.java b/src/club/wpia/gigi/util/Notary.java index da807522..36f1fa48 100644 --- a/src/club/wpia/gigi/util/Notary.java +++ b/src/club/wpia/gigi/util/Notary.java @@ -45,7 +45,7 @@ public class Notary { } public static boolean checkVerificationIsPossible(User agent, Name target) { - try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `notary` where `to`=? and `from`=? and `method` = ? ::`notaryType` AND `deleted` IS NULL AND `when` > (now() - interval '1 days' * ?)")) { + try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `notary` where `to`=? and `from`=? and `method` = ? ::`notaryType` AND `deleted` IS NULL AND `when` > (now() - interval '1 days' * ?::INTEGER)")) { ps.setInt(1, target.getId()); ps.setInt(2, agent.getId()); ps.setEnum(3, VerificationType.FACE_TO_FACE); -- 2.39.2