codetoad.com
  ASP Shopping CartForum & BBS
  - all for $20 from CodeToad Plus!
  
  Home || ASP | ASP.Net | C++/C# | DHTML | HTML | Java | Javascript | Perl | VB | XML || CodeToad Plus! || Forums || RAM 
Search Site:



Home » ASP » Article

Removing single quotes for SQL statements

Article by:  Jeff Anderson  ( 1362 ) (3/28/2002)
Bookmark us now! Add to Favourites
Email a friend!Tell a friend
Summary: Explains how to overcome the database problem with single quotes when writing SQL statements
Viewed: 49161 times Rating (30 votes): 
 3.6 out of 5
 Rate this Article  Read Comments  Post Comments


Removing single quotes for SQL statements



One of the most common problems in submitting web forms to a database using SQL is the dreaded single quote. The database reads single quotes as part of the syntax of the SQL query and invariable produces an error.

It is therefore more than wise when dealing with any textbox or textarea fields entered by a user to overcome this difficulty by simply replacing the single quote. Here's the basic syntax :

<%
form_name=request.form("form_name")
form_name=replace(form_name,"'","&#39;")
%>



We've taken the user-entered 'form_name' field and replaced all single quotes with the ASCII equivalent code.

This is the best substitution to use if the form entry is likely to be displayed on a web page again in standard HTML text. If it is going to be printed out, or displayed in an editable form field, you might be better replacing single quotes with double quotes, or with the ` character, found on the top left of most keyboard. If formatting is important to you however, and you don't like this backwards slanting option, you can always re-replace the ASCII value with a single quote when it comes to re-displaying the data later on.





CodeToad Experts

Can't find the answer?
Our Site experts are answering questions for free in the CodeToad forums
Rate this article:     Poor Excellent
View highlighted Comments
User Comments on 'Removing single quotes for SQL statements'
Posted by :  Archive Import (luc) at 14:01 on Friday, October 18, 2002
i had some problems with this method.
it's better not to change the variables themselves but to change a single quote into a double quote at the moment one defines an SQL-query

example:

sql = sql & "AND fieldname= " & replace(variable,"'","''")

more details on:

http://www.devguru.com/features/knowledge_base/A100206.html
Posted by :  Archive Import (luc) at 14:02 on Friday, October 18, 2002
sorry, i meant change a single quote into two (2) single quotes (and not a double quote)
Posted by :  Archive Import (tony) at 19:58 on Thursday, April 03, 2003
But by replacing the single quote into double quote, wouldn't that look incorrect when displaying.

I'll look like:

I"m my father"s Son.
Posted by :  Archive Import (tony again) at 20:00 on Thursday, April 03, 2003
Jeff's method will display the single quote as a single quote if posted back into a website. Jeff I'm going to test it tonight... but it makes sence just by looking at it.
Posted by :  Archive Import (john) at 21:51 on Sunday, April 20, 2003
jeffs way works...although you shouldnt need the first variable definition; just the second one
Posted by :  Archive Import (Marj) at 21:53 on Friday, May 16, 2003
I think John is wrong. I found Jeffs way worked exactly as written. For those of us who are neophites, I add that I needed to change my strSQL = strSQL & "'" & Request.form("form_name") & "')
to
strSQL = strSQL & "'" & (form_name) & "')


To post comments you need to become a member. If you are already a member, please log in .

 



RELATED ARTICLES
ASP FilesystemObject
by Jeff Anderson
An introduction to the Filesystemobject
ASP GetTempName
by Jeff Anderson
Use the GetTempName method to create a randomly generated temporary file on the server.
ASP OpenTextFile
by Jeff Anderson
An introduction to the OpenTextFile Method of the FileSystemObject
ASP Format Date and Time Script
by Jeff Anderson
An ASP script showing the variety of date and time formats possible using the FormatDateTime Function.
Email validation using Regular Expression
by Jeff Anderson
Using regular expression syntax is an exellent way to thoroughly validate an email. It's possible in ASP.
Creating a Dynamic Reports using ASP and Excel
by Jeff Anderson
A simple way to generate Excel reports from a database using Excel.
Concatenate strings in sql
by Jeff Anderson
A brief introduction to concatenating strings in an sql query (using SQL server or access databases).
ASP FileExists
by Jeff Anderson
An introduction to the FileExistsMethod of the FileSystemObject
Creating an SQL Trigger
by Jeff Anderson
A beginners guide to creating a Trigger in SQL Server
Create an ASP SQL Stored Procedure
by Jeff Anderson
A beginners guide to setting up a stored procedure in SQL server and calling it from an ASP page.








Recent Forum Threads
• Regular Expression Query Replace -- Twice Wrongly
• Processing MS Office documents with Java
• Re: help!
• Re: Can javascript preload swf files?
• ADODB.Recordset error (0x800A0CB3)
• Need Your Inputs About Level Editor Interface
• `section` is an unexpected token. Expecting white space. Line 1, position 137.
• Javascript rollover toggle help please!
• help monitoting cpu load on windows machine


Recent Articles
ASP GetTempName
Decode and Encode UTF-8
ASP GetFile
ASP FolderExists
ASP FileExists
ASP OpenTextFile
ASP FilesystemObject
ASP CreateFolder
ASP CreateTextFile
Javascript Get Selected Text


© Copyright codetoad.com 2001-2009