0

Update: See also this preexisting question, and its answers, of which this question is effectively a duplicate.

I'm using a 3rd-party API that takes a WHERE condition fragment of a SQL Statement

e.g. ThirdPartyFunction(where:"Category = 'abc'", top:5)

I have strings passed through a UI or web service and need to prevent against SQL injection attack.

Without the use of parameterized queries, and without an alternate method in the .NET framework to escape SQL strings (that I know of), I expect to manually escape the SQL string.

I have ideas about the best way to write an escape method but am looking for THE most secure solution.

Community
  • 1
  • 1
John K
  • 28,441
  • 31
  • 139
  • 229
  • 2
    The 3rd party API doesn't already do the cleansing? If they are using something like NHibernate which supports its own SQL-like query language (NQL) then you may not need to do any cleansing of your own. – Daniel Renshaw Apr 25 '11 at 17:50
  • See [Avoiding SQL injection without parameters](http://stackoverflow.com/questions/910465/avoiding-sql-injection-without-parameters) and [Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?](http://stackoverflow.com/q/139199/513811) – Martin Smith Apr 25 '11 at 17:53
  • @Martin - Yep, there's the answer too. I didn't find it when searching before posting. – John K Apr 25 '11 at 18:06
  • 1
    I've voted to close this question as a dupe, in favour of Martin's link which I've also posted to my question. Please vote to close this question with the same link. Thanks. Just to keep things tidy. – John K Apr 25 '11 at 18:08
  • Possible duplicate of [Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?](https://stackoverflow.com/questions/139199/can-i-protect-against-sql-injection-by-escaping-single-quote-and-surrounding-use) – aknosis Aug 23 '17 at 21:24

1 Answers1

1

Parameterized query would definitely be the safest, however you could do a replace on any single quotes, with two single quotes. So if the user tries to enter malicious query within the 'abc' portion, it would handle it as a string.

What third party library are you using? Have you checked to ensure that they do not provide ability to parameterize your calls to their methods also?

mservidio
  • 12,817
  • 9
  • 58
  • 84
  • I've sifted through the third party library and might post a separate question for it, instead of naming it here. In general I'd like a definitive answer to this generic question because the scenario can crop up in other programming situation. – John K Apr 25 '11 at 17:54
  • @John K: I'd say the definitive answer is to see if the library itself already does the checks. If it doesn't then the only secure answer is to dump the library as it likely has many other issues – NotMe Apr 25 '11 at 18:00
  • Good call Chris - to inspect the actual SQL calls. Dumping such a library is ideal from a development perspective, however when tools are selected by the customer I need to use them and find a programmatic solution to some of these oddities. There might be other kinds of methods, safer options, in the API that have the same end result. – John K Apr 25 '11 at 18:05