sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

A Look at SQL Injection

Issue 2 Number 8 August 2010

SQL Injection is a set of methods that hackers use to inject malicious SQL code snippets into your application code. Quite often the malicious code is injected into data entry forms on your web pages or directly into the urls that carry query parameter information to your application pages.

The coding practice that allows this to happen is called dynamic SQL. Dynamic SQL is the practice of assembling a SQL command by concatenating (joining) text strings together. For example, the first text string might contain static portions of a command: “SELECT * FROM tblUsers WHERE UserName = “.

This command text is concatenated with another text string containing the text entered by a user into a text box on your website:

  • “SELECT * FROM tblProduct
  • WHERE ProductName = “ + “user supplied text”

The expected input in this example is a product name, but a hacker has many different ways to insert actual SQL commands that will do things you do not intend. Here is a simple example of SQL injection that you can test in the Adventureworks sample database (downloadable from Microsoft if you don’t already have it).

Injection Example

Imagine that the AdventureWorks intranet site has a web page where employees can enter their social security number and view their salary history. The application code concatenates the text of a SQL command with the social security number input by the user and retrieves the user’s salary history.

However, if the employee does not enter his social security number but instead inserts text like: 1'' OR 1=1 -- , interesting things can happen. In this example the command created by the application would look like this:

  • select LoginID ,Rate ,RateChangeDate
  • from HumanResources.EmployeePayHistory eph
  • join HumanResources.Employee e on e.EmployeeID = eph.EmployeeID
  • Where NationalIDNumber = '1' OR 1=1 --'

By anticipating how the query is created internally and appending the always-true condition ‘OR 1=1’ instead of seeing only his/her own salary history, the malicious user has returned the salary history for every employee in the company.

SQL injection is not limited to this simple code. There are an endless number of malicious statements that can be successfully injected into a vulnerability like this. If you want to run this example for yourself, the necessary code is at the end of this article.

This example demonstrates an attack where user input is a textbox in the application or web user interface. Another way that hackers can inject harmful code is through the parameters sent in urls. For example:

http://www.AdventureWorkx.com/myaccount.asp?SSN=123345.

Attackers typically have a proxy program installed ahead of their browser. The proxy captures the request URL before it is sent and allows them to inject code into the SSN parameter in the same manner as in the previous example.

If you do not use dynamic SQL code in your application it is extremely unlikely that you will have SQL Injection problems, but eliminating dynamic SQL is far easier said than done. It may be possible in new applications you develop, but it is often impractical or impossible to do in large legacy applications.

There are many places in an application where there seems to no other choice than using dynamic SQL, however it is worth spending time trying to find other solutions before implementing dynamic SQL in places where you will be inserting user input into the command string.

Alternatives to Dynamic SQL

There are two principal alternatives to dynamic SQL. prepared statements and stored procedures. Both of these allow you to separate the command code from the parameters that are supplied to the command. In this programming model, user supplied text cannot be interpreted as a SQL command.

There are several different ways to prepare SQL. Here is the same example as above but using SP_ExecuteSQL. You can run this code in the AdventureWorks database if you would like to test it.

  • Execute SP_ExecuteSQL N'select LoginID ,Rate ,RateChangeDate
  • from HumanResources.EmployeePayHistory eph
  • join HumanResources.Employee e on e.EmployeeID = eph.EmployeeID
  • Where NationalIDNumber = @SSN'
  • , N'@SSN varchar(12)'
  • , @SSN = '998320692'

If a hacker attempted to inject malicious code into the SSN parameter it would be interpreted as a non-existent social security number and the query would return no records. As additional protection this method also limits the input to 9 characters.

Prepared SQL also has some performance advantages. SQL Server will compile an execution plan for this statement at the first execution. Subsequent executions with different parameters will most likely re-use the same execution plan and the statement will not have to be compiled again.

Stored procedures can be used in the same manner for separation of command code and parameters. Since it follows the same principle as the prepared SQL example we won’t provide another example.

Will Stored Procedures and Prepared SQL Keep Me Safe?

It depends on how you use them. Both sp_executeSQL and stored procedures will execute dynamic SQL statements if that is how you code them. Dynamic SQL containing user input still carries the same risk of SQL Injection no matter how it is executed.

This is a very important point. I have encountered many developers who thought they were safe simply because they were using stored procedures or prepared SQL. But when I inspect their code I sometimes find dynamic SQL strings within the stored procedures or executed directly by sp_executeSQL without parameterization. The danger here is the same as if they had not used stored procedures or prepared SQL.

What if I can’t eliminate all dynamic SQL?

Don’t feel alone. It is extremely difficult to develop a large, complex application without using some dynamic SQL. Legacy applications written in a less dangerous time are likely to be full of dynamic SQL. If you have to use dynamic SQL in cases where user input is concatenated with SQL commands, you are always going to have some degree of vulnerability to injection. But there are things you can do at many levels to minimize your risk.

The Principle of Least Privilege

SQL injection attacks, if they succeed, will connect to your application database with the permissions of the user account that your application uses to connect. Make sure that this account has the absolute minimum set of privileges necessary in the application database and is denied access to other databases on the server.

