Search Wiki:

How to search using all or partial columns without Dynamic SQL

Jonathan Kehayias, April 1, 2008

A common item in most database applications is a search page where end users
can search based on various fields in the database or perhaps across all of the
fields. Often this problem is resolved with the use of Dynamic SQL. This however,
is dangerous and will leave the application open to SQL Injection if unchecked inputs
are concatenated into the SQL string. It is best instead to use a parameterized
statement to handle the search functionalities.

This example will be based on a single table dbo.WebContacts which is the storage
point for website visitor information that has been entered through an online form.
The example assumes the following table structure and sample data:

Create Sample Data
-- Suppress data loading messages
-- Create Sample Table
CREATE TABLE dbo.WebContacts
	ContactID int identity primary key,
	FirstName varchar(30), 
	LastName varchar(30), 
	Address varchar(100), 
	City varchar(50), 
	State char(2), 
	Zip varchar(10), 
	Phone varchar(12), 
	Email varchar(50)
-- Load Sample Data
INSERT INTO dbo.WebContacts 
VALUES ('Donald', 'Duck', '123 Main St.', 'Disney Land', 'FL', 
		'32247', '123-456-7890', '')
INSERT INTO dbo.WebContacts 
VALUES ('Daisy', 'Duck', '123 Main St.', 'Disney Land', 'FL', 
		'32247', '222-456-7890', '')
INSERT INTO dbo.WebContacts 
VALUES ('Uncle', 'Scrooge', '123 Main St.', 'Disney Land', 'FL', 
		'32247', '407-456-7890', '')
INSERT INTO dbo.WebContacts 
VALUES ('Mickey', 'Mouse', '123 Main St.', 'Disney Land', 'FL', 
		'32247', '407-456-7890', '')
Return to Top

The above table can be searched from a user search form by creating a procedure that has a
parameter for each specific column that is to be searched, and a single generic search parameter
to allow a single value to be searched in all columns.

Search Procedure
CREATE PROCEDURE [dbo].[usp_SearchWebContacts] 
	@FirstName varchar(30) = null, 
	@LastName varchar(30) = null, 
	@Address varchar(100) = null, 
	@City varchar(50) = null, 
	@State char(2) = null, 
	@Zip varchar(10) = null, 
	@Phone varchar(12) = null, 
	@Email varchar(50) = null,
	@Generic varchar(100) = null
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SELECT	ContactID, FirstName, LastName, Address, City, State, Zip, Phone, Email
FROM WebContacts
WHERE ((@FirstName IS NULL OR FirstName LIKE '%'+@FirstName+'%')
		  AND (@LastName IS NULL OR LastName LIKE '%'+@LastName+'%')
		  AND (@Address IS NULL OR Address LIKE '%'+@Address+'%')
		  AND (@City IS NULL OR City LIKE '%'+@City+'%')
		  AND (@State IS NULL OR State LIKE '%'+@State+'%')
		  AND (@Zip IS NULL OR Zip LIKE '%'+@Zip+'%')
		  AND (@Phone IS NULL OR Phone LIKE '%'+@Phone+'%')
		  AND (@Email IS NULL OR Email LIKE '%'+@Email+'%'))
AND	(@Generic IS NULL
		  OR	(FirstName LIKE '%'+@Generic+'%' 
		  OR	LastName LIKE '%'+@Generic+'%'
		  OR	Address LIKE '%'+@Generic+'%'
		  OR	City LIKE '%'+@Generic+'%'
		  OR	State LIKE '%'+@Generic+'%'
		  OR	Zip LIKE '%'+@Generic+'%'
		  OR	Phone LIKE '%'+@Generic+'%'
		  OR	 Email LIKE '%'+@Generic+'%'))

Return to Top

The above code has two blocks of logic in the where clause, one for specific column searches, and
one for generic searching of all columns. It is important to always evaluate for the parameter having
a NULL value as a part of its search block. Since each parameter is assigned a default NULL value
in the procedure, you only have to specify those parameters that match the specific column(s) to be
searched. Multiple parameters may be used to restrict your results further.

Using the Procedure
execute usp_SearchWebContacts @LastName = 'Duck'
ContactID   FirstName    LastName    Address         City            State   Zip        Phone        Email
----------- ------------ ----------- --------------- --------------- ------- ---------- ------------ --------------------------
1           Donald       Duck        123 Main St.    Disney Land     FL      32247      123-456-7890
2           Daisy        Duck        123 Main St.    Disney Land     FL      32247      222-456-7890
Return to Top

Page Created By: Jonathan Kehayias, Apr 1, 2008
Last edited Apr 28 2008 at 1:59 AM  by ArnieRowland, version 5
zuomin wrote  Apr 17 2008 at 11:47 AM  
As far as I know, this option won't get a good execution plan as dynamic sql does.

If you use sp_executessql to call your dynamic sql, then it will take care of the data type check.

sytelus wrote  Apr 21 2008 at 12:28 AM  
The front "%" is a huge performance sucker so such kind of LIKE should not be used for "partial" searches. Also this code has big red SQL Injection issue. It will never pass code review in our team :)

JonathanKehayias wrote  Apr 24 2008 at 10:50 PM  

While this may not provide the same execution plan that a dynamic query would, it doesn't carry the SQL Injection Risks that dynamic code does. None of the parameters are concatenated into an executed statement, so there is not chance of executing code passed into any of the parameters. They are only used for data matching so if you pass a command string like:

OR 1=1; exec sp_msforeachtable 'DROP TABLE ?';

nothing happens because this procedure will concatenate a wildcard % on both ends and compare this as a string to the values in the column of the table. I can almost guarantee that there is no contact in my database with a firstname LIKE the above code injection string.

I understand that you can also use a ParamsDefinition with sp_executesql preventing SQL Injection by passing the parameters as parameters to the execution, but you also require that the executing user have select permissions on the underlying table when you execute dynamic strings, which is not allowed in all environments. With this solution, the user doesn't need table level access, they only need execute on this procedure.

I have a procedure much like this one, but using eight more columns, that returns results in three to five seconds for almost any search you execute on a table with 1.8 million rows in it.

greenleaves wrote  May 1 2008 at 1:05 AM  
I need to write a stored procedure similar to this except for there are multiple generic search words that need to be matched with all the columns. I have these generic search words in a temp table. But I am stuck with how to get the functionality of @Generic for each of these words. Any help regarding this is greatly appreciated.

Page view tracker