Re: cfqueryparam and LIKE operator error
On Tuesday 16 Dec 2008, Mike Little wrote: WHERE #PreserveSingleQuotes(boolsearch)# Note that doesn't protect against SQL injection. -- Tom Chiverton Helping to dramatically reintermediate 24/7 low-risk cross-platform applications This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office together with a list of those non members who are referred to as partners. We use the word partner to refer to a member of the LLP, or an employee or consultant with equivalent standing and qualifications. Regulated by the Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316853 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and LIKE operator error
Try wrapping the QueryParams... i.e. Change (P.product_code LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) to (P.product_code LIKE (cfqueryparam cfsqltype=cf_sql_varchar value=%tents%)) HTH 2008/12/16 Mike Little m...@nzsolutions.co.nz: hi guys, getting an error using the following syntax... WHERE ((P.product_code LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.product_title LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.product_description LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%)) the error i get is... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') OR (PD.product_title ' at line 9 this was working fine till i added the cfqueryparams. mike ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316854 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and LIKE operator error
It is as Dave suggested, you can not use CFQUERYPARAM as part of the actual SQL query. You are writting the WHERE clause as a variable, remove the queryparam from that since you are declaring the variable. From: JediHomer jediho...@gmail.com Sent: Wednesday, December 17, 2008 3:17 AM To: cf-talk cf-talk@houseoffusion.com Subject: Re: cfqueryparam and LIKE operator error Try wrapping the QueryParams... i.e. Change (P.product_code LIKE value=%tents%) to (P.product_code LIKE (value=%tents%)) HTH 2008/12/16 Mike Little : hi guys, getting an error using the following syntax... WHERE ((P.product_code LIKE ) OR (PD.product_title LIKE ) OR (PD.product_description LIKE )) the error i get is... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') OR (PD.product_title ' at line 9 this was working fine till i added the cfqueryparams. mike ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316855 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cfqueryparam and LIKE operator error
hi guys, getting an error using the following syntax... WHERE ((P.product_code LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.product_title LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.product_description LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%)) the error i get is... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') OR (PD.product_title ' at line 9 this was working fine till i added the cfqueryparams. mike ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316824 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and LIKE operator error
What does the rendered query transaction look like? It should be somewhere in the error dump. Mike Little wrote: hi guys, getting an error using the following syntax... WHERE ((P.product_code LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.product_title LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.product_description LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%)) the error i get is... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') OR (PD.product_title ' at line 9 this was working fine till i added the cfqueryparams. mike ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316825 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and LIKE operator error
jake this is the actual dump... SELECT P.pid, P.price, P.price_sale, P.bid, P.display, P.views, PD.ptitle, PD.availability FROM product P INNER JOIN product_description PD ON P.pid = PD.pid INNER JOIN product_to_categories PTC ON P.pid = PTC.pid INNER JOIN categories C ON PTC.cid = C.cid INNER JOIN categories_description CD ON C.cid = CD.cid INNER JOIN brand B ON P.bid = B.bid WHERE ((P.code LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.ptitle LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.pdesc LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.fill_type LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.summary LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.features LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (CD.ctitle LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (B.btitle LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%)) AND P.display = 1 GROUP BY P.pid ORDER BY PD.ptitle ASC ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316826 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and LIKE operator error
The generated SQL has the cfqueryparam tags in it There's something wrong with that picture. How are you writing that query? On Tue, Dec 16, 2008 at 3:13 PM, Mike Little wrote: jake this is the actual dump... SELECT P.pid, P.price, P.price_sale, P.bid, P.display, P.views, PD.ptitle, PD.availability FROM product P INNER JOIN product_description PD ON P.pid = PD.pid INNER JOIN product_to_categories PTC ON P.pid = PTC.pid INNER JOIN categories C ON PTC.cid = C.cid INNER JOIN categories_description CD ON C.cid = CD.cid INNER JOIN brand B ON P.bid = B.bid WHERE ((P.code LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.ptitle LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.pdesc LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.fill_type LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.summary LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (PD.features LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (CD.ctitle LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%) OR (B.btitle LIKE cfqueryparam cfsqltype=cf_sql_varchar value=%tents%)) AND P.display = 1 GROUP BY P.pid ORDER BY PD.ptitle ASC ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316827 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and LIKE operator error
ah, it comes from a function matt. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316829 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and LIKE operator error
in my query, i have... WHERE #PreserveSingleQuotes(boolsearch)# boolsearch is a string that is generated based on the search string. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316830 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and LIKE operator error
in my query, i have... WHERE #PreserveSingleQuotes(boolsearch)# boolsearch is a string that is generated based on the search string. You can only use CFQUERYPARAM within a query. You can't build a string with CFQUERYPARAM, then use it in a query. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316832 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
