SQL Injection: Detection and prevention

Summary

With an understanding of what SQL injection is and why it is important to an organization, we can shift into a discussion of how to prevent it. We ultimately want systems where SQL injection is impossible or very difficult to pull off. We then want systems where exploiting bugs is slow, laborious, and likely to raise monitoring alarms within an organization when attempted. The trio of layered security, prevention, and alerting can provide an immense advantage against not only SQL injection, but other data security threats.

Preventing SQL Injection

This conversation’s ultimate goal is to provide us with the context needed to prevent SQL injection whenever possible. The following are tips that assist in preventing SQL injection altogether. Some of these tips also help in reducing the scope of SQL injection so that exploiting security holes is more challenging, less lucrative, or impossible.

Limit Result Sets

Put limits on all result sets, such as:

Implement a date range limit that ensures data is returned from a narrow date/time range
Limit row counts processed or returned. This prevents reading or returning too much data. In addition to preventing large result sets, limiting data processed can ensure good performance, regardless of parameters
Prevent blank searches. Allowing a user to search through everything with no filters or return all possible results will likely perform poorly and provide little value. If blank search criteria make no sense logically, then do not allow them

By guiding users towards common use cases, we can improve performance and bolster security at the same time. Allowing users freedom to do what they want sounds noble, but ultimately leads to more bugs, security holes, and exploits. A user will be happier with a limited set of quality options than a massive set of buggy ones.

Cleanse and Validate Freeform User Input

This is one of the most important steps to preventing SQL injection. Any data that a user can provide, whether via a web form, file, API, or other application needs to be cleansed and validated. This process will check user input for invalid characters, unacceptable length, or any other abnormalities prior to processing or storing it on any production systems.

The simplest step is for the application UI to detect invalid characters and provide instant feedback. Our example form from earlier provided a solid example of this behavior:

Not only did the form refuse my bogus data, but it provided a clear error message as to why my input was unacceptable. Username and password fields are often the dubious targets for this treatment, but realistically, ALL freeform input should be scrutinized for validity.

Being thorough and layering security means that we should also perform this exercise in TSQL as well. Here are a few examples of ways that we can cleanse inputs in TSQL to ensure that bad data does not get stored or actioned upon within our database:

Use parameterized stored procedures to accept inputs for common searches. In addition to providing more security options to you, they can more easily be fine-tuned for performance, as needed

Parameterize dynamic SQL, when used. This provides far more resilience towards SQL injection. The following is a simple example of a search in which the input @search_criteria is parameterized, rather than hard-coded into the inline TSQL:

DECLARE @CMD NVARCHAR(MAX);

SELECT @CMD = 'SELECT * FROM Person.Person

WHERE LastName = @search_criteria';

PRINT @CMD;

EXEC sp_executesql @CMD, N'@search_criteria NVARCHAR(1000)', @search_criteria;

Note that the parameter @search_criteria is redefined within the dynamic SQL as an additional parameter list. By passing parameters from step to step, we avoid building TSQL by hand, as well as the need to check manually for apostrophes and other common SQL injection hacks

Use sp_executesql when executing dynamic SQL. This is a versatile stored procedure that provides immensely more flexibility than EXEC(). It is also safer and allows for built-in parameterization. Keep in mind that both the dynamic SQL statement and parameter list can be customized freely prior to passing into sp_executesql
If needed, use QUOTENAME to cleanse a variable of potentially unwanted characters. For example, the following TSQL delimits a string in apostrophes, replacing an attempt to break a string with apostrophes with a valid string:

1
2
3

DECLARE @form_input VARCHAR(250) = '''SELECT * FROM dbo.all_of_my_passwords';
SELECT @form_input;
SELECT QUOTENAME(@form_input, '''');

The results show apostrophes around the string. In addition, the apostrophe in the input string has been replaced by double-apostrophes, which ensure that strings that use it will not break:

There are many poor alternatives to using QUOTENAME, the worst of which is applying lots of REPLACE functions to manually adjust character combinations. The result often looks like this:

1
2

DECLARE @form_input VARCHAR(250) = '''SELECT * FROM dbo.all_of_my_passwords';
SELECT REPLACE(@form_input, '''', '''''');

This is a challenging way to sanitize inputs as we need to anticipate every bad move that a hacker will make and account for it here. If we are the poor saps that are executing dynamic SQL within another delimited string, then expect the volume of apostrophes to approach dizzying quantities that result in messy and error-prone code
Ensure that application and web code are also cleansing inputs. This provides added layers of protection that shield us from the negative effects of bad code, human error, or a security vulnerability

Remove Freeform User Input When Possible

The easiest way to prevent SQL injection via form fields is to remove the users’ freedom from entering whatever they’d like. While providing users with additional options may seem a noble thing to do, we quickly learn that users that can enter anything will enter anything. Ask for a title and you will get Jedi. Ask for nationality and you’ll get Vulcan. Or Klingon perhaps. Freeform input is ideal when you truly want a user to provide anything, such as when enumerating notes, long-form text, or opinions.

Whenever possible, use drop-down menus, radio buttons, or other input methods that provide a set list of options. Customizable options can be easily created via configuration settings or user preferences. This allows flexibility while maintaining stability and predictability at runtime. An organization or user can choose a set of valid options via a configuration menu. Once entered and validated in this location, they are available elsewhere in the application via restricted menu structures.

More importantly, removing freeform fields reduces the number of places that SQL injection will be used to target. As a bonus, removing freeform fields simplifies code and increases stability as the specter of unknown user input vanishes, leaving a set of known entries that are easy and safe to manage.

Validate Data Prior to Processing

Cleansing data removes invalid characters and ensures that inputs will not break code or become security vulnerabilities. Validation checks data to ensure it is logically correct and does not contain invalid or unwanted values. At first glance, validation may not seem relevant to security, but oftentimes validation errors will lead to the discovery of more interesting problems. These problems might involve bad data, code problems, poorly cleansed data, or a malicious user trying to find security holes.

Validating data and logging results allows us to ensure that only valid data is stored in a table. As a bonus, we can log validation errors so that developers can research and improve code as time allows. Validating data in multiple places can provide deeper insights. For example, validating analytics, reporting, and data warehousing data can often uncover other data issues that may be indicative of application problems that have yet to manifest themselves as significant bugs.

At a higher level, in addition to validating data, consider validating metadata and metrics, such as table row counts, data size, and utilization changes over time. These can point out scenarios in which data is growing unacceptably fast and could help pinpoint a problem before it becomes an emergency.

Ensure Errors are Not User-Facing

Error messages are an immediate hint to any app user that something is wrong. If the errors are caught and transformed into a friendly error that is intended for general consumption, then most people will either report it or move on. If the error is a SQL Server error, though, a hacker would immediately assume that they have found a hole into the application. If one error can inadvertently be thrown, can use of SQL injection force additional errors or worse, valid and unintended TSQL?

Ideally, errors should be caught and handled gracefully at all levels of an application. Sensitive TSQL can be executed within TRY and CATCH blocks. This provides an immediate chance to respond to exceptions before entering the application code. Once past SQL Server, all SQL errors need to be caught by the application, logged, and handled as peacefully. We can ask ourselves the following questions:

Does an error need to be communicated with the user? No is a better answer, if allowable
Do error details need to be shared with the user? A generic error message is far more secure than one with specifics. Only provide additional information when safe and necessary to do so
Are there common or standard error conditions that can quietly be responded to with a retry? For example, if a web search times out, can it be rerun a few times until it succeeds? If allowable, retrying on an error condition forgoes the need to communicate it with a user. It may result in latency, but if it is infrequent, it is likely preferable to an error condition
Can feedback be provided to the user to prevent errors in the future? For a known issue, this is useful. For example, if I run a wide-open message search of my entire email history, it will take a very, very long time to complete (if it ever finishes). A smarter email client would gently remind me that entering more search criteria, such as dates or subjects can greatly speed it up

Use Stored Procedures to Abstract Business Logic and Control parameters

Stored procedures are more rigid than the application code. They accept a known set of parameters and return predictable results. For very common or sensitive processes, this can be a positive way to ensure that important code executes and performs the same way every time. In addition to writing secure TSQL within the proc, we can assign permissions to a stored procedure separately from underlying tables, allowing us to greatly restrict and customize who can or cannot access it.

Use LIKE Operators Carefully

When user input is passed into a query that utilizes a LIKE operator, we need to be certain that the resulting query only requests valid data. For example, we can do a simple search for people named “Edward” like this:

DECLARE @Search_Criteria NVARCHAR(MAX) = 'Edward';

SELECT

FROM Person.Person

WHERE Person.FirstName LIKE @Search_Criteria;

The results are a set of 72 people named Edward:

What if the user were to add a percent symbol to their search:

DECLARE @Search_Criteria NVARCHAR(MAX) = '%%' -- User searches for "Thom", and gets no results returned.

SELECT

FROM Person.Person

While not an empty search, the query above will return the entire contents of Person.Person:

In this scenario, the result set is 19,972 rows. We either need to cleanse the input to ensure that the percent symbols are treated as character literals or disallow them altogether. If necessary, TSQL can be used to manipulate the string and turn the percent symbols into character literals:

DECLARE @Search_Criteria NVARCHAR(MAX) = '%%' -- User searches for "Thom", and gets no results returned.

SELECT @Search_Criteria = REPLACE(@Search_Criteria, '%', '[%]');

SELECT

FROM Person.Person

WHERE Person.FirstName LIKE @Search_Criteria;

This is not an ideal solution as it only addresses a single symbol, but if that were the only character issue, then it would be acceptable. The key takeaway from this behavior is to exercise caution with LIKE comparisons and ensure that the input string cannot pass through unfiltered character or regex modifiers that might allow a user to return results that are not intended for them. Similarly, if a table is massive, we do not want a user to be capable of issuing an index scan against that large table as poring through billions of rows would present a performance problem, on top of a security problem.

Limit Use of xp_cmdshell and Other Extended Stored Procedures

Extended stored procedures facilitate interaction between SQL Server and other server components, such as services and disk resources. This can be a huge convenience if you need to read a file, output a backup, or interact with operating system settings, but it also provides an additional security hole that can be exploited.

Instead of xp_cmdshell, consider using Powershell or other scripting protocols that are built for interactions between different systems. There, permissions can be carefully controlled and scripts isolated to areas that are not reachable by application code or database code.

If xp_cmdshell is truly needed or is a part of legacy code that cannot easily be removed, following best practices with regards to security can help when managing that access:

Limit users with access to xp_cmdshell. Explicitly deny access to application users or others that should never have access to it
When a non-sysadmin accesses xp_cmdshell, security falls back onto the ##xp_cmdshell_proxy_account## credential. If this is undesired behavior, then ensure that no proxy security exists for this extended stored procedure
When a sysadmin executes xp_cmdshell, it will do so using the security rights of the SQL Server service account. Ensure that the service account does not have any more rights than is needed for the operation of that specific SQL Server

Without a doubt, try at all costs to avoid using any “xp_” extended stored procedures. They provide links between SQL Server and the operating system that are hard to identify, quantify, and assess. For a server that is accessible to any user base beyond internal administrators, this can be a serious security hole, and one that can put an organization at risk if exploited.

Perform Penetration Tests

Internal QA and security testing are important, but catching everything is next to impossible. Many third-party companies will perform tests against an application to test for many vulnerabilities, including SQL injection.

As companies whose sole purpose is to locate security flaws, they will tend to have a high level of success in uncovering vulnerabilities that were overlooked internally. Once documented, an organization can fix each vulnerability and a future test can validate the improvement over time.

Common threats found by penetration-testing companies are:

Insecure Passwords
Default users or passwords are used
Injection (including SQL injection)
Unpatched software vulnerabilities
Out-of-support/end-of-life software
Configuration mistakes
Cross-Site Scripting

An organization benefits greatly from hiring a third-party company and allowing them to perform a site-wide security test. Even more important is to repeat these tests regularly to ensure that new vulnerabilities are found and dealt with quickly, so as to minimize the exposure period.

Even if you have a security officer or team, the ability to find everything on one’s own is practically impossible. An alternative to hiring a security firm would be to purchase penetration testing software and run the tests yourself. This can also be effective and removes the potential challenges of allowing third-party company access to your systems.

Code Review

This is a critical step of the software development process. The code should always be reviewed prior to testing and release. In an ideal world, code would be reviewed by multiple people, each with a different area of expertise, such as security, performance, or application domain knowledge.

Code review allows problems to be located and fixed early in the development lifecycle and long before they reach users. Code review cannot replace other security measures, but is an exceptionally important step that can quickly allow us to find bugs that could very well lead to security vulnerabilities (among other bugs). The time spent up-front on code review will save significantly more time later on bug identification and remediation.

Minimizing the Impact of SQL Injection

In addition to preventing SQL injection, we would be negligent if we did not identify our ability to make mistakes and acknowledge the need to have other security measures as well. Building solid security, in general, helps in reducing the impact of SQL injection and ensures that we are not one coding mistake away from a data breach!

While writing better code is important, it alone will not prevent us from being the victim of SQL injection. Why?

We do not have the time to review and re-review all code (and do so perfectly) to ensure that we have caught everything
We cannot control code outside of our domains, such as vendor code, OS code, microservices, or other apps that are maintained by other parties
Anticipating all bugs, past, present, and future is a futile task
Applications evolve and the code that is secure today may not be so secure tomorrow. Now infamous Intel vulnerabilities Meltdown and Spectre underscore this fact!
People are human and make mistakes

The moral of this story is that we should have tight security on top of preventing SQL injection. This minimizing the impact of any mistakes or vulnerabilities we may encounter and ensures that even if someone gains unauthorized access, their success will be greatly limited and (hopefully) detected.

Principle of Least Privilege & Login Security

Always limit server and database security the bare minimum of what is needed. The sysadmin role should truly be limited to those few people whose job it is to administer a server. The db_owner security role should be reserved for the small number of use-cases in which an operator needs full database control, which is generally rare.

Applications should not need either of these roles and should be able to operate within the confines of some amount of read/write/execute access to a specific database or set of databases. Vendor apps will occasionally request sysadmin or db_owner privileges for the purpose of installations or updates. These scenarios should be investigated thoroughly and if those permissions are indeed required, determine if they can be rescinded when the installation is complete. Allowing an app to have unfettered server access is dangerous. Allowing that access to an app that is out of your control is even riskier.

Different apps should use different logins. Login sharing is dangerous and makes it harder to identify the source of a connection. Ideally, Windows authentication is used whenever possible. In addition to being more secure, it is easier to control access as there are additional permissions that can be adjusted in Active Directory that can enable, disable, or alter users via user-specific or group policies.

Similarly, different SQL Server services should have different logins. SQL Server, SQL Server Agent, SSRS, and SSIS should operate on different credentials than the applications that use them. Each of those services should also take advantage of unique logins, thus minimizing exposure if any one of those logins were compromised. These logins should also be distinct from those used for other servers, services, and file shares.

Never use the sa login. After Windows is installed and configured, change the password for sa to something incredibly secure, disable the login, and do not look back 🙂

Secure Linked Servers and Data Sources

Linked servers allow for access to other data sources outside of the current SQL Server:

Once created, they can be queried very similarly to other local objects. Linked servers can be created that access many types of data sources, including:

Other SQL Servers
MySQL, PostgreSQL, MongoDB, and other types of databases
SSIS
SSAS
Other ODBC data sources

Security on linked servers is completely customizable. Users can be mapped from local to remote and granular permissions assigned to control what access is allowed over a given linked server. The linked server properties allow you to control many aspects of data transfer, such as whether RPC (remote procedure calls) are allowed to and from the server, timeouts, and the behavior of DTC:

The ideal linked server provides minimal access to the target server needed to accomplish its goal. When managing users and logins, the optimal scenario is to give the linked sever a distinct login that is separate from those used by other processes on the target server. This not only makes it easy to differentiate linked server traffic from other activity, but allows permissions to be set at a granular level, ensuring that a linked server can only access the tables, procs, or views that are required to execute its tasks:

In this example, a single local login gets mapped to a distinct remote login. All other connection attempts will be refused. This prevents a hacker from finding some common user between the local and remote servers that could provide additional access. It also greatly restricts permissions to the minimum needed by a given application, further restricting data access, and therefore also restricting unauthorized data access.

Conclusion

The SQL injection is a massive topic that has continued to grow and evolve over time. It is a vulnerability that garners attention across all areas of data security and has shown no sign of any sort of industry-wide resolution. This will continue to be a major security concern for years to come and anyone developing software or systems needs to be aware of how it can put sensitive data at risk.

When writing software, use extra caution with user input. Ensure that freeform text is carefully handled and that any systems that consume that data cleanse and validate it first. In addition to handling data carefully, monitor and respond to security vulnerabilities throughout your organization. Maintain up-to-date software and remember that bugs and vulnerabilities anywhere in an organization can lead to data loss elsewhere.

Lastly, maintain a thorough software development lifecycle that allows for sufficient code review, testing, and debugging to catch as many bugs as possible prior to release. With all of this in mind, I wish you good luck and happy bug hunting!

SQLShack

SQL Injection: Detection and prevention

Summary

Preventing SQL Injection

Limit Result Sets

Cleanse and Validate Freeform User Input

Remove Freeform User Input When Possible

Validate Data Prior to Processing

Ensure Errors are Not User-Facing

Use Stored Procedures to Abstract Business Logic and Control parameters

Use LIKE Operators Carefully

Limit Use of xp_cmdshell and Other Extended Stored Procedures

Perform Penetration Tests

Code Review

Minimizing the Impact of SQL Injection

Principle of Least Privilege & Login Security

Secure Linked Servers and Data Sources

Conclusion

Further Reading

Summary

Preventing SQL Injection

Limit Result Sets

Cleanse and Validate Freeform User Input

Remove Freeform User Input When Possible

Validate Data Prior to Processing

Ensure Errors are Not User-Facing

Use Stored Procedures to Abstract Business Logic and Control parameters

Use LIKE Operators Carefully

Limit Use of xp_cmdshell and Other Extended Stored Procedures

Perform Penetration Tests

Code Review

Minimizing the Impact of SQL Injection

Principle of Least Privilege & Login Security

Secure Linked Servers and Data Sources

Conclusion

Further Reading

Related posts: