Re: CFqueryParam Coldfusion 11
Does anyone know of any supposed issues with CF 11 and cfqueryparam ? I had a few of them in a query. One by one I removed them to see if I could get the query working and then, adding them in. All but one work. Any ideas? You need to provide more information. Did the query work with CF 9 and CFQUERYPARAM? What is the error you're seeing, exactly? Dave Watts, CTO, Fig Leaf Software 1-202-527-9569 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Service-Disabled Veteran-Owned Small Business (SDVOSB) on GSA Schedule, and provides the highest caliber vendor- authorized instruction at our training centers, online, or onsite. ~| 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:359723 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFqueryParam Coldfusion 11
The errors getting back are all The request has exceeded the allowable time limit Tag Seems that the whole server eventually crashes/slows to crawl, even though other websites on that same server are fine. CF Version 11 / SQL Server Express Looking at the JVM free memory, it always seems to be going down. not sure what this points toany ideas? ~| 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:359724 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFqueryParam Coldfusion 11
Does the line number on that error indicate that the query is what's timing out? On Fri, Nov 28, 2014 at 11:46 AM, Don danfar...@hotmail.com wrote: The errors getting back are all The request has exceeded the allowable time limit Tag Seems that the whole server eventually crashes/slows to crawl, even though other websites on that same server are fine. CF Version 11 / SQL Server Express Looking at the JVM free memory, it always seems to be going down. not sure what this points toany ideas? ~| 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:359725 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFqueryParam Coldfusion 11
The errors getting back are all The request has exceeded the allowable time limit Tag What's happening on the database server? Have you stopped and restarted the database server? Have you disabled and reenabled the Maintain Connections option in your database driver? Dave Watts, CTO, Fig Leaf Software 1-202-527-9569 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Service-Disabled Veteran-Owned Small Business (SDVOSB) on GSA Schedule, and provides the highest caliber vendor- authorized instruction at our training centers, online, or onsite. ~| 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:359726 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFqueryParam Coldfusion 11
Does anyone know of any supposed issues with CF 11 and cfqueryparam ? I had a few of them in a query. One by one I removed them to see if I could get the query working and then, adding them in. All but one work. Any ideas? ~| 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:359717 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFqueryParam Coldfusion 11
Were you getting an error? On Thu, Nov 27, 2014 at 3:08 AM, Don danfar...@hotmail.com wrote: Does anyone know of any supposed issues with CF 11 and cfqueryparam ? I had a few of them in a query. One by one I removed them to see if I could get the query working and then, adding them in. All but one work. Any ideas? ~| 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:359718 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfqueryparam EncodeForHTML
I did - many times before I sent the message. So, explain, please. From CF: (cfqueryparam) Verifies the data type of a query parameter ... My example is a text field. The potential inject/bad data language is text. I just tested it and cfqueryparam did not prevent me from entering potentially bad data into the table. Larry V. Stephens -Original Message- From: .jonah [mailto:jonah@creori.com] Sent: Monday, November 03, 2014 9:46 PM To: cf-talk Subject: Re: cfqueryparam EncodeForHTML Read up on how query param works. It will protect against Johnson Johnson's;delete * ~| 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:359557 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam EncodeForHTML
cfqueryparam will not prevent the malicious data from getting entered into the table. However it does prevent the malicious text from executing as T-SQL. cfqueryparam does not parse or cleanse data in any way. Basically it passes the text as a variable to the sql statement. Thus preventing any malicious code in the text from executing. So instead of T-SQL actually executing like this: select * from myTable where x='some'; delete * from myTable --' cfqueryparam is actually running T-SQL something like this. select * from myType where x= @aVar CF and the database driver are assigning @aVar your text string, @aVar = some'; delete * from myTable -- . So the value of the string inside the variable can never be execute, as it is not part of the actual T-SQL syntax. Just a variable value at that point. You can specify a data type to the query param. So if you specified cf_sql_integer and attempted to insert text with that parameter you would get an T-SQL error data type mismatch of sorts. Hope this helps explain a bit. ~Byron ~| 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:359558 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam EncodeForHTML
Text input field Entry is Johnson Johnson's I store it in a table using cfqueryparam. All is good. Let's say the hacked entry is Johnson Johnson's;delete * (or something akin to that - you get the drift) I use cfqueryparam but it won't catch the hack; it's still just a string. Actually, it will prevent the value from being used to execute malicious SQL. To me, that's catching the hack. Converting the entire value to a string prevents the hack from working. At some point, before storing or after retrieval, I use EncodeForHTML to make that safe. Now I have either ... I'm not sure what you're trying to accomplish. If it's to remove the part of the value containing something that would be malicious SQL if it were executable, you have to determine what exactly is that part of the string, and how you differentiate it from other parts of the value that wouldn't be malicious SQL if they were executable. But at this point, this has nothing to do with safety unless your application sends the string as-is to another application which isn't parameterizing its SQL statements. Dave Watts, CTO, Fig Leaf Software 1-202-527-9569 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Service-Disabled Veteran-Owned Small Business (SDVOSB) on GSA Schedule, and provides the highest caliber vendor- authorized instruction at our training centers, online, or onsite. ~| 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:359559 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam EncodeForHTML
Like querying malicious data and using it in another cfquery without cfqueryparam. As an extra safety feature, if your application does not use multiSQL statements at all, and depending on the type of database engine used, you could also streatly deactivate the multi statement facility. If you're using an Access database, you don't even have to deactivate it: there is NO multi statement facility. ~| 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:359560 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam EncodeForHTML
what you really need is a Web Application Firewall which will clean all form and url params and strip out anything dodgy. There are plenty of generic web server WAF's, or if you want a CF specific solution then try FuseGuard. On Tue, Nov 4, 2014 at 5:26 PM, wrote: Like querying malicious data and using it in another cfquery without cfqueryparam. As an extra safety feature, if your application does not use multiSQL statements at all, and depending on the type of database engine used, you could also streatly deactivate the multi statement facility. If you're using an Access database, you don't even have to deactivate it: there is NO multi statement facility. ~| 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:359561 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam EncodeForHTML
cfqueryparam and EncodeForHTML are used to prevent two different types of attack. cfqueryparam is for SQL injection attacks, as Byron explained. EncodeForHTML is used to prevent cross site scripting attacks (it does not prevent/escape sql injection), which exist when the attacker can execute arbitrary client side code (such as javascript). Suppose we specified companyName=Johnson Johnson'sscript src=//hackers.r.us/badstuff.js If you inserted that value into the db everyone who views that record in the DB may potentially execute the hackers javascript file which can do lots of things, potentially on behalf of the currently authenticated user. You don't necessarily need to run EncodeForHTML before inserting the data into the database, you just need to make sure that you call EncodeForHTML every time you output the variable. The reason for this is that you can end up double encoding when you edit the record, or maybe you want to output the value in another context such as a HTML attribute, then you want to use EncodeForHTMLAttribute on the raw value instead. In some apps though it can be a much easier solution to just encode it before inserting the data if it is output in a million places. -- Pete Freitag - Adobe Community Professional http://foundeo.com/ - ColdFusion Consulting Products http://hackmycf.com - Is your ColdFusion Server Secure? http://www.youtube.com/watch?v=ubESB87vl5U - FuseGuard your CFML in 10 minutes On Mon, Nov 3, 2014 at 3:41 PM, Stephens, Larry V steph...@iu.edu wrote: Text input field Entry is Johnson Johnson's I store it in a table using cfqueryparam. All is good. Let's say the hacked entry is Johnson Johnson's;delete * (or something akin to that - you get the drift) I use cfqueryparam but it won't catch the hack; it's still just a string. At some point, before storing or after retrieval, I use EncodeForHTML to make that safe. Now I have either Johnson amp; Johnson#x27;s or Johnson amp; Johnson#x27;s#x3b;delete #x2a; Supposedly, that's safe. Regex could strip out the delete or kill the thing when I tried to save the data in the first place, but I tried several examples and none seemed to work. The thing is, Johnson amp; Johnson#x27;s may display correctly on the screen but it's not good for a search function, particularly if I have a legacy database. To parody a commercial for a different product, what are you using for protection? Larry V. Stephen ~| 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:359562 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfqueryparam EncodeForHTML
Text input field Entry is Johnson Johnson's I store it in a table using cfqueryparam. All is good. Let's say the hacked entry is Johnson Johnson's;delete * (or something akin to that - you get the drift) I use cfqueryparam but it won't catch the hack; it's still just a string. At some point, before storing or after retrieval, I use EncodeForHTML to make that safe. Now I have either Johnson amp; Johnson#x27;s or Johnson amp; Johnson#x27;s#x3b;delete #x2a; Supposedly, that's safe. Regex could strip out the delete or kill the thing when I tried to save the data in the first place, but I tried several examples and none seemed to work. The thing is, Johnson amp; Johnson#x27;s may display correctly on the screen but it's not good for a search function, particularly if I have a legacy database. To parody a commercial for a different product, what are you using for protection? Larry V. Stephen ~| 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:359552 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam EncodeForHTML
Read up on how query param works. It will protect against Johnson Johnson's;delete * On 11/3/14, 12:41 PM, Stephens, Larry V wrote: Text input field Entry is Johnson Johnson's I store it in a table using cfqueryparam. All is good. Let's say the hacked entry is Johnson Johnson's;delete * (or something akin to that - you get the drift) I use cfqueryparam but it won't catch the hack; it's still just a string. At some point, before storing or after retrieval, I use EncodeForHTML to make that safe. Now I have either Johnson amp; Johnson#x27;s or Johnson amp; Johnson#x27;s#x3b;delete #x2a; Supposedly, that's safe. Regex could strip out the delete or kill the thing when I tried to save the data in the first place, but I tried several examples and none seemed to work. The thing is, Johnson amp; Johnson#x27;s may display correctly on the screen but it's not good for a search function, particularly if I have a legacy database. To parody a commercial for a different product, what are you using for protection? Larry V. Stephen ~| 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:359555 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam EncodeForHTML
Larry, You are already using cfqueryparam so you are protected for the most part. I say most part, because you could still extract the data from the db and have bad consequences if you are not considerate of the underlying data and how you use it. Like querying malicious data and using it in another cfquery without cfqueryparam. In general it is best practice to save data as it was transmitted and in as raw a format as possible and leave the logic up to the application on how to proceess and present data. Could get rebuttals on that but it is my preference. That said, it's not that you shouldn't or can't html encode. You just need to make the decision based on the requirements at hand. If you're storing html code for presentation later, this may very well make sense, where doing so for a company name probably does not. +1 on being so security aware. Byron ~| 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:359556 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam to varchar raw problem
I found that if I decouple the encrypt_column from the insert script by requesting it from dual first, that it then works fine. While I would prefer to do it without a two-step process, this works fine for now. cfquery name=qInsertF9099AlienInfo_prep datasource=#application.dbDataSource# select Encrypt_Column(RPAD(UPPER(NVL(cfqueryparam cfsqltype=cf_sql_varchar value=#arguments.inStruct.a_alien_last_name#,' ')),56,' '),'CFASJAAAEPTSKEJPI') as a_alien_ln from dual /cfquery cfquery name=qInsertF9099AlienInfo datasource=#application.dbDataSource# INSERT INTO F9099_ALIEN_INFO( F9099_id, alien_last_name ) VALUES ( cfqueryparam cfsqltype=cf_sql_INTEGER value=#arguments.inStruct.l_F9099_id#, cfqueryparam cfsqltype=CF_SQL_VARBINARY value=#qInsertF9099AlienInfo_prep.a_alien_ln# ) /cfquery ~| 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:358707 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam to varchar raw problem
I suspect you have a charset mismatch. What is the DB charset, CF charset and what is the setting for sending cfqueryparams as Unicode? Our Oracle 11g displays âAL16UTF16â as the char set. PERM is UTF-8 However encrypt_column is returning a binary and it's going into a RAW field. So should that matter then? I've done some further experiments that seem to exonerate encrypt_column and I'll comment on those below. ~| 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:358678 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam to varchar raw problem
I did an experiment, calling ENCRYPT_COLUMN without doing the INSERT. I ran it 300 times and it didn't fail once. cfloop index = x from = 1 to = 300 cfquery name=qInsertF9099AlienInfo datasource=#application.dbDataSource# select Encrypt_Column(RPAD(UPPER(NVL(cfqueryparam cfsqltype=cf_sql_varchar value=#a_alien_last_name#,' ')),56,' '),'CFASJAAAEPTSKEJPI') as my_test from dual /cfquery cfoutput#x#:/cfoutput cfdump var = #qInsertF9099AlienInfo.my_test# !--- cfoutputploop = #x#:#qInsertF9099AlienInfo.my_test#/p/cfoutput --- /cfloop However, I couldn't output the data directly since it's binary (CF said NO!), so I did a dump and that was fine. Since ENCRYPT_COLUMN didn't fail once, I'm led to believe that the problem is that the actual INSERT has problems with the data returned or it has problems transmitting the returned data properly. ~| 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:358679 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam to varchar raw problem
On Tue, May 20, 2014 at 9:01 PM, daniel kessler wrote: I am adding CFQueryparams to an INSERT. The item that I am sending over to the oracle 11g db is empty text. The CFQueryParam is varchar because I am sending it over as text. From there, it is encrypted and a Cast_to_RAW is done on it to be put in a RAW field. This is failing with an invalid hex number error. I suspect you have a charset mismatch. What is the DB charset, CF charset and what is the setting for sending cfqueryparams as Unicode? Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:358673 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam to varchar raw problem
Here is the code, simplified somewhat. This is the INSERT. The a_alien_last_name goes into a RAW (56bytes) field. It used to go into a VARCHAR. Following this is the ENCRYPT_COLUMN function in Oracle that is called in the INSERT. CFSET a_alien_last_name = '' cfquery name=qInsertF9099AlienInfo datasource=#application.dbDataSource# INSERT INTO F9089_ALIEN_INFO ( F9089_id, alien_last_name ) VALUES ( cfqueryparam cfsqltype=cf_sql_INTEGER value=#arguments.inStruct.l_F9099_id#, Encrypt_Column(RPAD(UPPER(NVL(cfqueryparam cfsqltype=cf_sql_varchar value=#a_alien_last_name#,' ')),56,' '),'CFASJAAAEPTSKEJPI')) /cfquery encrypt_column in Oracle: CREATE OR REPLACE function encrypt_column (input_string in varchar2, key_string in varchar2) return raw is encrypted_string RAW(2048); begin if input_string is NULL then return NULL; else encrypted_string:=dbms_obfuscation_toolkit.DES3Encrypt(input = UTL_RAW.CAST_TO_RAW(input_string), key = UTL_RAW.CAST_TO_RAW(key_string)); return (encrypted_string); end if; end; ~| 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:358674 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam to varchar raw problem
I'll ask about this. I have to wait for the dba to come in. ~| 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:358675 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam to varchar raw problem
Previously the encrypt_column was going to a varchar, so it didn't need to do any of the RAW stuff. ~| 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:358676 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfqueryparam to varchar raw problem
I may be outside of the CF bounds here, but this problem wasn't showing up until we added CFQueryParams. We are in CF9. I am adding CFQueryparams to an INSERT. The item that I am sending over to the oracle 11g db is empty text. The CFQueryParam is varchar because I am sending it over as text. From there, it is encrypted and a Cast_to_RAW is done on it to be put in a RAW field. This is failing with an invalid hex number error. Overall, I don't receive the error consistently. However, I haven't been able to reproduce this without the CFQueryparam. Any thoughts on this? Or any clarification questions? ~| 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:358671 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam to varchar raw problem
On the whole, posting the code rather than describing the code is more helpful in these situations. The before and after versions might be helpful too. Did anything other than the usage of cfqueryparam change? -- Adam On 20 May 2014 20:01, daniel kessler dani...@umd.edu wrote: I may be outside of the CF bounds here, but this problem wasn't showing up until we added CFQueryParams. We are in CF9. I am adding CFQueryparams to an INSERT. The item that I am sending over to the oracle 11g db is empty text. The CFQueryParam is varchar because I am sending it over as text. From there, it is encrypted and a Cast_to_RAW is done on it to be put in a RAW field. This is failing with an invalid hex number error. Overall, I don't receive the error consistently. However, I haven't been able to reproduce this without the CFQueryparam. Any thoughts on this? Or any clarification questions? ~| 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:358672 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFQueryParam
I know using cfqueryparam helps with hack attempts on your database and it helps performance for the execution of the queries. Question, does using the cfqueryparam help with performance on the ORDER BY clause? One some of our pages we give the user the ability to change the sort order of the data being displayed. But, we do not use CFQUERYPARAM on the ORDER BY clauses. Thanks, Dave Hatz ~| 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:356262 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQueryParam
No, cfqueryparam does not work in the order by clause. However, when using url params passed to a query for altering the order I use if/then logic to set the order by clauses to prevent SQLi. if (url.sortby EQ 'D') { orderby mycolum desc } else { order by mycolumn ASC } THis is a simple and effective way to prevent SQLi in the order by clauses. Wil Genovese Sr. Web Application Developer/ Systems Administrator CF Webtools www.cfwebtools.com wilg...@trunkful.com www.trunkful.com On Jul 21, 2013, at 12:50 PM, Dave Hatz daveh...@hatzventures.org wrote: I know using cfqueryparam helps with hack attempts on your database and it helps performance for the execution of the queries. Question, does using the cfqueryparam help with performance on the ORDER BY clause? One some of our pages we give the user the ability to change the sort order of the data being displayed. But, we do not use CFQUERYPARAM on the ORDER BY clauses. Thanks, Dave Hatz ~| 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:356263 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfqueryparam issue
One of my table fields (and this occurs in many of my tables) is edited using CKEditor. This means the data will look like ptext data /p I am using Access at the moment, converting to MySQL. I have not tried this with the MySQL database; it fails using the Access database. (But, I have a number of applications away from work which will continue using Access into the near future.) My code in an insert query (CF10) is cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar The error is Application uses a value of the wrong type for the current operation. Take out the p /p and it works fine, implying the paragraph tags are the problem. Take out the cfqueryparam (i.e., '#CovNote#') and it works fine. Any ideas? ~| 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:354407 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam issue
Try to encode the string to base64 before adding it to the db. So it becomes cfqueryparam value=#toBase64(conNote)# cfsqltype=cf_sql_varchar /. When you read the data back out of the db, convert it back to a string like so: #toString(toBinary(field_name))# HTH Steve On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu wrote: One of my table fields (and this occurs in many of my tables) is edited using CKEditor. This means the data will look like ptext data /p I am using Access at the moment, converting to MySQL. I have not tried this with the MySQL database; it fails using the Access database. (But, I have a number of applications away from work which will continue using Access into the near future.) My code in an insert query (CF10) is cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar The error is Application uses a value of the wrong type for the current operation. Take out the p /p and it works fine, implying the paragraph tags are the problem. Take out the cfqueryparam (i.e., '#CovNote#') and it works fine. Any ideas? ~| 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:354408 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfqueryparam issue
Thanks - but same error. -Original Message- From: Steve Milburn [mailto:scmilb...@gmail.com] Sent: Friday, February 08, 2013 11:56 AM To: cf-talk Subject: Re: cfqueryparam issue Try to encode the string to base64 before adding it to the db. So it becomes cfqueryparam value=#toBase64(conNote)# cfsqltype=cf_sql_varchar /. When you read the data back out of the db, convert it back to a string like so: #toString(toBinary(field_name))# HTH Steve On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu wrote: One of my table fields (and this occurs in many of my tables) is edited using CKEditor. This means the data will look like ptext data /p I am using Access at the moment, converting to MySQL. I have not tried this with the MySQL database; it fails using the Access database. (But, I have a number of applications away from work which will continue using Access into the near future.) My code in an insert query (CF10) is cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar The error is Application uses a value of the wrong type for the current operation. Take out the p /p and it works fine, implying the paragraph tags are the problem. Take out the cfqueryparam (i.e., '#CovNote#') and it works fine. Any ideas? ~| 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:354409 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam issue
What if you try to encode it prior to the cfqueryparam tag? cfset tmp = toBase64(CovNote) / cfqueryparam value=#tmp# cfsqltype=cf_sql_varchar / On Fri, Feb 8, 2013 at 12:02 PM, Stephens, Larry V steph...@iu.edu wrote: Thanks - but same error. -Original Message- From: Steve Milburn [mailto:scmilb...@gmail.com] Sent: Friday, February 08, 2013 11:56 AM To: cf-talk Subject: Re: cfqueryparam issue Try to encode the string to base64 before adding it to the db. So it becomes cfqueryparam value=#toBase64(conNote)# cfsqltype=cf_sql_varchar /. When you read the data back out of the db, convert it back to a string like so: #toString(toBinary(field_name))# HTH Steve On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu wrote: One of my table fields (and this occurs in many of my tables) is edited using CKEditor. This means the data will look like ptext data /p I am using Access at the moment, converting to MySQL. I have not tried this with the MySQL database; it fails using the Access database. (But, I have a number of applications away from work which will continue using Access into the near future.) My code in an insert query (CF10) is cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar The error is Application uses a value of the wrong type for the current operation. Take out the p /p and it works fine, implying the paragraph tags are the problem. Take out the cfqueryparam (i.e., '#CovNote#') and it works fine. Any ideas? ~| 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:354410 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfqueryparam issue
This gets stranger and stranger. It doesn't appear to have anything to do with the p tags. I've found all I have to do is remove some of the end of the message and it works. I played with several iterations and finally took off the last sentence and then it works okay. Why the last sentence? Beats me. I worked up a program to look at the ASCII code of the entire string and everything fell into the 32 - 126 range except for some 10 (LF) and 9 (TAB) chars, and they don't seem to be a factor. No EOL shows up. I'm at a loss. On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu wrote: One of my table fields (and this occurs in many of my tables) is edited using CKEditor. This means the data will look like ptext data /p I am using Access at the moment, converting to MySQL. I have not tried this with the MySQL database; it fails using the Access database. (But, I have a number of applications away from work which will continue using Access into the near future.) My code in an insert query (CF10) is cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar The error is Application uses a value of the wrong type for the current operation. Take out the p /p and it works fine, implying the paragraph tags are the problem. Take out the cfqueryparam (i.e., '#CovNote#') and it works fine. Any ideas? ~| 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:354415 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam issue
What is the datatype and length of the column in the Access database? This sounds like a pretty standard data truncation problem. -Cameron On Fri, Feb 8, 2013 at 2:41 PM, Stephens, Larry V steph...@iu.edu wrote: This gets stranger and stranger. It doesn't appear to have anything to do with the p tags. I've found all I have to do is remove some of the end of the message and it works. I played with several iterations and finally took off the last sentence and then it works okay. Why the last sentence? Beats me. I worked up a program to look at the ASCII code of the entire string and everything fell into the 32 - 126 range except for some 10 (LF) and 9 (TAB) chars, and they don't seem to be a factor. No EOL shows up. I'm at a loss. On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu wrote: One of my table fields (and this occurs in many of my tables) is edited using CKEditor. This means the data will look like ptext data /p I am using Access at the moment, converting to MySQL. I have not tried this with the MySQL database; it fails using the Access database. (But, I have a number of applications away from work which will continue using Access into the near future.) My code in an insert query (CF10) is cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar The error is Application uses a value of the wrong type for the current operation. Take out the p /p and it works fine, implying the paragraph tags are the problem. Take out the cfqueryparam (i.e., '#CovNote#') and it works fine. Any ideas? ~| 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:354417 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfqueryparam issue
And, FWIW, converting it to base 64 doesn't get rid of whatever cfqueryparam is choking on. This is in the cfqueryparam tag as an insert without it works fine. -Original Message- From: Stephens, Larry V [mailto:steph...@iu.edu] Sent: Friday, February 08, 2013 2:41 PM To: cf-talk Subject: RE: cfqueryparam issue This gets stranger and stranger. It doesn't appear to have anything to do with the p tags. I've found all I have to do is remove some of the end of the message and it works. I played with several iterations and finally took off the last sentence and then it works okay. Why the last sentence? Beats me. I worked up a program to look at the ASCII code of the entire string and everything fell into the 32 - 126 range except for some 10 (LF) and 9 (TAB) chars, and they don't seem to be a factor. No EOL shows up. I'm at a loss. On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu wrote: One of my table fields (and this occurs in many of my tables) is edited using CKEditor. This means the data will look like ptext data /p I am using Access at the moment, converting to MySQL. I have not tried this with the MySQL database; it fails using the Access database. (But, I have a number of applications away from work which will continue using Access into the near future.) My code in an insert query (CF10) is cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar The error is Application uses a value of the wrong type for the current operation. Take out the p /p and it works fine, implying the paragraph tags are the problem. Take out the cfqueryparam (i.e., '#CovNote#') and it works fine. Any ideas? ~| 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:354418 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam issue
What is the datatype and length of the column in the Access database? Also, which datasource type? I remember someone mentioning problems with Memo fields when using the unicode driver a ways back. They suggested using *_clob instead of *_longvarchar. -Leigh ~| 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:354419 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfqueryparam issue
It's a memo field. -Original Message- From: Cameron Childress [mailto:camer...@gmail.com] Sent: Friday, February 08, 2013 2:52 PM To: cf-talk Subject: Re: cfqueryparam issue What is the datatype and length of the column in the Access database? This sounds like a pretty standard data truncation problem. -Cameron On Fri, Feb 8, 2013 at 2:41 PM, Stephens, Larry V steph...@iu.edu wrote: This gets stranger and stranger. It doesn't appear to have anything to do with the p tags. I've found all I have to do is remove some of the end of the message and it works. I played with several iterations and finally took off the last sentence and then it works okay. Why the last sentence? Beats me. I worked up a program to look at the ASCII code of the entire string and everything fell into the 32 - 126 range except for some 10 (LF) and 9 (TAB) chars, and they don't seem to be a factor. No EOL shows up. I'm at a loss. On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu wrote: One of my table fields (and this occurs in many of my tables) is edited using CKEditor. This means the data will look like ptext data /p I am using Access at the moment, converting to MySQL. I have not tried this with the MySQL database; it fails using the Access database. (But, I have a number of applications away from work which will continue using Access into the near future.) My code in an insert query (CF10) is cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar The error is Application uses a value of the wrong type for the current operation. Take out the p /p and it works fine, implying the paragraph tags are the problem. Take out the cfqueryparam (i.e., '#CovNote#') and it works fine. Any ideas? ~| 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:354420 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfqueryparam issue
Thank you - that did it. Does this apply to MySQL, too? (I'm porting the thing over...) -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: Friday, February 08, 2013 3:07 PM To: cf-talk Subject: Re: cfqueryparam issue What is the datatype and length of the column in the Access database? Also, which datasource type? I remember someone mentioning problems with Memo fields when using the unicode driver a ways back. They suggested using *_clob instead of *_longvarchar. -Leigh ~| 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:354421 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfqueryparam issue
Well MySQL has very different data types (and unlike Access, it uses a pure jdbc driver). So this probably does not apply to MySQL. -Leigh ~| 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:354422 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
convert to cfqueryparam
I have a line of Oracle pl/sql (9i) where I'm trying to add cfqueryparam in CF7. I have: #iif(len(trim(arguments.inStruct.a_person_id))eq 0,DE(NULL),DE(#arguments.inStruct.a_person_id#))#, a coworker suggested: cfqueryparam value = #arguments.inStruct.a_person_id# CFSQLType = CF_SQL_NUMERIC null=#IIF(len(arguments.inStruct.a_person_id),0,1)# But in reviewing it, I cannot tell if that is correct or not. I've gone over the sql several times. Also, I don't know DE(). Any information on this is welcome. Thank you. ~| 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:352378 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: convert to cfqueryparam
I have a line of Oracle pl/sql (9i) where I'm trying to add cfqueryparam in CF7. I have: #iif(len(trim(arguments.inStruct.a_person_id))eq 0,DE(NULL),DE(#arguments.inStruct.a_person_id#))#, a coworker suggested: cfqueryparam value = #arguments.inStruct.a_person_id# CFSQLType = CF_SQL_NUMERIC null=#IIF(len(arguments.inStruct.a_person_id),0,1)# But in reviewing it, I cannot tell if that is correct or not. I've gone over the sql several times. Also, I don't know DE(). The DE function is kind of confusing, as it's only used within string evaluation to ... delay that evaluation. So, in your starting case, DE prevents its string argument from being evaluated as code. (You want the literal value of arguments.inStruct.a_person_id, not to treat that value as a CF expression from which you'd later get a literal value.) Your coworker's suggestion may well be correct, but it can be simplified. You don't need IIF in this case: cfqueryparam ... null=#not len(arguments.inStruct.a_person_id)# Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:352379 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: convert to cfqueryparam
Ah I understand. Great, thank you. ~| 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:352380 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
MS SQL and cfqueryparam slow-down
We've been profiling our MS SQL traffic, and we're seeing the occasional spike in execution times related directly to the operations that CF perform in relation to the cfqueryparam tag in getting table column details. The problem crops up only on cfquery tags where cfqueryparam is used for values, and the specific operation to get column details hangs the process up for up to 45 seconds. At this time, I'd like to ask if anyone else has seen this kind of behavior. Thank you! ~| 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:350833 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MS SQL and cfqueryparam slow-down
It could be that you are not indexing the columns you're using variable data to search on. ~ Mike Stemle, jr. On Apr 25, 2012, at 12:18, Christopher Watson skyg...@gmail.com wrote: We've been profiling our MS SQL traffic, and we're seeing the occasional spike in execution times related directly to the operations that CF perform in relation to the cfqueryparam tag in getting table column details. The problem crops up only on cfquery tags where cfqueryparam is used for values, and the specific operation to get column details hangs the process up for up to 45 seconds. At this time, I'd like to ask if anyone else has seen this kind of behavior. Thank you! ~| 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:350834 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MS SQL and cfqueryparam slow-down
Actually, the prolonged execution times are for simple tables where the proper indexing has been in place. For assessment purposes, we have removed the cfqueryparam tags from that set of queries in our processes that manipulate the tables on which we are seeing the degradation in the profiler. We will look at the profiler data again in the morning to see if any of the same prolonged execution times exist. If not, then we'll stick with no cfqueryparam tags, since this is all related to tables and column data types and values where we have complete internalized control, and there really is no compelling reason to have the cfqueryparam tags in there. We'd LIKE to have them in there anyway, because of the other benefits realized. But if using them causes this degradation, and removing them does not, we'll opt for the latter. It could be that you are not indexing the columns you're using variable data to search on. ~| 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:350837 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MS SQL and cfqueryparam slow-down
Cfqueryparam has 2 purposes, to paramatise the params which adds your injection security and to create a prepared statement for better execution plan. It seems the execution plan is where u need to look for problems. But a workaround would be to use cfparam outside the cfquery instead if cfqueryparam, which would do the same job as far as sql injection protection from paramatisation goes. Regards Russ Michaels From my mobile On 25 Apr 2012 20:37, Christopher Watson skyg...@gmail.com wrote: Actually, the prolonged execution times are for simple tables where the proper indexing has been in place. For assessment purposes, we have removed the cfqueryparam tags from that set of queries in our processes that manipulate the tables on which we are seeing the degradation in the profiler. We will look at the profiler data again in the morning to see if any of the same prolonged execution times exist. If not, then we'll stick with no cfqueryparam tags, since this is all related to tables and column data types and values where we have complete internalized control, and there really is no compelling reason to have the cfqueryparam tags in there. We'd LIKE to have them in there anyway, because of the other benefits realized. But if using them causes this degradation, and removing them does not, we'll opt for the latter. It could be that you are not indexing the columns you're using variable data to search on. ~| 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:350838 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MS SQL and cfqueryparam slow-down
What is the ODBC driver in play here? On Wed, Apr 25, 2012 at 4:21 PM, Russ Michaels r...@michaels.me.uk wrote: Cfqueryparam has 2 purposes, to paramatise the params which adds your injection security and to create a prepared statement for better execution plan. It seems the execution plan is where u need to look for problems. But a workaround would be to use cfparam outside the cfquery instead if cfqueryparam, which would do the same job as far as sql injection protection from paramatisation goes. Regards Russ Michaels From my mobile On 25 Apr 2012 20:37, Christopher Watson skyg...@gmail.com wrote: Actually, the prolonged execution times are for simple tables where the proper indexing has been in place. For assessment purposes, we have removed the cfqueryparam tags from that set of queries in our processes that manipulate the tables on which we are seeing the degradation in the profiler. We will look at the profiler data again in the morning to see if any of the same prolonged execution times exist. If not, then we'll stick with no cfqueryparam tags, since this is all related to tables and column data types and values where we have complete internalized control, and there really is no compelling reason to have the cfqueryparam tags in there. We'd LIKE to have them in there anyway, because of the other benefits realized. But if using them causes this degradation, and removing them does not, we'll opt for the latter. It could be that you are not indexing the columns you're using variable data to search on. ~| 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:350839 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfqueryparam problem
I have this query in a remote accessible cfc: cfquery datasource=staff name=rq SELECT * FROM intervention WHERE studentId = cfqueryparam value=#arguments.id# cfsqltype=cf_sql_integer AND year=cfqueryparam value=#arguments.year# cfsqltype=cf_sql_integer /cfquery At issue is the second cfqueryparam. The arguments are passed in by a get request. When I pass in 2012 I get this error: [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type. Trying to pass it in as an integer gets the same error. The odd thing is that any other value passed in works right, but 2012 throws an error every time. Any suggestions? ~| 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:348002 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam problem
Try this. http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B270-3056B422E2F6FCAB Wil Genovese Sr. Web Application Developer/ Systems Administrator CF Webtools www.cfwebtools.com wilg...@trunkful.com www.trunkful.com On Oct 7, 2011, at 3:56 PM, Kris Sisk wrote: I have this query in a remote accessible cfc: cfquery datasource=staff name=rq SELECT * FROM intervention WHERE studentId = cfqueryparam value=#arguments.id# cfsqltype=cf_sql_integer AND year=cfqueryparam value=#arguments.year# cfsqltype=cf_sql_integer /cfquery At issue is the second cfqueryparam. The arguments are passed in by a get request. When I pass in 2012 I get this error: [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type. Trying to pass it in as an integer gets the same error. The odd thing is that any other value passed in works right, but 2012 throws an error every time. Any suggestions? ~| 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:348003 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam problem
That got it. Thanks, I was going nuts trying to figure it out. Try this. http://www.coldfusionjedi.com/index. cfm?mode=entryentry=7D417738-DF64-B270-3056B422E2F6FCAB Wil Genovese Sr. Web Application Developer/ Systems Administrator CF Webtools www.cfwebtools.com wilg...@trunkful.com www.trunkful.com ~| 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:348004 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam problem
Cool - The only reason I knew about this was that one of our developers had just ran into the same issue here today and she spent time researching it. She just told us about it a minute before you posted to HOF. I think is is solar flare related - totally unrelated systems having the same error at the same time. Must be solar flares.. Where's my tin hat? Wil Genovese Sr. Web Application Developer/ Systems Administrator CF Webtools www.cfwebtools.com wilg...@trunkful.com www.trunkful.com On Oct 7, 2011, at 4:11 PM, Kris Sisk wrote: That got it. Thanks, I was going nuts trying to figure it out. Try this. http://www.coldfusionjedi.com/index. cfm?mode=entryentry=7D417738-DF64-B270-3056B422E2F6FCAB Wil Genovese Sr. Web Application Developer/ Systems Administrator CF Webtools www.cfwebtools.com wilg...@trunkful.com www.trunkful.com ~| 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:348005 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfqueryparam sqltype for text field?
I am having an issue updating a record that has a text field (MSSQLServer2005). I'm using cfqueryparam and the docs are silent about what is the correct type for a MSSQL Text field. It's rejecting the update, saying The data types text and varchar are incompatible in the equal to operator I've tried setting the sqltype to varchar, and longvarchar but it still gives the error. I've never had this problem updating records with text fields before. What's the correct sqltype to use for a MSSQLServer Text field type? -- Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/mon ~| 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:347266 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam sqltype for text field?
OH wait.I've done it again slams forehead on the desk the problem was i was updating the record using the text field in the WHERE clause. Bad bad bad me. Sorry about that, everyone. We return you to normal programming. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Wed, Sep 7, 2011 at 5:46 PM, Mike Kear afpwebwo...@gmail.com wrote: I am having an issue updating a record that has a text field (MSSQLServer2005). I'm using cfqueryparam and the docs are silent about what is the correct type for a MSSQL Text field. It's rejecting the update, saying The data types text and varchar are incompatible in the equal to operator I've tried setting the sqltype to varchar, and longvarchar but it still gives the error. I've never had this problem updating records with text fields before. What's the correct sqltype to use for a MSSQLServer Text field type? -- Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month ~| 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:347267 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
On Mon, May 2, 2011 at 11:48 AM, Mike Kear wrote: Thankfully I'd heeded good advice and used cfqueryparam on all the queries in that site and nothing they tried worked. They were submitting urls with parameters like : /index.cfm?pid=111825pgm=../../../../../../../../../../proc/self/environguestprogID=2 and many many variations. That is not an attempts at SQL injection at all. That is an attempt to test for a Linux vulnerability: http://lwn.net/Articles/191954/ Considering how long ago that vulnerability was fixed this was most likely a generic scan, not something tailored to ColdFusion or your sites. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:344209 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFQUERYPARAM and caching
Having read the blog and comments about CFQUERYPARAM and caching:- http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B270- 3056B422E2F6FCAB I am wondering if it is a viable option to leave Maintain connections across client requests unchecked in a development environment? No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.894 / Virus Database: 271.1.1/3611 - Release Date: 05/02/11 15:51:00 ~| 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:344169 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERYPARAM and caching
Having read the blog and comments about CFQUERYPARAM and caching:- http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B270- 3056B422E2F6FCAB I am wondering if it is a viable option to leave Maintain connections across client requests unchecked in a development environment? Sure, that's a viable option in development. Less so in production, as the creation of new database connections is expensive - that's why JDBC provides connection pooling. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:344170 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CFQUERYPARAM and caching
Awesome, thanks Dave. -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: 03 May 2011 18:03 To: cf-talk Subject: Re: CFQUERYPARAM and caching Having read the blog and comments about CFQUERYPARAM and caching:- http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B270- 3056B422E2F6FCAB I am wondering if it is a viable option to leave Maintain connections across client requests unchecked in a development environment? Sure, that's a viable option in development. Less so in production, as the creation of new database connections is expensive - that's why JDBC provides connection pooling. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:344171 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Bless cfqueryparam - helped defend against a persistent hack attempt
Same here. I'm not aware of a way (in CF) to achieve caching of execution plans without fully qualified, paramterized SQL. CFQUERYPARAM gets you the latter when done right. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Mark A. Kruger [mailto:mkru...@cfwebtools.com] Sent: Monday, May 02, 2011 4:28 PM To: cf-talk Subject: RE: Bless cfqueryparam - helped defend against a persistent hack attempt I almost always see a significant gain in query performance - at least on all versions of MSSQL - as much as 20-30 percent in some cases. Mark A. Kruger, MCSE, CFG (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Monday, May 02, 2011 1:54 PM To: cf-talk Subject: Re: Bless cfqueryparam - helped defend against a persistent hack attempt cfqueryparam creates bound sql parameters, which improve query performance. This is purely theoretical, in practice, the gain in performance is neglectible. I prefer have a query to take 11 ms and see the values submitted in case of error, than having the same query take 10 ms, but no information in errors. In your practice, perhaps. In the practice of others, not so much. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite ~| 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:344206 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
You forgot a round in there too. ;) On Mon, May 2, 2011 at 9:10 PM, Justin Scott leviat...@darktech.org wrote: of course). If I have a numeric ID, I'm going to min(abs(val(trim(url.id))), 200) that variable before it ever makes it to the query (forces a positive in-range integer or zero; the two billion value can be modified if you're using larger numbers in your situation; an error can occur if the integer value is out of range so I force it in range as well). ~| 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:344208 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Bless cfqueryparam - helped defend against a persistent hack attempt
Last night I watched as someone made a pretty determined attempt to attack one of my web sites. Thankfully I'd heeded good advice and used cfqueryparam on all the queries in that site and nothing they tried worked.They were submitting urls with parameters like : /index.cfm?pid=111825pgm=../../../../../../../../../../proc/self/environguestprogID=2 and many many variations.Like most of us, I get lots of hack attempts but this was more persistent than any I'd seen before. The site is still running happily now, after about 8 hours of this. I don't know why they persisted for that long with no result - I would have thought there were other targets they could go for if they're getting no result here. I'm pleased I bothered to do all those things at the time. When I was building the site (which is only a small hobby site), I remember saying to myself several times this is overkill there's no need for all this paranoia.But I'm glad now that I did all that. The techniques I've used that worked for me in this case were a combination of factors: [A] ALL queries - every single one of them - have cfqueryparam including the cf-sqltype parameter around any parameters sent to the database. [B] no feedback is given to the user about the nature of the error, only an error-handling page with the generic statement that 'there is an error - perhaps we're updating the database - check back soon' so they have no clue why their attempt failed. [C] an email is sent to me with the exception struct, cgi vars, etc so I can be aware of what they're doing - that way i can make sure I am covering all the vulnerabilities. I have a banning system on forms on this site, so that forms submitted with values that match certain parameters cause the ip address of that user to be banned from the site. I think perhaps I'll need to expand that to include URL variables now. I just wanted to pass on, that the warnings everyone hears about using cfqueryparam are valid, and we should never let a chink appear in our defences by listening to that voice in the back of your head, specially when you're under time pressure this is overkill there's no need for all this paranoia. -- Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month ~| 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:344116 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
Right. However about CFQUERYPARAM, keep in mind that: - this will prevent SQL injection, not all form of attacks; - CFQUERYPARAM makes all values look like parameter 1... in the error messages, instead of the real values, not really handful when debugging; - CFQUERYPARAM makes all queries more difficult to write AND to read for programmers; - disabling multiple statement execution in the database will prevent from SQL injection as well. - ending a query on an error because of CFQUERYPARAM will prevent the injection, but will not give you any hint that it was really an attack. Personally I prefer checking crucial form and url parameters and eventually ban the intruder before submiting the query. ~| 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:344119 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
- CFQUERYPARAM makes all values look like parameter 1... in the error messages, instead of the real values, not really handful when debugging; - CFQUERYPARAM makes all queries more difficult to write AND to read for programmers; Are you implying that cfqueryparam _shouldn't_ be used for these reasons?? - Gabriel On Mon, May 2, 2011 at 8:49 AM, wrote: Right. However about CFQUERYPARAM, keep in mind that: - this will prevent SQL injection, not all form of attacks; - CFQUERYPARAM makes all values look like parameter 1... in the error messages, instead of the real values, not really handful when debugging; - CFQUERYPARAM makes all queries more difficult to write AND to read for programmers; - disabling multiple statement execution in the database will prevent from SQL injection as well. - ending a query on an error because of CFQUERYPARAM will prevent the injection, but will not give you any hint that it was really an attack. Personally I prefer checking crucial form and url parameters and eventually ban the intruder before submiting the query. ~| 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:344120 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
Are you implying that cfqueryparam _shouldn't_ be used for these reasons?? No, I'm just saying that there are better and more efficient ways of fighting attacks. I use cfqueryparam on some occasions, but not everywhere blindly. ~| 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:344121 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
- CFQUERYPARAM makes all queries more difficult to write AND to read for programmers; I couldn't disagree more with this statement. Aside from the protection benefits, the use of cfqueryparam creates bound sql parameters, which improve query performance. I don't disagree with your suggestion to check form and url parameters. In fact I encourage it heavily, and hope that folks will remember the 'type' attribute of the cfparam tag. That said, use of cfqueryparam is a must, IMO, and the more you use it the more it becomes second nature. Yes, your error messages may need some help, but a combination of usage and proper error handling (too often ignored) can go a long way towards saving your bacon. Steve 'Cutter' Blades Adobe Community Professional Adobe Certified Expert Advanced Macromedia ColdFusion MX 7 Developer http://blog.cutterscrossing.com Co-Author Learning Ext JS 3.2 Packt Publishing 2010 https://www.packtpub.com/learning-ext-js-3-2-for-building-dynamic-desktop-style-user-interfaces/book The best way to predict the future is to help create it On 5/2/2011 7:49 AM, =?ISO-8859-1?Q?Claude_Schn=E9egans wrote: Right. However about CFQUERYPARAM, keep in mind that: - this will prevent SQL injection, not all form of attacks; - CFQUERYPARAM makes all values look like parameter 1... in the error messages, instead of the real values, not really handful when debugging; - CFQUERYPARAM makes all queries more difficult to write AND to read for programmers; - disabling multiple statement execution in the database will prevent from SQL injection as well. - ending a query on an error because of CFQUERYPARAM will prevent the injection, but will not give you any hint that it was really an attack. Personally I prefer checking crucial form and url parameters and eventually ban the intruder before submiting the query. ~| 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:344122 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
- CFQUERYPARAM makes all values look like parameter 1... in the error messages, instead of the real values, not really handful when debugging; There are parsers that will show the actual queries with values. My favorite is the one that Ben Nadel wrote. It is a little JS bookmark widget. - CFQUERYPARAM makes all queries more difficult to write AND to read for programmers; Opinion. Depends on the person. I have not problem with using them. Besides the bulk of the SQL code for most of my CRUD's are machine generated. G! On Mon, May 2, 2011 at 8:49 AM, wrote: Right. However about CFQUERYPARAM, keep in mind that: - this will prevent SQL injection, not all form of attacks; - CFQUERYPARAM makes all values look like parameter 1... in the error messages, instead of the real values, not really handful when debugging; - CFQUERYPARAM makes all queries more difficult to write AND to read for programmers; - disabling multiple statement execution in the database will prevent from SQL injection as well. - ending a query on an error because of CFQUERYPARAM will prevent the injection, but will not give you any hint that it was really an attack. Personally I prefer checking crucial form and url parameters and eventually ban the intruder before submiting the query. ~| 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:344123 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
In case anyone is interested. This is the JS bookmark widget that parses Queryparam debugging info. Very handy. http://www.bennadel.com/blog/459-Merging-ColdFusion-SQL-Debugging-And-Query-Params-With-Javascript.htm HTH G! On Mon, May 2, 2011 at 9:14 AM, Gerald Guido gerald.gu...@gmail.com wrote: - CFQUERYPARAM makes all values look like parameter 1... in the error messages, instead of the real values, not really handful when debugging; There are parsers that will show the actual queries with values. My favorite is the one that Ben Nadel wrote. It is a little JS bookmark widget. - CFQUERYPARAM makes all queries more difficult to write AND to read for programmers; Opinion. Depends on the person. I have not problem with using them. Besides the bulk of the SQL code for most of my CRUD's are machine generated. G! On Mon, May 2, 2011 at 8:49 AM, wrote: Right. However about CFQUERYPARAM, keep in mind that: - this will prevent SQL injection, not all form of attacks; - CFQUERYPARAM makes all values look like parameter 1... in the error messages, instead of the real values, not really handful when debugging; - CFQUERYPARAM makes all queries more difficult to write AND to read for programmers; - disabling multiple statement execution in the database will prevent from SQL injection as well. - ending a query on an error because of CFQUERYPARAM will prevent the injection, but will not give you any hint that it was really an attack. Personally I prefer checking crucial form and url parameters and eventually ban the intruder before submiting the query. ~| 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:344124 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
Ok. I'll respect your opinion. But for anyone new to CF that may read this, I don't believe there is a debate about using cfqueryparam. My impression is that the settled majority best practice is to use it blindly, use it everywhere rather than not use it, or use it sparingly, or not use it because of code aesthetic reasons. - Gabriel On Mon, May 2, 2011 at 9:04 AM, wrote: Are you implying that cfqueryparam _shouldn't_ be used for these reasons?? No, I'm just saying that there are better and more efficient ways of fighting attacks. I use cfqueryparam on some occasions, but not everywhere blindly. ~| 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:344125 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
Just for the record, I use other techniques to protect my sites too but in this case, i was outlining what saved me in this case. Clearly there are a bazillion ways nasty people can do damage or exploit weaknesses in sites. In this case, I was grateful that I hadn't given in to the temptation to take the shortcut. The fields that the hacker was trying to inject into are integer fields. If I hadn't had the cfqueryparam set up for all variables to the query, AND the cf-sqltype=integer parameter as well, the injection might have got through the other defences I have set up. But the cfqueryparam stopped the text injection getting into the query, and the fact that i use error handling to trap errors, and give no feedback as to why the url failed, meant it was difficult for the hacker to figure out what he had to do to get around my defences. As it is, I got to watch over a period of 8 hours several hundred attempts to exploit my database and do damage, without having to lift a finger. I use a code writer to write most of my queries - at least the CRUD ones anyway, so it's no difficulty to ensure all the queries have cfqueryparam but at the time I wrote this site, I didnt have the code writer ready yet. I'm just glad i took the time back then to do the job properly and not cut corners. All I'm saying about this is:whenever you think it's not likely someone will try to hack your site, and anyway you're up against a deadline and cfqueryparam is a long-winded way to type out the simple parameter you want to use, THINK AGAIN!! it is DEFINITELY worth putting up all the defences you can, provided there isnt an undue price to pay in terms of performance. In fact, using cfqueryparam improves performance of queries- that site of mine is a database-driven site for a radio show, and it just lopes along during the show when load is higher. If anyone is curious about what site it is, I'll be happy to tell you and let you have a look for yourself, but I'm reluctant to post the url here since I've discussed so much about the security of the site. Just send me an email off line and i'll tell you. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Mon, May 2, 2011 at 11:49 PM, Dorioo dor...@gmail.com wrote: Ok. I'll respect your opinion. But for anyone new to CF that may read this, I don't believe there is a debate about using cfqueryparam. My impression is that the settled majority best practice is to use it blindly, use it everywhere rather than not use it, or use it sparingly, or not use it because of code aesthetic reasons. - Gabriel On Mon, May 2, 2011 at 9:04 AM, wrote: Are you implying that cfqueryparam _shouldn't_ be used for these reasons?? No, I'm just saying that there are better and more efficient ways of fighting attacks. I use cfqueryparam on some occasions, but not everywhere blindly. ~| 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:344126 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
As an interesting aside, the same ip address started trying to probe my mail server a few minutes ago, and my version of cfformprotect picked it up and added the ip address to the banned ips. Now that same ip address is banned from using any forms on any site I administer. Again, they get no feedback that they've been banned.Just seems like they've successfully submitted a form, but in fact their submission just gets dumped in the bitbucket and nothing gets done. it doesn't even get to the database. The reason for not letting them know they're banned is they can submit forms till the cows come home and nothing will happen. It means they will be less likely to try to find other holes in the defences to exploit. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month ~| 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:344127 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
Mike, out of curiosity, what seems to be motivating these attacks? Malware injections? Hacker cred? Revenge or grudge? Or just to vandalize and do damage? It seems like a LOT of effort. I am also surprised that they are using the same IP for so long and not changing proxies. Thanx G! On Mon, May 2, 2011 at 10:42 AM, Mike Kear afpwebwo...@gmail.com wrote: As an interesting aside, the same ip address started trying to probe my mail server a few minutes ago, and my version of cfformprotect picked it up and added the ip address to the banned ips. Now that same ip address is banned from using any forms on any site I administer. Again, they get no feedback that they've been banned.Just seems like they've successfully submitted a form, but in fact their submission just gets dumped in the bitbucket and nothing gets done. it doesn't even get to the database. The reason for not letting them know they're banned is they can submit forms till the cows come home and nothing will happen. It means they will be less likely to try to find other holes in the defences to exploit. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month ~| 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:344129 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
I dont know what the motivation is. All I've seen is repeated attempts to inject stuff into the database - never getting anywhere. So that means all the attempts so far are simply attempts to get at passwords or schema. I'm supposing once they get a look at the database they'll be able to figure out what's there that they want. If they're after credit card or user info I have bad news for them ... it's not a commercial site. There AREN'T any customer details because there are no customers LOL I get attempts on the email server every day. At least a hundred attempts a day over all the sites I manage. I dont see them now because I have automated the defences. Once an ip address is banned, they can submit hundreds of forms a minute and i wont see any of them because the first line of the form processing is to check if the user's ip address is banned, and only proceed further if it's not. I'm guessing the reason for these probes is to find mail servers that are open to exploitation, so they can send spams through them. Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month On Tue, May 3, 2011 at 1:09 AM, Gerald Guido gerald.gu...@gmail.com wrote: Mike, out of curiosity, what seems to be motivating these attacks? Malware injections? Hacker cred? Revenge or grudge? Or just to vandalize and do damage? It seems like a LOT of effort. I am also surprised that they are using the same IP for so long and not changing proxies. Thanx G! ~| 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:344130 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
However about CFQUERYPARAM, keep in mind that: - this will prevent SQL injection, not all form of attacks; - CFQUERYPARAM makes all values look like parameter 1... in the error messages, instead of the real values, not really handful when debugging; - CFQUERYPARAM makes all queries more difficult to write AND to read for programmers; - disabling multiple statement execution in the database will prevent from SQL injection as well. - ending a query on an error because of CFQUERYPARAM will prevent the injection, but will not give you any hint that it was really an attack. Personally I prefer checking crucial form and url parameters and eventually ban the intruder before submiting the query. This is silly contrarianism. Yes, CFQUERYPARAM only prevents SQL injection attacks. That's not a criticism. I've been using it for years, and I haven't found it significantly more difficult to read errors or debug query problems. Also, it is not the case that all SQL injection attacks require the ability to execute multiple statements. SQL injection attacks can be written within a single statement, although the attacker does lose some flexibility within that constraint. Hell, the Wikipedia page on SQL injection attacks lists as its first attack (an information disclosure attack) a single statement. Finally, how you trap errors is entirely up to you. CFQUERYPARAM is definitely more likely to be successful at preventing SQL injection attacks than your checking crucial form and URL parameters. CFQUERYPARAM, by defining a separation between data and executable code, prevents them entirely. It prevents known SQL injection attacks, and is guaranteed to prevent as-yet-undiscovered SQL injection attacks in the future. You have no guarantee that you can prevent attacks entirely by attempting to filter them out individually. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:344131 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
The site is still running happily now, after about 8 hours of this. I don't know why they persisted for that long with no result - I would have thought there were other targets they could go for if they're getting no result here. Chances are, it was an automated attack - the attacker might not have even know that your site was being attacked. Time is on the attacker's side here. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite ~| 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:344132 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
cfqueryparam creates bound sql parameters, which improve query performance. This is purely theoretical, in practice, the gain in performance is neglectible. I prefer have a query to take 11 ms and see the values submitted in case of error, than having the same query take 10 ms, but no information in errors. ~| 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:344134 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
And we prefer to hack Schneegans apps. :D On Mon, May 2, 2011 at 1:10 PM, wrote: cfqueryparam creates bound sql parameters, which improve query performance. This is purely theoretical, in practice, the gain in performance is neglectible. I prefer have a query to take 11 ms and see the values submitted in case of error, than having the same query take 10 ms, but no information in errors. ~| 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:344135 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
On Mon, May 2, 2011 at 11:10 AM, wrote: cfqueryparam creates bound sql parameters, which improve query performance. This is purely theoretical, in practice, the gain in performance is neglectible. I prefer have a query to take 11 ms and see the values submitted in case of error, than having the same query take 10 ms, but no information in errors. The gains are not purely theoretical but like most things, it depends on your situation. If you are using very simple queries, the benefit very well may be negligible. If you are using more complex queries that require more time to compute the query plan and are under load the benefits are much more obvious. If you are optimizing for errors, you are doing it wrong. It isn't that difficult to do proper error handling and debugging with cfquery, you just have to plan for it. I wrote up a quick blog article on the subject last year. http://judah.posterous.com/debugging-with-prepared-statements-with-cfque If you provide a result attribute on your cfquery you'll get a struct returned that includes not only the results but also the parameterized query that was run, the parameters that were passed in and whether or not the query was cached. Hope that helps, Juda ~| 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:344137 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
cfqueryparam creates bound sql parameters, which improve query performance. This is purely theoretical, in practice, the gain in performance is neglectible. I prefer have a query to take 11 ms and see the values submitted in case of error, than having the same query take 10 ms, but no information in errors. In your practice, perhaps. In the practice of others, not so much. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite ~| 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:344140 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Bless cfqueryparam - helped defend against a persistent hack attempt
I almost always see a significant gain in query performance - at least on all versions of MSSQL - as much as 20-30 percent in some cases. Mark A. Kruger, MCSE, CFG (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Monday, May 02, 2011 1:54 PM To: cf-talk Subject: Re: Bless cfqueryparam - helped defend against a persistent hack attempt cfqueryparam creates bound sql parameters, which improve query performance. This is purely theoretical, in practice, the gain in performance is neglectible. I prefer have a query to take 11 ms and see the values submitted in case of error, than having the same query take 10 ms, but no information in errors. In your practice, perhaps. In the practice of others, not so much. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite ~| 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:344142 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
I generally see a pretty significant gain on Oracle as well. Thanks, Eric Cobb http://www.cfgears.com Help me make a difference this summer - http://bit.ly/i8dJvQ On 5/2/2011 3:27 PM, Mark A. Kruger wrote: I almost always see a significant gain in query performance - at least on all versions of MSSQL - as much as 20-30 percent in some cases. Mark A. Kruger, MCSE, CFG (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Monday, May 02, 2011 1:54 PM To: cf-talk Subject: Re: Blesscfqueryparam - helped defend against a persistent hack attempt cfqueryparam creates bound sql parameters, which improve query performance. This is purely theoretical, in practice, the gain in performance is neglectible. I prefer have a query to take 11 ms and see the values submitted in case of error, than having the same query take 10 ms, but no information in errors. In your practice, perhaps. In the practice of others, not so much. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite ~| 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:344143 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
Sure, your MS Access DB doesn't cache execution plans, but real databases do. On Tuesday, 3 May 2011, wrote: cfqueryparam creates bound sql parameters, which improve query performance. This is purely theoretical, in practice, the gain in performance is neglectible. I prefer have a query to take 11 ms and see the values submitted in case of error, than having the same query take 10 ms, but no information in errors. -- -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org ~| 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:344150 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
On Mon, May 2, 2011 at 2:10 PM, wrote: cfqueryparam creates bound sql parameters, which improve query performance. This is purely theoretical, in practice, the gain in performance is neglectible. I prefer have a query to take 11 ms and see the values submitted in case of error, than having the same query take 10 ms, but no information in errors. no information in errors -- Untrue. The error/catch object has one key SQL with the sql statement containing the (param 1) etc placeholders, and another key where with all the parameters, types, and values. ~| 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:344151 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Bless cfqueryparam - helped defend against a persistent hack attempt
Yes, your error messages may need some help, but a combination of usage and proper error handling (too often ignored) can go a long way towards saving your bacon. Perhaps I'm going a little overboard, but my personal philosophy is that it shouldn't be possible for an end-user to generate a ColdFusion error message. Proper error handling is an absolute must so you get notified of errors that do occur, but I will generally massage the incoming data before passing it into a query (which uses cfqueryparam, of course). If I have a numeric ID, I'm going to min(abs(val(trim(url.id))), 200) that variable before it ever makes it to the query (forces a positive in-range integer or zero; the two billion value can be modified if you're using larger numbers in your situation; an error can occur if the integer value is out of range so I force it in range as well). There is something to be said for getting notified when an attack is in progress, but I'd lean on the side of logging unusual requests and sending a notice when the app sees greater than X unusual requests within a certain time period so I can check it out. It's very rare that the error notification on my sites ever need to kick in, and it becomes an event when they do rather than thousands of error emails getting generated from an automated attack. Kudos to the original poster for making sure they used some protection though. Attackers don't care what your deadline looked like when the app was written and their automated scanning tools don't care how big your site is, so you have to assume they're going to probe long and hard regardless of the size of the site. -Justin ~| 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:344152 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second cfqueryparam
OK, just not my night with decimal values. Trying to store distances (for throwing and jumping events), which are measured in feet and inches. In order to maintain proper sort order, I decided to convert feet and inches (with fractions of an inch as decimal value) to inches with fractions of an inch as decimal values. Again, database is SQL Server 2008, and I'm using cfqueryparam. Data type for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL as the cfsqltype value. I can see in the debug output that the decimal portion of the value is part of the value to be inserted, for instance, 825.25, but looking at the database table, the stored value is 825. Here's the debug output for that query: *insertResult* (Datasource=track, Time=2ms, Records=1) in C:\inetpub\wwwroot\track\enter_results.cfm @ 21:59:59.059 INSERT INTO tblEventResults(eventid, athleteid, time, distance, competition) VALUES (?, ?, ?, ?, ?) Query Parameter Value(s) - Parameter #1(CF_SQL_INTEGER) = 1 Parameter #2(CF_SQL_INTEGER) = 1 Parameter #3(CF_SQL_TIMESTAMP) = Parameter #4(CF_SQL_DECIMAL) = 825.25 Parameter #5(CF_SQL_BIT) = NO Should I be using a different precision value for the decimal datatype? Or am I just doing something idiotic? Thanks, Pete On Sat, Feb 26, 2011 at 4:59 PM, Pete Ruckelshaus pruckelsh...@gmail.comwrote: Hey, that did it! Thanks! Pete On Sat, Feb 26, 2011 at 4:43 PM, Charlie Griefer charlie.grie...@gmail.com wrote: Hi Pete: Have you tried cf_sql_timestamp as the cfsqltype (as opposed to cf_sql_time)? -- Charlie Griefer http://charlie.griefer.com I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote: I'm building an app for keeping track of high school track meet results using CF9 and MS SQL Server 2008. I am using the time(7) data type in the database, and when doing the insert query, I'm using cfqueryparam with a sqltype of cf_sql_time. However, when entering a time value of, for example, 00:01:22.05, the value that actually gets inserted drops the decimal part of the seconds. When I do the insert query without using cfqueryparam, it works fine. I prefer using cfqueryparam for obvious reasons and am always hesitant to not use it, so is there a way to get it to work and still have it retain the decimal portion of the second? Thanks, Pete ~| 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:342602 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second cfqueryparam
I think your code is fine, but take a look at the precision value for the column in SQL Server. Ensure that it's set to 2. -- Charlie Griefer http://charlie.griefer.com I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. On Saturday, February 26, 2011 at 8:08 PM, Pete Ruckelshaus wrote: OK, just not my night with decimal values. Trying to store distances (for throwing and jumping events), which are measured in feet and inches. In order to maintain proper sort order, I decided to convert feet and inches (with fractions of an inch as decimal value) to inches with fractions of an inch as decimal values. Again, database is SQL Server 2008, and I'm using cfqueryparam. Data type for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL as the cfsqltype value. I can see in the debug output that the decimal portion of the value is part of the value to be inserted, for instance, 825.25, but looking at the database table, the stored value is 825. Here's the debug output for that query: *insertResult* (Datasource=track, Time=2ms, Records=1) in C:\inetpub\wwwroot\track\enter_results.cfm @ 21:59:59.059 INSERT INTO tblEventResults(eventid, athleteid, time, distance, competition) VALUES (?, ?, ?, ?, ?) Query Parameter Value(s) - Parameter #1(CF_SQL_INTEGER) = 1 Parameter #2(CF_SQL_INTEGER) = 1 Parameter #3(CF_SQL_TIMESTAMP) = Parameter #4(CF_SQL_DECIMAL) = 825.25 Parameter #5(CF_SQL_BIT) = NO Should I be using a different precision value for the decimal datatype? Or am I just doing something idiotic? Thanks, Pete On Sat, Feb 26, 2011 at 4:59 PM, Pete Ruckelshaus pruckelsh...@gmail.comwrote: Hey, that did it! Thanks! Pete On Sat, Feb 26, 2011 at 4:43 PM, Charlie Griefer charlie.grie...@gmail.com wrote: Hi Pete: Have you tried cf_sql_timestamp as the cfsqltype (as opposed to cf_sql_time)? -- Charlie Griefer http://charlie.griefer.com I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote: I'm building an app for keeping track of high school track meet results using CF9 and MS SQL Server 2008. I am using the time(7) data type in the database, and when doing the insert query, I'm using cfqueryparam with a sqltype of cf_sql_time. However, when entering a time value of, for example, 00:01:22.05, the value that actually gets inserted drops the decimal part of the seconds. When I do the insert query without using cfqueryparam, it works fine. I prefer using cfqueryparam for obvious reasons and am always hesitant to not use it, so is there a way to get it to work and still have it retain the decimal portion of the second? Thanks, Pete ~| 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:342611 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second cfqueryparam
On 2/26/2011 10:08 PM, Pete Ruckelshaus wrote: OK, just not my night with decimal values. Trying to store distances (for throwing and jumping events), which are measured in feet and inches. In order to maintain proper sort order, I decided to convert feet and inches (with fractions of an inch as decimal value) to inches with fractions of an inch as decimal values. Again, database is SQL Server 2008, and I'm using cfqueryparam. Data type for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL as the cfsqltype value. I can see in the debug output that the decimal portion of the value is part of the value to be inserted, for instance, 825.25, but looking at the database table, the stored value is 825. Why not store two fields; one for feet and one for inches. That seems like the easiest way to do it other than just use one float for both. The decimal issue is probably more a database issue than a CF issue. ~| 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:342617 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second cfqueryparam
Primarily because it's easier and more accurate to sort on a single overall value (inches). Of course, if it were up to me, we'd be using metric :) On Mon, Feb 28, 2011 at 7:12 PM, Roger Austin raust...@nc.rr.com wrote: On 2/26/2011 10:08 PM, Pete Ruckelshaus wrote: OK, just not my night with decimal values. Trying to store distances (for throwing and jumping events), which are measured in feet and inches. In order to maintain proper sort order, I decided to convert feet and inches (with fractions of an inch as decimal value) to inches with fractions of an inch as decimal values. Again, database is SQL Server 2008, and I'm using cfqueryparam. Data type for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL as the cfsqltype value. I can see in the debug output that the decimal portion of the value is part of the value to be inserted, for instance, 825.25, but looking at the database table, the stored value is 825. Why not store two fields; one for feet and one for inches. That seems like the easiest way to do it other than just use one float for both. The decimal issue is probably more a database issue than a CF issue. ~| 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:342629 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second cfqueryparam
You need to set the scale attribute in the cfqueryparam tag. http://cfquickdocs.com/cf9/?getDoc=cfqueryparam -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 1 March 2011 10:43, Pete Ruckelshaus pruckelsh...@gmail.com wrote: Primarily because it's easier and more accurate to sort on a single overall value (inches). Of course, if it were up to me, we'd be using metric :) On Mon, Feb 28, 2011 at 7:12 PM, Roger Austin raust...@nc.rr.com wrote: On 2/26/2011 10:08 PM, Pete Ruckelshaus wrote: OK, just not my night with decimal values. Trying to store distances (for throwing and jumping events), which are measured in feet and inches. In order to maintain proper sort order, I decided to convert feet and inches (with fractions of an inch as decimal value) to inches with fractions of an inch as decimal values. Again, database is SQL Server 2008, and I'm using cfqueryparam. Data type for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL as the cfsqltype value. I can see in the debug output that the decimal portion of the value is part of the value to be inserted, for instance, 825.25, but looking at the database table, the stored value is 825. Why not store two fields; one for feet and one for inches. That seems like the easiest way to do it other than just use one float for both. The decimal issue is probably more a database issue than a CF issue. ~| 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:342631 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Storing decimal parts of a second cfqueryparam
I'm building an app for keeping track of high school track meet results using CF9 and MS SQL Server 2008. I am using the time(7) data type in the database, and when doing the insert query, I'm using cfqueryparam with a sqltype of cf_sql_time. However, when entering a time value of, for example, 00:01:22.05, the value that actually gets inserted drops the decimal part of the seconds. When I do the insert query without using cfqueryparam, it works fine. I prefer using cfqueryparam for obvious reasons and am always hesitant to not use it, so is there a way to get it to work and still have it retain the decimal portion of the second? Thanks, Pete ~| 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:342599 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second cfqueryparam
Hi Pete: Have you tried cf_sql_timestamp as the cfsqltype (as opposed to cf_sql_time)? -- Charlie Griefer http://charlie.griefer.com I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote: I'm building an app for keeping track of high school track meet results using CF9 and MS SQL Server 2008. I am using the time(7) data type in the database, and when doing the insert query, I'm using cfqueryparam with a sqltype of cf_sql_time. However, when entering a time value of, for example, 00:01:22.05, the value that actually gets inserted drops the decimal part of the seconds. When I do the insert query without using cfqueryparam, it works fine. I prefer using cfqueryparam for obvious reasons and am always hesitant to not use it, so is there a way to get it to work and still have it retain the decimal portion of the second? Thanks, Pete ~| 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:342600 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Storing decimal parts of a second cfqueryparam
Hey, that did it! Thanks! Pete On Sat, Feb 26, 2011 at 4:43 PM, Charlie Griefer charlie.grie...@gmail.comwrote: Hi Pete: Have you tried cf_sql_timestamp as the cfsqltype (as opposed to cf_sql_time)? -- Charlie Griefer http://charlie.griefer.com I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote: I'm building an app for keeping track of high school track meet results using CF9 and MS SQL Server 2008. I am using the time(7) data type in the database, and when doing the insert query, I'm using cfqueryparam with a sqltype of cf_sql_time. However, when entering a time value of, for example, 00:01:22.05, the value that actually gets inserted drops the decimal part of the seconds. When I do the insert query without using cfqueryparam, it works fine. I prefer using cfqueryparam for obvious reasons and am always hesitant to not use it, so is there a way to get it to work and still have it retain the decimal portion of the second? Thanks, Pete ~| 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:342601 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Best Practive for Updating using cfqueryparam
What is the best way to write the following update loop using cfqueryparam? cfset loopcount=0 cfloop index=UpdateRecord list=#FORM.RecordsToUpdate# cfset loopcount = loopcount+1 cfset NewOrder=#ListGetAt(FORM.DisplayOrder, loopcount)# cfquery name=updDisplayOrder datasource=#DSN# UPDATE Pages SET Pages.DisplayOrder = #NewOrder# WHERE Pages.PageID = #PageIndex# /cfquery /cfloop ~| 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:340424 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Best Practive for Updating using cfqueryparam
What is the best way to write the following update loop using cfqueryparam? cfset loopcount=0 cfloop index=UpdateRecord list=#FORM.RecordsToUpdate# cfset loopcount = loopcount+1 cfset NewOrder=#ListGetAt(FORM.DisplayOrder, loopcount)# cfquery name=updDisplayOrder datasource=#DSN# UPDATE Pages SET Pages.DisplayOrder = #NewOrder# WHERE Pages.PageID = #UpdateRecord# /cfquery /cfloop or cfset loopcount=0 cfloop index=UpdateRecord list=#FORM.RecordsToUpdate# cfset loopcount = loopcount+1 cfquery name=updDisplayOrder datasource=#DSN# UPDATE Pages SET Pages.DisplayOrder = cfqueryparam cfsqltype=cf_sql_integer value=#ListGetAt(FORM.DisplayOrder, loopcount)# WHERE Pages.PageID = #UpdateRecord# /cfquery /cfloop David Moore UpstateWeb ~| 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:340426 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Best Practive for Updating using cfqueryparam
Just add the queryparam where it is supposed to go on the set. Also where are people learning to write code like this? cfset NewOrder=#ListGetAt(FORM.DisplayOrder, loopcount)# The correct way is cfset NewOrder = ListGetAt(FORM.DisplayOrder, loopcount) / Notice that the hashes has been removed! Regards, Andrew Scott http://www.andyscott.id.au/ -Original Message- From: David Moore [mailto:dgmoor...@hotmail.com] Sent: Wednesday, 5 January 2011 9:11 AM To: cf-talk Subject: Best Practive for Updating using cfqueryparam What is the best way to write the following update loop using cfqueryparam? cfset loopcount=0 cfloop index=UpdateRecord list=#FORM.RecordsToUpdate# cfset loopcount = loopcount+1 cfset NewOrder=#ListGetAt(FORM.DisplayOrder, loopcount)# cfquery name=updDisplayOrder datasource=#DSN# UPDATE Pages SET Pages.DisplayOrder = #NewOrder# WHERE Pages.PageID = #PageIndex# /cfquery /cfloop ~~ ~~~| 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:340424 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf- talk/unsubscribe.cfm ~| 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:340427 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Best Practive for Updating using cfqueryparam
cfset loopcount=0 cfloop index=UpdateRecord list=#FORM.RecordsToUpdate# cfset loopcount = loopcount+1 cfquery name=updDisplayOrder datasource=#DSN# UPDATE Pages SET Pages.DisplayOrder = cfqueryparam cfsqltype=cf_sql_integer value=#ListGetAt(FORM.DisplayOrder, loopcount)# WHERE Pages.PageID = cfqueryparam cfsqltype=cf_sql_integer value=#UpdateRecord# /cfquery /cfloop OR (assuming I understand correctly): cfset loopcount=0 cfloop index=UpdateRecord list=#FORM.RecordsToUpdate# cfset loopcount = loopcount+1 cfquery datasource=#DSN# UPDATE Pages SET Pages.DisplayOrder = cfqueryparam cfsqltype=cf_sql_integer value=#loopcount# WHERE Pages.PageID = cfqueryparam cfsqltype=cf_sql_integer value=#UpdateRecord# /cfquery /cfloop Or (couldn't help myself here, sorry): cfset Application.DataMgr.saveSortOrder(Pages ,DisplayOrder ,FORM.RecordsToUpdate) http://www.bryantwebconsulting.com/docs/datamgr/change-sort-order.cfm Hope that helps, Steve What is the best way to write the following update loop using cfqueryparam? cfset loopcount=0 cfloop index=UpdateRecord list=#FORM.RecordsToUpdate# cfset loopcount = loopcount+1 cfset NewOrder=#ListGetAt(FORM.DisplayOrder, loopcount)# cfquery name=updDisplayOrder datasource=#DSN# UPDATE Pages SET Pages.DisplayOrder = #NewOrder# WHERE Pages.PageID = #UpdateRecord# /cfquery /cfloop or cfset loopcount=0 cfloop index=UpdateRecord list=#FORM.RecordsToUpdate# cfset loopcount = loopcount+1 cfquery name=updDisplayOrder datasource=#DSN# UPDATE Pages SET Pages.DisplayOrder = cfqueryparam cfsqltype=cf_sql_integer value=#ListGetAt(FORM.DisplayOrder, loopcount)# WHERE Pages.PageID = #UpdateRecord# /cfquery /cfloop David Moore UpstateWeb ~| 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:340434 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Working with cfqueryparam
It probably shouldn't be used for columns that don't allow null values. On Sun, Nov 14, 2010 at 11:52 AM, David Moore dgmoor...@hotmail.com wrote: Should this be written in on every cfqueryparam as a failsafe or should other steps be taken so that is not needed? cribe.cfm http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm -- - Scott Brady http://www.scottbrady.net/ ~| 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:339228 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Working with cfqueryparam
null=#Trim(FORM.Contributed) EQ ''# I like this. I find this easier to read: null=#yesNoFormat(len(trim(form.contributed)))# just because the nested quotes can be confusing. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:339198 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Working with cfqueryparam
Dave, I find this easier to read: null=#yesNoFormat(len(trim(form.contributed)))# I really like this in that is seems to be truer to the actual use of the variable as CF intended it to be used. To confirm what you are proposing, if form.contributed is NULL it will produce the following: null=yes Otherwise, if form.contribute has some information it will produce: null=no Should this be written in on every cfqueryparam as a failsafe or should other steps be taken so that is not needed? ~| 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:339199 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Working with cfqueryparam
I really appreciate everyone's input. This kind of documentation is very helpful for a lot of people. I have found CF-Talk to be the best CF resource available. Maybe some of the experts on this list can provide other examples of cfqueryparam uses that have been helpful, but not mentioned here. ~| 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:339202 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Working with cfqueryparam
On 11/14/2010 1:52 PM, David Moore wrote: I find this easier to read: null=#yesNoFormat(len(trim(form.contributed)))# What about using null=#isDate(form.contributed)# ? It looks like you are assuming a valid date or an empty string. What if someone puts in foo into the form field? Perhaps you are doing something else to validate the field that I am not following. -- LinkedIn: http://www.linkedin.com/pub/8/a4/60 Twitter: http://twitter.com/RogerTheGeek Blog: http://rogerthegeek.wordpress.com/ MissHunt: http://www.misshunt.com/ (Home of the Clean/Dirty Magnet) ~| 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:339203 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Working with cfqueryparam
Roger, What about using null=#isDate(form.contributed)# ? Would you want to add the yesNoFormat before the isDate as in Dave's suggestion? As to the second part, I am checking using ColdFusion's cfform methods within cfinput prior to submitting the information. Would your suggestion be a good way to provide more security or should I do more between the form input and the cfqueryparam? ~| 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:339206 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Working with cfqueryparam
Seems to me that there is a lot more to cfqueryparam than I have been utilizing. I built the following query using as many elements of cfqueryparam as I could find based on examples from CF-Talk and other resources. I would love to get expert opinion from those on this forum in an effort to expand my knowledge and provide documentation for others. Code is for MS Access: cfquery name=insNewRecord datasource=#DSN# INSERT INTO Database (Active, Contributed, DateAdded, AddedBy, UUID , ParentRecord, PageReference, RecordName, RecordText, ImageFile , DateTaken, TakenBy, PlaceTaken, ProjectReference) VALUES (cfqueryparam cfsqltype=cf_sql_integer value=#FORM.Active#/ , cfqueryparam cfsqltype=cf_sql_integer value=#Trim(FORM.Contributed)# null=#IIF(Trim(FORM.Contributed) EQ '', true, false)# , cfqueryparam cfsqltype=cf_sql_date value=#CreateODBCDateTime(Now())#/ , cfqueryparam cfsqltype=cf_sql_varchar value=#getUser.EmailAddress#/ , cfqueryparam cfsqltype=cf_sql_varchar value=#Trim(FORM.UUID)# maxlength=255/ , cfqueryparam cfsqltype=cf_sql_integer value=#Trim(FORM.ParentRecord)# , cfqueryparam cfsqltype=cf_sql_integer value=#Trim(FORM.PageReference)# , cfqueryparam cfsqltype=cf_sql_varchar value=#Trim(FORM.RecordName)# maxlength=255/ , cfqueryparam cfsqltype=cf_sql_clob value=#Trim(FORM.RecordText)#/ , cfqueryparam cfsqltype=cf_sql_varchar value=#Trim(FORM.ImageFile)# maxlength=255/ , cfqueryparam cfsqltype=cf_sql_date value=#Trim(FORM.DateTaken)# null=#IIF(Trim(FORM.DateTaken) EQ '', true, false)# , cfqueryparam cfsqltype=cf_sql_varchar value=#Trim(FORM.TakenBy)# null=#IIF(Trim(FORM.TakenBy) EQ '', true, false)# maxlength=255 , cfqueryparam cfsqltype=cf_sql_varchar value=#Trim(FORM.PlaceTaken)# null=#IIF(Trim(FORM.PlaceTaken) EQ '', true,false)# maxlength=255 , cfqueryparam cfsqltype=cf_sql_integer value=#Trim(URL.ProjectID)#) /cfquery ~| 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:339161 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Working with cfqueryparam
Seems to me that there is a lot more to cfqueryparam than I have been utilizing. I built the following query using as many elements of cfqueryparam as I could find based on examples from CF-Talk and other resources. I would love to get expert opinion from those on this forum in an effort to expand my knowledge and provide documentation for others. Code is for MS Access: ... I'd be happy to provide my opinion, but there's no question here that I can identify. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:339163 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm