Securing Java Applications from SQL injection

Question

If we pass 1=1 in an SQL query it will return all data as it becomes true for all conditions.

String query =  "select * from users where userId= 'abcd' or '1'='1'";

I was looking into PreparedStatement interface and found about setString method which appends the value of a column to prepare statement. I want to know the internally how it is preventing sql injections? If I pass something like this -

Connection con=DriverManager.getConnection(url,username,password);  
PreparedStatement query = con.prepareStatement("select * from users where userId=?");  
query.setString(1,userId);'

 

Answer 1

Ultimately this depends on the database and its JDBC driver.

For example the MySQL server supports prepared statements natively (ServerPreparedStatement and that means that when you execute query.executeQuery() the driver sends to the server the following data:

1. the prepared SQL statement (select * from users where userId=?)
2. the value of the first parameter ("abcd' or '1'='1")

The MySQL server in turn doesn't need to parse a complete SQL statement ("select * from users where userId= 'abcd'") and extract the comparison value ("abcd") from that.

Instead it can parse the prepared statement ("select * from users where userId=?") and take the comparison value from the second data element.

Caveat: you need to inform the MySQL connector that you want to use server side prepared statements by setting the useServerPrepStmts=true connection property, see the MySQL Connector documentation

---

If the SQL server implementation doesn't support prepared statements (or is not configured the use server side prepared statements), it is the job of the JDBC driver to change

1. the prepared SQL statement (select * from users where userId=?)
2. the value of the first parameter ("abcd' or '1'='1")

into a safe SQL query

select * from users where userId='abcd'' or ''1''=''1'

In the case of this simple query it is easy enough to escape the single quotes within the value, but for more complex values this is much more involved (see https://www.netsparker.com/blog/web-security/sql-injection-cheat-sheet/ for possible examples used in SQL injection attacks).

If you try to implement the escaping yourself chances are high that you miss some special case.