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> |
[ Comment, Edit or Article Submission ]
