X-Git-Url: http://matita.cs.unibo.it/gitweb/?a=blobdiff_plain;f=server%2Fcom%2Fcompany%2FdbConnect.java;fp=server%2Fcom%2Fcompany%2FdbConnect.java;h=e7c7e20224ce72bb98cd83ffc7a5874e6cd44be1;hb=39f6e6d4d05ad6819318e8a372147dd3d4ec6af7;hp=28d851cb5bf38ba08393c067c30bf9e76b13c042;hpb=4b262f8a52b8822c469a7e934b67473af9bf37f4;p=logicplayer.git diff --git a/server/com/company/dbConnect.java b/server/com/company/dbConnect.java index 28d851c..e7c7e20 100644 --- a/server/com/company/dbConnect.java +++ b/server/com/company/dbConnect.java @@ -3,8 +3,6 @@ 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; @@ -33,10 +31,10 @@ public class dbConnect //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<=?"); + PreparedStatement preparedStmt = db.prepareStatement("DELETE FROM "+global.dbConstant.tbConfermaMail+" WHERE "+global.dbConstant.clTime+"<=?"); preparedStmt.setLong (1,(time()-600000)); preparedStmt.execute(); - preparedStmt = db.prepareStatement("DELETE FROM connessi WHERE time<=?"); + preparedStmt = db.prepareStatement("DELETE FROM "+global.dbConstant.tbConnessi+" WHERE "+global.dbConstant.clTime+"<=?"); preparedStmt.setLong (1,(time()-600000)); preparedStmt.execute(); } @@ -57,32 +55,35 @@ public class dbConnect 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"; + if (pass.length()>6) { + 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"; + } } + 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+"'"); + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbUtenti+" where "+global.dbConstant.clUser+"='"+user+"'"); if(returnQuery!=null) { if(returnQuery.next()) { - EmailSender email = new EmailSender(global.email, - global.emailPass, + EmailSender email = new EmailSender(global.emailConstant.email, + global.emailConstant.emailPass, "smtp.gmail.com", - global.email, + global.emailConstant.email, user, - "OGGETTO: recupero password", - "la tua password è "+returnQuery.getString("pass")); + "OGGETTO: recupero password LogicPlayer", + "la tua password di LogicPlayer è "+returnQuery.getString(global.dbConstant.clPass)); email.inviaEmail(); return "mail-pass-ok"; } @@ -98,24 +99,24 @@ public class dbConnect 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+"'"); + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbUtenti+" where "+global.dbConstant.clUser+"='"+user+"'"); if(returnQuery!=null) { if(returnQuery.next()) { - if(returnQuery.getString("user").compareTo(user)==0 && returnQuery.getString("pass").compareTo(pass)==0) + if(returnQuery.getString(global.dbConstant.clUser).compareTo(user)==0 && returnQuery.getString(global.dbConstant.clPass).compareTo(pass)==0) return "già-registrato"; } } - returnQuery=stmt.executeQuery("SELECT * FROM confmail where user='"+user+"'"); + returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConfermaMail+" where "+global.dbConstant.clUser+"='"+user+"'"); if(returnQuery!=null) { if(returnQuery.next()) { - ran=returnQuery.getString("hash"); - if(returnQuery.getString("pass").compareTo(pass)!=0) + ran=returnQuery.getString(global.dbConstant.clRandom); + if(returnQuery.getString(global.dbConstant.clPass).compareTo(pass)!=0) { - PreparedStatement preparedStmt = db.prepareStatement("UPDATE confmail SET pass =? where user=?"); + PreparedStatement preparedStmt = db.prepareStatement("UPDATE "+global.dbConstant.tbConfermaMail+" SET "+global.dbConstant.clPass+" =? where user=?"); preparedStmt.setString (1,pass); preparedStmt.setString (2,user); preparedStmt.execute(); @@ -125,8 +126,7 @@ public class dbConnect { 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(?,?,?,?)"); + PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbConfermaMail+" ("+global.dbConstant.clUser+","+global.dbConstant.clPass+","+global.dbConstant.clRandom+","+global.dbConstant.clTime+") VALUES(?,?,?,?)"); preparedStmt.setString (1,user); preparedStmt.setString (2,pass); preparedStmt.setString (3,ran); @@ -138,21 +138,20 @@ public class dbConnect { 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(?,?,?,?)"); + PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbConfermaMail+" ("+global.dbConstant.clUser+","+global.dbConstant.clPass+","+global.dbConstant.clRandom+","+global.dbConstant.clTime+") VALUES(?,?,?,?)"); preparedStmt.setString (1,user); preparedStmt.setString (2,pass); preparedStmt.setString (3,ran); - preparedStmt.setDouble (4,(double)time()); + preparedStmt.setDouble(4, (double) time()); preparedStmt.execute(); } - EmailSender email = new EmailSender(global.email, - global.emailPass, - "smtp.gmail.com", - global.email, + EmailSender email = new EmailSender(global.emailConstant.email, + global.emailConstant.emailPass, + global.emailConstant.smtpName, + global.emailConstant.email, user, - "OGGETTO: Registrazione App", - "per confermare la tua email clicka sul link: http://"+global.serverName+"/test?id="+ran); + "OGGETTO: Registrazione LogicPlayer", + "per confermare la tua email all'applicazione LogicPlayer clicka sul link: http://"+global.serverName+":"+global.portaHttpServer+"/test?id="+ran); email.inviaEmail(); return "mail-reg-ok"; } @@ -160,16 +159,16 @@ public class dbConnect //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+"'"); + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConfermaMail+" where "+global.dbConstant.clRandom+"='"+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")); + PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbUtenti+" ("+global.dbConstant.clUser+","+global.dbConstant.clPass+") VALUES(?,?)"); + preparedStmt.setString (1,returnQuery.getString(global.dbConstant.clUser)); + preparedStmt.setString (2,returnQuery.getString(global.dbConstant.clPass)); preparedStmt.execute(); - preparedStmt = db.prepareStatement("DELETE FROM confmail WHERE hash=?"); + preparedStmt = db.prepareStatement("DELETE FROM "+global.dbConstant.tbConfermaMail+" WHERE "+global.dbConstant.clRandom+"=?"); preparedStmt.setString (1,hash); preparedStmt.execute(); return "registrazione-ok"; @@ -182,12 +181,12 @@ public class dbConnect 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+"'"); + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbUtenti+" where "+global.dbConstant.clUser+"='"+user+"' and "+global.dbConstant.clPass+"='"+pass+"'"); if(returnQuery!=null) { if(returnQuery.next()) { - if(returnQuery.getString("user").compareTo(user)==0 && returnQuery.getString("pass").compareTo(pass)==0) + if(returnQuery.getString(global.dbConstant.clUser).compareTo(user)==0 && returnQuery.getString(global.dbConstant.clPass).compareTo(pass)==0) return putConnessi(user); } } @@ -201,7 +200,7 @@ public class dbConnect ArrayList split= suString.stringToArrayList(message); if(split.size()>=1) { - ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi WHERE tempkey='"+split.get(1)+"'"); + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConnessi+" WHERE "+global.dbConstant.clTempKey+"='"+split.get(1)+"'"); if(returnQuery!=null) { if(returnQuery.next()) @@ -218,28 +217,18 @@ public class dbConnect { 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(?,?,?,?)"); + PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbConnessi+" ("+global.dbConstant.clUser+","+global.dbConstant.clTempKey+","+global.dbConstant.clTime+") VALUES(?,?,?)"); preparedStmt.setString (1,user); preparedStmt.setString (2,ran); - preparedStmt.setBoolean (3,false); - preparedStmt.setDouble (4,(double)time()); + preparedStmt.setDouble(3, (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=?"); + PreparedStatement preparedStmt = db.prepareStatement("UPDATE "+global.dbConstant.tbConnessi+" SET "+global.dbConstant.clTime+" =? where "+global.dbConstant.clTempKey+"=?"); preparedStmt.setDouble (1,(double)time()); preparedStmt.setString (2,key); preparedStmt.execute(); @@ -248,12 +237,12 @@ public class dbConnect 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)+"'"); + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM " + global.dbConstant.tbConnessi + " where " + global.dbConstant.clTempKey + "='" + input.get(0) + "'"); String utente=null; if(returnQuery!=null) { if(returnQuery.next()) - utente=returnQuery.getString("user"); + utente=returnQuery.getString(global.dbConstant.clUser); } if(utente!=null) { @@ -264,7 +253,7 @@ public class dbConnect { if(MD5.digest(esercizio).contentEquals(input.get(2)))//se il digest è uguale { - PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO esercizi (esercizio,utente,click,time) VALUES(?,?,?,?)"); + PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO "+global.dbConstant.tbEsercizi+" ("+global.dbConstant.clEsercizio+","+global.dbConstant.clUser+","+global.dbConstant.clVoto+","+global.dbConstant.clTime+") VALUES(?,?,?,?)"); preparedStmt.setDouble(4, Double.parseDouble(input.get(3))); preparedStmt.setInt (3,Integer.valueOf(input.get(4))); preparedStmt.setString (2,utente); @@ -282,19 +271,19 @@ public class dbConnect ArrayList arrayResult=new ArrayList(); ArrayList input=suString.stringToArrayList(request); String result=null; - ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi where tempkey='"+input.get(0)+"'"); + ResultSet returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbConnessi+" where "+global.dbConstant.clTempKey+"='"+input.get(0)+"'"); String utente=null; if(returnQuery!=null) { if(returnQuery.next()) - utente=returnQuery.getString("user"); + utente=returnQuery.getString(global.dbConstant.clUser); } if(utente!=null) { - returnQuery=stmt.executeQuery("SELECT * FROM esercizi where utente='"+utente+"'"); + returnQuery=stmt.executeQuery("SELECT * FROM "+global.dbConstant.tbEsercizi+" where "+global.dbConstant.clUser+"='"+utente+"'"); while(returnQuery.next()) { - arrayResult.add(returnQuery.getString("esercizio")+"/"+MD5.digest(returnQuery.getString("esercizio"))+"/"+returnQuery.getDouble("time")+"/"+returnQuery.getInt("click")); + arrayResult.add(returnQuery.getString(global.dbConstant.clEsercizio)+"/"+MD5.digest(returnQuery.getString(global.dbConstant.clEsercizio))+"/"+returnQuery.getDouble(global.dbConstant.clTime)+"/"+returnQuery.getInt(global.dbConstant.clVoto)); } result=suString.multiToString(arrayResult); } @@ -307,7 +296,7 @@ public class dbConnect String result = login(input.get(0), input.get(1)); if(!result.contains("err")) { - PreparedStatement preparedStmt = db.prepareStatement("UPDATE utenti SET pass =? where user=?"); + PreparedStatement preparedStmt = db.prepareStatement("UPDATE "+global.dbConstant.tbUtenti+" SET "+global.dbConstant.clPass+" =? where "+global.dbConstant.clUser+"=?"); preparedStmt.setString (1,input.get(2)); preparedStmt.setString (2,input.get(0)); preparedStmt.execute(); @@ -317,9 +306,21 @@ public class dbConnect return "error"; } + private String esAndMD5() + { + String list=EsNameParser.esName(); + ArrayList esList=suString.stringToArrayList(list); + ArrayList ritorno=new ArrayList(); + for(String esercizio : esList) + { + ritorno.add(esercizio+"/"+MD5.digest(esercizio)); + } + return suString.multiToString(ritorno); + } + 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!"); + db = DriverManager.getConnection(global.dbConstant.dbLink+global.dbConstant.dbName, global.dbConstant.dbAdmin, global.dbConstant.dbPassword); stmt = db.createStatement(); deletetmp(); if (input.charAt(0) == '0') @@ -336,14 +337,8 @@ public class dbConnect 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) == '7') + return esAndMD5(); else if (input.charAt(0) == '8') return exerciseAccept(input.substring(2)); else if (input.charAt(0) == '9')