T-SQL Smarts -- Preventing Injection Attacks

Working with dynamic SQL is all well and good, but avoid the risks that can lead to your code being susceptible to a SQL injection attack.

• By Joshua Jones
• 05/03/2010

As much as making your T-SQL work smarter for you, a la dynamic SQL, is helpful, it can also introduce a significant about of risk. I'm speaking specifically about SQL injection attacks. Not sure what a SQL injection attack is? Read on.

SQL injection is a method of finding unvalidated, unchecked data inputs in an application and placing, or injecting, T-SQL into those inputs. For example, imagine a Web application that asks the user for a city name in order to validate inventory. When the user enters a city, the application may generate a SQL command similar to this:

var sql = "SELECT [inventoryinfo] FROM inventory WHERE region = '" + TextInput + "'";

So, the ASP/VB is dynamically generating a SQL query via string concatenation, and is filling out the WHERE clause using the input from the form. For a "regular" query where the user entered the city Colorado Springs, the resulting SQL query would simply be:

SELECT [inventoryinfo] FROM inventory WHERE region = 'Colorado Springs'

However, this code is extremely vulnerable, because someone whose intention is to hack the database might try to enter a piece of T-SQL in the field to see what it returns. For example, if they enter:

Colorado Springs'; DROP TABLE inventory--

The command that will be generated and executed on the server would be:

SELECT [inventoryinfo] FROM inventory WHERE region = 'Colorado Springs'; DROP TABLE inventory --

Since the semicolon is a batch separator, anything entered after it will be executed (as long as it is syntactically correct). The double dash serves to comment out anything else on that line, to prevent SQL Server from throwing an error because of commands being run in the same batch that can't be. Obviously, DROP TABLE wouldn't work unless the Web site's credentials have that permission, but you can see where this could lead.

So, how do we stop this from being a problem? It can't be detected on the fly, so we have to make sure the app/SQL is never vulnerable in the first place. All applications should validate their input, and most applications should avoid dynamic SQL when possible. This isn't always easy to achieve, but trying to find non-dynamic solutions first will help minimize those situations where dynamic SQL is even present. Here are a few tips on minimizing injection attacks:

• Never build a dynamic string directly out of user input. For example, if you are expecting a city name, validate it against a list of valid entries in your database.
• Enforce data types. Make sure that any input you receive is valid, i.e. if you expect a phone number, make sure you have numbers and not alpha characters. Not only does this stop injection attacks, but can help prevent buffer overruns.
• Use stored procedures. Instead of executing T-SQL directly from an application, build a stored procedure to do the work, and call the stored procedure from the application. This does increase development time, but almost always prevents SQL injection problems (and helps with performance).
• In multi-layered environments, always validate the data between EACH layer or tier. Never assume that data has been validated at the front end; validate it at the middle tier, and on the database server.

If you follow these rules, you should be able to keep your system secure while making your T-SQL work for you. Have fun!