• Madeira Team

Dynamic Search Queries versus SQL Injection

Dynamic Search Queries

SQL Injection is a widely known issue with databases that work as back-ends for websites and applications. In the most part, protecting your website from SQL Injection is fairly easy and you can find a lot of resources on that around the internet. Usually, this issue is solved by using stored procedures with strongly-typed parameters, thus ensuring that non-numerical values won’t be entered instead of numerical values, and textual values won’t be broken by single quotes and such. 

But, what if “SQL Injection” is actually a part of your website’s normal intended logic? What I’m talking about is “Dynamic Search Queries”, or “Dynamic Search Conditions”, which is a popular method of building a dynamic SQL command based on a set of optional parameters, and then executing it using sp_executesql.

There are a lot of resources on the internet about how to implement this method, so I won’t go into detail about it here. However, I strongly recommend this article by Erland Sommarskog (SQL Server MVP) about dynamic search conditions. Specifically, read the part about “Dynamic SQL” and “sp_executesql”.

After you read that article, you’d know that in such scenarios you know which parameters to expect and which operands to use per each parameter. This method will be enough for most applications and websites.

But the scenario which I really want to talk about is where you actually don’t know in advance which parameters to expect, and you don’t know in advance the operand to use per each parameter.

TL;DR: Scroll to the bottom of this post for a downloadable script.

Dynamic Search Parameterization

For example, suppose our dynamic search procedure uses the column “CustomerName”. However, there’s only one parameter provided for this column (called @CustomerName) and it’s used with a “contains” kind of predicate:

Transact-SQL

CustomerName LIKE '%' + @CustomerName + '%'

1

CustomerName LIKE '%' + @CustomerName + '%'

But, what if I want to give the user the power to choose the kind of operand they want for this parameter? For example, “starts with”?:

Transact-SQL

CustomerName LIKE @CustomerName + '%'

1

CustomerName LIKE @CustomerName + '%'

Or, what if I want to let the user choose several different values to filter? For example:

Transact-SQL

CustomerName LIKE @CustomerNameStartsWith + '%' AND CustomerName LIKE '%' + @CustomerName_Contains_1 + '%' AND CustomerName LIKE '%' + @CustomerName_Contains_2 + '%'

1

2

3

CustomerName LIKE @CustomerNameStartsWith + '%'

AND CustomerName LIKE '%' + @CustomerName_Contains_1 + '%'

AND CustomerName LIKE '%' + @CustomerName_Contains_2 + '%'

Now that’s a little more complicated, isn’t it?

Sure, I could add several new parameters to the procedure for the different variations, but eventually it’ll become difficult to maintain, and there will always be a limit to what the user will be able to accomplish (without resorting to SQL Injection) – because there’s a limit to the number of parameters in a stored procedure.

Let’s take a look at another dynamic search example, suppose we have a column “Age” which is of a numerical type (e.g. int). What if instead of “minimum” and “maximum” values, I want to give the user the ability to provide a list of values for this column. For example:

Transact-SQL

Age IN (5, 8, 10, 20)

1

Age IN (5, 8, 10, 20)

I could use a single parameter for this column which I concatenate its value to the query. For example, the value of the parameter @AgeValues will literally be “5, 8, 10, 20” and I’ll concatenate it to the query like this:

Transact-SQL

SET @SQL = @SQL + N' AND Age IN (' + @AgeValues + N')'

1

SET @SQL = @SQL + N' AND Age IN (' + @AgeValues + N')'

But, this is obviously a major SQL Injection security gap.

Usually, from what I’ve seen, organizations implement such dynamic search solutions within the software code itself – dynamically constructing an Ad-Hoc query and executing it, instead of using a stored procedure with parameters. Sometimes they’d even implement a clever function to parse all the parameters and verify each of them based on its data type.

But all of these dynamic search solutions are either extremely difficult to maintain, or expose the system to dangerous SQL Injection attacks on some level.

The Solution: XML

After I was faced with exactly such a problem, I finally came up with the solution: XML.

First, you’d need two important “utility” tables in your database:

FilterColumns – You’ll need a table which will define, for your front-end application as well as for your back-end database, which columns can be filtered using the user interface. Each row in this table will represent a table column which can be filtered, including its data type, and a unique identifier (textual or numeric).

FilterOperands – You’ll also need a table with the available operand types that you can apply on any of the columns (starts with, greater than, equals to, contains, etc.). Each such an operand needs to provide you with information on how to build its syntax and integrate into it the relevant column name and the value used for the filter. Each operand will have a unique identifier (textual or numeric).

Next, you’ll need to design an acceptable “schema format” for your parameter values which you’ll pass on