How to escape a string for a SQL query when you really CAN'T use parameters

Question

I've seen this question more than a few times obviously but haven't seen a good example of when parameterized queries truly aren't an option…but I think I have one.

I'm working with the Cisco Call Manager AXL API. Its backend is an Informix DB. Usually and whenever possible, I use the provided SOAP methods to get results, which since I'm using a WSDL-created interface class and passing parameters in actual object properties this takes care of any escaping necessary via the SOAP libraries.

However:

There are a few things I have to use direct SQL calls against the DB for, and the API provides a method where you can pass in an SQL query (as a string) and get back rows of results. Unfortunately this method doesn't provide any facility for parameterized queries. So, yes I am actually required to do my own escaping.

Well then, of course I could make my own regex, but A: I could easily miss something, and B: Really? There's not a utility class for this? Can I somehow use the SQL parameterization engine to spit back the escaped query? Obviously I know you have to deal with ', but I've read about the backspace-character injection method and I'm sure there are others that I don't yet know about…surely someone else has already written a pretty secure version?

Scope:

• I'm interested in solutions that use off-the-shelf libraries, preferably a built-in one.
• If I have to write my own, I can use the examples in the link above and elsewhere, but I really don't want to write my own, so lets try and refrain from telling me how to do that.
• No, I can't connect directly to the Informix DB and use an Informix driver with parameterized query support. That would be a good answer, but it's ruled out in this scenario.

Karlynkarma

The backspace example you linked is about hiding a query from humans, not the database (that does not mean there aren't ways to trick the database, I guess that depends on the database itself). – Entrance

@Entrance Quite right, looking again. I can't find the original SO question where I learned about this, but the idea I think was to pass a value that started with a backspace, e.g. \bNULL; DROP DATABASE; -- injected into "SELECT * FROM table WHERE uuid = '" + sQuoteEscapedVal + "'" would result in SELECT * FROM table WHERE uuid = NULL; DROP DATABASE; --'…serious air-coding there. – Ogletree

Answer 1

You may be able to get away with using the EscapeSequence class from Microsoft.SqlServer.Management.SqlParser.dll if the MsSql escaping is close enough to what your database back end uses.

You can find more information about it here. http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.parser.escapesequence.aspx

Cupboard

Answer 2

The ADO.NET abstraction does not process quotes, it simply passes them to the underlying provider. So if there is an off-the-shelf library, it would be an Informix DB-specific one, but I doubt you'll find one for .NET since everyone's happy with ADO.NET or an even higher abstraction.

Even PHP, known for its mysql_real_escape_string function does not seem to have an equivalent for Informix DB.

Tempted to try to help solve your problem, given your scope that's all I can say.

Entrance