4 import java.io.IOException;
6 import java.math.BigInteger;
7 import java.security.MessageDigest;
8 import java.security.NoSuchAlgorithmException;
10 import java.util.Random;
12 import java.sql.Timestamp;
14 import java.util.ArrayList;
16 import java.lang.Double;
18 public class dbConnect
20 private static Connection db;
21 private static Statement stmt;
26 public static Long time()
28 java.util.Date date= new java.util.Date();
29 Timestamp now=new Timestamp(date.getTime());
33 //pulisce confmail e connessi da tutte le richieste maggiori di 10 minuti
34 private static void deletetmp() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
36 PreparedStatement preparedStmt = db.prepareStatement("DELETE FROM confmail WHERE time<=?");
37 preparedStmt.setLong (1,(time()-600000));
38 preparedStmt.execute();
39 preparedStmt = db.prepareStatement("DELETE FROM connessi WHERE time<=?");
40 preparedStmt.setLong (1,(time()-600000));
41 preparedStmt.execute();
44 //divide la stringa in user e password
45 private static String splitUsrPass(String userAndPass, int code) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
49 ArrayList<String> userPass= suString.stringToArrayList(userAndPass);
50 if(userPass.size()>=1)
52 if(userPass.size()>=2)
56 if(!user.contains("@studio.unibo.it") && user.length()<16)
61 return registration(user, pass);
63 return login(user, pass);
65 return recupero(user);
71 //routin di gestione recupero pass
72 private static String recupero(String user) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
74 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM utenti where user='"+user+"'");
77 if(returnQuery.next())
79 EmailSender email = new EmailSender(global.email,
84 "OGGETTO: recupero password",
85 "la tua password è "+returnQuery.getString("pass"));
87 return "mail-pass-ok";
97 //routin di gestione della registrazione
98 private static String registration(String user, String pass) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
101 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM utenti where user='"+user+"'");
102 if(returnQuery!=null)
104 if(returnQuery.next())
106 if(returnQuery.getString("user").compareTo(user)==0 && returnQuery.getString("pass").compareTo(pass)==0)
107 return "già-registrato";
110 returnQuery=stmt.executeQuery("SELECT * FROM confmail where user='"+user+"'");
111 if(returnQuery!=null)
113 if(returnQuery.next())
115 ran=returnQuery.getString("hash");
116 if(returnQuery.getString("pass").compareTo(pass)!=0)
118 PreparedStatement preparedStmt = db.prepareStatement("UPDATE confmail SET pass =? where user=?");
119 preparedStmt.setString (1,pass);
120 preparedStmt.setString (2,user);
121 preparedStmt.execute();
126 Random random = new Random();
127 ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
128 //returnQuery=stmt.executeQuery("INSERT INTO confmail VALUES('"+user+"','"+pass+"',"+hash+"')");
129 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO confmail (user,pass,hash,time) VALUES(?,?,?,?)");
130 preparedStmt.setString (1,user);
131 preparedStmt.setString (2,pass);
132 preparedStmt.setString (3,ran);
133 preparedStmt.setDouble (4,(double)time());
134 preparedStmt.execute();
139 Random random = new Random();
140 ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
141 //returnQuery=stmt.executeQuery("INSERT INTO confmail VALUES('"+user+"','"+pass+"',"+hash+"')");
142 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO confmail (user,pass,hash,time) VALUES(?,?,?,?)");
143 preparedStmt.setString (1,user);
144 preparedStmt.setString (2,pass);
145 preparedStmt.setString (3,ran);
146 preparedStmt.setDouble (4,(double)time());
147 preparedStmt.execute();
149 EmailSender email = new EmailSender(global.email,
154 "OGGETTO: Registrazione App",
155 "per confermare la tua email clicka sul link: http://"+global.serverName+"/test?id="+ran);
157 return "mail-reg-ok";
160 //routin conferma registrazione
161 private static String confirmRegistration(String hash) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
163 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM confmail where hash='"+hash+"'");
164 if(returnQuery!=null)
166 if(returnQuery.next())
168 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO utenti (user,pass) VALUES(?,?)");
169 preparedStmt.setString (1,returnQuery.getString("user"));
170 preparedStmt.setString (2,returnQuery.getString("pass"));
171 preparedStmt.execute();
172 preparedStmt = db.prepareStatement("DELETE FROM confmail WHERE hash=?");
173 preparedStmt.setString (1,hash);
174 preparedStmt.execute();
175 return "registrazione-ok";
178 return "registrazione-error";
181 //routin di gestione login
182 private static String login(String user, String pass) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
185 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM utenti where user='"+user+"' and pass='"+pass+"'");
186 if(returnQuery!=null)
188 if(returnQuery.next())
190 if(returnQuery.getString("user").compareTo(user)==0 && returnQuery.getString("pass").compareTo(pass)==0)
191 return putConnessi(user);
194 return "login-error";
198 //informazioni sulla connessione dell'utente
199 private String infoConnessi(String message) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
201 ArrayList<String> split= suString.stringToArrayList(message);
204 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi WHERE tempkey='"+split.get(1)+"'");
205 if(returnQuery!=null)
207 if(returnQuery.next())
213 return "request-login";
216 //inserisce l'utente in connessi
217 private static String putConnessi(String user) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
219 Random random = new Random();
220 String ran=String.valueOf(random.nextInt(100000000)+10000000)+String.valueOf(random.nextInt(100000000)+10000000);
221 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO connessi (user,tempkey,thread,time) VALUES(?,?,?,?)");
222 preparedStmt.setString (1,user);
223 preparedStmt.setString (2,ran);
224 preparedStmt.setBoolean (3,false);
225 preparedStmt.setDouble (4,(double)time());
226 preparedStmt.execute();
230 //segnalare morte di un thread
231 private String threadDead(String key) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
233 PreparedStatement preparedStmt = db.prepareStatement("DELETE FROM confmail WHERE tempkey=?");
234 preparedStmt.setString (1,key);
235 preparedStmt.execute();
239 //remposta il rimer dell'utente nella tabella connessi
240 private String connessiTimer(String key) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException
242 PreparedStatement preparedStmt = db.prepareStatement("UPDATE connessi SET time =? where tempkey=?");
243 preparedStmt.setDouble (1,(double)time());
244 preparedStmt.setString (2,key);
245 preparedStmt.execute();
246 return "timer-settato";
249 private String exerciseAccept(String request) throws SQLException, IOException, NoSuchAlgorithmException {
250 ArrayList<String> input=suString.stringToArrayList(request);
251 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi where tempkey='"+input.get(0)+"'");
253 if(returnQuery!=null)
255 if(returnQuery.next())
256 utente=returnQuery.getString("user");
260 String[] esercizi=suString.stringToVectorString(EsNameParser.esName());
261 for(String esercizio : esercizi)
263 if(esercizio.contentEquals(input.get(1)))//se l'esercizio esiste
265 if(MD5.digest(esercizio).contentEquals(input.get(2)))//se il digest è uguale
267 PreparedStatement preparedStmt = db.prepareStatement("INSERT INTO esercizi (esercizio,utente,click,time) VALUES(?,?,?,?)");
268 preparedStmt.setDouble(4, Double.parseDouble(input.get(3)));
269 preparedStmt.setInt (3,Integer.valueOf(input.get(4)));
270 preparedStmt.setString (2,utente);
271 preparedStmt.setString (1,input.get(1));
272 preparedStmt.execute();
281 private String userExercise(String request) throws SQLException {
282 ArrayList<String> arrayResult=new ArrayList<String>();
283 ArrayList<String> input=suString.stringToArrayList(request);
285 ResultSet returnQuery=stmt.executeQuery("SELECT * FROM connessi where tempkey='"+input.get(0)+"'");
287 if(returnQuery!=null)
289 if(returnQuery.next())
290 utente=returnQuery.getString("user");
294 returnQuery=stmt.executeQuery("SELECT * FROM esercizi where utente='"+utente+"'");
295 while(returnQuery.next())
297 arrayResult.add(returnQuery.getString("esercizio")+"/"+MD5.digest(returnQuery.getString("esercizio"))+"/"+returnQuery.getDouble("time")+"/"+returnQuery.getInt("click"));
299 result=suString.multiToString(arrayResult);
304 private String cambiaPass(String request) throws ClassNotFoundException, SQLException, InstantiationException, IOException, IllegalAccessException {
305 ArrayList<String> input=suString.stringToArrayList(request);
306 if(input.size()>=3) {
307 String result = login(input.get(0), input.get(1));
308 if(!result.contains("err"))
310 PreparedStatement preparedStmt = db.prepareStatement("UPDATE utenti SET pass =? where user=?");
311 preparedStmt.setString (1,input.get(2));
312 preparedStmt.setString (2,input.get(0));
313 preparedStmt.execute();
320 public String dbQuery(String input) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException, NoSuchAlgorithmException {
321 Class.forName("com.mysql.jdbc.Driver").newInstance();
322 db = DriverManager.getConnection("jdbc:mysql://localhost/logintesi", "root", "csc@CerCo!");
323 stmt = db.createStatement();
325 if (input.charAt(0) == '0')
326 return splitUsrPass(input.substring(2), 0);
327 else if (input.charAt(0) == '1')
328 return splitUsrPass(input.substring(2), 1);
329 else if (input.charAt(0) == '2')
330 return recupero(input.substring(2));
331 else if (input.charAt(0) == '3')
332 return confirmRegistration(input.substring(1));
333 else if (input.charAt(0) == '4')
334 return EsNameParser.esName();
335 else if (input.charAt(0) == '5')
336 return infoConnessi(input.substring(2));
337 else if (input.charAt(0) == '6')
338 return connessiTimer(input.substring(2));
339 /*else if (input.charAt(0) == '7')
340 return threadDead(input.substring(2));*/
341 /*else if(input.charAt(0)=='8')
342 return aggToClient(input.substring(2));
343 else if(input.charAt(0)=='9')
344 return aggForClient(input.substring(2));
345 else if(input.charAt(0)=='a' && input.charAt(1)=='0')
346 return check(input.substring(3));*/
347 else if (input.charAt(0) == '8')
348 return exerciseAccept(input.substring(2));
349 else if (input.charAt(0) == '9')
350 return userExercise(input.substring(2));
351 else if(input.charAt(0)=='a' && input.charAt(1)=='0')
352 return cambiaPass(input.substring(3));