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?

3 Answers
3

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:

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.

...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):

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.