SQL injections are one of the most common attack vectors used by attackers. In fact, as recently as 2019, SQL injections (SQLi) represented nearly two-thirds of all web application attacks. These numbers are staggering, and even more so when put into context. Despite SQLi’s reign as one of the top 10 CVE vulnerabilities since 2003, it has only begun to pick up pace and popularity in the past few years; just two years prior to 2019, SQLi only accounted for 44% of web application attacks. This growth shows how important it is to add automated security testing to your DevOps pipeline so that these bugs are caught before they hit production.
Let’s break down this bug class and how to avoid it.
What is a SQL injection?
A SQL injection is a web application attack where the attacker “injects” SQL statements that will manipulate or access application data, whether it be sensitive or public. These attacks leverage areas in web applications that ask for user input. If user inputs in an app are not sanitized properly, an attacker can use a SQL injection to gain access to the associated app datastore.
An Example SQL Injection
Attackers commonly use SQL injections to infiltrate web applications through user input. This includes form fills for usernames, user IDs, first and last names, and more. If you do not sanitize these inputs before accepting them or make strong use of parameterized SQL statements, an attacker can pass SQL statements through that input that unknowingly run on your database.
For example, say you are taking the input of a user ID in from a user. When your application fetches information about a user, the URL may look something like this:
The legitimate SQL query would look like this:
SELECT * FROM users WHERE id = '42'
They enter their user ID, you take in their input, use it to find their information in your database, and display their data for them.
But, consider this: instead of inputting their user ID, they input what can be interpreted as a SQL query. For example:
'42' OR '1'='1'
If you take their input as-is, without sanitizing, this will result in a SQL query like:
SELECT * FROM users WHERE id = '42' OR '1'='1';
Since 1=1 is always true, this will return every data field for all users. This is a classic example of a SQL injection.
It’s important to note that this is the output the database is designed to give for this type of query. In this instance, the attacker is not looking to break the application you’ve made…just use what’s already available to access things they shouldn’t. When developing an application, try to consider what things might be accessible that shouldn’t be, and then implement ways to prevent that access from happening.
What types of SQL injection are there?
There are three main types of SQL injections: In-band SQLi, Inferential SQLi, and Out-of-Band SQLi.
When an attacker initiates a SQL injection from the same place used to gather the output of the injection, it’s known as an In-band SQLi. This is one of the most common types of SQLi attacks, and it is often separated into Error-based SQLi and Union-based SQLi.
An Error-based SQLi is a type of SQL injection that outputs error messages thrown by the database. This type of injection can be used to give attackers valuable information about the database, like its size and elements. Many times, attackers will use an Error-based SQLi to perform reconnaissance on the database before ultimately executing a SQL injection meant to perform more complex tasks like outputting data.
Example of an Error-based SQLi
Consider a situation where you have left error logging on with your web application, which is now in production. In order to gather information about your database, the attacker modifies the user input with something they know will return an error.
This results in the SQL query:
SELECT * FROM users WHERE id = '42''
Which will throw an error because of the extraneous tick mark at the end. If error logging is on, the error is then presented to the attacker:
Error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near….
The attacker now knows that the web application is vulnerable to Error-based SQLi, and can take advantage of this by invoking more informative error messages that give information about the database.
To prevent this, error logging should be disabled in a live web application, or it should output to a restricted file.
A Union-based SQLi uses the UNION operator to output additional data into a single result, typically to the already-visible table in the web application. In order to successfully execute a Union-based SQLi, the attacker must have information about the database like the table name, number of columns in the query, and data type. This is because, in order for a UNION to succeed, the SELECT queries being unioned must:
- Have the same number of columns.
- Have compatible data types.
One way this reconnaissance data can be gathered is through an Error-based SQLi when error logging is enabled. Information gathered from error logs may give enough information for an attacker to understand the size of the table and the data types used.
Example of a Union-based SQLi
Consider a situation where the attacker has managed to gather information about the size of the table, the data type in use, and the name of a second table, names.
http://mycoolapp.com/allusers.php?id=' UNION SELECT * FROM names --
This will result in the SQL query:
SELECT * FROM users WHERE id ='' UNION SELECT * FROM names -- ' and password = 'abcd'
The — is a comment in SQL, so anything after — is automatically commented out. The initial SELECT statement returns a null set, as there is no user with id ” in users, while the second SELECT statement returns all information in names.
Blind SQLi are very similar to In-Band SQLi, with one difference: responses from the web application do not output the results of the query or database errors. Basically, the web application developer suppresses error messages from the database, making it more difficult for attackers to use SQL injections. However, this does not solve the problem of SQL injections. Attackers can still use Blind SQLi, which come in two forms: Content-based Blind SQLi and Time-based Blind SQLi.
Content-based Blind SQLi
Content-based Blind SQLi uses queries for conditional responses instead of data outputs. These SQL queries ask the database true or false questions so the attacker can evaluate the output and determine if a web application is vulnerable. This can be extremely tedious, so attackers will sometimes automate these attacks.
Example of a Content-based Blind SQLi
Back to our cool app example. Consider a situation where you have taken some precautions and do not show outputs from query results or database errors. In order to gather reconnaissance about your database, the attacker injects queries hoping for the system to return false.
This results in the SQL query:
SELECT * FROM users WHERE id = 42 and 4=1
Of course, four does not equal one. If the application is vulnerable to a Content-based Blind SQLi, nothing will be returned from this query or the page will differ in some way from normal functionality. While this does not necessarily confirm that the application is vulnerable, it may actually be a good sign for the attacker. To confirm the application is vulnerable, the attacker will then inject a query that should return true and observe the output.
This results in the SQL query:
SELECT * FROM users WHERE id = 42 and 4=4
This returns true and outputs the data for user with id 42.
If the web application has a different response to the database returning true than it returning false, the attacker knows the app is vulnerable to a SQL injection. By continuing to use true/false tests against the database, the attacker can find additional information about it and potentially even the contents of the database itself.
Time-based Blind SQLi
TIme-based Blind SQLi queries the system to perform time-intensive operations. A typical time-intensive operation that can be used for a Time-based Blind SQLi is the sleep() operation. An attacker can send a query to the database to sleep for a certain period, and if the web application delays its response by that period, it is vulnerable.
Example of a Time-based Blind SQLi
Consider again a situation where you have taken some precautions to prevent outputs from query results to the database or database errors. In order to gather reconnaissance about your database, the attacker can attempt to affect the database with the SLEEP() function.
If the database has a slow response, this means the query was executed successfully and that the attacker is able to execute SQL queries on the database. From there, the attacker can use other Blind SQL injection techniques to gather additional information about the database.
In contrast to In-band SQLi, Out-of-band SQLi requires an attacker to use a different channel to initiate the SQL injection than the one to gather the output of the SQL injection. For example, if an attacker uses an Out-of-band SQL injection on a web application, they manipulate the database server to deliver data to their own separate server that they control. These injections abuse tools like Microsoft SQL Server’s xp_dirtree command to make DNS requests to an attacker-controlled server. Out-of-band SQLi are much less common than other types of SQL injections, as they are very dependent on what features are enabled on the database server.
How to Write Secure Code that Prevents SQL Injections
Sanitize User Inputs
The best way to prevent SQL injections is to sanitize your database inputs. Any type of user input should be assessed, similar to how you might check that a new registrant gave you a legitimate email address instead of a random string. Validate user input on the server-side, not just the client-side, of your application. Client-side validation can make it more difficult for an attacker to alter user input, but there are many tools that allow attackers to bypass client-side validation when necessary.
Example of Sanitizing Inputs
The easiest way to sanitize user inputs before they are added to your database is to disallow content using a regular expression.
For example, say you want to have a user input and want to only allow letters, numbers, and spaces. The regex may look something like this:
This regex will allow alphanumeric characters and spaces. By comparing the input you are receiving with this, you can identify and stop any other characters from being accepted. Using regular expressions is just one example of how to sanitize inputs, and it’s important to take additional precautions alongside this, like the ones listed below.
Other Helpful Tips
Follow the Principle of Least Privilege
The principle of least privilege limits access for users based only on what privileges they need. For example, a user for a web application may not need access to an entire database. Instead, grant users access to the tables they need to reduce the potential impact of misuse.
Keep Sensitive Data and Public Data Separate
Storing sensitive data must be handled differently than storing public data. Sensitive data should only be stored if necessary for the application, and it should be encrypted. Take extra precautions with sensitive data that you wouldn’t necessarily take with public data to ensure your users privacy.
Automate Testing for SQL Injection in the Build Pipeline
While blogs like this and security training for the engineering team are helpful, the best way to avoid SQL injection vulnerabilities in your application is to automate testing in the pipeline. We built StackHawk to help engineering teams find and fix security bugs like SQL injections and more, but there are also many other tools out there depending on your use case. Bottom line, use automation to help ensure you ship code that is free from security bugs.