SQL Injection: What is it? Causes and exploits

Summary

Many security vulnerabilities are discovered, patched, and go away forever. Some linger and continue to plague software development and will continue to do so for years to come. Setting aside social engineering and non-technical attacks, SQL injection remains one of the top security threats to our data, as well as one of the most misunderstood.

This article takes a modern look at SQL Injection and the many ways in which it persists, despite our knowledge into what it is, what causes it, and how to eliminate it. We’ll also discuss misconceptions and myths that often hinder our security efforts along the way.

What is SQL Injection?

Definitions vary amongst professionals, but I prefer the broadest one possible: SQL Injection is any attempt to run unauthorized code on a SQL Server via an authorized path. Unauthorized code includes absolutely anything from security commands to string searches to schema changes. The authorized path allows us to exclude traditional hacking, as well as social engineering and other exploits that clearly are not relevant here.

SQL injection is a subset of an even larger exploit known as an injection, which also includes application code, web components, networking hardware, and the other various components that make up the framework of an application.

This threat is the most frequent and consistently rated top security exploit in the history of database software. Despite years of research, identification, and attention, SQL injection persists and continues to plague organizations via unprotected endpoints.

For our work, we will consider a SQL injection attempt successful if a user can access any data or SQL Server components that they are not normally authorized to access.

Sources of SQL Injection

SQL injection requires some entry point to execute. Some common endpoints include:

Dynamic SQL

This is often mistaken as the only source of SQL injection, which can be detrimental to security efforts. Dynamic SQL provides the ability to splice variables into TSQL at runtime, allowing us to accomplish tasks that otherwise could be time-consuming or inefficient. This makes it a valuable tool, but one where its users need to ensure that each of those variables is cleansed appropriately prior to use.

Consider the following TSQL:

DECLARE @Sql_Command NVARCHAR(MAX);

DECLARE @Search_Criteria NVARCHAR(MAX);

SELECT @Sql_Command = '

SELECT

FROM Person.Person

WHERE Person.FirstName = ''';

SELECT @Search_Criteria = 'Edward'; -- This text is captured by a form field within an application.

SELECT @Sql_Command = @Sql_Command + @Search_Criteria + '''';

EXEC (@Sql_Command);

It’s a simple script that searches a table for anyone with a first name matched by the variable @Search_Criteria. If this variable is being populated via a web form or some open input, then there are a multitude of ways it could be abused. Some common tactics are to:

Close the string and enter your own TSQL
Use special characters to augment a LIKE comparison, such as %, [], or insert regex
Input an empty search
Use UNION ALL to append additional TSQL to the original search query
Use comments to eliminate any TSQL on the same line as the dynamic SQL

The following is an example of TSQL and the results that can be gotten if the inputs have no protection:

DECLARE @Sql_Command NVARCHAR(MAX);

DECLARE @id NVARCHAR(MAX) = '3';

DECLARE @password NVARCHAR(128) = ''' UNION ALL SELECT * FROM Person.Password WHERE '''' = ''';

SELECT @Sql_Command = '

SELECT

FROM Person.Password

WHERE Password.BusinessEntityID = ' + @id + '

AND PasswordHash = ''' + @password + '''';

EXEC (@Sql_Command);

The intention is a query that returns data for a given user ID and password, but since there are no protections on either input, the TSQL can easily be hacked into returning additional data from the table.

Being able to get a list of usernames, tables, passwords, or any related data can be quite dangerous, even if that data is incomplete. User names can be used to intentionally fail logins and lockout users. Information about schema can be used to determine other ways to attack a server and ultimately steal data.

Modification of URL Strings

While most modern web sites protect against this, URL hacking is still one of the most common ways in which someone attempts to gain access to data. This method is hard to detect, requires little skill, and determining success or failure is relatively quick.

If a URL contains search strings, security descriptors, or other sensitive components, then an application needs to validate URL contents prior to processing them. In addition, the application should obscure any URL contents that might be used to gain undesired app information, such as primary key IDs or entity names.

