Single Quotes in Form Input Lead to SQL Errors
Remember that in INSERT or MODIFY SQL statements where the field in the database is a text field, the value in the SQL statement must be surrounded by single quotes. The value in this case is being supplied by the user's input to a form. If that input itself has a single quote in it, the resulting SQL statement syntax won't be correct.
There are two solutions to this problem:
1) Instruct the user on your web page to enter two single quotes instead of one. (Try it -- it works!), or
2) Put code in your page that searches for a single quote in the user's input and replaces it with a double quote. I create a function called fixquote(), and then whenever I am using dealing with form input that could contain single quotes, I call that function so that all instances of a single quote are replace by two single quotes. I tried to use the JavaScript replace() method to work, but couldn't get it to work, so instead did used the split() method for strings and its inverse, join, for arrays.
Revised: October 20, 2002. Comments to William Pegram, wpegram@nvcc.edufunction fixquote(x) {
// this function replaces all instances of a single quote with two single quotes
x += ""; // convert to string if not already
a = x.split("'"); // split at the single quote, with parts going into array
return a.join("''"); // rejoin with two single quotes instead of one
}
sep = "', '" ;
str = "UPDATE students SET first='" + fixquote(Request.QueryString("first")) + "', last='"
str += fixquote(Request.QueryString("last")) + "', state='" + fixquote(Request.QueryString("state"))
str += "' WHERE sid=" + Request.QueryString("sid");