Like clause and sql injection

I am having a doubt about this situation.

I've a query like this within a Stored Procedure:

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

My question is, this is vulnerable to SQL Injection? Do I need开发者_如何学JAVA to change this to something like this: (?)

declare @column1Like nvarchar(200);

@column1Like = '%' + @column1 + '%'

SELECT column1, column2
FROM table1
WHERE column1 like @column1Like

Regards

The quick answer is no. To be vulnerable to SQL injection one must be using dynamic SQL execution.

This would be vulnerable:

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

That also means there is no real difference between both of your examples (from a security standpoint at least).

Like is no different from = or any other predicate.

However the user can inject additionnal wildcard characters (%, _) in the pattern; if that matters.

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

Since this query only works with variables, it has no place to put code instead of data and hence is not vulnerable to SQL injection.

I am of course assuming that @column1 here is an SQL Server variable and you are using parametrized query functionality in your client-side language to bind a value to it.

It is not vulnerable since it already is a string value in the SQL space. Although, it could break the query.

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

I think it is vulnerable, for example : '%' or 1=1-- will show all registers of the database if you don´t format it like @column1Like.

In this case, I think it´s the same than (@column1Like= '' or @column1Like is null) but you must think another 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`.