SQL Injection

What is SQL Injection?

SQL injection (SQLi) is an application security weakness that allows attackers to control an application’s database – letting them access or delete data, change an application’s data-driven behavior, and do other undesirable things – by tricking the application into sending unexpected SQL commands.

SQL injection weaknesses occur when an application uses untrusted data, such as data entered into web form fields, as part of a database query. When an application fails to properly sanitize this untrusted data before adding it to a SQL query, an attacker can include their own SQL commands which the database will execute. Such SQLi vulnerabilities are easy to prevent, yet SQLi remains a leading web application risk, and many organizations remain vulnerable to potentially damaging data breaches resulting from SQL injection.

Exploit SQLi Vulnerabilities

Attackers provide specially-crafted input to trick an application into modifying the SQL queries that the application asks the database to execute. This allows the attacker to:

• Control application behavior that’s based on data in the database, for example by tricking an application into allowing a login without a valid password.
• Alter data in the database without authorization, for example by creating fraudulent records, adding users or promoting users to higher access levels, or deleting data.
• Access data without authorization, for example by tricking the database into providing too many results for a query.

SQL Injection Attack

A developer defines a SQL query to perform some database action necessary for their application to function. This query has an argument so that only desired records are returned, and the value for that argument can be provided by a user (for example, through a form field, URL parameter, web cookie, etc.).

A SQLi attack plays out in two stages:

1. Research: Attacker tries submitting various unexpected values for the argument, observes how the application responds, and determines an attack to attempt.
2. Attack: Attacker provides a carefully-crafted input value that, when used as an argument to a SQL query, will be interpreted as part of a SQL command rather than merely data; the database then executes the SQL command as modified by the attacker.

The research and attack stages can be easily automated by readily-available tools.

Defending Against SQLi Attacks

There are easy ways to avoid introducing SQLi vulnerabilities in an application, and to limit the damage they can cause.

• Discover SQLi vulnerabilities by routinely testing your applications both using static testing and dynamic testing.
• Avoid and repair SQLi vulnerabilities by using parameterized queries. These types of queries specify placeholders for parameters so that the database will always treat them as data rather than part of a SQL command. Prepared statements and object relational mappers (ORMs) make this easy for developers.
• Remediate SQLi vulnerabilities in legacy systems by escaping inputs before adding them to the query. Use this technique only where prepared statements or similar facilities are unavailable.
• Mitigate the impact of SQLi vulnerabilities by enforcing least privilege on the database. Ensure that each application has its own database credentials, and that these credentials have the minimum rights the application needs.

Attack and Defense Examples

1. Returning more data than expected

Imagine a developer needs to show the account numbers and balances for the current user’s id as provided in a URL. They might write (in Java):

String accountBalanceQuery = 
  "SELECT accountNumber, balance FROM accounts WHERE account_owner_id = " 
  + request.getParameter("user_id");

try {
    Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery(accountBalanceQuery);
    while (rs.next()) {
        page.addTableRow(rs.getInt("accountNumber"), rs.getFloat("balance"));
    }
} catch (SQLException e) { ... }

Under normal operation, the user with ID 984 might be logged in, and visit the URL:

https://website/show_balances?user_id=984

This means that accountBalanceQuery would end up being:

SELECT accountNumber, balance FROM accounts WHERE account_owner_id = 984

This is passed to the database, and the accounts and balances for user 984 are returned, and rows are added to the page to show them.

The attacker could change the parameter user_id to be interpreted as:

0 OR 1=1

And this results in accountBalanceQuery being:

SELECT accountNumber, balance FROM accounts WHERE account_owner_id = 0 OR 1=1

When this query is passed to the database, it will return all the account numbers and balances it has stored, and rows are added to the page to show them. The attacker now knows every user’s account numbers and balances.

Repair

The developer could easily repair this vulnerability by using a prepared statement to create a parameterized query as below:

String accountBalanceQuery = 
  "SELECT accountNumber, balance FROM accounts WHERE account_owner_id = ?";

try {
    PreparedStatement statement = connection.prepareStatement(accountBalanceQuery);
    statement.setInt(1, request.getParameter("user_id")); 
    ResultSet rs = statement.executeQuery();
    while (rs.next()) {
        page.addTableRow(rs.getInt("accountNumber"), rs.getFloat("balance"));
    }
} catch (SQLException e) { ... }

If an attacker attempts to supply a value that’s not a simple integer, then statement.setInt() will throw a SQLException error rather than permitting the query to complete.

2. Making your user into an administrator

Imagine a developer implements a login form, and writes (in Java):

String userLoginQuery =
  "SELECT user_id, username, password_hash FROM users WHERE username = '"
  + request.getParameter("user") + "'";

int user_id = -1;
HashMap userGroups = new HashMap();

try {
    Satement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery(userLoginQuery);
    rs.first();
    user_id = rs.getInt("user_id");
    if (!
     hashOf(request.getParameter("password")).equals(rs.getString("password_hash"))
    ) {
        throw BadLoginException();
    }

    String userGroupQuery = "SELECT group FROM group_membership WHERE user_id = " + user_id;
rs = statement.executeQuery(userGroupQuery);
    while (rs.next()) {
        userGroup.put(rs.getString("group"), true);
    }
} 
catch (SQLException e) { ... }
catch (BadLoginException e) { ... }

Normally, a user would provide a username (say mike) and password, and the first query executed would be:

SELECT user_id, username, password_hash FROM users WHERE username = 'mike'

The database returns Mike’s user ID and password hash, Mike’s password is hashed compared, and then the database gets the list of groups to which Mike belongs.

However, if an attacker has Mike’s password (maybe the attacker is Mike!), they could make Mike into an admin. They’d login using the following as a username (newlines added for ease of reading):

mike'; 
INSERT INTO group_membership (user_id, group) 
VALUES (SELECT user_id FROM users WHERE username='mike', 'Administrator'); --

This means the query passed to the database would be:

SELECT user_id, username, password_hash FROM users WHERE username = 'mike'; 
INSERT INTO group_membership (user_id, group) 
VALUES (SELECT user_id FROM users WHERE username='mike', 'Administrator'); --'

Note that the single-quote character in the input results in the remainder of the input being interpreted as part of the SQL statement, rather than part of the value. Mike will now belong to the group Administrator. Note that the attacker can change Mikes’s group without completing the login process in this scenario; that means an attacker that hasn’t guessed Mike’s password could perhaps delete accounts, reset passwords, and the like.

Repair

The developer could easily repair this vulnerability by using a prepared statement to create a parameterized query as below:

String userLoginQuery =
  "SELECT user_id, username, password_hash FROM users WHERE username = ?";

…

try {
    PreparedSatement statement = connection.prepareStatement(userLoginQuery);
    statement.setString(1, request.getParameter("user"));
    ResultSet rs = statement.executeQuery();
… 

This query will return no results when the ‘user’ parameter contains a SQL Injection attack, since the parameter’s value is sanitized by the PreparedStatement.