how to deal with single and double quotes in inserts

[topr8]

scenario: vbscript asp

page 1
fill in form > submit form to page 2

page 2
grab form values, set them to variables , build a sql UPDATE statement and update the database, but i'm having problems when the user fils in a form field and uses single quotes,

eg yadda yadda yadda' yadda etc etc

this mucks up the sql statement, now this is presumably quite a common problem with a well known solution, except the solution isn't well known to me... any ideas? thnx

[f00sion]

any field that is accepting text inputs should be checked for the single quote, on certain systems a sql error will display code including but not limited to the server name, table names, etc..all things that could potentially be exploited. I do this to bypass the problem:

var1 = replace(request.form("var1"),"'","''")
var2 = replace(request.form("var2"),"'","''")
etc
.
.
.

that will replace all occurences of ' with '' in the string and your sql statements will work fine.

[topr8]

ok thanks, i'd been doing it by replace with "'"

your method is better of course.

is there any way of putting all the request.form variables into some kind of collection so that i can run them through a For each loop or similiar?

[f00sion]

you can go through the collection.. im actually not sure if those variables are read only though..it would be easy to do a quick check

for each item in request.form
request.form(item) = replace(request.form(item),"'","''")
next

[topr8]

thanks ...

[mattglet]

one of the first functions i ever made with vbscript... Stringify

function Stringify( value )
Stringify = "'" & replace(value, "'", "''") & "'"
end function

that's my old glory right there... use it.

-Matt

[aspdaddy]

Stringify = "'" &

That is a great tip, prefixing all strings with an empty string prevents a lot of errors when dealing with possible null values