Mitigation of SQL Injection Attack using Prepared Statements (Parameterized Queries)
Last Updated :
20 Aug, 2024
SQL injection is one of the most common and dangerous vulnerabilities that can affect a database-driven application. Attackers can exploit these vulnerabilities by injecting malicious SQL code into input fields which can lead to unauthorized access, data breaches, or even complete loss of data.
In this article, We will discuss how these methods can help safeguard against SQL injection and provide examples of common attack scenarios.
What is SQL Injection?
- SQL Injection is a type of attack where malicious SQL code is inserted into input fields of an application, allowing the attacker to manipulate the underlying SQL queries executed by the database.
- This can lead to unauthorized data access, data corruption and even the complete compromise of the database.
- SQL injection exploits vulnerabilities in the application's handling of user input, bypassing authentication mechanisms or altering queries to benefit the attacker.
Terminology
- Validation: Validation is the process of checking if the input meets a set of criteria (such as a string containing no standalone single quotation marks).
- Sanitization: Sanitization is the process of modifying the input to ensure that it is valid (such as doubling single quotes).
- To prevent SQL injection, all inputs used in dynamic SQL should be properly validated, sanitized, and securely handled, avoiding direct concatenation within queries..
Anatomy of an SQL Attack
An SQL attack has the following two parts:
- Research: View the vulnerable parts of the user-end application that connect with the database.
- Attack: Input malicious fields that can morph the query to your own advantage.

