How to stop SQL injection and prevent data compromises

While they're some of the easiest attacks to prevent, SQL injections are also some of the least protected against forms of attack. Learn how to stop them with this advice.

SQL injection is one of the most common attack vectors today, but these attacks are, also, some of the easiest to prevent.

This chapter from Securing SQL Server discusses what SQL injection attacks are and why they're so successful, then gives advice on how to stop SQL injection, how to detect any data compromises, and how to make sure your database is clean following an SQL injection attack.

Securing SQL Server
SQL Injection Attacks

Table of contents:
What is an SQL injection attack?
Why are SQL injection attacks so successful?

Download Chapter 6 of "Securing SQL Server" as a .pdf

 What is an SQL injection attack?
An SQL Injection Attack is probably the easiest attack to prevent, while being one of the least protected against forms of attack. The core of the attack is that an SQL command is appended to the back end of a form field in the web or application front end (usually through a website), with the intent of breaking the original SQL Script and then running the SQL script that was injected into the form field. This SQL injection most often happens when you have dynamically generated SQL within your front-end application. These attacks are most common with legacy Active Server Pages (ASP) and Hypertext Preprocessor (PHP) applications, but they are still a problem with ASP.NET web-based applications. The core reason behind an SQL Injection attack comes down to poor coding practices both within the front-end application and within the database stored procedures. Many developers have learned better development practices since ASP.NET was released, but SQL Injection is still a big problem between the number of legacy applications out there and newer applications built by developers who didn’t take SQL Injection seriously while building the application.

As an example, assume that the front-end web application creates a dynamic SQL Script that ends up executing an SQL Script similar to that shown in Example 6.1.

SELECT * FROM Orders WHERE OrderId=25
Example 6.1: A simple dynamic SQL statement as expected from the application.

This SQL Script is created when the customer goes to the sales order history portion of the company’s website. The value passed in as the OrderId is taken from the query string in the URL, so the query shown above is created when the customer goes to the URL http://www.yourcompany.com/orders/orderhistory.aspx?Id¼25. Within the .NET code, a simple string concatenation is done to put together the SQL Query. So any value that is put at the end of the query string is passed to the database at the end of the select statement. If the attacker were to change the query string to something like “/orderhistory.aspx?id¼25; delete from Orders,” then the query sent to the SQL Server will be a little more dangerous to run as shown in Example 6.2.

SELECT * FROM Orders WHERE ORderID=25; delete from Orders;
Example 6.2: A dynamic SQL String that has had a delete statement concatenated to the end of it.

The way the query in Example 6.2 works is that the SQL database is told via the semicolon “;” that the statement has ended and that there is another statement that should be run. The SQL Server then processes the next statement as instructed.

While the initial query is run as normal now, and without any error being generated but when you look at the Orders table, you won’t see any records in the Orders table because the second query in that batch will have executed against the database as well. Even if the attacker omits the value that the query is expecting, they can pass in “; delete from Orders;” and while the first query attempting to return the data from the Orders table will fail, the batch will continue moving on to the next statement, which will delete all the records in the Orders table.

Many people will inspect the text of the parameters looking for various key words in order to prevent these SQL Injection attacks. However, this only provides the most rudimentary protection as there are many, many ways to force these attacks to work. Some of these techniques include passing in binary data, having the SQL Server convert the binary data back to a text string, and then executing the string. This can be proven by running the T/SQL statement shown in Example 6.3.

DECLARE @v varchar(255)
SELECT @v = cast(0x73705F68656C706462 as varchar(255))
EXEC (@v)

Example 6.3: Code showing how a binary value can be used to hide a T/SQL statement.

When data is being accepted from a user, either a customer or an employee, one good way to ensure that the value won’t be used for an SQL Injection attack is to validate that the data being returned is of the expected data type. If a number is expected, the front-end application should ensure that there is in fact a number within the value. If a text string is expected, then ensure that the text string is of the correct length, and it does not contain any binary data within it. The front-end application should be able to validate all data being passed in from the user, either by informing the user of the problem and allowing the user to correct the issue, or by crashing gracefully in such a way that an error is returned and no commands are sent to the database or the file system. Just because users should be sending up valid data doesn’t mean that they are going to. If users could be trusted, most of this book wouldn’t be needed.

The same technique shown in Example 6.3 can be used to send update statements into the database, causing values to be places within the database that will cause undesirable side effects on the websites powered by the databases. This includes returning javascript to the client computers causing popups that show ads for other projects, using HTML iframes to cause malicious software to be downloaded, using HTML tags to redirect the browser session to another website, and so on.

