How to Fix SQL Injection Using Java PreparedStatement & CallableStatement
A PreparedStatement represents a precompiled SQL statement that can be executed multiple times without having to recompile for every execution.
PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE userid=? AND password=?"); stmt.setString(1, userid); stmt.setString(2, password); ResultSet rs = stmt.executeQuery();
This code is not vulnerable to SQL Injection because it correctly uses parameterized queries. By utilizing Java's PreparedStatement class, bind variables (i.e. the question marks) and the corresponding setString methods, SQL Injection can be easily prevented.
// Example #1 String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'"; Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(query);
This code is vulnerable to SQL Injection because it uses dynamic queries to concatenate malicious data to the query itself. Notice that it uses the Statement class instead of the PreparedStatement class.
// Example #2 String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'"; PreparedStatement stmt = connection.prepareStatement(query); ResultSet rs = stmt.executeQuery();
This code is also vulnerable to SQL Injection. Even though it uses the PreparedStatement class it is still creating the query dynamically via string concatenation.
Java CallableStatement class is used to execute SQL stored procedures. Invoking a stored procedure or a function using CallableStatement in itself is not vulnerable to SQL Injection; however, the underlying database code could be vulnerable. Refer to the Database Code section of this guide to see how to write secure database code.