Escape SQL strings when Parameterized Queries cannot be used?

Question

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.

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
– Daniel Renshaw, Commented Apr 25, 2011 at 17:50
See Avoiding SQL injection without parameters and Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes? — Martin Smith
– Martin Smith, Commented Apr 25, 2011 at 17:53
@Martin - Yep, there's the answer too. I didn't find it when searching before posting. — John K
– John K, Commented Apr 25, 2011 at 18:06
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
– John K, Commented Apr 25, 2011 at 18:08
Possible duplicate of Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes? — aknosis
– aknosis, Commented Aug 23, 2017 at 21:24

mservidio · Accepted Answer · 2011-04-25 17:48:30Z

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?

answered Apr 25, 2011 at 17:48

mservidio

13.1k9 gold badges61 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John K Over a year ago

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.

ChrisLively Over a year ago

@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

John K Over a year ago

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.

Collectives™ on Stack Overflow

Escape SQL strings when Parameterized Queries cannot be used?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related