By

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.

Leave a Reply

Your email address will not be published. Required fields are marked *