Thwarting SQL Injection: Defense in Depth

Vaijayanti Korde

Last updated on: January 27, 2021

SQL as a language is vulnerable to injection attacks because it allows mixing of instructions and data, which attackers can conveniently exploit to achieve their nefarious objectives.

The root cause behind successful SQL injection attacks is the execution of user-supplied data as SQL instructions. This classic cartoon illustrates the perils of trusting user inputs, and how they can lead to a successful SQLi attack:

From the webcomic xkcd:

Did you really name your son Robert'); DROP TABLE Students;--

Contents

In this blog post, I describe some different types of SQLi attacks and their effects, and I show some ways to protect against them and to prepare your systems to minimize damage in case they do occur.

The Rise in SQLi Attacks

SQLi attacks are on the rise. According to Akamai’s most recent State  of  the  Internet  Security  Report, there was an 87% increase in SQLi attacks in this year’s first quarter compared with 2015’s fourth quarter.

According to the latest IBM X-Force data report, 170 SQLi attacks have been reported since 2011 through mid-2016. In the last 18 months over 20 million records have been leaked by exploiting SQLi attacks.

SQLi attacks can have severe consequences. For example, an attacker can gain control over your organization’s entire database server.

There have been several high profile SQLi attacks recently:

  • Just this week, Epic Games warned users of a breach attributed to a SQLi attack that impacts 800,000 user accounts.
  • In April a SQLi flaw present in a CMS used by the law firm Mossack Fonseca most likely allowed hackers to steal 11.5 million confidential documents. Many of the documents contained information about dubious offshore tax affairs of current and former world leaders, an embarrassing scandal that became known as The Panama Papers.
  • A few months ago, SoftPedia found a SQLi vulnerability in the Cardio Server ECG Management System, a data management system used by many hospitals and clinics.
  • Late last year, hackers exploited a SQLi vulnerability to steal the login credentials of officials attending a climate summit in Paris.

About SQL Injection

SQLi is a form of command injection vulnerability exploited with the aid of malicious input. For example, the following SQL query echoes the transaction history of a logged-in user for a given month:

SQL query = "SELECT date, branch_code, vendor, dr_cr, amount, balance FROM transactions WHERE user_id = " + session.getCurrentUserId() + " AND transaction_month = " + request.getParameter("month");

In a valid case, the user would enter a month and the SQL query would look like this:

SELECT date, branch_code, vendor, dr_cr, amount, balance FROM transactions WHERE user_id = bob AND transaction_month = 12;

A malicious user might enter 0 OR 1 = 1, resulting in this query:

SELECT date, branch_code, vendor, dr_cr, amount, balance FROM transactions WHERE user_id = bob AND transaction_month = 0 OR 1 = 1;

In the above query, the “where” condition will always evaluate to true, giving an attacker unauthorized access to other users’ transaction history.

A More Serious Attack

Let’s consider a UNION-based attack, which is more serious because it allows the attacker to extract information from any field in any table in the database, not just from the fields specified in the query.

In this attack, the malicious user enters transaction_month as 0 AND 1 = 0 UNION SELECT 1, expiration_month, 1, 1, expiration_year, card_number FROM credit_cards.

Then, the malicious query will look like the following:

SELECT date, branch_code, vendor, dr_cr, amount, balance FROM transactions WHERE user_id = bob AND transaction_month = 0 AND 1 = 0 UNION SELECT 1, expiration_month, 1, 1, expiration_year, card_number FROM credit_cards;

In the above query AND 1 = 0 will result in false, and the attacker will proceed to extract data from another table and to combine the resultset of the primary query with the union injected query.

In order to successfully combine the resultset, the number of fields and associated data types of the union injected query must be identical to the base query. That’s because the SQL union operator can only be used if both queries have the exact same structure.

A Destructive Attack

An attacker could set transaction_month equal to 0 ; DROP Table credit_cards; to make the SQLi more destructive.

This is how the SQLi query would look:

SELECT date, branch_code, vendor, dr_cr, amount, balance FROM transactions WHERE user_id = bob AND transaction_month = 0 ; DROP TABLE credit_cards;

As a result of the above query, the credit_cards table is removed from the database.

Blind SQLi

Blind SQLi is a technique to extract information from the database schema via a series of binary (yes/no) questions. Blind SQLi relies on the database returning different responses depending on the result of the SQL fragment injected into the query being used by the web app. Then the query result can tell you if your guess is correct (the injected fragment returns true) or if it’s incorrect (fragment returns false). By iterating through a series of such guesses, an attacker can perform a binary search on some part of the database.

