X-Git-Url: http://matita.cs.unibo.it/gitweb/?a=blobdiff_plain;ds=inline;f=server%2Fcom%2Fcompany%2FdbConnect.java;fp=server%2Fcom%2Fcompany%2FdbConnect.java;h=28d851cb5bf38ba08393c067c30bf9e76b13c042;hb=bd7a266f1a37cb373614b8fa3d0c1e832c4b82e3;hp=0000000000000000000000000000000000000000;hpb=edfa62efb21b128dce6de134a3fb0d85f77bd2b8;p=logicplayer.git diff --git a/server/com/company/dbConnect.java b/server/com/company/dbConnect.java new file mode 100644 index 0000000..28d851c --- /dev/null +++ b/server/com/company/dbConnect.java @@ -0,0 +1,355 @@ +package com.company; + +import java.sql.*; +import java.io.IOException; + +import java.math.BigInteger; +import java.security.MessageDigest; +import java.security.NoSuchAlgorithmException; + +import java.util.Random; + +import java.sql.Timestamp; + +import java.util.ArrayList; + +import java.lang.Double; + +public class dbConnect +{ + private static Connection db; + private static Statement stmt; + dbConnect() + {} + + + public static Long time() + { + java.util.Date date= new java.util.Date(); + Timestamp now=new Timestamp(date.getTime()); + return now.getTime(); + } + + //pulisce confmail e connessi da tutte le richieste maggiori di 10 minuti + private static void deletetmp() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException + { + PreparedStatement preparedStmt = db.prepareStatement("DELETE FROM confmail WHERE time<=?"); + preparedStmt.setLong (1,(time()-600000)); + preparedStmt.execute(); + preparedStmt = db.prepareStatement("DELETE FROM connessi WHERE time<=?"); + preparedStmt.setLong (1,(time()-600000)); + preparedStmt.execute(); + } + + //divide la stringa in user e password + private static String splitUsrPass(String userAndPass, int code) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException + { + String user=null; + String pass=null; + ArrayList userPass= suString.stringToArrayList(userAndPass); + if(userPass.size()>=1) + user=userPass.get(0); + if(userPass.size()>=2) + pass=userPass.get(1); + + assert user != null; + if(!user.contains("@studio.unibo.it") && user.length()<16) + return "mail-error"; + else { + assert pass != null; + if(code==0) + return registration(user, pass); + else if(code==1) + return login(user, pass); + else if(code==2) + return recupero(user); + else + return "error"; + } + } + + //routin di gestione recupero pass + private static String recupero(String user) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException + { + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM utenti where user='"+user+"'"); + if(returnQuery!=null) + { + if(returnQuery.next()) + { + EmailSender email = new EmailSender(global.email, + global.emailPass, + "smtp.gmail.com", + global.email, + user, + "OGGETTO: recupero password", + "la tua password è "+returnQuery.getString("pass")); + email.inviaEmail(); + return "mail-pass-ok"; + } + else + return "user-error"; + } + else + return "user-error"; + } + + + //routin di gestione della registrazione + private static String registration(String user, String pass) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException + { + String ran; + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM utenti where user='"+user+"'"); + if(returnQuery!=null) + { + if(returnQuery.next()) + { + if(returnQuery.getString("user").compareTo(user)==0 && returnQuery.getString("pass").compareTo(pass)==0) + return "già-registrato"; + } + } + returnQuery=stmt.executeQuery("SELECT * FROM confmail where user='"+user+"'"); + if(returnQuery!=null) + { + if(returnQuery.next()) + { + ran=returnQuery.getString("hash"); + if(returnQuery.getString("pass").compareTo(pass)!=0) + { + PreparedStatement preparedStmt = db.prepareStatement("UPDATE confmail SET pass =? where user=?"); + preparedStmt.setString (1,pass); + preparedStmt.setString (2,user); + preparedStmt.execute(); + } + } + else + { + Random random = new Random(); + ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000); + //returnQuery=stmt.executeQuery("INSERT INTO confmail VALUES('"+user+"','"+pass+"',"+hash+"')"); + PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO confmail (user,pass,hash,time) VALUES(?,?,?,?)"); + preparedStmt.setString (1,user); + preparedStmt.setString (2,pass); + preparedStmt.setString (3,ran); + preparedStmt.setDouble (4,(double)time()); + preparedStmt.execute(); + } + } + else + { + Random random = new Random(); + ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000); + //returnQuery=stmt.executeQuery("INSERT INTO confmail VALUES('"+user+"','"+pass+"',"+hash+"')"); + PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO confmail (user,pass,hash,time) VALUES(?,?,?,?)"); + preparedStmt.setString (1,user); + preparedStmt.setString (2,pass); + preparedStmt.setString (3,ran); + preparedStmt.setDouble (4,(double)time()); + preparedStmt.execute(); + } + EmailSender email = new EmailSender(global.email, + global.emailPass, + "smtp.gmail.com", + global.email, + user, + "OGGETTO: Registrazione App", + "per confermare la tua email clicka sul link: http://"+global.serverName+"/test?id="+ran); + email.inviaEmail(); + return "mail-reg-ok"; + } + + //routin conferma registrazione + private static String confirmRegistration(String hash) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException + { + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM confmail where hash='"+hash+"'"); + if(returnQuery!=null) + { + if(returnQuery.next()) + { + PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO utenti (user,pass) VALUES(?,?)"); + preparedStmt.setString (1,returnQuery.getString("user")); + preparedStmt.setString (2,returnQuery.getString("pass")); + preparedStmt.execute(); + preparedStmt = db.prepareStatement("DELETE FROM confmail WHERE hash=?"); + preparedStmt.setString (1,hash); + preparedStmt.execute(); + return "registrazione-ok"; + } + } + return "registrazione-error"; + } + + //routin di gestione login + private static String login(String user, String pass) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException + { + + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM utenti where user='"+user+"' and pass='"+pass+"'"); + if(returnQuery!=null) + { + if(returnQuery.next()) + { + if(returnQuery.getString("user").compareTo(user)==0 && returnQuery.getString("pass").compareTo(pass)==0) + return putConnessi(user); + } + } + return "login-error"; + } + + + //informazioni sulla connessione dell'utente + private String infoConnessi(String message) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException + { + ArrayList split= suString.stringToArrayList(message); + if(split.size()>=1) + { + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi WHERE tempkey='"+split.get(1)+"'"); + if(returnQuery!=null) + { + if(returnQuery.next()) + { + return "loggato"; + } + } + } + return "request-login"; + } + + //inserisce l'utente in connessi + private static String putConnessi(String user) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException + { + Random random = new Random(); + String ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000); + PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO connessi (user,tempkey,thread,time) VALUES(?,?,?,?)"); + preparedStmt.setString (1,user); + preparedStmt.setString (2,ran); + preparedStmt.setBoolean (3,false); + preparedStmt.setDouble (4,(double)time()); + preparedStmt.execute(); + return ran; + } + + //segnalare morte di un thread + private String threadDead(String key) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException + { + PreparedStatement preparedStmt = db.prepareStatement("DELETE FROM confmail WHERE tempkey=?"); + preparedStmt.setString (1,key); + preparedStmt.execute(); + return "kill-ok"; + } + + //remposta il rimer dell'utente nella tabella connessi + private String connessiTimer(String key) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException + { + PreparedStatement preparedStmt = db.prepareStatement("UPDATE connessi SET time =? where tempkey=?"); + preparedStmt.setDouble (1,(double)time()); + preparedStmt.setString (2,key); + preparedStmt.execute(); + return "timer-settato"; + } + + private String exerciseAccept(String request) throws SQLException, IOException, NoSuchAlgorithmException { + ArrayList input=suString.stringToArrayList(request); + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi where tempkey='"+input.get(0)+"'"); + String utente=null; + if(returnQuery!=null) + { + if(returnQuery.next()) + utente=returnQuery.getString("user"); + } + if(utente!=null) + { + String[] esercizi=suString.stringToVectorString(EsNameParser.esName()); + for(String esercizio : esercizi) + { + if(esercizio.contentEquals(input.get(1)))//se l'esercizio esiste + { + if(MD5.digest(esercizio).contentEquals(input.get(2)))//se il digest è uguale + { + PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO esercizi (esercizio,utente,click,time) VALUES(?,?,?,?)"); + preparedStmt.setDouble(4, Double.parseDouble(input.get(3))); + preparedStmt.setInt (3,Integer.valueOf(input.get(4))); + preparedStmt.setString (2,utente); + preparedStmt.setString (1,input.get(1)); + preparedStmt.execute(); + return "es-ok"; + } + } + } + } + return "es-err"; + } + + private String userExercise(String request) throws SQLException { + ArrayList arrayResult=new ArrayList(); + ArrayList input=suString.stringToArrayList(request); + String result=null; + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi where tempkey='"+input.get(0)+"'"); + String utente=null; + if(returnQuery!=null) + { + if(returnQuery.next()) + utente=returnQuery.getString("user"); + } + if(utente!=null) + { + returnQuery=stmt.executeQuery("SELECT * FROM esercizi where utente='"+utente+"'"); + while(returnQuery.next()) + { + arrayResult.add(returnQuery.getString("esercizio")+"/"+MD5.digest(returnQuery.getString("esercizio"))+"/"+returnQuery.getDouble("time")+"/"+returnQuery.getInt("click")); + } + result=suString.multiToString(arrayResult); + } + return result; + } + + private String cambiaPass(String request) throws ClassNotFoundException, SQLException, InstantiationException, IOException, IllegalAccessException { + ArrayList input=suString.stringToArrayList(request); + if(input.size()>=3) { + String result = login(input.get(0), input.get(1)); + if(!result.contains("err")) + { + PreparedStatement preparedStmt = db.prepareStatement("UPDATE utenti SET pass =? where user=?"); + preparedStmt.setString (1,input.get(2)); + preparedStmt.setString (2,input.get(0)); + preparedStmt.execute(); + return "cambiata"; + } + } + return "error"; + } + + public String dbQuery(String input) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException, NoSuchAlgorithmException { + Class.forName("com.mysql.jdbc.Driver").newInstance(); + db = DriverManager.getConnection("jdbc:mysql://localhost/logintesi", "root", "csc@CerCo!"); + stmt = db.createStatement(); + deletetmp(); + if (input.charAt(0) == '0') + return splitUsrPass(input.substring(2), 0); + else if (input.charAt(0) == '1') + return splitUsrPass(input.substring(2), 1); + else if (input.charAt(0) == '2') + return recupero(input.substring(2)); + else if (input.charAt(0) == '3') + return confirmRegistration(input.substring(1)); + else if (input.charAt(0) == '4') + return EsNameParser.esName(); + else if (input.charAt(0) == '5') + return infoConnessi(input.substring(2)); + else if (input.charAt(0) == '6') + return connessiTimer(input.substring(2)); + /*else if (input.charAt(0) == '7') + return threadDead(input.substring(2));*/ + /*else if(input.charAt(0)=='8') + return aggToClient(input.substring(2)); + else if(input.charAt(0)=='9') + return aggForClient(input.substring(2)); + else if(input.charAt(0)=='a' && input.charAt(1)=='0') + return check(input.substring(3));*/ + else if (input.charAt(0) == '8') + return exerciseAccept(input.substring(2)); + else if (input.charAt(0) == '9') + return userExercise(input.substring(2)); + else if(input.charAt(0)=='a' && input.charAt(1)=='0') + return cambiaPass(input.substring(3)); + return "error"; + } +}