Like a sentence and SQL injection

Question

I have doubts about this situation.

I have a request like this in a stored procedure:

SELECT column1, column2 FROM table1 WHERE column1 like '%' + @column1 + '%' 

My question is, is this vulnerable to SQL Injection? I need to change this to something like this: (?)

 declare @column1Like nvarchar(200); @column1Like = '%' + @column1 + '%' SELECT column1, column2 FROM table1 WHERE column1 like @column1Like 

Hi

Answer 1

The quick answer is no. To be vulnerable to SQL injection, you must use dynamic SQL execution.

This will be vulnerable:

 EXECUTE ('SELECT column1, column2 FROM table1 WHERE column1 like ' + @column1Like); 

It also means that between the two examples (from a security point of view, at least) there is no real difference.

Answer 2

 SELECT column1, column2 FROM table1 WHERE column1 like '%' + @column1 + '%' 

Since this query only works with variables, it has nowhere to put code in place of data and is therefore not vulnerable to SQL injection.

Of course, I assume that @column1 is a SQL Server variable here, and you use the parameterized query functions in your client language to bind the value to it.

Answer 3

As no different from = or any other predicate.

However, the user can add additional wildcard characters ( % , _ ) to the template; if that matters.

Answer 4

It is not vulnerable since it is already a string value in SQL space. Although, this may break the request.

Answer 5

Your query is not vulnerable to SQL injection in any case, because you are using a parameterized query.

Answer 6

I think it is vulnerable, for example: '%' or 1 = 1 - will show all database registers if you do not format it as @column1Like .

In this case, I think this is the same as ( @column1Like= '' or @column1Like is null ) but you should think about other examples like

 '%' union select SELECT `column11`, `column22` FROM table2 where `colum11` -- is the same type than `column1` --and `column22` is the same type than `column22`.