+++ /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";
- }
-}