|
|
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: 49161 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 .
 
| |
