How do I deal with an apostrophe (') in a SQL statement?

Easily one of the most frequently-asked questions in ASP and database-related forums. 
 
The apostrophe is an illegal character in T-SQL because it is interpreted as a string delimiter. To allow a ' mark to be inserted into a database, simply double-up all occurences of the ' mark: 
 

<%      criteria = Replace(criteria,"'","''")  %>

 
So to generate SQL queries: 
 

<%      mycrit = Replace(mycrit,"'","''")      Response.Write("INSERT table VALUES('" & mycrit & "')<p>")      Response.Write("SELECT column FROM table WHERE column LIKE '%" & mycrit & "%'<p>")  %>

 
In JScript, you could use the Replace() method also, however it behaves differently than in VBScript. Each call to .Replace() only affects the *first* instance it comes across. You can use RegExp to remind JScript to replace globally: 
 

<script language=jscript runat=server>      var myCrit = "bob's bait and tackle";      var q = /\'/g; // regexp apostrophe, global      myCrit = myCrit.replace(q, "''");      Response.Write("INSERT table VALUES('" + myCrit + "')<P>");      Response.Write("SELECT column FROM table WHERE column LIKE '%" + myCrit + "%'<P>");  </script>