Re: CFQuery Select and single quotes in string
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: > > >SELECT Formation, Play, BallCarrier, YardsGained, Result >FROM PlaysCalled >WHERE (((PlaysCalled.Formation)="#Form.Formation#") AND > ((PlaysCalled.Play)="#Form.Play#") AND > ((PlaysCalled.BallCarrier)="#Form.BallCarrier#")); > > > 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: > 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
re: CFQuery Select and single quotes in string
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. SELECT Formation, Play, BallCarrier, YardsGained, Result FROM PlaysCalled WHERE PlaysCalled.Formation = AND PlaysCalled.Play = AND PlaysCalled.BallCarrier = From: "Jim Brundle" Sent: Friday, June 10, 2011 9:15 AM To: "cf-talk" Subject: CFQuery Select and single quotes in string I'm doing a look up like this: SELECT Formation, Play, BallCarrier, YardsGained, Result FROM PlaysCalled WHERE (((PlaysCalled.Formation)="#Form.Formation#") AND ((PlaysCalled.Play)="#Form.Play#") AND ((PlaysCalled.BallCarrier)="#Form.BallCarrier#")); 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: 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
