Recent Articles |
Oracle SQL Developer 1.2
Oracle released the latest version of its SQL Devleoper the other day, version 1.2. This version features new capabilities to ease the migration and consolidation of third-party databases onto Oracle Database...
Using Pligg As An Easy Corporate Web 2.0 Site
For those who do not know the Pligg system is a digg clone site that allows people to submit and vote on stories, we run one as a way to collect social book marks and things we think are important at work, and...
SQL Server Express & Remote Access
What a pain this has been! I've never dealt with Microsoft SQL Server before but now that Vista runs acceptably on VMware Fusion, I figured that it would be a good opportunity to install SQL Server Express as...
How Much Does MySQL Make?
Reading Dave's post on MySQL's IPO range of $600M - $1B, I started to wonder what type of revenue we're talking about. If you have access to IDC's massive...
Getting The Autonumber Insert ID
I love this. So I assume most folks knew about the result attribute added to cfquery in ColdFusion 7. If not - you are missing out. It returns quite a bit of good information about your query. But best of all - it was...
MySQL Conference 2007, Santa Clara, CA
This is my first trip to Silicon Valley and the first thing that struck me as I approached the hotel was that I am certainly in one of the foremost tech havens on the planet. On the way down Great America Pkwy towards...
|
|
 |
|
10.08.07
Single Quotes In A Query Issue
 By Raymond Camden
Alex had a problem with his SQL.
This is actually a frequently asked question and I've covered it here before (I think so anyway), but I thought I'd mention it again.
It comes up from time to time as people forget. Anyway - his question:
I'm using a web service to retrieve zip codes within a given radius from another zip code.
The service sends me back a string that is formatted to use in a SQL WHERE clause:
xxx="10001" or xxx="10002" or xxx="10003" ...
I am using MySQL as the DB and the double quotes in the return string above won't work in the WHERE clause, so I am using a simple CF replace() function to replace the double quotes with single quotes (the zip field in my DB is setup as a string) for processing.
For some reason, after I replace the quotes, the SQL statement in my CFQUERY tag includes the double quotes again! I can't figure out why this is happening.
Cost Effective Website and Network Monitoring
IPCheck Server Monitor - Free Download |
|
There is a simple explanation for this. ColdFusion auto escapes single quotes. Why? Imagine you have a search for, and a user searches for "Ray's Hotness is greater than Paris Hilton". Your sql could do:
where name like '#form.search#'
(Although hopefully you would use cfqueryparam instead.) As you can see - the single quote in my search would break.
So in cases where you need a single quote to be left alone, you tell ColdFusion to stop that change with the preserveSingleQuotes function:
where whatever = #preserveSingleQuotes(somefunc)#
Comments
About the Author:
Raymond Camden, ray@camdenfamily.com
http://ray.camdenfamily.com
Raymond Camden is Vice President of Technology for roundpeg, Inc. A long
time ColdFusion user, Raymond has worked on numerous ColdFusion books
and is the creator of many of the most popular ColdFusion community web
sites. He is an Adobe Community Expert, user group manager, and the
proud father of three little bundles of joy.
|
