Question:
I have built several pages that use VBScript to pull information from a Microsoft Access database. My problem is that if a user tries to use a single quote ‘ in the form supplied, SQL blows up because of an unterminated single quote mark in the query string. Is there a way to encapsulate the single quote?

Answer:
Single quotes are kind of a pain when trying to mix SQL with ASP. In general, the best way to set up your queries is to make sure that the string that you retrieve doesn’t contain a single quote (yeah, that does sound rather obvious, but in this case it disguises a layer of complexity). One way I’ve found around it is to create a SAFESTRING function. Before passing an expression up to the server through ADO or similar mechanism, I will use the VBScript Replace function to catch both single and double quotes, and replace them with placeholders:

 function MakeSafeString(expr)   expr=replace(expr,chr(34),"#DBLQUOTE#")   expr=replace(expr,"'","#SNGQUOTE#")   SaveString=exprend function

This will turn the expression:

This isn't the "only" solution

into

This is#SNGQUOTE#t the #DBLQUOTEonly#DBLQUOTE# solution

The ConvertSafeString() function takes a safe string pulled from a database query and converts it back into its previous form:

function ConvertSafeString(safeExpr)    expr=Replace(safeExpr,"#SNGQUOTE#","'")    expr=Replace(expr,"#DBLQUOTE#",chr(34))    ConvertSafeString=exprend function

You could also create a version that will save the expression out to HTML safe forms (double quotes become ” while single quotes become ‘ ).