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.

Solution 1

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

Solution 2

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

Solution 3

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