Sanitize search string for Dynamic SQL Queries

Question

I know i can use stored procedures to eliminate sql injection attacks but it would bloat the code by more than I'm willing to accept and making it costly to maintain.

In my dynamic sql query, I would like to search a string of text in 2 columns in one of my tables but before that happens, I would like my business layer, which is written in c#, to sanitize sanitize the input. I would like the input to have special characters (ie: #,!, $, etc.) What is the minimal character set that i have to strip out in my search string to sanitize it? I'm thinking that stripping out single and double quotes is sufficient. Is that correct?

Thanks

Answer 1

Also semicolons to stop subsequent statements begin defined (necessary, but not sufficient)

Answer 2

You're approaching this from an unsafe direction. You want to define the set of characters that should be allowed (checking that they're not special, etc), and then strip everything not in that set.

You should probably also look into SqlCommands as a safer way to build the string sent to the DB.

Answer 3

You don't need to use stored procedures to be safe. (As a matter a fact, stored procedures don't necessarily guarantee safety against injection attacks if the stored procedures themselves construct dynamic queries.) And manual escaping is difficult to do 100% safely, and not recommended.

Instead, use parameterized queries, which nearly all databases support.

Answer 4

If you use stored procedures or parameterized statements, you shouldn't need to sanitize anything, unless you are building strings blindly in dynamic SQL within the procedure. If that is the case, please read Erland's excellent article on dynamic SQL:

http://sommarskog.se/dynamic%5Fsql.html