1

I need to build search conditions to be used with WHERE clause. This search condition is then passed to a different application to be executed as a part of SQL query. Because there search conditions can be quite complex (including sub-queries) I don't believe receiving application can intelligently parse them to prevent SQL injection attacks.

Best practices state that parametrized queries should be used. That works fine when you use command object to execute the query yourself. In my case I wish to obtain that query string with parameters merged into it, and parse out where search clause I am interested in. Is there a way to do this?

I work with MS SQL Server and currently simply replace all single quotes with two single quotes in string I receive from a caller. Is there a better way to achieve some level of protection from SQL injection attacks?

LeffeBrune
  • 3,441
  • 1
  • 23
  • 36
  • 1
    If the string you get already has the parameters merged into it, how can you "replace all single quotes with two single quotes"? Won't that break stuff like `WHERE name = 'KOTMATPOCKUH'`? – Heinzi Dec 06 '09 at 15:35
  • @Heinzi I am to build the WHERE, so I am given the search value KOTMATPOCKUH. However it may also contain a malicious code. Using parameters would've been ideal, but it doesn't look like I can get .NET to do the work for me and return me the query. – LeffeBrune Dec 06 '09 at 15:41
  • @KOTMATPOCKUH: Thanks, I understand now. .net won't be able to return the "merged" string, because parameterized queries are resolved on the SQL Server side (i.e. .net sends the string plus the parameters in separate data fields). – Heinzi Dec 06 '09 at 16:38

3 Answers3

2

Have a look at these 2 links

Does this code prevent SQL injection?

and

Proving SQL Injection

Community
  • 1
  • 1
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • Informative read, but unfortunately points me into direction of replacing/stripping illegal characters from the user input, as parameterization is not possible in my case. – LeffeBrune Dec 06 '09 at 15:56
  • I am sorry if I misunderstood, but I thought that was your intention? – Adriaan Stander Dec 06 '09 at 15:58
  • 1
    I was looking for a better than "replacing quotes" way of protecting against injection attacks. Or a proof that replacing quotes will suffice. One of the answers for the second thread in your reply seems to indicate that handling quotes should be good enough. I will however, apply strict rules to my input strings, because I will know what possible value a valid string might contain. Thanks! – LeffeBrune Dec 06 '09 at 17:33
1

some guidelines from OWASP

ram
  • 11,468
  • 16
  • 63
  • 89
1

I think you are fine: According to the SQL Server Books Online, a solitary single quote seems to be the only way to exit a quoted string that was started with a single quote. Thus, replacing ' with '' should suffice to avoid SQL injection through string variables.

I cannot think of any way to inject SQL through other, non-string native C# data types, if they are properly (locale-invariant) converted to strings.

Nevertheless, parameterized queries are the "recommended" solution. At the moment, your application seems to be organized like this:

  1. Part A creates a WHERE statement based on user input.
  2. A string containing this WHERE statement is passed to Part B.
  3. Part B adds SELECT etc. and sends it to SQL Server.

Would it be an option to rewrite your application like this?

  1. Part A creates a parameterized WHERE statement plus a set of parameters based on user input.
  2. A string containing the WHERE statement plus a Hashtable (or something similar) containing the parameters is passed to Part B.
  3. Part B creates a command, adds SELECT etc., adds the parameters and sends it to SQL Server.

I was in a similar situation and solved it by creating a SubSQL class, which basically contains a parameterized string with the CommandText and a hash table with the parameters. You could then use this as mySubSQL.CommandText += ..., mySubSQL.Parameters("@myfield") = myValue and mySubSQL.MergeInto(myCommand) (the implementation should be obvious and straight-forward).

Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • Rewrite the application to use parameterized queries is not an option unfortunately. My .Net application has to feed queries into SugarCRM's SOAP API (written in PHP). The best I can do so far is simply be very strict with what can be passed into my code. For example if I am looking for user by email address using SOAP API I must make sure the string passed to me by caller is in fact an email address, if I am looking for a product SKU make sure it's alphanumeric etc. Thanks for the help though! – LeffeBrune Dec 06 '09 at 17:16