Insert string into SQL query as plain text in stored procedure

Question

I'm trying to write a stored procedure in SQL Server that checks if search input exists in a column.

SELECT * 
FROM Product 
WHERE @Type LIKE @SearchResult

My problem is that when I fetch @Type from user's input, it comes as a string in SQL therefore syntax is wrong and it returns NULL.

Is there a way to get rid off single quotations or convert it to plain text in SQL?

So if @Type is "ProductName" it would appear in SQL as

SELECT * 
FROM Product 
WHERE ProductName LIKE @SearchResult (no single quotes around `ProductName`)

This would require dynamic SQL, but would also be vulnerable to SQL injection. — SS_DBA
– SS_DBA, Commented Apr 20, 2020 at 23:08
That's what I want to avoid so I was wondering if there is a way around it in a stored procedure. — koydek
– koydek, Commented Apr 20, 2020 at 23:10

Gordon Linoff · Accepted Answer · 2020-04-20 23:12:19Z

1

You have to use dynamic SQL to replace anything other than a constant in a query:

DECLARE @sql NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Product WHERE  @Type LIKE @SearchResult';

SET @SQL = REPLACE(@SQL, '@Type', @Type);

exec sp_executesql @sql,
                   N'@SearchResult NVARCHAR(MAX)',
                   @SearchResult=@SearchResult;

You can still pass the @SearchResult value using a parameter.

answered Apr 20, 2020 at 23:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Antonín Lejsek Over a year ago

@koydek You can use quotename(@Type) to avoid sql injection if needed.

Collectives™ on Stack Overflow

Insert string into SQL query as plain text in stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related