--- /dev/null
+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<String> 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<String> 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<String> 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<String> arrayResult=new ArrayList<String>();
+ ArrayList<String> 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<String> 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";
+ }
+}
projects / logicplayer.git / blobdiff