As an example, last year over a million websites were defaced by a SQL injection attack that planted the url of a malicious java script in every text column in the database. If visitors to the website clicked on the url, their systems were infected also. Shortly after the website was cleaned up, the attack would begin again.

At SQL Consuting, we were flooded with calls from desperate owners of sites that could not be quickly and easily protected. After examining the attack we found that the attack required access to the sysobjects and syscolumns databases in order to insert the url into every text column available. By default ordinary user accounts have access to these accounts.

We changed permissions on the application user account to deny access to those tables and the attacks stopped immediately. We couldn’t stop the penetration of the attacker on such short notice but we were able to prevent the attack from succeeding by eliminating unnecessary permissions.

That event was in fact the origin of this newsletter. We sent out a proactive warning to all our current and former clients while this epidemic was in progress. We explained the simple steps necessary to prevent the attack from succeeding. The response was overwhelmingly positive so we continued sending out a monthly SQL Server article.

Sanitize User Inputs

Your next line of defense for vulnerable code is to filter all user input to prevent malicious code from getting into your dynamic SQL. There are two common methods of filtering. Blacklisting compares user input to a list of forbidden words, characters, phrases etc. Whitelisting takes the opposite approach. It compares user input to allowed words, characters, phrases, etc. Either method must implement regular expression parsing specific to your requirements.

Whitelisting is generally considered the more secure method because hackers have found many ways to get around blacklists. They pass in forbidden strings as their hexadecimal equivalents; they use url encoding. They look for input processing that is not recursive.

For example, if your filter is stripping out SQL reserved words like DELETE, a hacker might send it as DELDELETEETE. On the first pass your process strips out the reserved word from the middle of this string and what remains is “DELETE”. Your list processing must repeat until no forbidden input is found.

You can apply other sorts of filters to specific points of user input. For example a zipcode field should be limited in length to the length of a zip code. It should only accept numeric characters, etc.

Sanitizing inputs is a big, complex subject that we do not have the space to cover in detail. There are references at the bottom of this article that will have much more detail.

Other Defenses

Escape User Input

SQL Server has a way of marking or “escaping” input characters that have special meaning to SQL Server (such as the single quote character). If not handled, special characters such as the single quote character in the input can be utilized to cause SQL injection.

Avoid Informative Error Messages

Don’t tell attackers what they are doing wrong. They rely on causing errors to probe the application’s response. Often an attacker will enter a single quote into a text box input. If the single quote is concatenated into a dynamic SQL command, it will generate a syntax error.

If you send back the error to the user it will tell the attacker that his input is being directly inserted in a SQL command and that he has found a vulnerability. Knowing this, he will probe the vulnerability with more sophisticated methods.

Conclusion

Don’t believe that you are too small or too anonymous to become a target. Professional and amateur hackers have automated programs that constantly scan blocks of IP addresses looking for injection vulnerabilities. These programs don’t know or care if you are Chase Manhattan or Joe’s BBQ.

And do not underestimate the ingenuity and persistance of a hacker who is trying to gain control of your database. SQL Injection is no longer the domain of renegade geeks.

It is big business and criminal elements all over the world are putting time and money into stealing valuable data. Even unsophisticated young hackers (“script kiddies”) can download attacks scripted by very very skilled hackers.

Useful Links for Further Research

Open Web Application Security Project is the first place to stop for SQL injection information. Here are two SQL injection specific pages to start with.

http://www.owasp.org/index.php/SQL_Injection
http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

If you really want to get deep into SQL injection defenses, Justin Clarke’s book is a good place to start. Justin is currently a security consultant, but it seems obvious from his writing that he may have honed his craft from the other side of the aisle. This book was a real eye-opener for me to all the very sophisticated ways that attackers can penetrate your defenses.

http://www.amazon.com/Injection-Attacks-Defense-Justin-Clarke/dp/1597494240

Example Code

  • USE AdventureWorks;
  • declare @SQL varchar(512)
  • declare @UserInput varchar(512)
  • declare @CmdString varchar(1024)
  • -- this is the hardcoded part of the application’s command string
  • SET @SQL = 'select LoginID
  • ,Rate
  • ,RateChangeDate
  • from HumanResources.EmployeePayHistory eph
  • join HumanResources.Employee e on e.EmployeeID = eph.EmployeeID
  • Where NationalIDNumber = '''
  • /* Test normal and malicious user entry by uncommenting
  • -- one of these statements at a time*/
  • -- user enters his own SS number and views his salary history
  • --Set @UserInput = '509647174'
  • /* malicious user injects malicious content
  • , changing the query to return salary history for all employees*/
  • Set @UserInput = '1'' OR 1=1 --' -- Employee views all records
  • /*this is the step that allows SQL injection,
  • the concatenation of user input into a command string.
  • The single quote characters are required in this
  • case for correct command syntax */
  • Set @CmdString = @SQL + @UserInput + ''''
  • /* uncomment command below if you want to see the command text
  • that is sent to SQL Server*/
  • -- select @CmdString
  • execute (@CmdString)

Return to Top

Go to SQL Consulting Home

On this page:

Defending Against SQL Injection

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator