How to Fix SQL Injection Using Java PreparedStatement parameterized queries?

What is SQL injection?

SQL injection happens when you inject some content into a SQL query string, and the result modifies the syntax of your query in ways you didn't intend.

Injected SQL commands can alter SQL statement and compromise the security of an application.

SQL Injection Based on 1=1 is Always True

SELECT * FROM Users WHERE UserId = 105

Injection using “1=1”

SELECT * FROM Users WHERE UserId = 105 or 1=1;

SQL Injection Based on ""="" is Always True

SELECT * FROM Users WHERE Name ="John Doe" AND Pass ="myPass"

Injection using “=”

SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""

Vulnerable Usage

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.

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.

Fix SQL injection using PreparedStatement

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.

Advantage of PreparedStatement?

Improves performance: The performance of the application will be faster if you use PreparedStatement interface because query is compiled only once.