What is
SQL Injection?

SQL injections are one of the most common attack vectors used by attackers. In fact, as recently as 2019, SQL injection attacks, also referred to as SQLi attack, 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.

In-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.

Error-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:

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. 

Union-based SQLi

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:

1. Have the same number of columns.

2. 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. 

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

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.

Out-of-Band SQLi

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.  

Preventing SQL Injection by Framework

Many frameworks offer specific best practices to prevent SQLi, usually in the form of allowing developers to use a prepared statement or parameterized query to build their SQL statement from user input. If you are interested in learning how to prevent SQL injection for a specific framework that you work in, be sure to check out one of our guides:

• Preventing SQL Injection in Django

• Preventing SQL Injection in Rails

• Preventing SQL Injection in Laravel

• Preventing SQL Injection in Spring

Other Helpful Tips 

Utilize Prepared Statements and Stored Procedures

When it comes to preventing SQL injection attempts, a standard way to prevent them is to use prepared statements in your code. On top of this, you may also want to leverage stored procedures that you can create inside the database itself. Both of these options prevent users from being able to easily inject malicious SQL into SQL code that the database server will execute. Instead of running "raw" queries (creating a SQL query in a string and then running it directly in the database), ensure that each SQL command is only executed through these mechanisms.

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.

How Can StackHawk Help?

When it comes to SQL injection attacks, prevention is the best path forward. Of course, proper coding techniques and standards can help to prevent SQL injection from occurring but what if someone forgets these practices? This is where StackHawk can help to identify and remedy potential SQL injection issues by scanning the code locally or right in your CI/CD pipeline. With every commit, your applications and APIs are scanned and tested with StackHawk’s Dynamic Application Security Testing (DAST) platform. Once the scan and analysis are complete, the results, such as a potential injection vulnerability, are highlighted. Developers can then easily identify a fix and update code to remedy the vulnerability.

Along with detecting SQL injection vulnerabilities, StackHawk helps developers uncover hundreds of other types of potential vulnerabilities in their code. When you're trying to create the most secure applications you possibly can, leaning on automation as part of your application security stack is a must. Trying out StackHawk is super easy and within minutes you’ll be able to identify and address potential security issues. You can try it out today by signing up for a free account here.

Already using a security tool? StackHawk’s DAST platform is complementary to many other security tools. Many security professionals recommend various types of tools to be included in your application security stack.