X-Git-Url: https://code.wpia.club/?p=gigi.git;a=blobdiff_plain;f=src%2Forg%2Fcacert%2Fgigi%2FdbObjects%2FEmailAddress.java;h=a3208165b4d324bbfa13b6fca59fdee75ab40acf;hp=2acf59006e257f33fd7cf2f9bb493dbd05e23372;hb=f6ad4c3784fd9864b52def8a564006d1a459cf3d;hpb=9031f0cf4327eaef9ede27f83cadf0b44833193c

diff --git a/src/org/cacert/gigi/dbObjects/EmailAddress.java b/src/org/cacert/gigi/dbObjects/EmailAddress.java
index 2acf5900..a3208165 100644
--- a/src/org/cacert/gigi/dbObjects/EmailAddress.java
+++ b/src/org/cacert/gigi/dbObjects/EmailAddress.java
@@ -1,18 +1,22 @@
 package org.cacert.gigi.dbObjects;
 
 import java.io.IOException;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
+import java.util.Date;
+import java.util.LinkedList;
+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;
 import org.cacert.gigi.email.MailProbe;
 import org.cacert.gigi.localisation.Language;
+import org.cacert.gigi.output.template.SprintfCommand;
 import org.cacert.gigi.util.RandomToken;
 
-public class EmailAddress implements IdCachable {
+public class EmailAddress implements IdCachable, Verifyable {
+
+    public static final int REPING_MINIMUM_DELAY = 5 * 60 * 1000;
 
     private String address;
 
@@ -20,54 +24,67 @@ public class EmailAddress implements IdCachable {
 
     private User owner;
 
-    private String hash = null;
-
-    private EmailAddress(int id) throws SQLException {
-        PreparedStatement ps = DatabaseConnection.getInstance().prepare("SELECT memid, email, hash FROM `emails` WHERE id=? AND deleted=0");
-        ps.setInt(1, id);
+    private EmailAddress(int id) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `memid`, `email` FROM `emails` WHERE `id`=? AND `deleted` IS NULL")) {
+            ps.setInt(1, id);
 
-        ResultSet 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);
         }
-        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) {
+    public EmailAddress(User owner, String address, Locale mailLocale) throws GigiApiException {
+        address = address.toLowerCase();
         if ( !EmailProvider.MAIL.matcher(address).matches()) {
             throw new IllegalArgumentException("Invalid email.");
         }
         this.address = address;
         this.owner = owner;
-        this.hash = RandomToken.generateToken(16);
+        insert(Language.getInstance(mailLocale));
     }
 
-    public void insert(Language l) {
-        if (id != 0) {
-            throw new IllegalStateException("already inserted.");
-        }
+    private void insert(Language l) throws GigiApiException {
         try {
-            PreparedStatement ps = DatabaseConnection.getInstance().prepare("INSERT INTO `emails` SET memid=?, hash=?, email=?");
-            ps.setInt(1, owner.getId());
-            ps.setString(2, hash);
-            ps.setString(3, address);
             synchronized (EmailAddress.class) {
-                ps.execute();
-                id = DatabaseConnection.lastInsertId(ps);
+                if (id != 0) {
+                    throw new IllegalStateException("already inserted.");
+                }
+                try (GigiPreparedStatement psCheck = new GigiPreparedStatement("SELECT 1 FROM `emails` WHERE email=? AND deleted is NULL"); GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `emails` SET memid=?, email=?")) {
+                    ps.setInt(1, owner.getId());
+                    ps.setString(2, address);
+                    psCheck.setString(1, address);
+                    GigiResultSet res = psCheck.executeQuery();
+                    if (res.next()) {
+                        throw new GigiApiException("The email address is already known to the system.");
+                    }
+                    ps.execute();
+                    id = ps.lastInsertId();
+                }
                 myCache.put(this);
             }
-            MailProbe.sendMailProbe(l, "email", id, hash, address);
-        } catch (SQLException e) {
-            e.printStackTrace();
+            ping(l);
         } catch (IOException e) {
             e.printStackTrace();
         }
     }
 
+    private void ping(Language l) throws IOException {
+        String hash = RandomToken.generateToken(16);
+        try (GigiPreparedStatement statmt = new GigiPreparedStatement("INSERT INTO `emailPinglog` SET `when`=NOW(), `email`=?, `result`='', `uid`=?, `type`='active', `status`='open'::`pingState`, `challenge`=?")) {
+            statmt.setString(1, address);
+            statmt.setInt(2, owner.getId());
+            statmt.setString(3, hash);
+            statmt.execute();
+        }
+
+        MailProbe.sendMailProbe(l, "email", id, hash, address);
+    }
+
     public int getId() {
         return id;
     }
@@ -77,46 +94,76 @@ public class EmailAddress implements IdCachable {
     }
 
     public synchronized void verify(String hash) throws GigiApiException {
-        if (this.hash.equals(hash)) {
-
-            try {
-                PreparedStatement ps = DatabaseConnection.getInstance().prepare("UPDATE `emails` SET hash='' WHERE id=?");
-                ps.setInt(1, id);
-                ps.execute();
-                hash = "";
-
-                // Verify user with that primary email
-                PreparedStatement 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();
-                this.hash = "";
-            } catch (SQLException e) {
-                throw new GigiApiException(e);
-            }
-
-        } else {
-            throw new GigiApiException("Email verification hash is invalid.");
+        try (GigiPreparedStatement stmt = new GigiPreparedStatement("UPDATE `emailPinglog` SET `status`='success'::`pingState` WHERE `email`=? AND `uid`=? AND `type`='active' AND `challenge`=?")) {
+            stmt.setString(1, address);
+            stmt.setInt(2, owner.getId());
+            stmt.setString(3, hash);
+            stmt.executeUpdate();
+        }
+        // Verify user with that primary email
+        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();
         }
     }
 
     public boolean isVerified() {
-        return hash.isEmpty();
+        try (GigiPreparedStatement statmt = new GigiPreparedStatement("SELECT 1 FROM `emailPinglog` WHERE `email`=? AND `uid`=? AND `type`='active' AND `status`='success'")) {
+            statmt.setString(1, address);
+            statmt.setInt(2, owner.getId());
+            GigiResultSet e = statmt.executeQuery();
+            return e.next();
+        }
+    }
+
+    public Date getLastPing(boolean onlySuccess) {
+        Date lastExecution;
+        try (GigiPreparedStatement statmt = new GigiPreparedStatement("SELECT MAX(`when`) FROM `emailPinglog` WHERE `email`=? AND `uid`=? AND `type`='active'" + (onlySuccess ? " AND `status`='success'" : ""))) {
+            statmt.setString(1, address);
+            statmt.setInt(2, owner.getId());
+            GigiResultSet e = statmt.executeQuery();
+            if ( !e.next()) {
+                return null;
+            }
+            lastExecution = e.getTimestamp(1);
+        }
+        return lastExecution;
+    }
+
+    public synchronized void requestReping(Language l) throws IOException, GigiApiException {
+        Date lastExecution = getLastPing(false);
+
+        if (lastExecution != null && lastExecution.getTime() + REPING_MINIMUM_DELAY >= System.currentTimeMillis()) {
+            throw new GigiApiException(SprintfCommand.createSimple("Reping is only allowed after {0} minutes, yours end at {1}.", REPING_MINIMUM_DELAY / 60 / 1000, new Date(lastExecution.getTime() + REPING_MINIMUM_DELAY)));
+        }
+        ping(l);
+        return;
     }
 
     private static ObjectCache<EmailAddress> myCache = new ObjectCache<>();
 
-    public static EmailAddress getById(int id) throws IllegalArgumentException {
+    public static synchronized EmailAddress getById(int id) throws IllegalArgumentException {
         EmailAddress em = myCache.get(id);
         if (em == null) {
-            try {
-                synchronized (EmailAddress.class) {
-                    myCache.put(em = new EmailAddress(id));
-                }
-            } catch (SQLException e1) {
-                throw new IllegalArgumentException(e1);
-            }
+            myCache.put(em = new EmailAddress(id));
         }
         return em;
     }
+
+    public User getOwner() {
+        return owner;
+    }
+
+    public static EmailAddress[] findByAllEmail(String mail) {
+        LinkedList<EmailAddress> results = new LinkedList<EmailAddress>();
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `emails`.`id` FROM `emails` INNER JOIN `users` ON `users`.`id` = `emails`.`memid` INNER JOIN `certOwners` ON `certOwners`.`id` = `users`.`id` WHERE `emails`.`email` LIKE ? AND `emails`.`deleted` IS NULL AND `certOwners`.`deleted` IS NULL ORDER BY `users`.`id`, `emails`.`email` LIMIT 100")) {
+            ps.setString(1, mail);
+            GigiResultSet rs = ps.executeQuery();
+            while (rs.next()) {
+                results.add(EmailAddress.getById(rs.getInt(1)));
+            }
+            return results.toArray(new EmailAddress[results.size()]);
+        }
+    }
 }