Stored Procedures Do Not Protect from SQL Injection Attacks

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:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes