Currently I learning the asp.net in c#, I found that if I allow users to input some value in Textbox field, the (') simbol will cause the sql statement throw error
the good practices to avoid this kind of error is to block the symbol using javascript like "event.keyCode!=222" or replace it by other symbol?
If you are using an ORM (such as entity framework) the (') symbol should not be an issue. In any case you should handle special characters on the server side (C#) and not the client side (JS) , otherwise your code will be vulnerable to SQL injection and other nasty pests.
It's not only the ' character, there can be more. Check this Which characters are actually capable of causing SQL injection in mysql for better understanding.
And you should do the filtering on server side, do not rely on client side JavaScript as that can be skipped.
Better option is to use a stored procedure, and pass the value submitted by user through a parameter, instead of dynamically constructing SQL strings. That could reduce the risk of SQL injection.
If you want to prevent SQL injection by escaping query string, either use methods which convert apostrophes and special symbols into plain text on server-side, or use parameterized queries with stored procedures.
You can perform Regex.Replace to escape all apostrophes and double minus (comment sign) to plain query string on server side similar to this:
String yourEscapePattern = "[escaped patterns]";
String replacementString = "[replacement here]";
Regex reg = new Regex(yourEscapePattern);
String query = Regex.Replace(input, replacementString);
Parameterized query example:
using (var command = new SqlCommand("[stored procedure name]", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Parameter1", SqlDbType).Value = Value1;
// other parameters here
command.ExecuteNonQuery(); // or ExecuteReader
}
you can use
HttpUtility.UrlEncode("some Text'(");
befor saving your text to datebase