For example, blind SQLi can be used to determine the structure of the database by repeatedly querying the data dictionary. The attacker can inject SQL that tests if the first letter of a tablename entry in the data dictionary starts in the first half of the alphabet; if true, then test if the first letter is in the first quarter of the alphabet, and so on until the attacker has guessed the first letter of the table name; and then do the same for the second letter and so on, until the attacker has guessed the table name. Some loop statements (binary search techniques) and an automated script make this simple. Once the attacker understands the database schema, it becomes possible to create follow-on attacks to extract specific data from the database.

Blind SQLi attacks can also be useful for guessing password hashes for specific user accounts.

A typical blind SQLi attack iterates over a query that looks like this:

SELECT date, branch_code, vendor, dr_cr, amount, balance FROM transactions WHERE user_id = bob AND transaction_month = 12 AND [true/false test goes here]

The good news is that it’s easy to test if a query is vulnerable to blind SQLi by testing if the query that always returns true:

SELECT date, branch_code, vendor, dr_cr, amount, balance FROM transactions WHERE user_id = bob AND transaction_month = 12 AND 1 = 1

gives different results than the query that always returns false:

SELECT date, branch_code, vendor, dr_cr, amount, balance FROM transactions WHERE user_id = bob AND transaction_month = 12 AND 1 = 0
SQLi Time Delay

A SQLi time delay attack injects a SQL segment that contains a specific DMBS function to generate a time delay. The attacker might deduce some information depending on the time it takes the server to respond. For example BENCHMARK(cnt, expr) executes a specified expression multiple times. The attacker can leverage the benchmark() function to cause a delay in the query execution. Similarly, Sleep(duration) is available in MySQL5 onwards and WAITFOR (DELAY/TIME) “hh:mm:ss” is for databases using T-SQL to be more specific, similar to SQL Server.

This is a malicious query (with time delay) that can be used to verify whether user is ‘sa’:

SELECT * FROM employees WHERE eId=1; IF SYSTEM_USER='sa' WAIT FOR DELAY '00:00:45'

“WAIT FOR TIME” can be used to bypass weak blacklist filters implemented to mitigate “WAIT FOR DELAY”.

Thus, using time delay and a conditional statement, the attacker can extract certain information about the database such as whether the current user is ‘system administrator (sa)’ or not.

Authentication Bypass Through SQLi

An attacker can use SQLi to defeat a login page. If the application is using the following login form for authentication process:

Login screen image

And the SQL query being used in the authentication module is this:

SELECT username, password FROM users WHERE username = "$username" AND password = "$password";

An attacker can inject " OR 1 = 1 -- in username and gain unauthorized access to the application.

A malicious query will look like the following:

SELECT username, password FROM users WHERE username = "" OR 1 = 1 -- " AND password = "$password";

In SQL everything before double hyphens (--) is executed like normal SQL, whereas everything after “--” is ignored and treated as a comment.

In the above query the attacker commented out the password check using double hyphens (--), and OR 1 = 1 always evaluates to true. Thus, the attacker successfully circumvented the password check and gained unauthorized access to the application.

SQLi Impact and Risk

In 2005, 40 million credit cards from Mastercard, VISA and other major credit card companies were reported stolen from CardSystems, a third party credit card payment processing company. After the investigation concluded, it was found CardSystems was storing credit card data as plain text instead of encrypting the sensitive information. Their database was compromised by exploiting a SQLi vulnerability. The attacker installed a script that acted like a virus, searching for certain types of cards, a task made easier by the fact that the data was unencrypted. For 6 months the attacker received emails with credit card numbers, and the company sustained significant financial damage from the attack. This incident later bootstrapped payment card protections.

The following are impact highlights of SQLi attacks:

Data Confidentiality & Information Leakage

A SQLi vulnerability might allow unauthorized access to any data residing on the database server, compromising data confidentiality of the organization.

Detailed and verbose SQL error messages can lead to information leakage. SQLi attacks don’t require any prior knowledge; however, the attack process can be expedited by providing the attacker any knowledge related the format of SQL queries being used by the application. Verbose error messages can provide detailed information to the attacker on how to construct valid SQL queries for the database being used by the web application.

Authentication Bypass

With the help of SQLi vulnerability an attacker can impersonate another user, circumvent an entire authentication process and gain access to the restricted area.

Non-Repudiation and Data Integrity

SQL DDL statements are used to alter databases. An attacker can leverage a SQLi vulnerability to manipulate data in the database leading to integrity and repudiation issues.

Availability (Denial of Service)

SQLi vulnerability can be exploited by an attacker to delete a database. Recovery from backup can take some time causing unavailability of an application resulting in DoS.

Defenses Against SQLi

Here are some best practices and remediation techniques to prevent SQLi attack. We will analyze the pitfalls and advantages of all techniques with examples. In the following section, we highlight how to mitigate the impact of SQLi attacks when they do occur.

Blacklisting and Whitelisting

