This is a frequently asked question on the various sql server
groups. You should escape all single quotes by doubling them.
You can do this in the scripting engine that comes with IIS4
with field = replace(field,"'","''").
This is critically important, you should do this to _all_ varchar and
text fields before constructing the sql statement. Otherwise it's a
severe security hole. People can submit a fields that contain
arbitrary SQL commands to execute on your servers. For instance if
your query looked like:
"SELECT foo WHERE f = '"&request("foo")&"'"
someone could submit a field like "foo' DELETE WHERE f = 'foo"
which when substituted would form:
SELECT foo WHERE f = 'foo' DELETE WHERE f = 'foo'
which is obviously not what you intended.
Hm, maybe I should send this to bugtraq, it's undoubtedly an extremely
widespread problem.
greg