In the previous articles we’ve talked about SQL injection and dynamic SQL but we lacked an answer on how to prevent SQL injection attacks. Today we’ll do exactly that and show you one possible approach to how to do it. We’ll also mention other possible threats and approaches you could take.

Data Model

Nothing had changed in our model since the last article, so we’ll use the same model we’ve used so far.

In this article, we won’t focus so much on data, but rather on the code we could use to prevent SQL injection attacks. This code could be easily modified to fit your needs, or you could add some checks in case we missed some of them.

Non-SQL Injection related threats

I first want to talk about threats and measures you can take and that are not related only to SQL injection. Let’s list them down:

• The golden rule is to trust no one. That stands for your employees, potential attackers, and even your applications. Customers want that application does what they need, and they don’t expect it to be used in any unplanned manner and that often leads to quick solutions that don’t cover such cases. Databases are a “game” of large numbers. If you leave a chance for a certain event to happen, it will happen sooner or later. This could result in something you could easily ignore, but also with your data corrupted (exposed, deleted, etc.). So, trust no one, even yourself. Implement checks that will cover such cases, at least that you expect most often to happen
• Another common threat is not even technical. Keep your passwords safe, don’t place them where someone could easily access them and use them later for a potential attack
• There is no need that people outside, or even inside your organization, know all details about your organization. This stands for database, but also for anything else in your organization – either it’s data in paper format, safe/vault code, etc.
• This one is related to the previous one and stands for both – databases and generally. Establish a set of roles and permissions who can do what inside the organization, IT system, etc.

What can you do to prevent SQL Injection related attacks?

We talked about SQL injection in this article, so we won’t spend additional time explaining it here. Before moving to the heart of this article, I’ll mention IT/SQL-related measures and approaches you could use to prevent SQL injection attacks. I’ll list them starting with mostly SQL-related and ending with these that are “evergreen” in IT.

• Use parameterized queries, ORM, or stored procedures. This will not only provide you with implemented checks (either these provided by the system or either these you’ve implemented) but also hide the database structure from the potential attacker. The less info you expose about your database, the better
• Use roles and privileges to control what a certain user can do with your database. This way you’ll limit actions a potential attacker could make on your pages and forms
• Log statements and monitor to find rouge SQL statements
• Remove any old code you don’t use. If you don’t need it, it’s better to get rid of it than to leave it as a possible chance to be used in an undesired manner
• Update your software to ensure all the latest patches are applied to your system
• Use a firewall

Our approach shall be based on using SQL Server dynamic SQL, user-defined functions, and stored procedures. We’ll also test only values that are passed as textual values. We won’t implement checks to confirm if the input string is email or zip code value.

A solution how to prevent SQL attacks

In our solution, we’ll combine what we’ve already learned in this series and create a code that will serve as a backbone used to prevent SQL injection attacks. My main assumption is that stored procedures shall be used for every action, from simple insert or select statements to complex reports. This will ensure that the application passes only the names of stored procedures and parameters. That way the potential attacker doesn’t have details about our database structure, and we can also grant privileges to users enabling them to run only certain procedures. The main goal is to test the last thing here, and that are parameters passed to our procedure. In order to do that, we’ll create a function that performs the check, and show how it works in combination with a stored procedure meant to execute a simple SQL query (we’ll also show what can happen if we don’t test parameters).

Prevent SQL Injection – Example Function

First, we’ll create a function that tests the input string passed to the procedure. In this function, we’ll list all substrings we don’t want to be passed as parts of the parameter values. We should be careful here because we might want to use some of these values, so exclude the ones you expect. I went with special characters and reserved words here. The idea is that the function returns value 1 if everything is OK, and 0 otherwise. So, let’s look at the function definition:

Let’s now call the function with few values just to test it works exactly what we wanted.

You can see that function returned 1 exactly for these combinations where we haven’t used any “forbidden” strings, and 0 in other cases.

Prevent SQL Injection – Example Procedure

Now we’re ready to write a stored procedure that will be used to insert data into the customer table using the previously created function to test input parameters. I’ll test only textual parameters here because they are the ones where anything could be passed.

If all checks pass, we’ll perform an insert action, otherwise, we’ll throw an error. Let’s look at the procedure code now.

As you can see, we’ve tested parameters customer_name and customer_address inside the procedure. Let’s make 3 procedures calls now.

In the first 2 calls, at least one condition fails and therefore the insert statement hadn’t been fired. In the 3^rd call, everything was OK, and we’ve inserted a new row.

While this approach will work well, there are numerous changes you could do here:

• Select a different set of substrings/keywords you want to test in the function body
• You could only test for certain and slightly different versions of these values – e.g., we’ve used %drop%, and maybe drop % would work for you
• You could also test other values before performing actions. E.g. you could test if some integer value is in the set of expected values (foreign key is related to the primary key in the different table, a numerical value is in some interval…) and raise custom errors to closely describe what happened here

Conclusion

There is no silver bullet solution on how to prevent SQL injection attacks. Still, you could do a lot to protect yourself. In this article, we’ve used one approach you could go with, in combination with other security measures you could take. Still, have in mind that approaches could be very different, yet pretty similar (prepared statements, ORM). They don’t always depend on your desire on how to deal with possible SQL injection but on several other factors as well, e.g. the technology stack you’re using. Therefore, if you decide to go with the SP approach, you could use what was mentioned here as a backbone, and if you choose another approach, you could use the general idea.

Do share your experience with SQL injection and how you dealt with it in the comments.

Table of contents

• Author
• Recent Posts

Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer.

His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...

You can find him on LinkedIn

View all posts by Emil Drkusic

Latest posts by Emil Drkusic (see all)

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020