Newbie ... CFSQLTYPE of CFQueryParam
I have been searching for some explanation of the different Types used in CFQueryParam. understand that SCALE= is used to validate the position of decimal, but still confused by ... FLOAT, DECIMAL, MONEY, MONEY4. I am using CFquery INSERT do I need to use CFQueryParam for all values ? -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12962 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message ~| 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:321645 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Hi Bob, Check here for a list of the available options and their corresponding data types for some major SQL providers (for some reason MySQL isn't there): http://www.cfquickdocs.com/cf8/#cfqueryparam. As for your question, generally using cfqueryparam is recommended for any value that could be provided by the user. The main purpose is to eliminate SQL injection attacks by binding the parameters and preventing the use of SQL commands within the values (see http://xkcd.com/327/ for a fun example). it also provides some level of optimization. Personnally I've taken the habit of putting it pretty much for all my dynamic values in my queries. It just helps me sleep better at night. hth Francois Levesque http://blog.critical-web.com/ On Thu, Apr 16, 2009 at 10:49 AM, BobSharp bobsh...@ntlworld.com wrote: I have been searching for some explanation of the different Types used in CFQueryParam. understand that SCALE= is used to validate the position of decimal, but still confused by ... FLOAT, DECIMAL, MONEY, MONEY4. I am using CFquery INSERT do I need to use CFQueryParam for all values ? -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12962 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message ~| 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:321646 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
I have been searching for some explanation of the different Types used in CFQueryParam. understand that SCALE= is used to validate the position of decimal, but still confused by ... FLOAT, DECIMAL, MONEY, MONEY4. These correspond with specific database field types. You'd need to know what your database is looking for to choose the most appropriate data type in CF. That said, you can usually use FLOAT with any floating point database field, but it won't give you the same level of precision that the more specific types provide. I am using CFquery INSERT do I need to use CFQueryParam for all values ? Any time you use user-supplied values in a query, you should use CFQUERYPARAM. It doesn't matter whether it's an INSERT, UPDATE, DELETE or SELECT. 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 inf ~| 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:321647 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Bob I have a function that sniffs out the CF data type based on the SQL datatype. I am not sure if it is 100% as I have not tested it extensively. But this should handle most of the MSSQL and MYSQL datatypes. HTH G! http://coz.pastebin.com/f588cde23 On Thu, Apr 16, 2009 at 10:49 AM, BobSharp bobsh...@ntlworld.com wrote: I have been searching for some explanation of the different Types used in CFQueryParam. understand that SCALE= is used to validate the position of decimal, but still confused by ... FLOAT, DECIMAL, MONEY, MONEY4. I am using CFquery INSERT do I need to use CFQueryParam for all values ? -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12962 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message ~| 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:321648 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
This is the link to the function code in case it was not apparent from my post... I really need to slow down. ;o) http://coz.pastebin.com/f588cde23 G! -- Gerald Guido http://www.myinternetisbroken.com http://www.cfsimple.org/ To invent, you need a good imagination and a pile of junk. -- Thomas A. Edison ~| 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:321649 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
I've always been curious as to how cfqueryparam works. Does anyone know if it just performs some scanning and filtering on the actual values of the parameters passed to it or whether it somehow signals to the RDBMS that the values are parameters to the query thereby treating an SQLI attack as an escaped string or something? cfqueryparam errors when you try to use it outside a cfquery tag, which limits some of the stuff you can do with it. ( Like using cfsavecontent to have various cffunctions append SQL to a query and then popping that variable inside of a cfquery tag ). Is there some other way to leverage the parameterized safety of cfqueryparam? Can you do it using pure SQL? The database driver? Any ideas on how I could provide the same security outside of cfquery tags? On Thu, Apr 16, 2009 at 9:32 AM, Gerald Guido gerald.gu...@gmail.comwrote: This is the link to the function code in case it was not apparent from my post... I really need to slow down. ;o) http://coz.pastebin.com/f588cde23 G! -- Gerald Guido http://www.myinternetisbroken.com http://www.cfsimple.org/ To invent, you need a good imagination and a pile of junk. -- Thomas A. Edison ~| 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:321650 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
I've always been curious as to how cfqueryparam works. Does anyone know if it just performs some scanning and filtering on the actual values of the parameters passed to it or whether it somehow signals to the RDBMS that the values are parameters to the query thereby treating an SQLI attack as an escaped string or something? It builds a prepared statement. It doesn't scan or filter anything. cfqueryparam errors when you try to use it outside a cfquery tag, which limits some of the stuff you can do with it. ( Like using cfsavecontent to have various cffunctions append SQL to a query and then popping that variable inside of a cfquery tag ). Is there some other way to leverage the parameterized safety of cfqueryparam? Can you do it using pure SQL? The database driver? Any ideas on how I could provide the same security outside of cfquery tags? You could build a prepared statement yourself. 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:321651 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Dave Watts wrote: it somehow signals to the RDBMS that the values are parameters to the query thereby treating an SQLI attack as an escaped string or something? It builds a prepared statement. It doesn't scan or filter anything. Thus the database knows the data is data and not commands and does not try to execute commands that just may happen to be in the data. Any ideas on how I could provide the same security outside of cfquery tags? You could build a prepared statement yourself. There you go. ~| 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:321657 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Am I correct in assuming ( after just having skimmed the topic with Google ) that to do that within CFML I'd have to drop into Java and use the MySQL Java API to achieve that? On Thu, Apr 16, 2009 at 10:36 AM, Dave Watts dwa...@figleaf.com wrote: I've always been curious as to how cfqueryparam works. Does anyone know if it just performs some scanning and filtering on the actual values of the parameters passed to it or whether it somehow signals to the RDBMS that the values are parameters to the query thereby treating an SQLI attack as an escaped string or something? It builds a prepared statement. It doesn't scan or filter anything. cfqueryparam errors when you try to use it outside a cfquery tag, which limits some of the stuff you can do with it. ( Like using cfsavecontent to have various cffunctions append SQL to a query and then popping that variable inside of a cfquery tag ). Is there some other way to leverage the parameterized safety of cfqueryparam? Can you do it using pure SQL? The database driver? Any ideas on how I could provide the same security outside of cfquery tags? You could build a prepared statement yourself. 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:321664 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Actually, it looks like as long as I managed my prepared statement naming manually across the entire MySQL server I'd be ok to just run multiple queries in a row ( because CF8 with MySQL breaks for me if I try more than one statement separated by semicolons ). So it'd play out like this, for anyone curious: cfquery/ prepare statement #appName#_someIdentifier ' select * from ? ' cfquery/ set @p1 := '#userSuppliedValue#' cfquery/ execute #appName#_someIdentifier using @p1 That sure is a lot of cfquery tags, considering you'd need an extra cfquery for each parameter. But I wonder if that would totally break because CF could be executing multiple queries in parallel through the same MySQL connection and the interwoven execution of all of these queries might make them overlap and overwrite each other's values. Is that about accurate? 2009/4/16 David McGuigan davidmcgui...@gmail.com Am I correct in assuming ( after just having skimmed the topic with Google ) that to do that within CFML I'd have to drop into Java and use the MySQL Java API to achieve that? On Thu, Apr 16, 2009 at 10:36 AM, Dave Watts dwa...@figleaf.com wrote: I've always been curious as to how cfqueryparam works. Does anyone know if it just performs some scanning and filtering on the actual values of the parameters passed to it or whether it somehow signals to the RDBMS that the values are parameters to the query thereby treating an SQLI attack as an escaped string or something? It builds a prepared statement. It doesn't scan or filter anything. cfqueryparam errors when you try to use it outside a cfquery tag, which limits some of the stuff you can do with it. ( Like using cfsavecontent to have various cffunctions append SQL to a query and then popping that variable inside of a cfquery tag ). Is there some other way to leverage the parameterized safety of cfqueryparam? Can you do it using pure SQL? The database driver? Any ideas on how I could provide the same security outside of cfquery tags? You could build a prepared statement yourself. 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:321667 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Update: You can actually enable multiple statements in a single query in ColdFusion by appending allowMultiQueries=true to your datasource connection string! 2009/4/16 David McGuigan davidmcgui...@gmail.com Actually, it looks like as long as I managed my prepared statement naming manually across the entire MySQL server I'd be ok to just run multiple queries in a row ( because CF8 with MySQL breaks for me if I try more than one statement separated by semicolons ). So it'd play out like this, for anyone curious: cfquery/ prepare statement #appName#_someIdentifier ' select * from ? ' cfquery/ set @p1 := '#userSuppliedValue#' cfquery/ execute #appName#_someIdentifier using @p1 That sure is a lot of cfquery tags, considering you'd need an extra cfquery for each parameter. But I wonder if that would totally break because CF could be executing multiple queries in parallel through the same MySQL connection and the interwoven execution of all of these queries might make them overlap and overwrite each other's values. Is that about accurate? 2009/4/16 David McGuigan davidmcgui...@gmail.com Am I correct in assuming ( after just having skimmed the topic with Google ) that to do that within CFML I'd have to drop into Java and use the MySQL Java API to achieve that? On Thu, Apr 16, 2009 at 10:36 AM, Dave Watts dwa...@figleaf.com wrote: I've always been curious as to how cfqueryparam works. Does anyone know if it just performs some scanning and filtering on the actual values of the parameters passed to it or whether it somehow signals to the RDBMS that the values are parameters to the query thereby treating an SQLI attack as an escaped string or something? It builds a prepared statement. It doesn't scan or filter anything. cfqueryparam errors when you try to use it outside a cfquery tag, which limits some of the stuff you can do with it. ( Like using cfsavecontent to have various cffunctions append SQL to a query and then popping that variable inside of a cfquery tag ). Is there some other way to leverage the parameterized safety of cfqueryparam? Can you do it using pure SQL? The database driver? Any ideas on how I could provide the same security outside of cfquery tags? You could build a prepared statement yourself. 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:321672 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
And for all those reading this and using MSSQL, an inline statement would look like this: cfquery ... declare @p1 nvarchar(50) set @p1 = '#userSuppliedValue#' select * from tableName where column = @p1 /cfquery So, basically cfqueryparam is creating the Declare and Set for you. ~| 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:321675 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Actually, I believe cfqueryparam uses bindings, which effectively passes parameters to the SQL engine. In your example, you are still open to SQL injection attacks. However, if you need to use your value several times, you can use declare / set to define a variable in SQL, rather than using multiple cfqueryparam statements: cfquery ... declare @p1 nvarchar(50) set @p1 = cfqueryparam cfsqltype=cf_sql_varchar value=#userSuppliedValue# / select * from tableName where column = @p1 and othercolumn @p1 /cfquery Francois Levesque http://blog.critical-web.com/ On Thu, Apr 16, 2009 at 2:29 PM, Jason Fisher ja...@wanax.com wrote: And for all those reading this and using MSSQL, an inline statement would look like this: cfquery ... declare @p1 nvarchar(50) set @p1 = '#userSuppliedValue#' select * from tableName where column = @p1 /cfquery So, basically cfqueryparam is creating the Declare and Set for you. ~| 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:321678 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Ah, yes, Francois, you are correct. I forgot to mention that in addition to creating the @var parameters (which you can see in the CF debug output), the CFQUERYPARAM also ensures that you don't get '; BAD SQL INJECTION' stuff getting through into your SET @p1 = '#myUserVar#' expression. Thanks for the catch! ~| 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:321683 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
:::It builds a prepared statement. It doesn't scan or filter anything. You could build a prepared statement yourself. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Are you sure? When I pass a string into a cfqueryparam of type cf_sql_integer I get the error: Invalid data etc for CFSQLTYPE CF_SQL_INTEGER. Which leads me to believe it is being scanned/validated before being sent to MySQL, and also makes me wonder whether cfqueryparam even uses prepared statements. It seems and not just inline SQL variables. ( The exception is a coldfusion.sql.Parameter$DataTypeMismatchException ) On Thu, Apr 16, 2009 at 10:36 AM, Dave Watts dwa...@figleaf.com wrote: It builds a prepared statement. It doesn't scan or filter anything. You could build a prepared statement yourself. 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:321684 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Just a little thing to add here is that I believe you should parametize all values in your query, whether user generated or constant values or whatever. This is not for security but performance. So: SELECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = 1 Should be: SELECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = cfqueryparam cf_sql_type=cf_sql_bit value=1 / The reason (and someone please correct me if I am wrong) is that prepared statements can perform better because the db engine caches their execution plan much in the same way as for stored procedures. By parametizing and supplying the type of each value, you help this process. Indeed I suspect that I have read somewhere that it will not cache at all if it finds values that are not parametized. This is my rough understanding of it; please somebody who knows more clarify or correct the point (I do know that db performance always goes by the rule: it depends though). Dominic ~| 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:321685 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Yes he is sure. And he is correct. With a prepared statement, an array of values is sent to the db along with a query string that looks like SELECT * FROM fu WHERE bar = ?. In preparing the statement, each value has to be added to the array using a type specific method (in java). Therefore CF is pretty much forced to validate them at this point. Dominic 2009/4/16 David McGuigan davidmcgui...@gmail.com: :::It builds a prepared statement. It doesn't scan or filter anything. You could build a prepared statement yourself. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Are you sure? When I pass a string into a cfqueryparam of type cf_sql_integer I get the error: Invalid data etc for CFSQLTYPE CF_SQL_INTEGER. Which leads me to believe it is being scanned/validated before being sent to MySQL, and also makes me wonder whether cfqueryparam even uses prepared statements. It seems and not just inline SQL variables. ( The exception is a coldfusion.sql.Parameter$DataTypeMismatchException ) ~| 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:321686 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Dominic, you are right that there are exceptions from a performance perspective ... can't remember who blogged about that in detail? See Simon Horwith's for one example: http://www.horwith.com/index.cfm/2009/4/5/some-cf-best-practices-that-break But you are right about the Query Plan getting cached. Not every value has to be a parameter, however, the query just has to be exactly the same. So, if it only ever looks like this: SELECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = 1 Then that will cache just fine, regardless of the value of #id#. If, however, you also have a query for archived: SELECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = 0 Then those 2 will have separate Query Plans on the DB server. By contrast, if the 'live' value is param'd, then you can re-use the Query Plan for both queries: SELECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = cfqueryparam cf_sql_type=cf_sql_bit value=1 / would use the same QP as SELECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = cfqueryparam cf_sql_type=cf_sql_bit value=0 / ~| 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:321687 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Roger that re execution plans Jason, that makes perfect sense. Dominc 2009/4/16 Jason Fisher ja...@wanax.com: LECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = cfqueryparam cf_sql_type=cf_sql_bit value=1 / ~| 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:321688 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Well from what I read today it seems like the performance is improved for reused prepared statements, which may not be how cfqueryparam is implemented. Prepared statements are stored and reused by name, passing in the values for the parameters. So if cfqueryparam is using prepared statements (which my guess would be that it's not), cfqueryparamming all of your variable values would improve performance because the statement itself isn't being re-sent to the RDMS on each subsequent query, only the values. But if it's not, it could actually slightly DIMINISH performance ( because the SQL generated is more verbose because of the variable declarations and binding syntax ). Any experts out there that can enlighten us? On Thu, Apr 16, 2009 at 12:33 PM, Dominic Watson watson.domi...@googlemail.com wrote: Just a little thing to add here is that I believe you should parametize all values in your query, whether user generated or constant values or whatever. This is not for security but performance. So: SELECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = 1 Should be: SELECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = cfqueryparam cf_sql_type=cf_sql_bit value=1 / The reason (and someone please correct me if I am wrong) is that prepared statements can perform better because the db engine caches their execution plan much in the same way as for stored procedures. By parametizing and supplying the type of each value, you help this process. Indeed I suspect that I have read somewhere that it will not cache at all if it finds values that are not parametized. This is my rough understanding of it; please somebody who knows more clarify or correct the point (I do know that db performance always goes by the rule: it depends though). Dominic ~| 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:321689 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Everything but your guess is correct ;) Dominic 2009/4/16 David McGuigan davidmcgui...@gmail.com: Well from what I read today it seems like the performance is improved for reused prepared statements, which may not be how cfqueryparam is implemented. Prepared statements are stored and reused by name, passing in the values for the parameters. So if cfqueryparam is using prepared statements (which my guess would be that it's not), cfqueryparamming all of your variable values would improve performance because the statement itself isn't being re-sent to the RDMS on each subsequent query, only the values. But if it's not, it could actually slightly DIMINISH performance ( because the SQL generated is more verbose because of the variable declarations and binding syntax ). Any experts out there that can enlighten us? On Thu, Apr 16, 2009 at 12:33 PM, Dominic Watson watson.domi...@googlemail.com wrote: Just a little thing to add here is that I believe you should parametize all values in your query, whether user generated or constant values or whatever. This is not for security but performance. So: SELECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = 1 Should be: SELECT fu FROM bar WHERE barId = cfqueryparam cf_sql_type=cf_sql_integer value=#id# / AND live = cfqueryparam cf_sql_type=cf_sql_bit value=1 / The reason (and someone please correct me if I am wrong) is that prepared statements can perform better because the db engine caches their execution plan much in the same way as for stored procedures. By parametizing and supplying the type of each value, you help this process. Indeed I suspect that I have read somewhere that it will not cache at all if it finds values that are not parametized. This is my rough understanding of it; please somebody who knows more clarify or correct the point (I do know that db performance always goes by the rule: it depends though). Dominic ~| 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:321690 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Are you sure? When I pass a string into a cfqueryparam of type cf_sql_integer I get the error: Invalid data etc for CFSQLTYPE CF_SQL_INTEGER. Which leads me to believe it is being scanned/validated before being sent to MySQL, and also makes me wonder whether cfqueryparam even uses prepared statements. It seems and not just inline SQL variables. ( The exception is a coldfusion.sql.Parameter$DataTypeMismatchException ) It's not sent to MySQL directly, it's sent to your JDBC driver, which has to validate it and pass it on. JDBC throws an exception, which is caught and rethrown in CF. 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:321696 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
So if cfqueryparam is using prepared statements (which my guess would be that it's not), cfqueryparamming all of your variable values would improve performance because the statement itself isn't being re-sent to the RDMS on each subsequent query, only the values. But if it's not, it could actually slightly DIMINISH performance ( because the SQL generated is more verbose because of the variable declarations and binding syntax ). Your guess is incorrect. CFQUERYPARAM builds a JDBC prepared statement. Using CFQUERYPARAM may improve or degrade performance, depending on additional factors. The SQL being more verbose is not one of those factors. 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:321697 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
I can't speak for MySQL, but in MSSQL, every query (not just prepared statements) is processed into a Query Plan before processing. The server then caches as many of these QPs as possible, so that repeated calls to the same 'query definition' do not have the overhead of having to re-generate the QP; they just execute in the pre-defined 'most efficient way'. The difference in performance is, therefore, likely to be greater the more complex the query is. Whether the same holds true for MySQL, I have no idea. ~| 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:321699 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Ah, thanks guys! What I didn't realize is that you can use unnamed / unstored prepared statements through the driver. That makes so much more sense now. So ColdFusion uses dynamically prepared statements which are implemented in the JDBC driver, which is a totally different ballgame from using the pure SQL interface, which requires you to register them by name and then reuse or overwrite them per call. So if I can hunt down the driver syntax for calling that stuff directly can I just use the Java commands from within my dynamic SQL in a cfquery tag? It looks like CF probably uses java.sql.PreparedStatement with syntax like this: PreparedStatement pstmt = con.prepareStatement(UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?); pstmt.setBigDecimal(1, 153833.00); pstmt.setInt(2, 110592); Does anyone know how I'd just pop that Java into a CFQuery tag? Or can I probably not because cfquery itself is what handles all of that and will already be either a Statement or PreparedStatement object depending on whether it detected any cfqueryparams? If so, does that mean my only option is writing a custom tag to simulate cfquery and having it handle all of that stuff the same way cfquery does? ( Retrieve the datasource info from the adminapi, create and manage the connection, call all of these execution methods and then translate their results to a CFML query object, etc. What a hassle. This is worse than the fact that you can't use cfform controls outside of a cfform tag in CFC cffunctions. Thanks again. On Thu, Apr 16, 2009 at 1:33 PM, Dave Watts dwa...@figleaf.com wrote: So if cfqueryparam is using prepared statements (which my guess would be that it's not), cfqueryparamming all of your variable values would improve performance because the statement itself isn't being re-sent to the RDMS on each subsequent query, only the values. But if it's not, it could actually slightly DIMINISH performance ( because the SQL generated is more verbose because of the variable declarations and binding syntax ). Your guess is incorrect. CFQUERYPARAM builds a JDBC prepared statement. Using CFQUERYPARAM may improve or degrade performance, depending on additional factors. The SQL being more verbose is not one of those factors. 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:321702 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Nevermind, you can totally hack cfquery! Woot. Thanks for all the help. On Thu, Apr 16, 2009 at 1:59 PM, David McGuigan davidmcgui...@gmail.comwrote: Ah, thanks guys! What I didn't realize is that you can use unnamed / unstored prepared statements through the driver. That makes so much more sense now. So ColdFusion uses dynamically prepared statements which are implemented in the JDBC driver, which is a totally different ballgame from using the pure SQL interface, which requires you to register them by name and then reuse or overwrite them per call. So if I can hunt down the driver syntax for calling that stuff directly can I just use the Java commands from within my dynamic SQL in a cfquery tag? It looks like CF probably uses java.sql.PreparedStatement with syntax like this: PreparedStatement pstmt = con.prepareStatement(UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?); pstmt.setBigDecimal(1, 153833.00); pstmt.setInt(2, 110592); Does anyone know how I'd just pop that Java into a CFQuery tag? Or can I probably not because cfquery itself is what handles all of that and will already be either a Statement or PreparedStatement object depending on whether it detected any cfqueryparams? If so, does that mean my only option is writing a custom tag to simulate cfquery and having it handle all of that stuff the same way cfquery does? ( Retrieve the datasource info from the adminapi, create and manage the connection, call all of these execution methods and then translate their results to a CFML query object, etc. What a hassle. This is worse than the fact that you can't use cfform controls outside of a cfform tag in CFC cffunctions. Thanks again. On Thu, Apr 16, 2009 at 1:33 PM, Dave Watts dwa...@figleaf.com wrote: So if cfqueryparam is using prepared statements (which my guess would be that it's not), cfqueryparamming all of your variable values would improve performance because the statement itself isn't being re-sent to the RDMS on each subsequent query, only the values. But if it's not, it could actually slightly DIMINISH performance ( because the SQL generated is more verbose because of the variable declarations and binding syntax ). Your guess is incorrect. CFQUERYPARAM builds a JDBC prepared statement. Using CFQUERYPARAM may improve or degrade performance, depending on additional factors. The SQL being more verbose is not one of those factors. 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:321705 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
If so, does that mean my only option is writing a custom tag to simulate cfquery and having it handle all of that stuff the same way cfquery does? ( Retrieve the datasource info from the adminapi, create and manage the connection, call all of these execution methods and then translate their results to a CFML query object, etc. It is so, but I think the option of creating the custom tag is both mildly barmy and not the only option. A possible easier coding solution may be to create a component that builds and executes a cfquery by taking a java style prepared statement. A rough idea: cfcomponent set variables.dsn = / cffunction name=init ... init component and set datasource name/cffunction cffunction name=executePreparedStatement returntype=query cfargument name=statement type=string hint=eg. select * from foo where bar = ? / cfargument name=params type=array hint=Array of structs with two keys, 'type' and 'value' / cfset var theQuery = / cfquery name=theQuery datasource=#_dsn# ...// code to parse the statement and insert cfqueryparams in place of the '?'s /cfquery cfreturn theQuery / /cffunction /cfcompont What a hassle. This is worse than the fact that you can't use cfform controls outside of a cfform tag in CFC cffunctions. I don't believe it makes sense for CF to be able to do what you are asking. Given a bare cfqueryparam .../, how is it to know what to do with it? Dominic ~| 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:321706 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Sounds interesting :). Give all the googlers a bone and drop us a link or an explanation... 2009/4/16 David McGuigan davidmcgui...@gmail.com: Nevermind, you can totally hack cfquery! Woot. Thanks for all the help. ~| 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:321707 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
Anyone remember this article? http://coldfusion.sys-con.com/node/45569 Lets you drop down into Java and pull out the field types that your db reports back to the JDBC driver. Its neat on the surface, but too expensive on resources to use on the fly. Plus you have to put the for-real username and password directly into the template... Using vars doesn't work. Still, I like to use it during development if I have a monster query that I need to write up. Saves me the need to look back and forth from screen to screen to determine what the next cfsqltype is in my query list. Or stick it in front of a custom tag that writes the sql and cfqueryparams for you !--- cfmodule template=create_record.cfm dbUserName=#attributes.DBUserName# dbPassword=#attributes.DBPassword# DSN=#attributes.DSN# tableName=woof fieldList=arf,bark,ruff,meow SQLTypeList=CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR valueList=how,now,brown,cow --- cfquery username=#attributes.DBUserName# password=#attributes.DBPassword# datasource=#attributes.DSN# INSERT INTO #attributes.tableName# ( #attributes.fieldList# ) VALUES ( cfloop list=#attributes.fieldList# index=FieldValue cfset variables.LoopCounter=ListFindNoCase(attributes.fieldList,FieldValue) cfqueryparam cfsqltype=#ListGetAt(attributes.SQLTypeList,variables.LoopCounter)# value=#ListGetAt(attributes.valueList,variables.LoopCounter)# null=#YesNoFormat(not Len(ListGetAt(attributes.valueList,variables.LoopCounter)))# cfif compareNoCase(ListLast(attributes.fieldList),FieldValue),/cfif /cfloop ) /cfquery -- -...@robertson-- Janitor, The Robertson Team mysecretbase.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:321712 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Newbie ... CFSQLTYPE of CFQueryParam
So my real issue was that I wanted to super dynamically assemble the SQL and then either execute it or do a variety of other things with it ( which I won't go into ). If you try to use cfqueryparam outside of a containing cfquery, ColdFusion breaks. So, cfsavecontent ...cfqueryparam... /cfsavecontent if something do something else with it else cfquery it errored. By hacking cfquery, I mean that it suddently occured to me that you can do this, instead of the other options: cfquery cfsavecontent ...cfqueryparam... /cfsavecontent if something select false else #cfsavecontent# /cfquery if something return cfsavecontent else return queryName Note that you'll still need to not use cfqueryparam in cases where you won't be executing the query ( and swap in the unprocessed values or placeholders ), but in my case that still dramatically reduced the code necessary to support the multiple channels ( solved my problem ). It's very hacky, but worked like a charm. On Thu, Apr 16, 2009 at 2:40 PM, Dominic Watson watson.domi...@googlemail.com wrote: If so, does that mean my only option is writing a custom tag to simulate cfquery and having it handle all of that stuff the same way cfquery does? ( Retrieve the datasource info from the adminapi, create and manage the connection, call all of these execution methods and then translate their results to a CFML query object, etc. It is so, but I think the option of creating the custom tag is both mildly barmy and not the only option. A possible easier coding solution may be to create a component that builds and executes a cfquery by taking a java style prepared statement. A rough idea: cfcomponent set variables.dsn = / cffunction name=init ... init component and set datasource name/cffunction cffunction name=executePreparedStatement returntype=query cfargument name=statement type=string hint=eg. select * from foo where bar = ? / cfargument name=params type=array hint=Array of structs with two keys, 'type' and 'value' / cfset var theQuery = / cfquery name=theQuery datasource=#_dsn# ...// code to parse the statement and insert cfqueryparams in place of the '?'s /cfquery cfreturn theQuery / /cffunction /cfcompont What a hassle. This is worse than the fact that you can't use cfform controls outside of a cfform tag in CFC cffunctions. I don't believe it makes sense for CF to be able to do what you are asking. Given a bare cfqueryparam .../, how is it to know what to do with it? Dominic ~| 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:321713 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
