Single Quote Handling in Dynamic SQL Stored Procedure

Question

I'm using a query like this:

ALTER procedure [dbo].[procedure]
@Search nvarchar(100) = ''
declare @LargeComplexQuery nvarchar(max) =
'select * from People where Name like ''%' + @Search + '% order by Name'
exec sys.sp_executesql @LargeComplexQuery

@Search is populated by a program. If the program returns a string containing a single quote the stored procedure errors, how can I handle this?

If possible, I'd like this to be handled by the stored procedure, rather than the program passing in the string.

Try replacing single quote with two single quotes inside the "@Search" string — Praveen, Apr 21 '17 at 08:03
Single quotes are escaped by doubling them up, just as you've shown us in your example. `DECLARE @my_table TABLE ( [value] VARCHAR(200) ) INSERT INTO @my_table VALUES ('hi, my name''s tim.') SELECT * FROM @my_table` [link](http://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) credits — Abel Masila, Apr 21 '17 at 08:04
Or do it properly without string concatenation - `sp_executesql` can take parameters — Bridge, Apr 21 '17 at 08:20

score 4 · Answer 1 · answered Apr 21 '17 at 08:10

Either escape the quote in the application before passing the parameter, or do it in the proc:

declare @Search nvarchar(100) = ''
declare @Search2 nvarchar(100)
declare @LargeComplexQuery nvarchar(max)

set @Search2 = replace(@Search, '''', '''''')

set @LargeComplexQuery = 'select * from People where Name like ''%' + @Search2 + '%''' -- I escaped the quote here too
exec sys.sp_executesql @LargeComplexQuery

score 3 · Accepted Answer · answered Apr 21 '17 at 08:29

You should escape the quotes after recovering the value.
And also COALESCE the parameter in case a NULL is passed to avoid the following error

EXEC sp_executesql NULL

Which would give

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Which gives the following statements

DECLARE @Search nvarchar(100) = '';
SET @Search = REPLACE(COALESCE(@Search, ''), '''', '''''');
SET @LargeComplexQuery = 'SELECT * FROM People WHERE Name LIKE ''%' + @Search + '%'''
EXEC sys.sp_executesql @LargeComplexQuery

Bridge · Answer 3 · 2017-04-21T08:58:49.560

Here as a version that uses sp_executesql parameters and so is not vulnerable to SQL injection - it should also provide better performance, to quote MSDN:

Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

CREATE PROCEDURE [dbo].[Yourproc]
(
  @Search NVARCHAR(100) = N''
)
AS
    DECLARE @LargeComplexQuery NVARCHAR(MAX) = 'SELECT * from People WHERE Name LIKE ''%'' + COALESCE(@Search, '''') + ''%'' ORDER BY Name'
    EXEC sys.sp_executesql @LargeComplexQuery, N'@Search NVARCHAR(100)', @Search = @Search

I've made some assumptions, such as if you pass empty string or NULL as a search condition then you get all people returned.

Testing it out - dummy schema & data:

CREATE TABLE People(Name NVARCHAR(MAX))

INSERT INTO People(Name)
VALUES ('Mr Smith'), ('Mrs Jones'), ('Miss O'' Jones')

Testing stored proc execution:

DECLARE @search NVARCHAR(100) = N'Jones';
EXEC YourProc @Search; --Should get back both Mrs Jones and Miss O'Jones
SET @search  = N'O'' Jones';
EXEC YourProc @Search; --Should get back just Miss O'Jones 
SET @search  = N'';
EXEC YourProc @Search; --Should get everyone, same as if you passed no search value at all
SET @search = NULL
EXEC YourProc @Search; --Should get everyone

MSDN Documentation on sp_executesql

Single Quote Handling in Dynamic SQL Stored Procedure

3 Answers3