As a follow on to yesterday’s editorial, "SQL Injection is Still Alive", I want to provide some quick tips on how to protect against SQL Injection.
The first thing you need to do is get rid of code that builds SQL Statements by concatenating commands with user input. The reason was demonstrated yesterday. If your source engine is SQL Server, there is an added benefit;. When using a dynamic SQL Statement, SQL Server caches the query plan. If your dynamic SQL Statement does not have any parameters, then nearly every execution is unique, and your dynamic plan cache is quickly filled up. The net result is slower performance.
How do you replace dynamic SQL Statements? I’m going to describe one method using the Microsoft stack.
When using ADO.Net, you create an SQL Statement in a Command object. The CommandText property of the Command instance may contain text, the name of a stored procedure, or the name of a table. The Command Type will be set to one of those three.
A stored procedure may have zero or more parameters. In order to set the parameters of a stored procedure, the command object contains a Parameters collection. You populate the Parameters collection with as many parameters as are required by the stored procedure (you can ignroe optional parameters if desired), setting each parameter to the necessary value. For this reason, stored procedures were historically preferred, because they disallow SQL Injection. They are often not used because, by default, they are not flexible when having different filtering criteria, or sorting requirements.
With ADO.Net you have the option of dynamically creating a query, and also using parameters. When you build your query, instead of concatenating your data, you create the query with parameter names in the query text. Then, you use the same technique for creating and populating the Parameters collection in the Command object. Parameters work the same in text based SQL statements and stored procedures. Now you have both worlds. Parameters provide protection. Text base SQL allows flexibility for filtering and ordering criteria. Moreover, since the CommandText does not include the parameter data, it is not included in the dynamic query plan. Thus, you greatly reduce the number of dynamic query plans in SQL Server cache, because parameterized SQL plans can be re-used.
Now you have the best of both worlds. Moreover, your query text can now be defined as a constant in your code, because it never changes. All that changes are the parameters you assign to the Command object Parameters collection. ADO.Net passes the SQL statement and the parameters to SQL Server, and SQL Server puts everything together more efficiently, without opening your application up for SQL Injection. Text is now just text. There is nothing hackers can do to change it. Everything they do is simply data now, and cannot be turned into SQL Code in the executing query.
If this sounds like a lot of work, you can always use an ORM tool. All of the popular ORM tools calling SQL Server implement parameterized queries on your behalf.
MondayI’ll take a quick look at ways you can use a stored procedure and still have dynamic sorting.
What are the practices you use to protect against SQL Injection? Share your experience with us with your comments. Perhaps you have some techniques you use with Java libraries, or calling other data engines. That would be highly valuable.
Cheers,
Ben