server/com/company/dbConnect.java

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