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 class of bugs and explore ways to prevent it.

What is SQL injection?

A SQL injection attack is a web application attack in which the attacker “injects” SQL statements with malicious SQL commands to manipulate or access application data, whether sensitive or public. These attacks leverage areas in web applications that ask for user input. If user inputs in an app are not properly sanitized through input validation, an attacker can use a SQL injection attack to gain access to the associated app datastore. 

How SQL Injection Works

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:

SQL

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 then display their data to them.

But, consider this: instead of inputting their user ID, they input what can be interpreted as a SQL string query as in the following example:

SQL

'42' OR '1'='1'

If you take their input as-is, without sanitizing, this will result in something like the following SQL query:

SQL

SELECT * FROM users WHERE id = '42' OR '1'='1';

Since 1=1 is always true, this statement (and other syntactically valid queries like it, including complex string concatenations) could return every data field for all users in the users table. This is a classic example of a SQL injection; it does not trigger a warning due to incorrect syntax or a modified query, but instead utilizes the system against itself.

It’s important to note that this is the output the database is designed to provide 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. 

Types of SQL Injection

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 attack 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 is often separated into error-based SQLi and union-based SQLi.

Error-based SQLi

An Error-based SQLi is a type of SQL injection attack 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 a times, attackers will use an Error-based SQLi to perform reconnaissance on the database before ultimately executing a SQL injection attack 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 in 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:

SQL

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 has an Error-based SQL injection vulnerability, and can take advantage of this by invoking more informative error messages that give information about the database through well-crafted malicious SQL statements. 

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 SQL injection attack uses the UNION operator to output additional data into a single result, typically in 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 statements 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. 

http://mycoolapp.com/allusers.php?id=‘ UNION SELECT * FROM names —

This will result in the following query:

SQL

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:

SQL

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:

SQL

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

Common Targets of SQL Injection Attacks

SQL injection attacks primarily focus on attacking vulnerabilities in a database interaction, and as such, they can be aimed at any system that utilizes a database. The particular method of attack and the aims of that attack will differ both due to the nature of the database in question as well as the intent of the attacker.

One particularly common target is the user authentication and authorization systems. By using basic injections, insecure databases might surface new attack vectors or even sensitive data that can help bypass login mechanisms entirely. This retrieval of sensitive data can also be used to expose and map the database schemas and metadata of the system underlying these security methodologies, bypassing a lot of security mechanisms.

In some cases, SQL injection attacks can even be used to work against the system itself. Utilizing well-crafted SQL injections can cause runaway data retrieval, undermine the way the database functions within the server, or even delete all data in entirety. This can have huge impacts on the overall system, especially if you don’t have recent or complete database backups.

Examples of SQL injection

SQL injection attacks have been used to great effect by attackers in many different fields. Let’s look at two specific instances.

2008 Heartland Payment Systems Breach

• What Happened – Heartland Payment Systems, a major U.S. payment processor, was breached by attackers, resulting in the exposure of 130 million credit and debit card numbers. This exposure was, at the time, one of the largest breaches in history.
• How It Worked – in 2007, attackers used an SQL injection to insert malicious commands into the computers that processed payments for Heartland Payment Systems. These computers were then leveraged to access a web login page, allowing for additional spread throughout the payment vendor’s network. Attackers were able to then capture transaction data, utilizing this data to make new physical cards for their own use.
• Impact – this was an absolutely massive attack and undermined the trust of hundreds of millions in the financial provider. It led to massive fines and financial losses, adding up to more than $200 million USD. Heartland lost more than 50% of its stock value in the days following the breach’s announcement, and the stock would see a further 77% drop over the next few months. Heartland Payment Systems lost its PCI DSS compliance for four months, losing many of its largest customers, and PCI DSS itself was made more strict as a result of an investigation into the breach.

2012 LinkedIn Data Breach

• What Happened – in 2012, LinkedIn suffered a data breach where hackers stole a massive amount of data. At the time, this was reported to be 6.5 million hashed passwords, but since, it has been revealed that the total number of potentially breached users may have reached 167 million users.
• How It Worked – the attackers utilized a SQL injection in the LinkedIn application which managed user credentials. Utilizing this attack vector, they were able to extract hashed passwords directly from the database, which were then cracked using brute force attacks.
• Impact – LinkedIn was significantly impacted by this attack. As all users were forced tor reset their passwords, LinkedIn lost a large amount of user trust, and were ultimately subject to a large class-action lawsuit seeking millions in restitution.

How to Prevent SQL Injection

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. Additionally, you may also want to leverage stored procedures or parameterized queries that you can create inside the database itself.

Both of these options prevent users from being able to easily inject malicious code via SQL injection 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

A skilled and determined attacker will find any potential vulnerability you might have, so the only real way to win – to paraphrase War Games – is not to play. The principle of least privilege limits access for users based only on what privileges they need and stops a lot of attacks in its tracks by denying the attacker a vector from which to escalate their threat.

For example, a user of a web application may not need access to an entire database. By granting users access only to the tables they need, you can reduce the potential impact of misuse by a malicious user.

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 resources such as this blog post 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. A successful SQL injection attack only really needs a single weakness to be effective, so automated testing will help make sure that you are able to prevent SQL injection vulnerabilities before they are ever pushed to production.

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. The bottom line is to use automation to help ensure you ship code that is free from security bugs.

Adopt Zero Trust to Build a Strong Security Posture

The simple fact is that any user could threaten your internal systems, and you should adopt a policy of zero trust. Implementing application security measures such as effective access control, limiting the proliferation of administrator privileges, preventing direct access to the SQL database – these are all simple steps that add up to a major upgrade for your security posture.

This can be taken a step further by ensuring that you are implementing a secure posture globally. Putting all of your effort into securing a single part of your approach is not going to stop malicious users. You need to pay as much attention to stopping the effort to manipulating databases as you are to implementing a strong web application firewall or other traffic filter. You need to be running routine user role audits as much as you are ensuring that database servers are running on up-to-date patches and operating servers.

SQL injection prevention is frighteningly common, but its presence could suggest a more systemic threat – so be aware and be holistic!

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 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 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. Most security professionals recommend multiple tools to be included in your application security stack.