3

I have a simple Stored Procedure:

create procedure [dbo].[test]
@str varchar (100)
AS
BEGIN
select * from [AdventureWorks2012].[HumanResources].[Employee] where JobTitle like '%' + @str +'%'
END
GO

Given this type of code, in what ways can I exploit it?

I've always been told that exploits of the sort of

exec test ''' ''; truncate table [AdventureWorks2012].[HumanResources].[Employee];--'

Should work, but now is the 1st time I got a chance to try it practically and it does not work.

The explanation I was given was that

''' ''; truncate table [AdventureWorks2012].[HumanResources].[Employee];--'

When concatenated with the query becomes

select * from [AdventureWorks2012].[HumanResources].[Employee] where JobTitle like '%' + ' ' ; truncate table [AdventureWorks2012].[HumanResources].[Employee];-- + '%'

So, the SELECT query will execute with the jobtitle searching for any character followed by a space. The ; terminates the where condition and then the truncate table statement executes. Where am I going wrong? I'm using Microsoft SQL server 2012.

3 Answers 3

6

where JobTitle like '%' + @str +'%'

This is not an SQL-injection vulnerability. It isn't injecting content into the query from a higher level of control such as a string EXEC or a call from C#.

There is an injection error here, however it is not SQL-injection. The concatenation that is being done is a search term into a LIKE query pattern. Metacharacters in LIKE patterns are %, _ and, in SQL Server, [ (non-ANSI-standard).

The upshot of this is that if anyone does a search for, say, 50%, they will get results that contain the substring 50 and not only ones that contain 50%. Use of [ can also probably result in a syntax error. That's undesirable, but it doesn't allow any of the traditional security attacks that SQL injections do.

To fix LIKE-injections, you need to nominate a custom LIKE-escape character, and use it to replace any LIKE-metacharacters, eg:

SET @liketerm = REPLACE(REPLACE(REPLACE(REPLACE(@str, '!', '!!'), '%', '!%'), '_', '!_'), '[', '![');

... WHERE jobtitle LIKE '%'+@liketerm+'%' ESCAPE '!';
3

Just to clarify @bobince's answer (accept his answer, not mine!), this is not a SQL injection concern because the statement is not dynamically built using your @str variable -- instead it is passed to a static statement.

If the procedure instead read:

BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'select * from [AdventureWorks2012].[HumanResources].[Employee] where JobTitle like ''%' + @str + '%''';
    EXEC @sql;
END

...you would be open to an injection using terms similar to those which you typed above. Typically, these sorts of constructs tend to happen in the application layer, such as a PHP or ASP app (not singling those out):

// NON-LANGUAGE-SPECIFIC PSEUDO CODE
var searchTerm = Request.Form("searchField");
var sql "select * from [AdventureWorks2012].[HumanResources].[Employee] where JobTitle like '%" & searchTerm & "%'";
var results = db.execute(sql);

One common place you do see such code is in multi-field search procedures, where the user can supply a number of values and each value is compared against a different field or requires a different join. The SQL is then built to only add those WHERE or JOIN clauses if and when a term is given (which can provide a more performant query). You should take considerable care to properly sanitise your input if you need to do this in your app.

0

The inputs that you are giving will not work.

You will need to close the '%' character first and then throw in your query. Your end query should be :

where JobTitle like '% str%';query to append -- '

Hope this helps.

1
  • 2
    AFAIK, the % operator does not need to be in pairs. like '%abc' should work without any issue, and it does
    – Akash
    Dec 6, 2012 at 6:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .