|
|
Home » ASP » Article
Removing single quotes for SQL statements
|
| Article by: |  Jeff Anderson ( 1362 ) (3/28/2002) |
|
| Summary: | Explains how to overcome the database problem with single quotes when writing SQL statements |
|
| Viewed: 53124 times |
Rating (30 votes): |
|
3.6 out of 5 |
|
|
|
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,"'","'")
%>
| |
|
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.
|
|
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. |
|
ASP FileExists
by Jeff Anderson
An introduction to the FileExistsMethod of the FileSystemObject |
|
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). |
|
Add or Subtract Hours in SQL or ASP using DateAdd
by Jeff Anderson
A beginners guide to using the SQL DATEADD function to add or subtract hours. Particularly useful when setting the time displayed on the ASP page to a different time zone (eg when the server is in the US, and the site is for a UK audience). |
|
ASP CreateTextFile
by Jeff Anderson
An explanation of the CreateTextFile Method, part of the ASP FileSystemObject |
|
| |
