SQL Server Dynamic SQL with LIKE clause and SQL Injection

Question

I'm having a dynamically built SQL as below. My question is, is it vulnerable to SQL Injection? If yes, how should I fix it?

  --search title only    
 if @SearchType =2    
 BEGIN     
  SET @strSQL = @strSQL + 'AND  (IDownload.FileTitle LIKE ''%'+@Search+'%''  ) '    
END

Yes, and it's fixable by using proper parameterisation for your dynamic SQL (e.g. using sp_executesql with @search as a parameter). — ZLK
– ZLK, Commented Jun 23, 2017 at 3:43
@ZLK what if my server side code (c#) is already using proper parameterisation & prepared statement, is my SQL Server code still vulnerable? — Aaron Tan
– Aaron Tan, Commented Jun 23, 2017 at 6:39
Yes, it's still vulnerable, because of the way the current statement is written. — ZLK
– ZLK, Commented Jun 25, 2017 at 10:32

Backs · Accepted Answer · 2017-06-23 03:50:00Z

1

Yes, check:

CREATE TABLE Test (Id int)
GO

CREATE TABLE IDownload (FileTitle nvarchar(100))

DECLARE @strSQL nvarchar(max)
DECLARE @Search nvarchar(max) = 'a'') DROP TABLE Test --'

SET @strSQL = 'SELECT 1 FROM IDownload WHERE 1 = 1 '

SET @strSQL = @strSQL + 'AND  (IDownload.FileTitle LIKE ''%'+@Search+'%''  ) '    

PRINT @strSQL
EXEC sp_executesql @strSQL

DROP TABLE IDownload

I can drop table Test passing special string in @Search. Rewrite your code using sp_executesql procedure and parameters.

answered Jun 23, 2017 at 3:50

Backs

25k5 gold badges66 silver badges92 bronze badges

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

1 Comment

Aaron Tan Over a year ago

But how should I rephrase the like statement to something which is SQL Injection proof? I've been looking over the internet and all the example seems to be similar as the one you have posted. I'm already using sp_executesql and paramdefinition in my code.

Collectives™ on Stack Overflow

SQL Server Dynamic SQL with LIKE clause and SQL Injection

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