Maintaining and monitoring weblogs can be an arduous task, but can provide valuable insight into areas in which hackers are trying to maliciously access a given site. Alerting on these logs can give us advance notice of potential attacks and the details of how they are being performed, as well as their origin.

Web/Application Forms

Any place where users can enter freeform data should be given careful attention. This is the source of most SQL injection attacks. Hackers play the role of quality assurance and will enter every odd combination of characters, symbols, or data length in an attempt to trigger an error or unexpected behavior.

I’m curious about what happens when special characters are entered into these text fields. Are they treated like a string (as they should be) or do they interfere with TSQL or app code, causing unexpected behavior?

In this scenario, there are enough protections in place to ensure that my attempts at triggering errors or breaking TSQL strings would fail. By greatly restricting what characters are allowed, we can head off SQL injection before it has a chance to rear its ugly head.

Employee Abuse of Limited Access

While we never want to consider internal employees as potential bad actors, we need to accept that in an organization large enough, someone may eventually resort to bad behavior, given a good enough opportunity. A login that has limited server access may be able to exploit SQL injection to gain additional access that normally would not be allowed.

Securing against SQL injection removes this avenue of attack and protects an organization from within. This is valuable in a world full of social engineering, phishing, and other attacks that may go beyond the simplicity of a single malicious employee. A hacker with control over app or database access for an internal resource may gain quite a bit of power that the employee didn’t even know they had. We want this access to be minimized as much as possible, and the many protections we implement to counter SQL injection greatly assist in this challenge!

Error Messages

There are a few things more dangerous than exposing error messages in an application. If you are using an app and see the following, what do you think?

Some people might move on and ignore this, but any hacker knows that the exposure of this error message accomplishes 2 tasks:

It provides some information about the application, database, and schema via the error details
It shows us that errors are not trapped correctly and that there may be an opportunity to inject SQL at the error’s source

Error messages should always be trapped, whether they be SQL, application, or web errors. Once caught, we can log them and provide a friendly (and less-specific) message to the user.

How can SQL Injection be exploited?

Once an entry point is established, a hacker’s actions will only be limited by the security of the user they are operating as. Some examples of unauthorized use include:

Modifying TSQL statements to return additional data
Modify stored procedures, functions, or other database schemas
Test for the existence of database or server objects, such as tables or users
Alter passwords or permissions
Access components outside of SQL Server, such as server or storage infrastructure
Delete, steal, alter, encrypt, or attempt to ransom data from within the database
Perform a denial-of-service attack on the database server by utilizing excessive resources
- A sneaky hacker will do this to a different server or service to introduce a distraction

Common Causes of SQL Injection

No one intentionally leaves behind security holes that can be exploited with SQL injection. There are many reasons why these security holes come about, and oftentimes they are not because we simply wrote bad code. Here is a shortlist of the most common causes of SQL injection:

Old, Legacy, or Lazy Code

Sometimes code was secure enough or adequate when it was written, but as time passed and technology changed, what was secure 10 years ago is no longer acceptable.

As we write new code and create new features, we need to also review old code and features to ensure that things that are old are not also becoming antiquated. Unused or unneeded code should also be removed as it is the most likely to receive zero attention in the future. This balance helps ensure that all code stays secure and relevant when faced with the test of time.

Outdated/Unpatched Applications

Making use of unsupported or legacy software or features introduces security holes that may not be patched or caught as quickly as they would with modern software.

Running patched and modern versions of software are critical to avoiding security exploits, including SQL injection. Continuously monitoring for new security vulnerabilities and reacting as needed is an important step towards avoiding unnecessary surprises.

Security Assumptions

Often, we are led to a false sense of security due to the isolation of different application teams or environments. If we are led to believe that an application’s web forms are protected against injection by default, then we might be lax when developing stored procedures or other database objects.

We should practice optimal security whenever possible and never assume that a high level of security elsewhere will also protect us. Applications change and the assumptions that were true in the past or present may not be true in the future. These assumptions eventually lead to compliance and security auditing failures anyway, and provide no benefit beyond saving a little bit of time now.

