/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package nl.uva.cs.lobcder.auth;

import lombok.Setter;
import lombok.extern.java.Log;
import nl.uva.cs.lobcder.util.Constants;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashSet;
import java.util.logging.Level;

/**
 *
 * @author skoulouz
 */
@Log
public class LocalDbAuth implements AuthI {

    @Setter
    private DataSource datasource = null;
    @Setter
    private PrincipalCacheI principalCache = null;
    private int attempts = 0;

    @Override
    public MyPrincipal checkToken(String uname,String token) {
//        Connection connection = null;
        MyPrincipal res = null;
        try {
            if (principalCache != null) {
                synchronized (principalCache) {
                    res = principalCache.getPrincipal(token);
                }
            }
            if (res == null) {
                
                int id;
                try (Connection connection = datasource.getConnection()) {
                    try (Statement s = connection.createStatement()) {
                        HashSet<String> roles = new HashSet<>();
                        //Fix this!!! Can use injection with % 
//                        fix
                        String query = "SELECT id, uname FROM auth_usernames_table WHERE token LIKE '" + token + "' AND uname LIKE '"+uname+"'";
                        try (ResultSet rs = s.executeQuery(query)) {
                            if (rs.next()) {
                                id = rs.getInt(1);
                                uname = rs.getString(2);
//                                roles.add("other");
                                roles.add(uname);
                            } else {
                                return null;
                            }
                            query = "SELECT roleName FROM auth_roles_tables WHERE unameRef = " + id;
                            try (ResultSet rs2 = s.executeQuery(query)) {
                                while (rs2.next()) {
                                    roles.add(rs2.getString(1));
                                }
                                res = new MyPrincipal(uname, roles, token);
                            }
                        }
                    }
                }
//                connection = datasource.getConnection();

            }
            if (principalCache != null) {
                synchronized (principalCache) {
                    principalCache.putPrincipal(token, res, new Date().getTime() + 10000);
                }
            }
            attempts = 0;
            return res;
        } catch (Exception ex) {
            if (ex instanceof SQLException
                    && attempts <= Constants.RECONNECT_NTRY) {
                attempts++;
                checkToken(uname, token);
            } else {
                LocalDbAuth.log.log(Level.SEVERE, null, ex);
                return null;
            }
        } finally {
//            try {
//                if (connection != null) {
//                    connection.close();
//                }
//            } catch (SQLException ex) {
//                LocalDbAuth.log.log(Level.SEVERE, null, ex);
//            }
        }
        return null;
    }
}

@@ -274,24 +274,59 @@
 Connection;%0A
+import java.sql.PreparedStatement;%0A
 import java.
@@ -336,24 +336,24 @@
 .ResultSet;%0A
-
 import java.
@@ -769,16 +769,17 @@
 g uname,
+ 
 String t
@@ -1091,25 +1091,9 @@
 ) %7B%0A
-                
 %0A
+
     
@@ -1351,56 +1351,76 @@
     
-//Fix this!!! Can use injection with %25 %0A//      
+try (PreparedStatement authStatement = connection.prepareStatement(%0A
     
@@ -1432,17 +1432,8 @@
     
-     fix%0A
     
@@ -1450,28 +1450,8 @@
     
-      String query =
  %22SE
@@ -1507,56 +1507,155 @@
 ken 
-LIKE '%22 + token + %22' AND uname LIKE '%22+
+= ? AND uname = ?%22)) %7B%0A                            authStatement.setString(1, token);%0A                            authStatement.setString(2, 
 uname
-+%22'%22
+)
 ;%0A  
@@ -1668,37 +1668,36 @@
                 
-try (
+    
 ResultSet rs = s
@@ -1695,17 +1695,29 @@
 et rs = 
-s
+authStatement
 .execute
@@ -1718,33 +1718,26 @@
 xecuteQuery(
-query)) %7B
+);
 %0A           
@@ -2126,79 +2126,235 @@
     
-query = %22SELECT roleName FROM auth_roles_tables WHERE unameRef = %22 +
+try (PreparedStatement roleStatement = connection.prepareStatement(%0A                                    %22SELECT roleName FROM auth_roles_tables WHERE unameRef = ?%22)) %7B%0A                                roleStatement.setInt(1,
  id
+)
 ;%0A  
@@ -2379,21 +2379,20 @@
         
-try (
+    
 ResultSe
@@ -2399,17 +2399,29 @@
 t rs2 = 
-s
+roleStatement
 .execute
@@ -2430,17 +2430,10 @@
 ery(
-query)) %7B
+);
 %0A   

Change query to auth