Stored Procedures Do Not Protect from SQL Injection Attacks

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS