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