Failure to Layer Security

The idea that security is a wall and that surrounding servers and applications in security measures can protect from hackers is no longer valid. More accurately, we should view application security like our homes: full of lots and lots of glass windows that can easily be broken.

The optimal way to secure an organization’s data is to have layers of security around it. Have a secure database server that is accessed by a secure application server that is accessed by a secure web server. Each of these devices is attached to secure networking devices and monitored by services that can alert when things go awry. The end result is a system where it takes significant effort to access data, to the point where such efforts would be sufficient enough to trip alerts and warn an organization that something bad is going on.

Within our personal control will be whatever components we manage, which are likely to be database servers. Build servers as securely as possible and never assume that other application components will shield you from bad actors.

Exploits

How SQL injection is used against up is very much up to the creativity or cruelty of a hacker. With unauthorized access to a database server, the level of severity of the attack will be measured by:

How long did the attack last before it was detected? How long after detection?
Was any unauthorized data viewed, downloaded, modified, or stolen?
Were any other systems accessed via the attack?
What are the repercussions of the attack? What is the impact on customers or consumers?

In the event of a breach, these and many other questions will be asked. The goal will be to fully understand a hack and its implications. In addition, privacy and compliance policies will be reviewed. If the hack also breached these agreements, then the complexity of the situation could be many orders of magnitude higher than it previously was. GDPR, HIPAA, EU-US Privacy Shield, Corporate privacy policies, and more all have an impact on a business and the level of security offered, as well as the necessary reaction when an exploit or breach is discovered. Under many agreements, it is necessary to disclose vulnerabilities, even if there is no evidence of a breach.

Most organizations now have security officers and teams that maintain complete knowledge of all business agreements, compliance agreements, and laws regarding data privacy and security. International organizations will have far greater and more complex needs as the laws of each country will need to be considered when determining how to operate. Many companies will choose to build their organization around the most stringent of requirements as a way to simplify operations and avoid the need to have completely different business models for each country-country relationship.

What can attackers do? Here are some examples:

Download unauthorized data
Delete/modify data
Permanently destroy data/backups
Long-term monitor a system
Infect systems with viruses or malware
Alter security to allow/disallow access as deemed fit by the hacker
Encrypt/steal/alter data and hold it for ransom
Publicly shame an organization via a web or social media hack
Use data to infiltrate an organization or its business operations

If any of this sounds outlandish or more likely to be the contents of a James Bond movie, then we’d be sorely mistaken. All of these scenarios have been the result of SQL injection attacks, and have happened many, many times.

The largest SQL injection attack to-date was on Heartland Payment Systems in 2008. The SQL injection attack was used to gain access to credit card processing systems. The attack began in March, 2008, but was not discovered until January, 2009. The company was unable to do business for 5 months afterward and was fined $145 million as compensation for fraudulent payments made.

Not all details of hacks and breaches are revealed to the public, so there likely are many more than we know about to date. In its most prevalent years, from 2010-2015, it has been estimated that over 2/3 of all companies and government agencies were vulnerable to SQL injection.

In general, older systems are targeted first under the assumption that an organization that does not stay up-to-date with software is more likely to be negligent on security. The top-attacked database platforms are Oracle, PostgreSQL, MySQL, and MongoDB. Within each platform, the rate of attacks increased with age. Having newer software versions not only provides more modern security options, but it discourages hackers, who typically are looking for easy/low-risk targets.

Where Do We Go Next?

With an understanding of what SQL injection is and its causes, we can begin to formulate strategies to detect and prevent it. This is our ultimate goal and one that is critical to data security and is one that affects any platform where data is stored, whether in SQL Server, MySQL, NoSQL, or some other. In our next article, we will continue this discussion into one where we attack this problem proactively, reducing the chances that we will be put on the defensive during a data breach.

SQLShack

SQL Injection: What is it? Causes and exploits

Summary

What is SQL Injection?