method to prepare string for a sql command

Question

I need to create a string method that takes in a string and escapes it so that it can be used in a database SQL query, for example:

"This is john's dog" ==> "This is john''s dog"
"This is a 'quoted' string" ==> "This is a ''quoted'' string"

I want my method to look something like this:

string PrepareForSQLCommand(string text)
{
    ...
}

Anyway, I don't know all of the characters that need to be escaped in SQL query. I am not sure what the best approach is to do this, or if there is some existing robust built-in stuff to do this in C#.

Apologies for not mentioning this earlier: I DO NOT HAVE THE OPTION TO USE PARAMETRIZED QUERIES.

Ted

Don't prepare - do the right thing - use parametrized queries right from the start. Don't try to escape your string - you'll forget something - for sure - just don't do it - use parametrized queries and all your worries go away! — marc_s
– marc_s, Commented Jan 30, 2013 at 22:21
Have you ever heard about parameters? No need to escape anything. It's db engine job's. — Steve
– Steve, Commented Jan 30, 2013 at 22:22
You should not have to do this if all your input parameters use bind parameters. — OldProgrammer
– OldProgrammer, Commented Jan 30, 2013 at 22:22
I don't know all of the characters that need to be escaped in SQL query. Basically nobody does. Especially considering each DB will have a slightly different set of possible vulnerabilities, many of which aren't nearly as straightforward as a simple find/replace. That's why such functions just don't exist; anywhere. — Servy
– Servy, Commented Jan 30, 2013 at 22:26

Fenton · Accepted Answer · 2013-01-30 22:29:10Z

9

The usual way to do this would be to use a parametrised query as part of a SqlCommand.

SqlCommand command = new SqlCommand("SELECT * FROM MyTable WHERE MyCol = @param", connection);
command.Parameters.AddWithValue("@param", "This is john's dog");

The framework then ensures safety for you, which is less error-prone than trying to work out all of the possible injection attacks for yourself.

edited Jan 30, 2013 at 22:29

answered Jan 30, 2013 at 22:23

Fenton

253k74 gold badges404 silver badges414 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brian Over a year ago

haha, I just about to comment that you should be using command.Parameters.AddWithValue("@param", "This is John's dog");. + 1 for beating me to the punch.

Clockwork-Muse Over a year ago

Err, you may want to make it more clear in your example that this results in a 'constant' value - that is, parameterized queries don't usually allow tokens and related items (ie SELECT, column names, table names, ORDER BY, etc) to be changed. This also has to do with optimization possibilities.

Fenton · Accepted Answer · 2013-01-30 23:13:48Z

Trigger Warning. This answer is in response to the following statement:

I do not have the option to use parametrized queries.

Please do not up-vote this answer and please don't accept this as the correct way of doing things. I don't know why the OP cannot use parametrized queries, so I am answering that specific question and not recommending this is how you should do this. If you are not the OP, please read the other answer I have given. Also, please bear in mind the above constraint before down-voting. Thanks.

End of trigger warning!

For Microsoft SQL Server (the answer is different depending on the server) you will need to escape the single quote characters.

But before you escape these characters, you should reject any character not on your white-list. This is because there are lots of very clever tricks out there and white-list validation is more secure than simply escaping characters you know are bad.

Regex whiteList = new Regex("[^'a-zA-Z0-9 -]");
query = whiteList.Replace(query, "");

For example, this would remove [ and ] characters, and ';' characters. You may need to adjust the regex to match your expectations as this is a very restrictive white-list - but you know what kind of data you are expecting to see in your application.

I hope this helps. Feel free to check out the OWASP website for more details on security and if you can find a way of using parametrized queries you'll sleep all the better for it.

Collectives™ on Stack Overflow

method to prepare string for a sql command

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related