Solved

Dealing with single quote marks in input boxes

Posted on 2003-11-25
8
1,699 Views
Last Modified: 2013-12-24
How do I deal with single quotes in input boxes?  When the form is submitted, it causes errors in the SQL statement.  What do I need to do to avoid that?
0
Comment
Question by:theamzngq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9820037
either replace them prior to submission using js, or during the submission process using rereplace

in js

in your form validation

document.form.textbox.value.replace(/\'/g,'"');


or in the coldfusion processing

#rereplace(#form.textbox#,"'","#chr(34)#","all")#

the above will replace single quote with double quote
0
 
LVL 6

Expert Comment

by:PE_CF_DEV
ID: 9820137
ColdFusion normally does this for you unless you are using a function already..so if you have

insert stuff
('#coldfusionfunction(form.textbox)#',...)

 it won't make it SQL safe you can do something like Jester suggested, I would recomend doing
#rereplace(#form.textbox#,"'","''","all")# (2 single quotes that escaped the ' in SQL like ## output # in coldfusion), or you can do this:
<cfset variablename=coldfusionfunctions(form.textbox)>
insert stuff
('#variablename#',...)

0
 
LVL 10

Accepted Solution

by:
Mause earned 125 total points
ID: 9820609
You can use #preservesinglequotes(yourvar)# to escape a single quote mark

But I think its better to use
<cfqueryPARAM value = "#yourvar#"  CFSQLType = "CF_SQL_CHAR">
for escaping your single quote mark and other trobleshooting characters

for more information see:
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm

hope this helps
Mause
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 2

Author Comment

by:theamzngq
ID: 9820925
wow, thanks.  Some of the tags/functions you all have mentioned are unfamiliar to me: rereplace, coldfusionfunction, preservesinglequotes

What are your (all) opinions about the answers that the others have given?  I've read several articles that stress the use of CFQUERYPARAM, would that be the best approach since it seems to be the 'proper' way of coding INSERTs?  Will that tag escape other special characters in addition to the single quote?
0
 
LVL 2

Author Comment

by:theamzngq
ID: 9820931
One more thing, do double quotes cause problems in some situations?  What if my SQL statements use double quotes instead of single quotes around CHAR data?
0
 
LVL 6

Expert Comment

by:PE_CF_DEV
ID: 9820939
preservesinglequotes(yourvar) will not work...it is used to KEEP the single quotes and to keep Coldfusion from doubling up the quotes...
Like if you have a string state_list : 'OH','FL','TX'

If you do
where state IN (#state_list#)

Coldfusion will try to make it :
where state in (''OH'',''FL'',''TX'')
 
You use preservesinglequotes()
where state IN (#preservesinglequotes(state_list)#)

ColdFusion does not try to double up the ' which will allow it to be
where state in ('OH','FL','TX')
Like it should be.
0
 
LVL 6

Expert Comment

by:PE_CF_DEV
ID: 9820946
coldfusionfunction is not a function it was just my way of saying any function you use. Sorry for the confusion :)
0
 
LVL 2

Author Comment

by:theamzngq
ID: 9821564
It looks like using CFQUERYPARAM did the trick, as well as take care of ANY special characters I entered.  Thanks for the input everyone!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question