I often hear people say that using stored procedures protects you against SQL injection attacks. This is incorrect. The vulnerability to SQL Injection comes from concatenating values into SQL Strings and then executing them, whether that is done in client side code or through the use of dynamic SQL.
For example, let’s say I need to retrieve all the Leads with a given surname. I could write a Stored Procedure for this as follows:
?and execute it:
Great, I have returned a list of Leads whose surname is Smith.
Now, suppose I am a hacker who wants to bring down your system. I execute your stored procedure like this:
This time I’ve returned all the table names in your system. It’s not going to take me long to work out which table contains your customers, or your users, or some other potential vital information. I can use this same trick to delete rows, drop tables, steal your customer base???anything my user account has permission to do.
Instead of concatenating the SQL string you should place the parameter directly in the query:
Now, when I try to execute your stored procedure using the hacker form, I only return a list of leads who’s surname is “;select * from sys.Tables;”. Unsurprisingly there aren’t any.
If you’re building the query in your client code, then use the appropriate parameter format. I’m a C# programmer, so I’m going to advocate ADO parameters. Whatever language you’re using will provide a similar mechanism. If it doesn’t, move to a different language!
Closing thought: This example was, admittedly, fairly unrealistic; you’d be unlikely to format this particular stored procedure using dynamic SQL. But as stored procedures, and the manner in which we want to use the parameters, become more complex, the temptation to fall back on some concatenated dynamic SQL will grow. Resist that temptation. Concatenation leads to injection, injection leads to breach, breach leads to a P45? and the Dark Side.
About the Author
Declan Hillier?has been developing business systems since 2001 and formed his own company, TopOut Software Ltd, in 2011. He doesn’t have a blog but probably should and promises to start one just as soon as he thinks of enough interesting things to write about.
