Using an Apostrophe in a Variable in MSSQL
A user created an account in my web application which had an apostrophe in their e-mail address. It was similar to First_D’Second@website.com. I didn’t think that an apostrophe was a valid character for an e-mail so I didn’t have any code to prevent it from crashing the app when it tried to run. The original code was:
string sql = "SELECT * FROM Users WHERE Email = '" + email + "'";
You can prevent an error by escaping the apostrophe in the email variable, i.e. replacing a single apostrophe with a double one. This can be done in C# with the .Replace function:
string sql = "SELECT * FROM Users WHERE Email = '" + email.Replace ("'", "''") + "'";
The sql string can then be executed without crashing the application.