Creating a dynamic, user generated query without limit to parameters in SQL

Question

I have an asp.net page that allows a user to search the database by generating the where clause to include in a stored procedure. The issue is that the procedure has to allow for a unknown number of parameters for varying conditions. An overly simplified example could look something like:

SELECT [Column1] FROM [TableName] WHERE 1=1
--Everything below user generated
AND
(
   ([Column2] = '1' AND [Column3] = '5' AND [Column4] = '9') OR
   ([Column2] = '2' AND [Column3] = '6' AND [Column4] = '8') OR
   ...
   ([Column2] = '25' AND [Column3] = '3' AND [Column4] = '1')
)
AND [Column5] BETWEEN '10' AND '200'

I was wondering what the best solution for this would be. I know that I can build the where clause as a string and pass it to the stored procedure as one big parameter, and execute the lot as dynamic sql, but what would be a better solution?

If it is really that dynamic, wouldn't it be easier to do it just with dynamic SQL without a stored procedure? — James Z, Feb 22 '17 at 14:43
@JamesZ This portion of of the query is a part of a much larger pre-existing stored procedure which is referenced in several other places. — wheresmyducky, Feb 22 '17 at 14:47
Execute the proc into a table. Select from table and add the where clause in dynamic sql. — Snowlockk, Feb 22 '17 at 15:00
Are you aware of what a SQL Injection attack is? If your 'user generated' parts here are indeed provided by the user and not correctly sanitized, this is a massive security hole — Milney, Feb 22 '17 at 15:18
Take a look at this article which goes into great detail about how to handle these types of queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ — Sean Lange, Feb 22 '17 at 15:19
@Milney Which is exactly why I am asking here for thoughts. User input is being checked and cleaned where ever possible, but I still dislike the idea of building a single big string to use as a parameter. — wheresmyducky, Feb 22 '17 at 15:28
[The Curse and Blessings of Dynamic SQL](http://sommarskog.se/dynamic_sql.html) should give you plenty of ideas. It's not possible to distill all of that into one easy answer, though. — Jeroen Mostert, Feb 22 '17 at 15:52
@JeroenMostert and Snowlockk both good reads which led to the article on [Dynamic Search Conditions](http://sommarskog.se/dyn-search.html) thanks. — wheresmyducky, Feb 22 '17 at 16:30

score 0 · Accepted Answer · edited May 23 '17 at 12:00

In the first place a stored procedure IS an optimized/contextualized execution plan.

What you want is to have no limits to your operated columns, operators (=,LIKE,<,...) and number of search conditions(AND/OR). This is NOT reducible to an execution plan.

And even if you do have "light" constraint you would have to use a lot of parameters with search conditions like ([ColumnX] IS NULL OR [ColumnX] = @parameterX) AND .... That will cause performance issue due to parameter sniffing (more information here). And after optimization, you still wouldn't be faster than a simple Select constructed from your ASP application.

In fact using dynamic sql would be the best solution if you still want to use a stored procedure. But you may still encounter some performance isue you wouldn't have in a simple query (an example of performance problem).

My answer to your question here is forget about stored procedure and use query.

Creating a dynamic, user generated query without limit to parameters in SQL

1 Answers1