CODEWITHSUNDEEP
c#sql
Flethuseo
Flethuseo

Reputation: 6199

method to prepare string for a sql command

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

Upvotes: 0

Views: 2829

Answers (2)

Fenton
Fenton

Reputation: 251062

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.

Upvotes: 3

Fenton
Fenton

Reputation: 251062

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.

Upvotes: 9

Related Questions