Is SQL injection possible even on a prepared statement

Question

I read many articles on Stack Overflow regarding how SQL injection can be prevented by using prepared statements

But is there any way to do SQL injection even on prepared statements or is it 100% safe?

Below is my java code

 String query = "SELECT * FROM Users WHERE username=? and password=?";

 ps=con.prepareStatement(query);  

 ps.setString(1,username);
 ps.setString(2,password);

 rs = ps.executeQuery();

 status = rs.next();

 if(status==true){
.....
}else{
....
}

I tried some sql injection queries like

Some Inputs:

SELECT * FROM users WHERE username = '[email protected]' OR 1 = 1 LIMIT 1 -- ' ] AND password = md5('1234');

SELECT * FROM users WHERE email = '[email protected]' AND password = md5('xxx') OR 1 = 1 -- ]');

I have also tried with some more queries but as the (single quote)' is escaped(/') none of the SQL injection queries seem to work.

Kindly suggest me if there are any SQL injection queries/techniques which can be applied to do SQL injection in the above code.

Answer 1

This query : String query = "SELECT * FROM Users WHERE username=? and password=?"; is safe, because whatever the parameters can be, it will still be executed as a simple select. At most, it will end browsing a whole table.

But prepared statement is just a tool and (bad) programmers may still misuse it.

Let's look at the following query

String query = "SELECT id, " + paramName + " FROM Users WHERE username=? and password=?";

where paramName would be a parameter name. It is only as safe as paramName is, because you use directly a variable to build the string that will be parsed by the database engine. Here PreparedStatement cannot help because JDBC does not allow to parameterize a column name.

So the rule here will be :

• avoid such a construct if you can !
• if you really need it, double check (regexes, list of allowed strings, etc.) that paramName cannot be anything other than what you expect because that control is the only prevention against SQL injection