Example1:
Consider the following piece of code for an authentication form written in Java:
Java
String query = "SELECT userName, balance FROM accounts"
+ "WHERE userID=" + request.getParameter("userID") +
"and password='" + request.getParameter("Password") + "'";
try
{
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(query);
while (rs.next())
{
page.addTableRow(rs.getString("userName"),
rs.getFloat("balance"));
}
}
catch (SQLException e)
{}
Under normal conditions, a user enters his or her userID and password, and this generates the following statement for execution:
SELECT userName, balance
FROM accounts
WHERE userID=512 and password='thisisyoda'
A possible SQL injection attack would exploit the password field to generate a boolean expression which would make the expression evaluate to true for all cases. Imagine setting the userID and password fields as
userID = 1' or '1' = '1
password = 1' or '1' = '1
The SQL statement then becomes
SELECT userName, balance
FROM accounts
WHERE userID='1' OR '1'='1' and
password='1' OR '1'='1'
The query will return a value because the condition (OR 1=1) is always true. In this way the system has authenticated the user without knowing the username and password.
The vulnerability can be mitigated using a prepared statement to create a parameterized query as follows:
Java
String query = "SELECT userName, balance "+
"FROM accounts WHERE userID = ?
and password = ?";
try {
PreparedStatement statement = connection.prepareStatement(query);
statement.setInt(1, request.getParameter("userID"));
ResultSet rs = statement.executeQuery();
while (rs.next())
{
page.addTableRow(rs.getString("userName"),
rs.getFloat("balance"));
}
} catch (SQLException e)
{ ... }
If an attacker attempts to give a value to the userID field that is not a simple integer, then statement.setInt() will throw a SQLException error rather than permitting the query to complete.
Example2:
Consider another type of attack during authentication:
Java
String query = "SELECT userID, userName, passwordHash"+
" FROM users WHERE userName = '"
+ request.getParameter("user") + "'";
int userID = -1;
HashMap userGroups = new HashMap();
try
{
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(query);
rs.first();
userID = rs.getInt("userID");
if (!hashOf(request.getParameter("password")).equals(rs.getString("passwordHash")))
{
throw BadLoginException();
}
String userGroupQuery = "SELECT group FROM groupMembership"+
" WHERE userID = " + userID;
rs = statement.executeQuery(userGroupQuery);
while (rs.next())
{
userGroup.put(rs.getString("group"), true);
}
}
catch (SQLException e){}
catch (BadLoginException e){}
A normal query would be as follows.
SELECT userID, userName, passwordHash
FROM users
WHERE userName = 'Anannya'
The attacker may inject the following into the userName field.
Anannya';
INSERT INTO groupMmbership (userID, group)
VALUES (SELECT userID FROM users
WHERE userName='Anannya', 'Administrator'); --
Due to this, the actual query will change to:
SELECT userID, userName, passwordHash FROM
users WHERE userName = 'Anannya';
INSERT INTO groupMmbership (userID, group)
VALUES (SELECT userID FROM users
WHERE userName='Anannya', 'Administrator'); --'
This will cause another SQL statement to get appended to the actual statement, causing the user to get added to the Administrator database. The attack can be mitigated by using a prepared statement with a parameterized query as follows.
Java
String query = "SELECT userID, userName, passwordHash"+
" FROM users WHERE userName = ?";
try
{
PreparedStatement statement =
connection.prepareStatement(userLoginQuery);
statement.setString(1, request.getParameter("user"));
ResultSet rs = statement.executeQuery();
}
Example3:
Consider another example of query vulnerability discussed below:
Java
String query = "INSERT INTO users VALUES(" +
request.getParameter("userName") + ");";
A general query will be:
INSERT INTO users VALUES("Anannya")
Consider if the attacker enters the following query into the userName field:
"Anannya); DROP TABLE users;"
The query will then change to:
INSERT INTO users VALUES("Anannya"); DROP TABLE users;
This query completely deletes the users table upon its execution. A workaround here, again, is a prepared statement.
How does using a Prepared Statement in Java help?
A prepared statement "sanitizes" the input. This means it makes sure that whatever the user enters is treated as a string literal in SQL and NOT as a part of the SQL query. It may also escape certain characters and detect/remove malicious code. In other languages such as PHP, filter_input or filter_input_array can be used to sanitize the string.
Conclusion
SQL injection is a serious threat to the security of any application that interacts with a database. Through a combination of validation, sanitization, and the use of prepared statements, developers can protect their systems from these attacks. By understanding the anatomy of an SQL attack and implementing best practices in coding, the risk of SQL injection can be minimized, ensuring a more secure and reliable application.
Similar Reads
Authentication Bypass using SQL Injection on Login Page SQL injection is a technique used to exploit user data through web page inputs by injecting SQL commands as statements. Basically, these statements can be used to manipulate the applicationâs web server by malicious users. SQL injection is a code injection technique that might destroy your database.
3 min read
Difference between Statement and PreparedStatement 1. Statement : It is used for accessing your database. Statement interface cannot accept parameters and useful when you are using static SQL statements at runtime. If you want to run SQL query only once then this interface is preferred over PreparedStatement. Example - //Creating The Statement Objec
2 min read
Command Injection Vulnerability and Mitigation Command injection is basically injection of operating system commands to be executed through a web-app. The purpose of the command injection attack is to inject and execute commands specified by the attacker in the vulnerable application. In situation like this, the application, which executes unwan
3 min read
How to execute an SQL query and fetch results using PHP ? In this article, we will discuss how to execute an SQL query and how to fetch its result? We can perform a query against the database using the PHP mysqli_query() method. Syntax: We can use the mysqli_query( ) method in two ways: Object-oriented styleProcedural style Parameters: connection: It is r
3 min read
SQL Injection that Gets Around mysql_real_escape_string() Function SQL injection is a serious security vulnerability that occurs when an attacker can manipulate SQL queries executed in a database. In an attempt to prevent SQL injection, developers often use functions like mysql_real_escape_string() in PHP to escape special characters. However, it's crucial to under
4 min read
Parameterize an PostgreSQL IN clause In PostgreSQL, the IN clause is a powerful tool for filtering data based on a set of specified values. However, when dealing with dynamic values or user input, it's essential to parameterize the IN clause to prevent SQL injection vulnerabilities and improve query performance. In this article, we'll
4 min read