Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Relational Databases
Expertise: Beginner
Oct 28, 1998

Single Quote Marks in a SQL Query

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=expr
end 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=expr
end function
You could also create a version that will save the expression out to HTML safe forms (double quotes become " while single quotes become ' ).
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date