Mitigation of SQL Injection Attack using Prepared Statements (Parameterized Queries)

As explained in this article, an SQL Injection attack, or an SQLi, is a way of exploiting the underlying vulnerability of an SQL statement by inserting nefarious SQL statements into its entry field for execution. It first made its appearance in 1998, and ever since, it mostly targets retailers and bank accounts. When compounded with other forms of attacks such as DDOS attacks, cross-site scripting (XSS) or DNS hijacking, it can lead to large scale results.

Terminology:

  • Validation: Validation is the process of checking if the input meets a set of criteria (such as a string contains 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 avoid SQL injection, all input that are to be concatenated in dynamic SQL must be correctly filtered and sanitized.

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.

No_WAF

Example1:
Consider the following piece of code for an authentication form written in Java:

filter_none

edit
close

play_arrow

link
brightness_4
code

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) 
    {}

chevron_right


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:

filter_none

edit
close

play_arrow

link
brightness_4
code

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)
       { ... }

chevron_right


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:

filter_none

edit
close

play_arrow

link
brightness_4
code

String query = "SELECT userID, userName, passwordHash"+
               " FROM users WHERE userName = '" 
               + request.getParameter("user") + "'";
int userID = -1;
HashMap userGroups = new HashMap();
try 
{
  Satement 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){}

chevron_right


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.

filter_none

edit
close

play_arrow

link
brightness_4
code

String query = "SELECT userID, userName, passwordHash"+
               " FROM users WHERE userName = ?";
  
try 
{
  PreparedSatement statement = 
         connection.prepareStatement(userLoginQuery);
  statement.setString(1, request.getParameter("user"));
  ResultSet rs = statement.executeQuery();

chevron_right


Example3:
Consider another example of query vulnerability discussed below:

filter_none

edit
close

play_arrow

link
brightness_4
code

String query = "INSERT INTO users VALUES("
        request.getParameter("userName") + ");";

chevron_right


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.

This article is contributed by Anannya Uberoi. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.



My Personal Notes arrow_drop_up