SQL Prepared Statements for complex filtering

Question

Ok, so I know you should use prepared statements to avoid injection attacks as well as for the huge speed improvements, but there is still one place that I find myself doing string manipulation to build my sql query.

Suppose I have a search feature that consists of six or seven or n dropdown lists. Based on whether there is a default value in each of those lists I want to append an item to my where clause. Is there a good way to do this with prepared statements where I can pass the parameters to a stored procedure and have it determine whether or not to include each particular where clause?

Answer 1

The server should optimize out queries such as where field like '%', so I would just prepare a statement with everything. This would probably also make your code a lot simpler.

Answer 2

No question like this would be complete without a link to this article. It goes over the use of Exec and sp_executesql, and discusses SQL Injection in depth, within the context of Dynamic SQL:

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Answer 3

Your best bet is parameterised SQL with sp_ExecuteSQL ... something like:

CREATE PROCEDURE usp_SampleTest
    @val1 INT
    @val2 INT
AS
    DECLARE @sql NVARCHAR(500)

    SET @sql = N'SELECT col1, col2, col3 FROM dbo.table WHERE 1 = 1'

    IF @val1 IS NOT NULL
       SET @sql = @sql + ' AND col3 = @pVal1'

    IF @val2 IS NOT NULL
       SET @sql = @sql + ' AND col4 = @pCol4'

    EXEC sp_ExecuteSQL @sql, '@pVal1 INT, @pVal2 INT', @val1, @val2

SQL Server will then cache execution plans the minimum number of execution plans with maximum flexibility in your SQL. Using sp_ExecuteSQL in this way as well means that it's secure from SQL injection attacks as well.

Answer 4

Have look at my answer to SO question SQL Stored Procedure: Conditional Return ... I think you could use the same type of thing.