Server/server/tesi/src/com/company/dbConnect.java

   1 package com.company;
   2
   3 import java.sql.*;
   4 import java.io.IOException;
   5
   6 import java.math.BigInteger;
   7 import java.security.MessageDigest;
   8 import java.security.NoSuchAlgorithmException;
   9
  10 import java.util.Random;
  11
  12 import java.sql.Timestamp;
  13
  14 import java.util.ArrayList;
  15
  16 import java.lang.Double;
  17
  18 public class dbConnect
  19 {
  20     private static Connection db;
  21     private static Statement stmt;
  22     dbConnect()
  23     {}
  24
  25
  26     public static Long time()
  27     {
  28         java.util.Date date= new java.util.Date();
  29         Timestamp now=new Timestamp(date.getTime());
  30         return now.getTime();
  31     }
  32
  33     //pulisce confmail e connessi da tutte le richieste maggiori di 10 minuti
  34     private static void deletetmp() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
  35     {
  36         PreparedStatement preparedStmt = db.prepareStatement("DELETE FROM confmail WHERE time<=?");
  37         preparedStmt.setLong (1,(time()-600000));
  38         preparedStmt.execute();
  39         preparedStmt = db.prepareStatement("DELETE FROM connessi WHERE time<=?");
  40         preparedStmt.setLong (1,(time()-600000));
  41         preparedStmt.execute();
  42     }
  43
  44     //divide la stringa in user e password
  45     private static String splitUsrPass(String userAndPass, int code) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
  46     {
  47         String user=null;
  48         String pass=null;
  49         ArrayList<String> userPass= suString.stringToArrayList(userAndPass);
  50         if(userPass.size()>=1)
  51             user=userPass.get(0);
  52         if(userPass.size()>=2)
  53             pass=userPass.get(1);
  54
  55         assert user != null;
  56         if(!user.contains("@studio.unibo.it") && user.length()<16)
  57             return "mail-error";
  58         else {
  59             assert pass != null;
  60             if(code==0)
  61                 return registration(user, pass);
  62             else if(code==1)
  63                 return login(user, pass);
  64             else if(code==2)
  65                 return recupero(user);
  66             else
  67                 return "error";
  68         }
  69     }
  70
  71     //routin di gestione recupero pass
  72     private static String recupero(String user) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
  73     {
  74         ResultSet returnQuery=stmt.executeQuery("SELECT * FROM utenti where user='"+user+"'");
  75         if(returnQuery!=null)
  76         {
  77             if(returnQuery.next())
  78             {
  79                 EmailSender email = new EmailSender(global.email,
  80                         global.emailPass,
  81                         "smtp.gmail.com",
  82                         global.email,
  83                         user,
  84                         "OGGETTO: recupero password",
  85                         "la tua password è "+returnQuery.getString("pass"));
  86                 email.inviaEmail();
  87                 return "mail-pass-ok";
  88             }
  89             else
  90                 return "user-error";
  91         }
  92         else
  93             return "user-error";
  94     }
  95
  96
  97     //routin di gestione della registrazione
  98     private static String registration(String user, String pass) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
  99     {
 100         String ran;
 101         ResultSet returnQuery=stmt.executeQuery("SELECT * FROM utenti where user='"+user+"'");
 102         if(returnQuery!=null)
 103         {
 104             if(returnQuery.next())
 105             {
 106                 if(returnQuery.getString("user").compareTo(user)==0 && returnQuery.getString("pass").compareTo(pass)==0)
 107                     return "già-registrato";
 108             }
 109         }
 110         returnQuery=stmt.executeQuery("SELECT * FROM confmail where user='"+user+"'");
 111         if(returnQuery!=null)
 112         {
 113             if(returnQuery.next())
 114             {
 115                 ran=returnQuery.getString("hash");
 116                 if(returnQuery.getString("pass").compareTo(pass)!=0)
 117                 {
 118                     PreparedStatement preparedStmt = db.prepareStatement("UPDATE confmail SET pass =? where user=?");
 119                     preparedStmt.setString (1,pass);
 120                     preparedStmt.setString (2,user);
 121                     preparedStmt.execute();
 122                 }
 123             }
 124             else
 125             {
 126                 Random random = new Random();
 127                 ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
 128                 //returnQuery=stmt.executeQuery("INSERT INTO confmail VALUES('"+user+"','"+pass+"',"+hash+"')");
 129                 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO confmail (user,pass,hash,time) VALUES(?,?,?,?)");
 130                 preparedStmt.setString (1,user);
 131                 preparedStmt.setString (2,pass);
 132                 preparedStmt.setString (3,ran);
 133                 preparedStmt.setDouble (4,(double)time());
 134                 preparedStmt.execute();
 135             }
 136         }
 137         else
 138         {
 139             Random random = new Random();
 140             ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
 141             //returnQuery=stmt.executeQuery("INSERT INTO confmail VALUES('"+user+"','"+pass+"',"+hash+"')");
 142             PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO confmail (user,pass,hash,time) VALUES(?,?,?,?)");
 143             preparedStmt.setString (1,user);
 144             preparedStmt.setString (2,pass);
 145             preparedStmt.setString (3,ran);
 146             preparedStmt.setDouble (4,(double)time());
 147             preparedStmt.execute();
 148         }
 149         EmailSender email = new EmailSender(global.email,
 150                 global.emailPass,
 151                 "smtp.gmail.com",
 152                 global.email,
 153                 user,
 154                 "OGGETTO: Registrazione App",
 155                 "per confermare la tua email clicka sul link: http://"+global.serverName+"/test?id="+ran);
 156         email.inviaEmail();
 157         return "mail-reg-ok";
 158     }
 159
 160     //routin conferma registrazione
 161     private static String confirmRegistration(String hash) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
 162     {
 163         ResultSet returnQuery=stmt.executeQuery("SELECT * FROM confmail where hash='"+hash+"'");
 164         if(returnQuery!=null)
 165         {
 166             if(returnQuery.next())
 167             {
 168                 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO utenti (user,pass) VALUES(?,?)");
 169                 preparedStmt.setString (1,returnQuery.getString("user"));
 170                 preparedStmt.setString (2,returnQuery.getString("pass"));
 171                 preparedStmt.execute();
 172                 preparedStmt = db.prepareStatement("DELETE FROM confmail WHERE hash=?");
 173                 preparedStmt.setString (1,hash);
 174                 preparedStmt.execute();
 175                 return "registrazione-ok";
 176             }
 177         }
 178         return "registrazione-error";
 179     }
 180
 181     //routin di gestione login
 182     private static String login(String user, String pass) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
 183     {
 184
 185         ResultSet returnQuery=stmt.executeQuery("SELECT * FROM utenti where user='"+user+"' and pass='"+pass+"'");
 186         if(returnQuery!=null)
 187         {
 188             if(returnQuery.next())
 189             {
 190                 if(returnQuery.getString("user").compareTo(user)==0 && returnQuery.getString("pass").compareTo(pass)==0)
 191                     return putConnessi(user);
 192             }
 193         }
 194         return "login-error";
 195     }
 196
 197
 198     //informazioni sulla connessione dell'utente
 199     private String infoConnessi(String message) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
 200     {
 201         ArrayList<String> split= suString.stringToArrayList(message);
 202         if(split.size()>=1)
 203         {
 204             ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi WHERE tempkey='"+split.get(1)+"'");
 205             if(returnQuery!=null)
 206             {
 207                 if(returnQuery.next())
 208                 {
 209                     return "loggato";
 210                 }
 211             }
 212         }
 213         return "request-login";
 214     }
 215
 216     //inserisce l'utente in connessi
 217     private static String putConnessi(String user) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
 218     {
 219         Random random = new Random();
 220         String ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
 221         PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO connessi (user,tempkey,thread,time) VALUES(?,?,?,?)");
 222         preparedStmt.setString (1,user);
 223         preparedStmt.setString (2,ran);
 224         preparedStmt.setBoolean (3,false);
 225         preparedStmt.setDouble (4,(double)time());
 226         preparedStmt.execute();
 227         return ran;
 228     }
 229
 230     //segnalare morte di un thread
 231     private String threadDead(String key) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
 232     {
 233         PreparedStatement preparedStmt = db.prepareStatement("DELETE FROM confmail WHERE tempkey=?");
 234         preparedStmt.setString (1,key);
 235         preparedStmt.execute();
 236         return "kill-ok";
 237     }
 238
 239     //remposta il rimer dell'utente nella tabella connessi
 240     private String connessiTimer(String key) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
 241     {
 242         PreparedStatement preparedStmt = db.prepareStatement("UPDATE connessi SET time =? where tempkey=?");
 243         preparedStmt.setDouble (1,(double)time());
 244         preparedStmt.setString (2,key);
 245         preparedStmt.execute();
 246         return "timer-settato";
 247     }
 248
 249     private String exerciseAccept(String request) throws SQLException, IOException, NoSuchAlgorithmException {
 250         ArrayList<String> input=suString.stringToArrayList(request);
 251         ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi where tempkey='"+input.get(0)+"'");
 252         String utente=null;
 253         if(returnQuery!=null)
 254         {
 255             if(returnQuery.next())
 256                 utente=returnQuery.getString("user");
 257         }
 258         if(utente!=null)
 259         {
 260             String[] esercizi=suString.stringToVectorString(EsNameParser.esName());
 261             for(String esercizio : esercizi)
 262             {
 263                 if(esercizio.contentEquals(input.get(1)))//se l'esercizio esiste
 264                 {
 265                     if(MD5.digest(esercizio).contentEquals(input.get(2)))//se il digest è uguale
 266                     {
 267                         PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO esercizi (esercizio,utente,click,time) VALUES(?,?,?,?)");
 268                         preparedStmt.setDouble(4, Double.parseDouble(input.get(3)));
 269                         preparedStmt.setInt (3,Integer.valueOf(input.get(4)));
 270                         preparedStmt.setString (2,utente);
 271                         preparedStmt.setString (1,input.get(1));
 272                         preparedStmt.execute();
 273                         return "es-ok";
 274                     }
 275                 }
 276             }
 277         }
 278         return "es-err";
 279     }
 280
 281     private String userExercise(String request) throws SQLException {
 282         ArrayList<String> arrayResult=new ArrayList<String>();
 283         ArrayList<String> input=suString.stringToArrayList(request);
 284         String result=null;
 285         ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi where tempkey='"+input.get(0)+"'");
 286         String utente=null;
 287         if(returnQuery!=null)
 288         {
 289             if(returnQuery.next())
 290                 utente=returnQuery.getString("user");
 291         }
 292         if(utente!=null)
 293         {
 294             returnQuery=stmt.executeQuery("SELECT * FROM esercizi where utente='"+utente+"'");
 295             while(returnQuery.next())
 296             {
 297                 arrayResult.add(returnQuery.getString("esercizio")+"/"+MD5.digest(returnQuery.getString("esercizio"))+"/"+returnQuery.getDouble("time")+"/"+returnQuery.getInt("click"));
 298             }
 299             result=suString.multiToString(arrayResult);
 300         }
 301         return result;
 302     }
 303
 304     private String cambiaPass(String request) throws ClassNotFoundException, SQLException, InstantiationException, IOException, IllegalAccessException {
 305         ArrayList<String> input=suString.stringToArrayList(request);
 306         if(input.size()>=3) {
 307             String result = login(input.get(0), input.get(1));
 308             if(!result.contains("err"))
 309             {
 310                 PreparedStatement preparedStmt = db.prepareStatement("UPDATE utenti SET pass =? where user=?");
 311                 preparedStmt.setString (1,input.get(2));
 312                 preparedStmt.setString (2,input.get(0));
 313                 preparedStmt.execute();
 314                 return "cambiata";
 315             }
 316         }
 317         return "error";
 318     }
 319
 320     public String dbQuery(String input) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException, NoSuchAlgorithmException {
 321         Class.forName("com.mysql.jdbc.Driver").newInstance();
 322         db = DriverManager.getConnection("jdbc:mysql://localhost/logintesi", "root", "csc@CerCo!");
 323         stmt = db.createStatement();
 324         deletetmp();
 325         if (input.charAt(0) == '0')
 326             return splitUsrPass(input.substring(2), 0);
 327         else if (input.charAt(0) == '1')
 328             return splitUsrPass(input.substring(2), 1);
 329         else if (input.charAt(0) == '2')
 330             return recupero(input.substring(2));
 331         else if (input.charAt(0) == '3')
 332             return confirmRegistration(input.substring(1));
 333         else if (input.charAt(0) == '4')
 334             return EsNameParser.esName();
 335         else if (input.charAt(0) == '5')
 336             return infoConnessi(input.substring(2));
 337         else if (input.charAt(0) == '6')
 338             return connessiTimer(input.substring(2));
 339         /*else if (input.charAt(0) == '7')
 340             return threadDead(input.substring(2));*/
 341         /*else if(input.charAt(0)=='8')
 342             return aggToClient(input.substring(2));
 343         else if(input.charAt(0)=='9')
 344             return aggForClient(input.substring(2));
 345         else if(input.charAt(0)=='a' && input.charAt(1)=='0')
 346             return check(input.substring(3));*/
 347         else if (input.charAt(0) == '8')
 348             return exerciseAccept(input.substring(2));
 349         else if (input.charAt(0) == '9')
 350             return userExercise(input.substring(2));
 351         else if(input.charAt(0)=='a' && input.charAt(1)=='0')
 352             return cambiaPass(input.substring(3));
 353         return "error";
 354     }
 355 }