How To Handle Special Characters With Dynamic SQL

If you use parameters with your SQL statements or stored procedures, these render any special characters as harmless because they are just passed as typed data to the database. If you cannot use typed parameters and you must use dynamic SQL, you need to handle any special characters that have meaning to the database. A blacklist approach where you reject specific characters is not recommended for the following reasons:

• The range of bad input normally exceeds the range of good input
• It's easier for you to know what is valid for your application that what is not
• It's easy to forget a bad character when attempting to construct a blacklist
• It's possibe to represent bad characters in many different ways by using different character encoding

Rather than use a blacklist you should perform validations against a whitelist - a list of what can be accepted.

If you need to accept a range of special characters that have meaning to the database (such as the apostrophe), then use escaping. With the escaping approach you prefix the special character with an escape character or character sequence which makes it harmless. Escaping ensures that the special character is treated as text and no longer has meaning within the database.

Here is an example of an escape routine that escapes a single apostrophe:

private string SafeSqlLiteral(string inputSQL) 
{ 
  return inputSQL.Replace("'", ""); 
}

Here is an example of how you would use it with a dynamic query:

strSQL = "SELECT * FROM t WHERE c = ‘" SafeSqlLiteral(Request.QueryString("input") + "’";