CFQuery Select and single quotes in string

2011-06-10 Thread Jim Brundle 

I'm doing a look up like this:

cfquery datasource=#DSN# name=GetResults
  SELECT Formation, Play, BallCarrier, YardsGained, Result
  FROM PlaysCalled
  WHERE (((PlaysCalled.Formation)=#Form.Formation#) AND  
((PlaysCalled.Play)=#Form.Play#) AND 
((PlaysCalled.BallCarrier)=#Form.BallCarrier#));
/cfquery

In some cases, the Ball Carrier's name has a single quote in it.  In these 
cases, the search doesn't find them.  

So for instance, it finds Bob Jones but not D'Quan Black.

Do I use cfqueryparam to resolve this?  I'm using cfqueryparam when I insert 
the data into the database, but when I use the line:
cfqueryparam value=#Trim(GetPlay.BallCarrier)# 
cfsqltype=CF_SQL_LONGVARCHAR it doesn't find anything.

Thanks,

Jim 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345144
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

re: CFQuery Select and single quotes in string

2011-06-10 Thread Jason Fisher 

Yes, and if this is form input, you should be using cfqueryparam anyway.  
Please see the news for Sony's latest data hack, which was simple web-based 
SQL injection.  cfsqltype will need to match column datatype in each case.

cfquery datasource=#DSN# name=GetResults
SELECT Formation, Play, BallCarrier, YardsGained, Result
FROM PlaysCalled
WHERE PlaysCalled.Formation = cfqueryparam cfsqltype=CF_SQL_VARCHAR 
value=#Form.Formation# /
AND PlaysCalled.Play = cfqueryparam cfsqltype=CF_SQL_VARCHAR 
value=#Form.Play# /
AND PlaysCalled.BallCarrier = cfqueryparam cfsqltype=CF_SQL_VARCHAR 
value=#Form.BallCarrier# /
/cfquery



From: Jim Brundle brundlefly...@gmail.com
Sent: Friday, June 10, 2011 9:15 AM
To: cf-talk cf-talk@houseoffusion.com
Subject: CFQuery Select and single quotes in string

I'm doing a look up like this:

cfquery datasource=#DSN# name=GetResults
SELECT Formation, Play, BallCarrier, YardsGained, Result
FROM PlaysCalled
WHERE   (((PlaysCalled.Formation)=#Form.Formation#) AND  
((PlaysCalled.Play)=#Form.Play#) AND 
((PlaysCalled.BallCarrier)=#Form.BallCarrier#));
/cfquery

In some cases, the Ball Carrier's name has a single quote in it.  In these 
cases, the search doesn't find them.  

So for instance, it finds Bob Jones but not D'Quan Black.

Do I use cfqueryparam to resolve this?  I'm using cfqueryparam when I 
insert the data into the database, but when I use the line:
cfqueryparam value=#Trim(GetPlay.BallCarrier)# 
cfsqltype=CF_SQL_LONGVARCHAR it doesn't find anything.

Thanks,

Jim 



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345145
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: CFQuery Select and single quotes in string

2011-06-10 Thread Carl Von Stetten 

What DBMS are you using? MSSQL, MYSQL, Access (bleeackk!), other?

MSSQL uses single quotes in the WHERE clause.  However, if you use 
cfqueryparam for all of your where clause pieces, single quotes in your 
strings should pass through in the database request just fine.

What is the data type of the BallCarrier column?  Would 
cfsqltype=CF_SQL_VARCHAR be more appropriate?

HTH,
Carl

On 6/10/2011 6:14 AM, Jim Brundle wrote:
 I'm doing a look up like this:

 cfquery datasource=#DSN# name=GetResults
SELECT Formation, Play, BallCarrier, YardsGained, Result
FROM PlaysCalled
WHERE  (((PlaysCalled.Formation)=#Form.Formation#) AND  
 ((PlaysCalled.Play)=#Form.Play#) AND 
 ((PlaysCalled.BallCarrier)=#Form.BallCarrier#));
 /cfquery

 In some cases, the Ball Carrier's name has a single quote in it.  In these 
 cases, the search doesn't find them.

 So for instance, it finds Bob Jones but not D'Quan Black.

 Do I use cfqueryparam to resolve this?  I'm using cfqueryparam when I insert 
 the data into the database, but when I use the line:
 cfqueryparam value=#Trim(GetPlay.BallCarrier)# 
 cfsqltype=CF_SQL_LONGVARCHAR  it doesn't find anything.

 Thanks,

 Jim

 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345147
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm