Click here to monitor SSC
  • Av rating:
  • Total votes: 42
  • Total comments: 9
Timothy Wiseman

SQL Injection: Defense in Depth

17 March 2011

So much has been written about SQL Injection, yet such attacks continue to succeed, even against security consultants' websites. The problem is often that only part of the solution is described, whereas the best practice requires the use of defense in depth.

In spite of the threat that is presented to data security by SQL Injection, many programmers and DBAs are either unaware of it, or do not know how to properly prevent it. This is partly because SQL injection, and methods to prevent it, are so rarely talked about in formal education: I went through two classes on database theory, several books on SQL Server, and a MCDBA before I first really learned about SQL Injection through “The curse and blessings of dynamic SQL” by Erland Sommarskog.

Because of this, SQL injection remains a common and effective attack. In a significant recent case, even a firm dedicated to security was at least partially compromised through a SQL injection attack (See ‘Anonymous speaks: the inside story of the HBGary hack’ ), providing salutary lessons to the industry on what can go wrong.

There are already a plethora of articles about protecting against SQL Server injection on SQL Server. Yet, few of them emphasise that the best defense against such attacks is a defense in depth, with a whole range of precautions. Many of these articles focus almost entirely on parameterizing SQL as the defense against SQL Injection. While parameterizing is the first and best defense against SQL Injection, it should not be the only one. Thus, I decided to add one more to list examining various layers of defense and using python for the examples.

What is SQL Injection?

SQL Injection attacks are carried out by passing specially-formatted strings as input. In a successful attack, those special strings are passed along to a database to either execute arbitrary code or cause the server to return unanticipated results. For example, if we have a python program using pyodbc which concatenates user input into a SQL query like this:

userInput = "'; Drop Table Test; --"

 

conn = pyodbc.connect(connString)

curs = conn.cursor()

 

sql = ("Select City, State from dbo.ZipCodes where zipcode = '"

        + userInput +"'")

 

curs.execute(sql)

conn.commit()

Then a malicious user who carefully formats the zipcode entry could execute unintended SQL commands. For instance, if the user provided:

'; Drop Table Test; --

Then the profiler would show that the server would receive:

Select City, State from dbo.ZipCodes where zipcode = ''; Drop Table Test; --'

Assuming the program had the proper permissions, the server would obediently drop the test table.

Basic Techniques to Prevent SQL Injection

Parameterize all Queries

The first, best, line of defense against SQL Injection is to parameterize all SQL queries in code. If the previous example using pyodbc had been parameterized, it could look like:

userInput = "'; Drop Table Test; --"

 

conn = pyodbc.connect(connString)

curs = conn.cursor()

 

sql = "Select City, State from dbo.ZipCodes where zipcode = ?"

 

curs.execute(sql, (userInput,))

conn.commit()

This causes the profiler to receive quite a few messages, but the key part is:

exec sp_prepexec @p1 output,N'@P1 varchar(22)',N'Select City, State from dbo.ZipCodes where zipcode = @P1','''; Drop Table Test; --'

Since it received the malicious code as a variable, the server would simply look for the value in the table and return a blank result sets. The malicious string is never executed, so the test table is never dropped.

Similarly, most ORMs like SQLAlchemy will automatically parameterize all SQL statements under normal circumstances. Thus, they provide a good initial defense against SQL injection.

Use Only Stored Procedures

The use of stored procedures by themselves does not provide direct protection against SQL injection, although it can properly be used as part of a more comprehensive defense. To see why stored procedures cannot by themselves protect against SQL injection, consider one that queries to retrieve the city and state for a zip code like:

create procedure dbo.GetCityState

      @zipcode varchar(15)

as

 

select city, State

from dbo.ZipCodes

where zipcode = @zipcode

Then if there is a python program that executes:

sql = "exec dbo.GetCityState '" + userInput + "'"

 

curs.execute(sql)

conn.commit()

Then an attacker might provide:

'; Drop Table Test; --

As the input, which will send...

exec dbo.GetCityState ''; Drop Table Test; --'

...to the server. Which, again assuming proper permissions, would drop the Test table just as it did without the stored procedure.

Of course, that type of attack can be prevented by parameterizing the input, just as with the standard select statement. However, if the stored procedure itself uses dynamic SQL that is made through concatentation, then the stored procedure may execute the malicious commands even if the calling program properly parameterizes. This can be prevented by parameterizing dynamic SQL in stored procedures through sp_executesql and is discussed in “The Curse and Blessings of Dynamic SQL”.

The greatest value for using stored procedures in preventing SQL injection is that the DBA can set permissions for the application account so that its only way to interact with the SQL Server is through stored procedures. (See SQL Server Security Workbench Part 1 ) This would mean that most SQL injection attacks would fail due to lack of permissions even if the calling program did not parameterize. This of course still leaves open the possibility of SQL injection working through dynamic SQL inside the stored procedures, but the stored procedures can be given an “execute as” clause which limits their permissions to only those needed by the procedure. It is generally easier to verify that all stored procedures are written to guard against SQL injection then it is to check every place where the application interacts with the SQL Server.

Limiting Permissions

This naturally leads to a very effective method of preventing some attacks and limiting the damages from SQL injection attacks, namely using the account with the lowest permissions possible for a job. If the account being used does not have permission to drop a table, then it will not be dropped even if the command is slipped to SQL Server. Similarly, if the account has only read access, an attacker might be able to gain some information, which can certainly cause problems, but the attacker will not be able to modify or destroy the data, which is frequently worse. Even read permissions can be strictly limited in SQL server, to limit which tables can be viewed. If the application only needs selected columns from a table, then read permission on the view can be granted rather than the full table.

Validating input

User input should always be treated with care and there are a number of reasons to validate all the user input before further processing. Validation code can also help to avoid wasting server resources by restricting requests that would not return useful results and they can provide much more helpful messages to the user than a SQL error message or empty result set would likely provide. They can also help stop SQL Injection by rejecting, outright, any forms of input that could be used to perform a SQL injection.

Because of its many advantages, it is always important to validate user input, but it is particularly significant when the user input is being passed on to other routines for further processing, or in some of the rare cases where it is impossible to fully parameterize the input. For instance, if you are dealing with the rare situation where the users are required to provide a table name for a DDL statement, the table name cannot be passed in as a parameter and must be concatenated at some point. In that situation, validation of the input is a crucial defense against injection attacks. Similarly, if the input is passed in to a stored procedure, then it is possible that the stored procedure will use it to generate dynamic SQL via concatenation, even if the program properly parameterizes the procedure call. With the benefits that validation can bring, it is generally wise to validate all user input, even when fully parameterizing database calls and using an account with limited permissions.

Concealing Error Messages

Injection attacks often depend on the attacker having at least some information about the database schema. He often gains this through trial and error, and the error messages will tell the attacker quite a lot about the schema. Both SQL Server and python generally provide clear, informative error messages that are incredibly helpful to programmers, but can also provide information to a malicious user. Pyodbc, in particular, will normally raise a pyodbc.ProgrammingError exception, which helpfully includes the SQL Server error message.

Encasing a python call to SQL Server in a try/except block will enable the program to provide a more user friendly error message, which does not contain useful information for attackers, to the end user. If used along with something like sys.exc_info and a logging package, the except blocks can log all errors for later analysis while displaying a user friendly message to the end user. A very basic example might look like:

import logging

import sys

 

logFile = 'test.log'

logging.basicConfig(filename=logFile,level=logging.DEBUG)

 

#establish connection, create faulty SQL, removed for brevity

try:

    curs.execute(sql)

    conn.commit()

except:

    print 'User Friendly Error'

    logging.debug(sys.exc_info())

Of course, to ensure no unfiltered messages get through it is possible to override the standard exception hook like:

import sys

 

def new_exceptionhandler(type, value, tb):

    #put in custom logging code here

    print 'There was a general error.'

   

sys.excepthook = new_exceptionhandler

 

print aVariable #NameError since aVariable is not defined

This will not have any impact on exception handling code, but it will prevent standard error messages from reaching a user for unhandled exceptions.

Limiting Damage

As well as taking steps to prevent attacks like SQL injection, there are other general security steps that can be taken to limit the damage. Limiting the permissions of the accounts used has been mentioned, as it can stop many attacks outright, but it will also limit the amount of damage that can be done by a successful attack. But there are other methods that can help mitigate the damage done by an attack.

Use encryption/hash functions where appropriate

When data is properly encrypted, it can be made of little value to someone without an encryption key. Cell level encryption can assist with protecting against unauthorized access to sensitive data SQL Server has supported it since SQL Server 2005. Transparent Data Encryption (TDE), while useful for protecting the database against other forms of attack, is of very little value against SQL injection.

Passwords in particular should not be stored in clear text, and hashing is generally better than encrypting as it makes it harder to recover the original plaintext. Of course, even hashing may provide only limited security if it is not handled properly. For example, rainbow tables (http://en.wikipedia.org/wiki/Rainbow_table) exist for the MD5 algorithm. They make it relatively practical to determine the original plaintext from a single iteration of MD5 without salting. There are libraries that can make hashing and salting a password relatively easy in python, including hashlib.

Segregate data

Segregating data into different systems, depending on the level of security it needs, can help limit the reach of an attack. It often even makes sense to ensure that truly sensitive data is stored in a way that is not accessible from an outside network. This helps to ensure that even if an attacker compromises a system, that it will not immediately lead to the attacker compromising all systems. Of course, it is necessary to ensure that the same log on credentials are not shared between the segregated systems, otherwise if one set of log on credentials is compromised in some way it may lead directly to compromising other systems.

Auditing and Logging

Auditing and logging will never help prevent SQL injection or any other attack. However, it is likely to help detect the attacks, and may help in recovering from it. There are a number of tools within SQL Server such as Change Data Capture and SQL Server Audit. Custom written triggers could also be used to monitor and log changes. There are also a number of external tools that can provide more options such as SQL Server Compare and SQL Server Data Compare. The Logging library and other similar libraries make logging from the python application also relatively easy.

Conclusion

SQL injection is one of the more common, and more effective, forms of attack on a system. By following principles of secure software design such as parameterizing input to the database, sanitizing and validating user input, and restricting the permissions given to all accounts to the minimum required it is possible to make it extremely difficult for a SQL injection attack to succeed. Also, by following basic security practices like encrypting sensitive data, segregating data, and maintaining logs it is possible to limit the amount of damage that even a successful attack can do.

More references

Timothy Wiseman

Author profile:

Timothy A Wiseman is a Database Administrator for SAIC with a focus on efficiency and readability of the database and its surrounding ecosystem. In addition to administering the core SQL Server system, he has experience working with Python and Microsoft Access in conjunction with SQL Server. He holds a Bachelor's of Science in Mathematics as well as an MCDBA and MCITP.

Search for other articles by Timothy Wiseman

Rate this article:   Avg rating: from a total of 42 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Thank you for article
Posted by: TATWORTH (view profile)
Posted on: Friday, March 18, 2011 at 12:42 PM
Message: Thank your for article. I have blogged about it at http://geekswithblogs.net/TATWORTH/archive/2011/03/18/sql-injection---some-sense-at-last.aspx

Subject: A simple solution and testing
Posted by: JamesC (view profile)
Posted on: Monday, March 21, 2011 at 4:35 AM
Message: The simplest solution I have found to this problem is to ensure that you always process strings to replace single quotes with double quotes. SQL Server always interprets a double quote as a single. For example the code above would become

sql = ("Select City, State from dbo.ZipCodes where zipcode = '" + Replace(userInput,"'","''") + "'")

I have found this technique along with a methodical approach to testing practical on projects of all sizes. Testers should be educated to ensure that they contain single quotes in inputs to find SQL Injection security weaknesses and bugs. I would also go one further and say that they should include other special characters (\"*@.) ect in there test strings to pick up other problems as these strings may be used for creation of folder paths and url addresses.

James

Subject: Another solution
Posted by: Anonymous (not signed in)
Posted on: Monday, March 21, 2011 at 5:26 AM
Message: Using the .Net framework it is much easier to defeat SQL injection. Just use the the ASPX controls on your web page and of course parametize all SQL scripts and the framework will do the rest for you. That's why we prefer .Net developement these days!

Subject: A simple solution and testing
Posted by: timothyawiseman@gmail.com (view profile)
Posted on: Monday, March 21, 2011 at 1:01 PM
Message: @JamesC, purging or escaping special characters that are in the strings generally will provide a good protection against SQL injection. However, you need to be careful about when and how it is used. For instance, replacing single quotes will not help if you are working with numeric values and no single quotes are expected.

I also do not see how that is simpler than paramaterizing queries through the library, which has the added benefit of encouraging query plan reuse (though admittedly that was more significant in earlier versions of SQL than it is now.)

Subject: Validate input with a WHITELIST not a BLACKLIST
Posted by: BigLar (view profile)
Posted on: Monday, March 21, 2011 at 6:56 PM
Message: About five years ago, I attended an in-depth workshop on securing applications. The presenter really stressed the importance of scanning web input for ALLOWED characters (the whitelist), instead of trying to screen out DANGEROUS characters (the blacklist).

Several white-hat hackers who were present thought they were pretty smart, but the presenter surprised them a dozen times with techniques they were not familiar with - techniques that had actually been used for mailicous purposes.

There are many ways of encoding characters, including reliance on code page transitions that convert non-english characters into ascii characters. These transitions can occur within the database engine or a middle tier - long after your validation code has run.

There are one-byte and two-byte character sets, and, you have to make sure you are handling both.

The number of encoding tricks used by injectors grows each year, so blacklist validation methods are obsolete as soon as they are coded.

Don't rely on interface-level screening. You have to thoroughly screen the input after it arrives on your servers. There are many powerful tools available to anyone who joins the hacking community, so it is very dangerous to rely on screening or data type-casting code that executes on any computer you do not have tight control over. You also need to periodically check that your validation code has not been replaced or tampered with.


Subject: An analogy in support of multiple layers of defense ...
Posted by: BigLar (view profile)
Posted on: Monday, March 21, 2011 at 8:00 PM
Message: Never think you have it all figured out.

I remember an early copy protection method for software disks that relied on a microscopic hole in the disk being cut in a specific place by a precision laser - after the software was written to it ("PC Vault", perhaps?).

The idea was that a write+read test on the sector that contained the laser hole would produce a specific type of error that would only occur if you had a hole of the same size in the same place of that sector, and then only if that hole was created after the disk was written.

An attempt to "diskcopy" (image copy) the disk would end with an error because part of the disk (the hole) could not be copied. An attempt to create a copy file-by-file would fail because the sector with the hole would not be copied - the validator would encountered a bad sector error and even if that was overcome, the validator would not find the expected data on the copy.

A special combination of custom disk controller and accompanying software had come out a year earlier, and that system would copy every bit it could, even so-called "weak bits" (another copy protection method). With this subsystem, the disk sector with the hole would be marked as good - just like the original and would contain all of the expected data. However, that was still not enough because the validator would try to write to the position that had the hole and if it could write to it, that meant the hold was not there, and would conclude that the disk was an unauthorized copy.

You can probably see now that this copy protection method was very clever. The designer of it made a lot of money and signed up a lot of big companies. Pretty soon it seemed like everyone was using it. It looked like copy protection was finally here to stay (circa 1986).

Then someone released a little utility that overcame the validator. This utility hooked itself into the disk subsystem and intercepted ALL disk calls. It watched for calls by the software validator to the sector that normally had the laser hole. When those calls were made, the utility circumvented the protection by returning the expected results/errors without actually accessing the disk. Suddenly, the validator was the emperor who had no clothes.

Such a simple trick, yet so effective. The John Wayne approach to software piracy ("cut 'em off at the pass"). Hundreds of commercial products were compromised overnight.

One person. Reportedly, just one day of effort.

It is worth noting that the author of that utility had sat on it for a year before releasing it, to ensure his utility would have a wide appeal and that his revenue would be substantial. You can imagine how much he worred that someone else would beat him to market.

The copy-protection scheme's designer, and the companies who bought into it, suffered a big blow from having too much confidence in their validation method.

And the cleverness of the solution opened up a pandora's box for copy protection. The cat and mouse game has on a new level and the mouse had just "owned" the cat.

When it comes to protection, never think you have it all figured out.

Subject: A simple solution and testing
Posted by: JamesC (view profile)
Posted on: Thursday, March 24, 2011 at 3:19 AM
Message: @timothyawiseman@gmail.com. I agree, the context depends on the datatype being parsed. This approach I use is quite old and can be used with dynamic SQL in legacy programming languages (VB6/VBA etc).

I also agree on your point regarding parameterised queries. Within .NET the approach I have presented is unnecessary as .NET parameterised queries handle it. I should have read this article more carefully before commenting.

Great thread though! Lots of points of interested.

Subject: Multiple layers
Posted by: Rick (view profile)
Posted on: Tuesday, March 29, 2011 at 6:29 PM
Message: I recommend:
1. Watching for malicious querystrings and blacklisting.
2. Parse all input.
3. Use parameterized Stored Procedures

It isn't always possible to use or write stored procedures. Also, sometimes an SP can contain dynamic SQL. This is why all input should be checked prior to executing the SQL. We normally run a small routine to parse all input before using it.

On top of that, I have scripts that look for attacks and block the attacker. If a querystring enters my site with malicious code, the attacker gets blocked.

Subject: Database Permissions
Posted by: David73 (view profile)
Posted on: Saturday, April 16, 2011 at 11:18 AM
Message: I think the author makes an important point about assigning proper database permissions as a security measure. Too often, I see databases in which the main application user has db owner permissions to the database. There is little reason why an application user should need DDL permissions to a database. The principle of least access should apply to the accounts that access a database from an application. Go through and figure out exactly what level of access the application needs and then assign exactly that level of access. While this will not necessarily protect you from a determined SQL Injection attack, it will help limit the damage if an attack is successful.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.