Am I vulnerable to SQL injection?
I am making an SSRS report with the below stored procedure. My users want a search tool that will return all possible results in the name field so that the name "AD" should return "ADAM" and "MADELYN".
I am worried that since I am using string concatenation for the where clause, is it possible that this stored procedure might fall victim to an SQL injection attack:
BEGIN
@location varchar(20),@name varchar(20)
SELECT location, name
FROM table
WHERE (location LIKE @location+'%') AND (name LIKE '%'+@name+'%')
END
Is this code vulnerable? And if so, how can I fix this to be safe?
source to share
The code you posted is not vulnerable to SQL injection. The string value concatenation that you use in a query is fine only when you create a query by concatenating strings together, which is where you are vulnerable.
For code that is in T-SQL, this means that if you don't use either EXEC
or sp_executeSQL
, you are unlikely to be vulnerable.
An example equivalent to your code and vulnerable to SQL injection :
BEGIN --Don't do this!
@location varchar(20),@name varchar(20)
sp_executesql('
SELECT location, name
FROM table
WHERE (location LIKE ' + @location + '%'') AND (name LIKE ''%' + @name + '%''')
END
source to share
This piece of code is at least fine. Yes, you are using string concatenation, but the concatenation happens at runtime after the query is compiled. The execution plan is already defined, and the result of the concatenation itself is used as a value; never like code. There is no extra character '
or anything else that could cause malicious elements of that line to leak and be interpreted as sql code.
String concatenation is an issue for the SQL string with the user data when it comes to the time of compilation ... or client code level or at the server before exec()
, or sp_executesql()
can be interpreted as a code or the like, because the result of the concatenation.
Of course, there may be other things in this procedure that we have not seen that still have problems.
source to share