SQL Injection: Vulnerabilities & SQL Injection Prevention

What is SQL Injection?

SQL injection (SQLi) is an application security vulnerability that allows attackers to control the database of an application by allowing them to access or delete information, alter the data-driven behavior of an application, and do other unwanted things by tricking the application into sending unexpected SQL commands. Injections of SQL are among the most frequent data security threats.

Weaknesses in SQL injection arise when an application uses untrusted data as part of a database query, such as data entered into web form fields. If this untrusted data is not properly sanitized by an application before adding it to a SQL query, an attacker can include their own SQL commands that will be executed by the database. Such SQLi vulnerabilities are easy to avoid, yet SQLi remains a major web application risk, and many organizations remain vulnerable to potentially harmful SQL injection data breaches.

How Attackers Exploit SQLi Vulnerabilities?

In order to trick an application into modifying the SQL queries that the application asks the database to run, attackers provide specially-crafted input. This enables the assailant to:

Control the behavior of the application based on data in the database, such as by tricking an application to allow a login without a valid password.
Alter data in the database without permission, such as creating fraudulent records, adding users or “promoting” users to higher levels of access or deleting data.
For example, accessing data without authorization by tricking the database into providing too many results for a query

Anatomy of a SQL Injection Attack

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

A SQLi attack plays out in two stages:

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

The phases of research and attack can easily be automated by tools that are readily available.

Defending Against SQLi Attacks

There are simple ways to prevent SQLi vulnerabilities in an application from being introduced and to reduce the damage they can cause.

Discover SQLi vulnerabilities by routinely testing both static testing and dynamic testing for your applications.
Use parameterized queries to avoid and repair SQLi vulnerabilities. These types of queries specify parameter placeholders so that they are always treated as data rather than part of a SQL command by the database. For developers, prepared statements and object relational mappers (ORMs) make this simple.
Removes SQLi vulnerabilities in legacy systems before adding them to the query by escaping inputs. Use this method only if it is not available for prepared statements or similar facilities.
Mitigate the impact of SQLi vulnerabilities by enforcing the database with the least privilege. Make sure that each application has its own credentials for the database and that these credentials have the minimum rights required by the application.
Examples for Attack and Defense

1. Returning more data than expected

Imagine that a developer needs to display the account numbers and balances as provided in a URL for the current user’s id. They might (in Java) write:
“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) {…); }Catch (SQLException e) }
The user with ID 984 may be logged in under normal operation and visit the URL:

https://banking/show balances website? ID user=9844

This implies that it will end up being: accountBalanceQuery:
SELECT account number, account balance FROM accounts WHERE account owner id = 9844
This is passed to the database and User 984 accounts and balances are returned and rows are added to the page to show them.

The attacker can change the “user id” parameter 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 “john”) and password, and the first query executed would be:
SELECT user id, username, password hash FROM users WHERE username = ‘john’
The database returns John’s user ID and password hash, John’s password is hashed compared, and then the database gets the list of groups to which John belongs.

However, if an attacker has John’s password (maybe the attacker is John!), they could make John into an admin. They’d login using the following as a username (newlines added for ease of reading):
john’; INSERT INTO group membership (user id, group) VALUES (SELECT user id FROM users WHERE username=’john’, ‘Administrator’); —
This means the query passed to the database would be:
SELECT user id, username, password hash FROM users WHERE username = ‘john’; INSERT INTO group membership (user id, group) VALUES (SELECT user id FROM users WHERE username=’john’, ‘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. John will now belong to the group “Administrator.” Note that the attacker can change John’s group without completing the login process in this scenario; that means an attacker that hasn’t guessed John’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.

Veracode Can Help with SQL Injection

Veracode Static Analysis can accurately identify SQL injection vulnerabilities and other flaws in your application and its third-party components, and tell your developers exactly where and how to repair them. All without ever looking at the source code.

Our cloud-based application security platform helps you manage your application security program, track progress, and educate your developers on avoiding and repairing SQL injection and other security flaws through integrated eLearning materials.

Did you find this page helpful? It is also available as an infographic.

SQL injection prevention is fairly easy, yet many organizations fail to take the simple steps to prevent breaches which can potentially cause significant damage to business, reputation and the bottom line.

Veracode provides support for SQL injection prevention and helps to eradicate other malicious software with a suite of on-demand application testing services that enable developers to embed security throughout the SDLC.

Veracode solutions can help to identify and eradicate many of the most dangerous security risks, including SQL injections, cryptographically insecure storage, broken authentication and session management, cross site scripting and many more. Learn more about stopping SQL injections with Veracode, or visit our AppSec knowledgebase to get answers to questions like “What is spoof? ” Or “What is cross site scripting? ”