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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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