SQL Injection attacks aren’t successful against only in-house applications. A number of third-party applications available for purchase are susceptible to these SQL Injection attacks. When purchasing third-party applications, it is often assumed that the product is a secure application that isn’t susceptible to the attack. Unfortunately, that isn’t the case, and any time a thirdparty application is brought into a company, it should be reviewed, with a full code review if possible, to ensure that the application is safe to deploy. When a company deploys a thirdparty application that is susceptible to attack and that application is successfully attacked, it is the company that deployed the application that will have to deal with the backlash for having an insecure application and their customer data compromised, not the company that produced and sold the insecure application.

Many people think that SQL Injection attacks are a problem unique to Microsoft SQL Server, and those people would be wrong. SQL Injection attacks can occur against Oracle, MySQL, DB2, Access, and so on. Any database that allows multiple statements to be run in the same connection is susceptible to an SQL Injection attack. Now some of the other database platforms have the ability to turn off this function, some by default and some via an optional setting. There are a number of tickets open in the Microsoft bug-tracking website http://connect.microsoft. com that are requesting that this ability be removed from a future version of the Microsoft SQL Server product. While doing so would make the Microsoft SQL Server product more secure, it would break a large number of applications, many of which are probably the ones that are susceptible to SQL Injection attacks.

Another technique that is easier to use against Microsoft SQL Server 7 and 2000 is to use the sp_makewebtask system stored procedure in the master database. If the attacker can figure out the name of the webserver, which can usually be done pretty easily by looking at the sysprocesses table, or the path to the website, then the sp_makewebtask procedure can be used to export lists of objects to HTML files on the web server to make it easier for the attacker to see what objects are in the database. Then they can simply browse to the website and see every table in the database.

exec master.dbo.sp_makewebtask '\\web1\wwwroot\tables.html", "select * from information_schema.tables"
Example 6.4: Code that an attacker could execute to export all table objects to an HTML file.

If xp_cmdshell is enabled on the server, then an attacker could use xp_cmdshell to do the same basic thing just by using Bulk Copy Protocol (BCP) instead of sp_makewebtask. The advantage to sp_makewebtask is that xp_cmdshell doesn’t need to be enabled, while the downside to sp_makewebtask is that it doesn’t exist on Microsoft SQL Server 2005 and up. The downside to xp_cmdshell is that, unless the application uses a login that is a member of the sysadmin fixed server role, the xp_cmdshell procedure will only have the rights that are granted by the proxy account. An attacker can use the xp_cmdshell procedure to send in the correct commands to give the account that is the proxy account more permissions, or even change the account to one that has the correct permissions. At this point BCP can be used to output whatever data is wanted. The attacker could start with database schema information, and then begin exporting your customer information, or they could use this information to change or delete the data from the database.

The catch to either of these techniques is that the NT File System (NTFS) permissions need to allow either the SQL Server account or the account that the xp_cmdshell proxy account uses to have network share and NTFS permissions to the web server. On smaller applications where the web server and the database server are running on the same machine, this is much, much easier as the SQL Server is probably running as the local system account that gives it rights to everything.

Why are SQL injection attacks so successful?
SQL Injection attacks are so successful for a few reasons, the most common of which is that many newer developers simply don’t know about the problem. With project timelines being so short, these junior developers don’t have the time to research the security implications of using dynamic SQL. These applications then get left in production for months or years, with little to no maintenance. These developers can then move through their career without anyone giving them the guidance needed to prevent these problems.

Now developers aren’t solely to blame for SQL Injection attack problems. The IT Management should have policies in place in order to ensure that newer developers that come in don’t have the ability to write dynamic inline SQL against the database engine. These policies should include rules like the following:

  1. All database interaction must be abstracted through stored procedures.
  2. No stored procedure should have dynamic SQL unless there is no other option.
  3. Applications should have no access to table or view objects unless required by dynamic SQL,which is allowed under rule #2.
  4. All database calls should be parameterized instead of being inline dynamic SQL.
  5. No user input should be trusted and thought of as safe; all user interactions are suspect.

With the introduction of Object Relational Mappings (ORM) such as Link to SQL and nHybernate, the SQL Injection problems are greatly lessened as properly done ORMcode will automatically parameterize the SQL queries. However, if the ORM calls stored procedures, and those stored procedures have dynamic SQL within them, the application is still susceptible to SQL Injection attacks.

For more information on preventing SQL injection, download the rest of Chapter 6: SQL Injection Attacks (.pdf).
Excerpted from Securing SQL Server by Denny Cherry (Syngress; 2011), with permission from Syngress.

Read more on Application security and coding requirements