Blacklisting consists of prohibiting certain dangerous characters. For example:

SQL query = "SELECT productId, productName, category, expiry_month, expiry_year FROM medicine AS m, batch AS b WHERE m.batchId = " + session.geBatchId() + " AND m.batchId = b.batchId AND category LIKE '" + eliminate_quotes(request.getParameter("category")) + "%';";

where eliminate_quotes() is:

String eliminate_quotes(String str) {
    StringBuffer result = new StringBuffer(str.length());
    for (int i = 0; i < str.length(); i++) {
        if (str.charAt(i) != "'") {
            result.append(str.charAt(i));
        }
    }
    return result.toString();
}

What’s the weakness of blacklisting? It’s not always feasible to list and filter all potentially dangerous characters. The above function eliminate_quotes fails to foil against numeric parameters such as month = 1 and 1=0 Union SELECT productID, ProductName, category, expiry_date, expiry_month. More importantly blacklisting potentially dangerous characters might conflict with functional requirements. For example what if a patient’s name is “O’Connor” (quote) or medicine name is “acyclovir cream 5%” (percent)? Hence blacklisting is not a comprehensive solution to mitigate SQLi attacks.

Whitelisting is considered a better solution than blacklisting, because it defines what valid input should look like and the regular expressions (regex) that can be used for accepting safe values from an end user.

Escaping

Applications are sometimes required to accept potentially dangerous characters. For example airline reservation system’s database needs to allow airport names such as “Chicago O’Hare”. Similarly payroll database must accept A’Dmure as a valid employee name. In such situations a developer can choose to escape quotes instead of blacklisting them, meaning that they treat quotes (and any other special character) as a data and not as an instruction. That way, data doesn’t become a part of the query. For example:

SQL query = "INSERT INTO patients (patientId, patientName, password) VALUES (" + escape(patientId) + ", " + escape(patientName) + ", " + escape(password) + ");";

However, escaping doesn’t mitigate SQLi attack completely either, as I explain soon, so what’s next?

Let’s look at innocuous error messages before moving to a reasonably effective and widely accepted remediation option for SQLi attacks.

Use Innocuous Error Messages

It’s not a good practice to spit out detailed error and exception messages in the response since they can reveal information about your database schema. After deploying them to production, it’s important to configure your web applications, web server and other associated components not be verbose.

The following is an example of the exception message returned after entering quote in username field on a login page:

System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'uname = ''' and password = 'pwned''. at 
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling ( Int32 hr) at 
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult ( tagDBPARAMS dbParams,  Object& executeResult) at

An attacker can not only figure out the database schema but can also extract database password(s) by exploiting the application’s behavior of triggering verbose messages after an unexpected input. One such example is QuickSilver forums, which revealed a database password in the exception message.

We recommend anticipating all corner cases and errors before deployment. Display innocuous messages in the response rather than verbose stack trace messages as they can aid an attacker to figure out database schema. Use custom error pages with customer-friendly messages. For example, Microsoft has documented best practices for designing custom error pages.

Parameterized Query

Parameterized query is the most effective of the manual defenses. Blacklisting, whitelisting, escaping and concealing error messages are not enough to thwart all SQLi attacks, as your application might be still be vulnerable to second-order SQLi attacks.

In second-order SQLi attacks, data gets stored in the database and executed later, something similar to stored XSS (Cross Site Scripting vulnerability).

new_password = request.getParameter("new_passwd");
patientName = session.getPatientName();
SQL query = "UPDATE users SET password = '" + escape(new_passwd) + "' WHERE patientName = '" + patientName + "'";

If a malicious user decides to enter administrator' -- and password as youarehacked, the query will look like this:

UPDATE patients SET password='youarehacked" WHERE patientName='administrator --

The result would be that:

  • An attacker has an administrative access and can take full control of the system.
  • — Comments out the trailing query.
  • Legacy application using old MySQL database SQL query can also be commented using #

One can argue about escaping for every parameter but at one point the query starts getting messy and confusing. Hence it’s recommended to use prepared statements and bind variables termed as parameterized queries. That explains why escaping is always not a good remediation technique for a SQLi attack.

The key problem arises when there is confusion between what is data and what are instructions (control), which leads to precarious situations. In prepared statements a query template is constructed, and a developer can put placeholders wherever s/he thinks there is going to be something that should be always interpreted as data. Once constructed then data can be specified for the query template.

Let’s elaborate with an example, parameterized query in Java:

String query = "SELECT productId, productName, category, expiry_month, expiry_year FROM products WHERE patientId = ? and expiry_month = ?"; 
PreparedStatement pstmt = database.prepareStatement(query);
pstmt.setInt(1, session.getCurrentUserId());
pstmt.setInt(1, Integer.parseInt(request_getParameter("month")));
ResultSet result = pstmt.executeQuery();

Parameterized queries allow creation of static queries with bind variables. Bind variables (?) are placeholders guaranteed to be data. As shown in the above example bind variables are typed too. It preserves the structure of an intended query, and parameters don’t get involved in SQL query parsing.

SQLi in Stored Procedures

Stored procedures are sequences of SQL statements with an assigned name store in the database, which can be shared by many programs. In short, a stored procedure is syntactic sugar for a set of SQL statements and equally vulnerable to a SQLi attacks.

For example:

CREATE PROCEDURE change_passwd @user varchar(25), @new_password varchar(25) AS UPDATE patients SET passwd = new_passwd WHERE uname = uname;
$database->exec("change_passwd ' " + uname + " , '" + new_passwd + "'");

The above procedure is vulnerable to a SQLi attack. Use the following to bind variables with a stored procedure to foil against SQLi

$pstmt = $database->prepare("change_passwd ?, ?"); $pstmt->execute(array($uname, $new_passwd));
Web Application Scanning

Web application scanning tools are useful in a number of ways, and are a critical defensive tool. For example,  Qualys Web Application Scanning (WAS) performs the following tests against your web applications, so that development teams can easily identify and fix SQLi vulnerabilities at scale:

  • Detect Blind SQLi vulnerabilities by performing a series of positive (OR 1 = 1 and AND 0 = 0) and negative (AND 1 = 0) tests and analyzing associated responses. If the results of a query change depending on whether a positive or negative test is injected, then you know the code is vulnerable.
  • Report verbose error and exception messages found during scanning.
  • Detect UNION-based SQLi vulnerabilities by injecting UNION statements.
  • Detect sql time delay attacks by injection delay statements in the query and analyzing the response time, considering network delay overhead.
  • Provide automated testing across your entire application. While the defense techniques described here can all be implemented manually, tools are necessary to scale consistent implementation across applications written by many developers over multiple release cycles.
  • Provide support across the entire software development lifecycle. Qualys WAS can be used by developers early in the development cycle so that quality assurance (QA) teams don’t need to find them and send the code back to engineering to be fixed.

Mitigation of SQLi Impact

In case a SQLi attack is executed against your database, the following techniques (when applied before the attack) will help you mitigate the impact:

Create Application-Specific User Accounts

Prohibit the use of system administrator accounts for an application. Instead, create user specific accounts for the web application with a least privilege model.

Encrypt Sensitive Data Stores in the Database

Encrypting sensitive data stored in the database prevents an attacker from reading and misusing it. It’s a second line of defense. The key to encrypt the data should be stored at different location and not in the database.

Adopt a Least-Privilege Model for User Accounts

Identify what kind of database queries need to be executed from the application and configure privileges required for the user accordingly. For example, if a user is only expected to read from the database, then don’t grant him privileges such as “insert,” “update” or “drop”.

Harden the Database Server and Host Operating System

By default, dangerous functions could be left on. For example, MS SQL Server allows users to open inbound and outbound sockets, which an attacker could leverage to steal data and upload binaries. To avoid such a problem, you should shut down all the services that are not in use.

Disable Unused Stored Procedures

Disable stored procedures that are not in use. For example, the TSQL sp_MSDropRetry standard stored procedure was vulnerable to a SQLi attack, which could be prevented if the stored procedure was disabled.

Conclusion

SQLi is a deadly attack and can wreak havoc if an application is not protected against it. As explained in this post, a holistic approach is required to foil the attack.

We also recommend using Qualys WAS for black box testing of your web applications and to help protect your application against various SQLi attack vectors such as SQL error messages, Blind SQLi and time delay attacks.

Show Comments (2)

Comments

Your email address will not be published. Required fields are marked *

  1. Nice explaining of SQL injection.
    “It’s not a good practice to spit out detailed error and exception messages in the response since they can reveal information about your database schema.”. Agree! A lot of developers will make the web applications throw out error for debug purpose during development, but they forget to remove or disable it when the web application is deployed on line.

  2. So here is the idea. Database servers take the incoming SQL query and run it through a parser resulting in a parse tree. Then they turn the tree into a plan and execute the plan.

    The essence of injection is that the parser produces a tree different from the one intended by the programmer.

    So the fix is to be able to detect unusual parse trees. Walk the tree after parsing and produce a string in canonical form minus the data values. Compute a SHA hash of the string. Keep a table of known hashes for the application/database user. Warn or abort if the server sees an unknown hash.

    Obviously, there is a startup problem. So the programmer would have to run the application in a testing mode, extract the hashes after exhaustive testing, and the load the server with the hashes on application startup. Then turn on abort-on-new-hash and no more SQL inject should be possible.