Escape SQL Parameter

Question

I need to work around an NHibernate bug I reported here. Essentially, NHibernate will generate multiple SQL parameters for the same HQL parameter, which results in problems for queries that use a parameter in a grouping construct.

Until the bug is fixed, I figured I'd just concatenate the parameter into the HQL. Obviously this is susceptible to SQL injection unless I escape the parameter value (since it's HQL, I can't use regular ADO.NET parameters).

Is there a method within System.Data somewhere that will escape a parameter value, making it safe to concatenate into a SQL string? I'm using SQL Server 2005, and I'm happy to do something specific to that platform for the short term until the NHibernate bug is fixed.

Thanks, Kent

Answer 1

To my knowledge there is nothing available for you to use (something similar to the Oracle DBMS_ASSERT library would work if it were available in Sql Server). One thing you could do that would protect you would be to simply check your parameter value (i.e. what you are going to concatenate) for any whitespace at all and throw an exception if it includes it - this should protect you against anything destructive in terms of injection. Naturally, this will only be a viable solution if you are concatenating a parameter that doesn't require the ability to find values that actually do contain whitespace, however I would think that would be a limited scenario.