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.

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