CODEWITHSUNDEEP
sql-serversql-injectionparameterized-query
user3419063
user3419063

Reputation: 15

Trying to escape a sp_executesql variable

I've run in to a problem with a software I'm configuring. I do not have access to the source code, only the config.

The issue is as follows, in the configuration the software expects me to enter a string, but I would like the string to jump out of the compare and instead execute a funtion.

Using sql profiler I get something like this:

exec sp_executesql N'SELECT * FROM dummyTable WHERE (Name LIKE @Pattern)',N'

This does not work in my setup, because pattern is not clearly defind in advanced. I need to take the variable passed as pattern and run it trough a sql function but I can't figure out how. Typically Pattern contains a single char, in my example "1". I've tried altering the Pattern to use an escape char and run my function on it, but I think I'm missing someting (If this is at all possible). The variable I've send from config is as follows:

{0}' or Name like dbo.RunCalulation({0})

Giving me the following:

'…@Pattern nvarchar(43)',@Pattern=N'1'' or Name like dbo.RunCalulation(1) '

This executes, but does not give any response, so I think the esacpe char does not work, and it compares the whole string to Name.

I'm real stuck at this, hope someone has a good idea what to do (I know that not having the source code is a real problem here.

Upvotes: 1

Views: 852

Answers (1)

stakx - no longer contributing
stakx - no longer contributing

Reputation: 84735

One of the huge advantages of query parameters (such as @Pattern) is that they help protect against SQL injection (which is what you are trying to do).

So the answer is that you cannot do what you want. There's no way to escape the @Pattern parameter and add some of your own SQL to that query, because everything you pass as @Pattern will be interpreted as data, and never as SQL command text (which is the reason why your SQL text ends up inside the single quotes, and why your quote is automatically escaped to ''.).

Upvotes: 1

Related Questions