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
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 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" 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 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
Were you getting an error? On Thu, Nov 27, 2014 at 3:08 AM, Don 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
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's
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
>>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
> 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
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
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
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 & 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 & Johnson's > > or > > Johnson & Johnson's;delete * > > 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 & Johnson'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 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. select Encrypt_Column(RPAD(UPPER(NVL(,' ')),56,' '),'CFASJAAAEPTSKEJPI') as a_alien_ln from dual INSERT INTO F9099_ALIEN_INFO( F9099_id, alien_last_name ) 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: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 did an experiment, calling ENCRYPT_COLUMN without doing the INSERT. I ran it 300 times and it didn't fail once. select Encrypt_Column(RPAD(UPPER(NVL(,' ')),56,' '),'CFASJAAAEPTSKEJPI') as my_test from dual #x#: 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
>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
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
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
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. INSERT INTO F9089_ALIEN_INFO ( F9089_id, alien_last_name ) VALUES ( , Encrypt_Column(RPAD(UPPER(NVL(,' ')),56,' '),'CFASJAAAEPTSKEJPI')) 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
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
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 change? -- Adam On 20 May 2014 20:01, daniel kessler 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
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 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
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
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
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 wrote: > This gets stranger and stranger. It doesn't appear to have anything to do > with the 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 > > 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 text data > > > > > > 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 > > > > > > > > > The error is "Application uses a value of the wrong type for the > current > > > operation." > > > > > > Take out the 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
> 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
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 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 > 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 text data > > > > 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 > > > > > > The error is "Application uses a value of the wrong type for the current > > operation." > > > > Take out the 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? This sounds like a pretty standard data truncation problem. -Cameron On Fri, Feb 8, 2013 at 2:41 PM, Stephens, Larry V wrote: > This gets stranger and stranger. It doesn't appear to have anything to do > with the 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 > > 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 text data > > > > > > 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 > > > > > > > > > The error is "Application uses a value of the wrong type for the > current > > > operation." > > > > > > Take out the 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
This gets stranger and stranger. It doesn't appear to have anything to do with the 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 > 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 text data > > > > 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 > > > > > > The error is "Application uses a value of the wrong type for the current > > operation." > > > > Take out the 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 if you try to encode it prior to the cfqueryparam tag? On Fri, Feb 8, 2013 at 12:02 PM, Stephens, Larry V 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 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 > 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 text data > > > > 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 > > > > > > The error is "Application uses a value of the wrong type for the current > > operation." > > > > Take out the 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
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 . 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 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 text data > > 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 > > > The error is "Application uses a value of the wrong type for the current > operation." > > Take out the 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
Try to encode the string to base64 before adding it to the db. So it becomes . 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 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 text data > > 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 > > > The error is "Application uses a value of the wrong type for the current > operation." > > Take out the 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 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=entry&entry=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
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=entry&entry=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
Try this. http://www.coldfusionjedi.com/index.cfm?mode=entry&entry=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: > > SELECT * > FROM intervention > WHERE studentId = cfsqltype="cf_sql_integer" > > AND year= > > > 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 sqltype for text field?
OH wait.I've done it again 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 wrote: > I am having an issue updating a record that has a text field > (MSSQLServer2005). I'm using 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: 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=entry&entry=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: CFQUERYPARAM and caching
> Having read the blog and comments about CFQUERYPARAM and caching:- > > http://www.coldfusionjedi.com/index.cfm?mode=entry&entry=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 list attribute
> > null="#!isDefined('myVar') OR !ListLen('myVar')#"
>
> Thanks Carl, I haven't actually tried both together, I've tried them
> by themselves (as well as isNumeric and was unsuccessful 100% of the
> time. I'll give this one a try next time I find a spot it could be
> used and tested. Thank you!
I wanted to just reply back with another modification I did because I was still
getting an error when there was no value coming through, but most likely the
variable itself was defined.
I simply added in the null field !isNumeric('myVar') which checks to see if
it's numeric or not. I thought isDefined would catch it, but again it's a
field that's accepting integers and so maybe it was passing a '' value which is
not accepted in a numeric field. Correct me if I'm wrong please. I'm still
experimenting.
So, for now this is what I'm using, though I haven't tested it on a list of
integers yet. We'll see.
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336250
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam list attribute
> null="#!isDefined('myVar') OR !ListLen('myVar')#"
Thanks Carl, I haven't actually tried both together, I've tried them by
themselves (as well as isNumeric and was unsuccessful 100% of the time. I'll
give this one a try next time I find a spot it could be used and tested. Thank
you!
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336228
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam list attribute
> http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336215 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam list attribute
> Except, since Coldfusion is declaring the bind variables > and passing them in, and there is no "null" in coldfusion, > just "empty string", I can see where that might be a problem. > Because CF tries to pass an > empty string into a bind variable that's expecting an int. It is only an issue when null="false". When the "null" attribute is set to true, the cfqueryparam "value" is ignored and a special sql type representing NULL is sent to the database instead. So this is perfectly valid WHERE IntColumn IN ( ) So as Carl mentioned, they need to modify their code so null="true" when the value is an empty string "". Not just when the variable does not exist. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336212 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam list attribute
On Wed, Aug 11, 2010 at 4:55 PM, Carl Von Stetten wrote: > > Matthew, > > SQL should have no problem with a null being passed for a list, even with > integers (I just tested this against MS SQL Server 2005, although I use > BlueDragon JX 7.1 instead of ACF). Except, since Coldfusion is declaring the bind variables and passing them in, and there is no "null" in coldfusion, just "empty string", I can see where that might be a problem. Because CF tries to pass an empty string into a bind variable that's expecting an int. I've never come across such an issue myself though. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336211 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam list attribute
Matthew,
SQL should have no problem with a null being passed for a list, even with
integers (I just tested this against MS SQL Server 2005, although I use
BlueDragon JX 7.1 instead of ACF).
If you are using the null="#!isDefined('myVar')#" attribute in your
cfqueryparam, then a SQL NULL should be passed if myVar is undefined. However,
you might want to change that to
null="#!isDefined('myVar') OR !ListLen('myVar')#"
which will also send a null if myVar is defined but empty. Then you can change
the value attribute to
value="#myVar#"
HTH,
Carl
> Hey Ric, let me just add a little bit of what I've experienced in the
> cfqueryparam list. I've actually recently used them in our sites
> extensively now in many different forms (Ref: http://www.houseoffusion.
> com/groups/cf-talk/thread.cfm/threadid:30658)
>
> If a list comes up as '' then sometimes if you have the sql type as
> integer it errors out and I've had to just leave the sql type
> attribute out of the cfqueryparam.
>
> So this,
> null="#!isDefined('myVar')#" list="Yes" cfsqltype="cf_sql_integer" />
>
> ends up going to this,
>
> null="#!isDefined('myVar')#" list="Yes" />
>
> I think it's because if there's no value or it's undefined then it
> can't/doesn't know it's an integer or not, so it defaults to nothing?
> Not sure why really I just know if I don't pass at least a '0' it
> craps out.
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336195
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam list attribute
>Thanks Ric! I kind of figured that, but needed to verify. Thanks again! > >Eric Hey Ric, let me just add a little bit of what I've experienced in the cfqueryparam list. I've actually recently used them in our sites extensively now in many different forms (Ref: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:30658) If a list comes up as '' then sometimes if you have the sql type as integer it errors out and I've had to just leave the sql type attribute out of the cfqueryparam. So this, ends up going to this, I think it's because if there's no value or it's undefined then it can't/doesn't know it's an integer or not, so it defaults to nothing? Not sure why really I just know if I don't pass at least a '0' it craps out. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336193 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam list attribute
On Wed, Aug 11, 2010 at 9:26 AM, Eric Roberts wrote: > > Does this mean that if i have a list if integers, that the type should be > CF_SQL_Integer or should it be CF_SQL_Varchar since it is a list? This is > my first time working with the list attribute... Your cfsqltype should be based on the datatype of the field itself, so if your field type is "integer" then your cfsqltype should be cf_sql_integer If you used varchar, it would probably still work - especially if you're using sql server, but it would certainly degrade performance because indexes wouldn't get used, or the engine would convert your varchar bind variables that CF creates to integers and you don't want that. Ric ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336186 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
re: cfqueryparam question
Thanks John! Eric ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336122 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam question
DatePart will return an integer so CF_SQL_INTEGER On Mon, Aug 9, 2010 at 10:46 AM, Eric Roberts < ow...@threeravensconsulting.com> wrote: > > We are putting a cfqueryparam aound all of our sql vars...I am not sure how > to handle this one: > > Here's the line: > > * > > AND* DatePart(m,inv_date)= cfsqltype= > "CF_SQL_date" null="false" list="false"> > > > > Should that be date or varchar since it is looking at the part of the date > (which I am assuming returns a char or varchar value). The actual column > in > the DB, inv_date is of type datetime. > > > > Thanks! > > Eric > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336121 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQueryParam and Unicode mixing [spamtrap heur]
On 7/25/2010 4:53 AM, Igor Ilyinsky wrote: > > Someone correct me if I am wrong, but if you enable "non-latin" text > for a datasource, cfqueryparam will treat ALL cf_sql_varchar data as > unicode. Meaning there is no way to specify a non-unicode (ansi) > string other than to NOT use cfqueryparam? depends on the db driver, as far as i can remember its datadirect's JDBC driver for sql server. > I'm trying to weigh the value of enabling the "non-latin" option > versus the "N" hinting, but I can't find a way to enter single-byte > strings with cfqueryparam once the option is checked, and I think > it's silly to sacrifice 50% of my storage space for this > convenience. that's not the issue, its the hit the query takes converting non-unicode strings back & forth. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335710 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam this.sql?
Oh wow! That's cool, I've used fcdeditor for years! Love that extension. >On Thu, May 27, 2010 at 5:17 PM, Matthew Lowrey wrote: > > >They use to make dreamweaver extension for a few different things. A rich >text editor like cftextarea or fckeditor and some other e-commerce related >plug-ins if I'm not mistaken. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334074 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam this.sql?
On Thu, May 27, 2010 at 5:17 PM, Matthew Lowrey wrote: > > Thanks everyone, I was pretty sure the answers you gave were totally > expected. The code is some legacy code I or my boss is not sure is being > used and what it's being used for. It's some third party code (according to > the comments) > > Copyright InterAKT Online 2000-2005 tNG.cfc is the main file used. > > I checked out their website and they have been "acquired" by Adobe so > there's not any reference as to what this is or what it does and since we're > not sure we're even using it, I'm not going to try and figure out what it > is or what it does. It may get deleted if we don't come up with a reason > for using it. > > They use to make dreamweaver extension for a few different things. A rich text editor like cftextarea or fckeditor and some other e-commerce related plug-ins if I'm not mistaken. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334073 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam this.sql?
Thanks everyone, I was pretty sure the answers you gave were totally expected. The code is some legacy code I or my boss is not sure is being used and what it's being used for. It's some third party code (according to the comments) Copyright InterAKT Online 2000-2005 tNG.cfc is the main file used. I checked out their website and they have been "acquired" by Adobe so there's not any reference as to what this is or what it does and since we're not sure we're even using it, I'm not going to try and figure out what it is or what it does. It may get deleted if we don't come up with a reason for using it. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334070 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam this.sql?
I am sure others will have a _lot_ more to say on the subject. But here is my $0.02 > can you put a cfqueryparam around the variable above? No. Cfqueryparam is used for parameter _values_. It prevents whatever value you pass in from being executed as part of the parent sql statement. So while you could technically wrap the string in cfqueryparam, it would not be executed. > is this secure from SQL attacks? Absolutely not. Normally, CF escapes single quotes automatically, to help fight sql injection. Using PreserveSingleQuotes _suppresses_ that behavior, making the query more vulnerable to attacks. > recommend doing to secure this more? Well, not using that kind of dynamic sql would be a good start ... ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334061 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam this.sql?
> We have been working to secure our cfquery statements throughout our site > using > cfqueryparam and I have come upon the following format: > > password="#Request.password#"> > #PreserveSingleQuotes(sql)# > > > My question is, (and I'm pretty sure it's no, but had to ask anyway) can you > put a > cfqueryparam around the variable above? If so, what would be the cfsqltype > for this > since it's passing an entire query? No, you can't do that. > If this is not possible, (which I'm 95% sure it's not) is this secure from > SQL attacks? No, it isn't. > If not, what would you recommend doing to secure this more? You really can't take a block of unknown text and make it "secure". CFQUERYPARAM relies on the ability to separate SQL "code" from "data". The code needs to be executed. The data can be mapped to placeholders. So, your best bet is to make your SQL a bit less dynamic. If that's not an option, you could limit the functionality exposed to the datasource login used by this dynamic SQL to the bare minimum necessary to run the query. You'd do that configuration within your database server, of course. 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 onsi ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334059 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam this.sql?
> You would need to put the cfqueryparam in the code that builds the sql > variable. Actually, that won't work. The only place you can use CFQUERYPARAM is within a CFQUERY tag. 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-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334058 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam this.sql?
well in that format there's no cfqueryparam sql injection protection.cause you're accepting whatever's in the variable. why do you have it that way? that's a very bad performance because there's no execution plan caching, no query caching. cfqueryparams cannot exist outside of a cfquery block. so you can't put them into the code that generates your sql string. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334057 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfqueryparam this.sql?
You would need to put the cfqueryparam in the code that builds the sql variable. Also, username and password aren't required in the cfquery statement if they are defined in the datasource. On Thu, May 27, 2010 at 12:49 PM, Matthew Lowrey wrote: > > We have been working to secure our cfquery statements throughout our site > using cfqueryparam and I have come upon the following format: > > password="#Request.password#"> > #PreserveSingleQuotes(sql)# > > > My question is, (and I'm pretty sure it's no, but had to ask anyway) can you > put a cfqueryparam around the variable above? If so, what would be the > cfsqltype for this since it's passing an entire query? > > If this is not possible, (which I'm 95% sure it's not) is this secure from > SQL attacks? If not, what would you recommend doing to secure this more? > > Thanks in advance fellow Fusione ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334056 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQueryparam Oh now I <3/ hate you o_O
null="#Len(Trim(ARGUMENTS.SourceRef1))#" that will evaluate to null="true" when ARGUMENTS.SourceRef1 IS an empty string. as Leigh said, you should have used null="#NOT Len(Trim(ARGUMENTS.SourceRef1))#" Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ On 13/11/2009 01:58, Brett Davis wrote: > Ok in the quest for looking for better ways to write my code I ran into a > small gotcha today that had me banging my head against the wall. Here is the > original code: > > > > INSERT INTO t_field( >structure_id, >source_ref_1, >field_fmt_type_id, >last_chg_user_id, >last_chg_dt > > ) > VALUES( > value="#ARGUMENTS.StructureID#"/>, > value="#ARGUMENTS.SourceRef1#" null="#Len(Trim(ARGUMENTS.SourceRef1))#"/>, > value="#ARGUMENTS.FieldFmtTypeID#" > null="#Len(Trim(ARGUMENTS.FieldFmtTypeID))#"/>, > value="#ARGUMENTS.LastChangeUserID#"/>, > #CreateODBCDate(Now())# > ); > SELECT SCOPE_IDENTITY() AS newid; > > > > > > > I am sure most of you know what I am attempting to do, but for those that > don't I'll explain. Instead of writing a bunch of CFIFs to determine if the > value passed to the query is an empty string and omitting parts of the SQL > query and allowing MSSQL to insert NULLs, I've opted to use the NULL > attribute of the CFQueryparam tag to do that work for me. I've been using > null="#Len(Trim(Arguments.Value))#" with much success and happiness until > today. When ColdFusion attempted to run this code I got a nice fat error: > Invalid data '' for CFSQLTYPE CF_SQL_INTEGER > > I was like WTF? Ummm hello McFly if I passed you an empty string that's what > the whole null="#Len(Trim(value))#" was there for... hello. So I did some > digging around and it turns out I was in fact as Fred G Sanford would say > "You Big Dummy!". After reading a blog post by Ben Nadel > http://www.bennadel.com/blog/1092-ColdFusion-CFQueryParam-Binding-vs-SQL-Execution.htm > turns out that if you are using CF_SQL_INTEGER type it's looking for an > integer value period. Pass it an empty string if you wish and it will fail. > So a quick re-write to the code below gave me the results I was expecting in > the first place. > > > > INSERT INTO t_field( >structure_id, >source_ref_1, >field_fmt_type_id, >last_chg_user_id, >last_chg_dt > > ) > VALUES( > value="#ARGUMENTS.StructureID#"/>, > value="#ARGUMENTS.SourceRef1#" null="#Len(Trim(ARGUMENTS.SourceRef1))#"/>, > value="#ARGUMENTS.FieldFmtTypeID#" null="#NOT > isNumeric(ARGUMENTS.FieldFmtTypeID)#"/>, > value="#ARGUMENTS.LastChangeUserID#"/>, > #CreateODBCDate(Now())# > ); > SELECT SCOPE_IDENTITY() AS newid; > > > > > > > Using the null="#NOT isNumeric(ARGUMENTS.value)# allows for the proper insert > of a NULL when an empty string is passed to the query. > > Cheers > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328334 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQueryparam Oh now I <3/ hate you o_O
> Handy little trick being > able to drop the NOT in there like that. though if you forget it, cfqueryparam does NOT work as expected ;) ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328333 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQueryparam Oh now I <3/ hate you o_O
I do a similar check with #not isDate(arguments.startDate)# with cf_sql_date types as well. Handy little trick being able to drop the NOT in there like that. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328328 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQueryparam Oh now I <3/ hate you o_O
> cfsqltype="cf_sql_integer" > value="#ARGUMENTS.FieldFmtTypeID#" > null="#Len(Trim(ARGUMENTS.FieldFmtTypeID))#"/>, Do you not mean: null="#NOT Len(Trim(ARGUMENTS.FieldFmtTypeID))#"/> ie Insert null when the value IS an empty string -Leigh ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328318 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam DECREASES performance?
I ran into this old thread while researching cfqueryparam and performance
issues.
Stephen,
Your idea about 2 seperate datasources for unicode and non-unicode queries is
useful for some queries. But it does not solve issues with queries where
unicode and non-unicode fields are used in same query.
In our database, all config/generic tables/fields are varchar. Whereas only
fields that can possibly have unicode characters are nvarchar. Now when these
tables/fields need to be joined or otherwise used in a single query, I cannot
use cfqueryparam (or use cfqueryparam with 'String Format' checked in CFAdmin
datasource definition and cause performance issues).
The right solution would be for CF to have a datatype called cf_sql_Nvarchar
for when you want to specify the datatype as unicode.
Have anyone else run into this issue? How have you resolved this?
> > Stephen Dupre wrote:
> > > The most common reason for this performance problem is:
> > > 1) migration from CF5 (unicode datasource setting gets set to
> 'true'
> > - table scan against varchar - known issue - see #2)
> > > 2) having "Enable Unicode for data sources configured for
> non-Latin
> > characters" ON.
>
> > isn't that a bit contradictory? why would somebody who needs unicode
> turn on unicode but *not* use "N" datatypes?
>
> Paul,
>
> Good question. The answer is in point #1.
>
> >> 1) migration from CF5 (unicode datasource setting gets set to
> 'true' - table scan against varchar - known issue - see #2) <<
>
> The CF5->MX MIGRATION that builds the neo-query.xml is supposed to
> turn off UNICODE by default. It sets the value to "false".
>
> Problem is ... the SQL Server driver setting takes either 1 or 0 and
> ignores "false"... (bug we can't fix).
>
> And the default for the unicode param (sendStringParametersAsUnicode)
> is 1 (ON). Keep this in mind.
>
> So what you have with CF5 migrations is
> 1) a datasource checkbox for unicode in the CF admin that shows
> "UNCHECKED" because the value ('false') doesn't match 1 or 0.
> 2) the DEFAULT from the driver getting you (because it doesn't
> understand "false") turning unicode ON behind your back. This forces
> that implicit conversion of any VARCHAR column query (where
> firstname='fred') and throws out indexes.
>
> The performance sucks and you can't figure it out from the CF end.
>
> When I hear an immediate performance issue on a migrated (CF5->MX) app
> with SQL Server, that's the #1 cause. Most people get around it by
> creating another datasource during their investigation and
> inadvertantly seeing the problem go away because new datasources have
> this unicode setting un-checked (and set to 0).
>
> In another case, if you have ONE unicode column in the app (NTEXT),
> you might use ONE datasource and turn this "unicode" setting "ON" in
> the SQL Server datasource but it'll mess up 95% of your other queries
> with VARCHARS. (including client variable lookups). You have to be
> acutely aware of your datasources with SQL Server and use 2 of them if
> you have some mixed unicode/non-unicode queries/INSERTS.
>
> SQL Server datatype precedence screws you here too. (NVARCHAR higher
> than VARCHAR)
>
> So the moral is: Create 2 datasources with SQL Server JDBC and call
> one "_Unicode" and one "_non-unicode" so no one trips over this
> problem (or at least they'll ask why you did this before using them).
> For client vars, and 95% of your queries, use the non-unicode
> datasource. Use the unicode one only when you're absolutely sure the
> column is NVARCHAR, NTEXT, etc.
>
> Stephen Dupre
> Adobe Systems,
Inc
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327336
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam with cfstoredprocparam
No, cfprocparam is equivalent to cfqueryparam. Adrian > -Original Message- > From: Scott Stewart [mailto:sstwebwo...@bellsouth.net] > Sent: 06 March 2009 15:10 > To: cf-talk > Subject: cfqueryparam with cfstoredprocparam > > > Hey all, > > > > I'm calling a stored procedure using cfstoredproc. > > Within the cfstoredproc I have two procparams, > > The values are function arguments > > > > They currently look like this: > > value="#arguments.login#" > null="no"> > > > > Do I still need to wrap the arguments.login in "cfqueryparam" > statements? > > > > -- > Scott Stewart > ColdFusion Developer > 4405 Oakshyre Way > Raleigh, NC 27616 > (h) 919.874.6229 (c) 703.220.2835 > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320164 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam with cfstoredprocparam
No, the stored procedure will look after it. Rob On Fri, Mar 6, 2009 at 10:09 AM, Scott Stewart wrote: > > Hey all, > > > > I'm calling a stored procedure using cfstoredproc. > > Within the cfstoredproc I have two procparams, > > The values are function arguments > > > > They currently look like this: > > value="#arguments.login#" > null="no"> > > > > Do I still need to wrap the arguments.login in "cfqueryparam" statements? > > > > -- > Scott Stewart > ColdFusion Developer > 4405 Oakshyre Way > Raleigh, NC 27616 > (h) 919.874.6229 (c) 703.220.2835 > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320163 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and LIKE operator error
It is as Dave suggested, you can not use CFQUERYPARAM as part of the actual SQL query. You are writting the WHERE clause as a variable, remove the queryparam from that since you are declaring the variable. From: JediHomer Sent: Wednesday, December 17, 2008 3:17 AM To: cf-talk Subject: Re: cfqueryparam and LIKE operator error Try wrapping the QueryParams... i.e. Change (P.product_code LIKE value="%tents%">) to (P.product_code LIKE (value="%tents%">)) HTH 2008/12/16 Mike Little : > hi guys, > > getting an error using the following syntax... > > WHERE ((P.product_code LIKE ) OR (PD.product_title LIKE ) OR > (PD.product_description LIKE )) > > the error i get is... > > You have an error in your SQL syntax; check the manual that corresponds to > your MySQL server version for the right syntax to use near ') OR > (PD.product_title ' at line 9 > > this was working fine till i added the cfqueryparams. > > mike > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316855 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and LIKE operator error
Try wrapping the QueryParams... i.e. Change (P.product_code LIKE ) to (P.product_code LIKE ()) HTH 2008/12/16 Mike Little : > hi guys, > > getting an error using the following syntax... > > WHERE ((P.product_code LIKE value="%tents%">) OR (PD.product_title LIKE cfsqltype="cf_sql_varchar" value="%tents%">) OR (PD.product_description LIKE > )) > > the error i get is... > > You have an error in your SQL syntax; check the manual that corresponds to > your MySQL server version for the right syntax to use near ') OR > (PD.product_title ' at line 9 > > this was working fine till i added the cfqueryparams. > > mike > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316854 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and LIKE operator error
On Tuesday 16 Dec 2008, Mike Little wrote: > WHERE #PreserveSingleQuotes(boolsearch)# Note that doesn't protect against SQL injection. -- Tom Chiverton Helping to dramatically reintermediate 24/7 low-risk cross-platform applications This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office together with a list of those non members who are referred to as partners. We use the word partner to refer to a member of the LLP, or an employee or consultant with equivalent standing and qualifications. Regulated by the Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316853 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and LIKE operator error
> in my query, i have... > > WHERE #PreserveSingleQuotes(boolsearch)# > > boolsearch is a string that is generated based on the search string. You can only use CFQUERYPARAM within a query. You can't build a string with CFQUERYPARAM, then use it in a query. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316832 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and LIKE operator error
in my query, i have... WHERE #PreserveSingleQuotes(boolsearch)# boolsearch is a string that is generated based on the search string. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316830 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and LIKE operator error
ah, it comes from a function matt. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316829 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam and LIKE operator error
The generated SQL has the tags in it There's something wrong with that picture. How are you writing that query? On Tue, Dec 16, 2008 at 3:13 PM, Mike Little wrote: > jake this is the actual dump... > > SELECT P.pid, P.price, P.price_sale, P.bid, P.display, P.views, PD.ptitle, > PD.availability > FROM product P > INNER JOIN product_description PD ON P.pid = PD.pid > INNER JOIN product_to_categories PTC ON P.pid = PTC.pid > INNER JOIN categories C ON PTC.cid = C.cid > INNER JOIN categories_description CD ON C.cid = CD.cid > INNER JOIN brand B ON P.bid = B.bid > WHERE ((P.code LIKE value="%tents%">) OR (PD.ptitle LIKE cfsqltype="cf_sql_varchar" value="%tents%">) OR (PD.pdesc LIKE cfsqltype="cf_sql_varchar" value="%tents%">) OR (PD.fill_type LIKE > ) OR (PD.summary > LIKE ) OR > (PD.features LIKE ) > OR (CD.ctitle LIKE value="%tents%">) OR (B.btitle LIKE value="%tents%">)) > AND P.display = 1 > GROUP BY P.pid > ORDER BY PD.ptitle ASC > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316827 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and LIKE operator error
jake this is the actual dump... SELECT P.pid, P.price, P.price_sale, P.bid, P.display, P.views, PD.ptitle, PD.availability FROM product P INNER JOIN product_description PD ON P.pid = PD.pid INNER JOIN product_to_categories PTC ON P.pid = PTC.pid INNER JOIN categories C ON PTC.cid = C.cid INNER JOIN categories_description CD ON C.cid = CD.cid INNER JOIN brand B ON P.bid = B.bid WHERE ((P.code LIKE ) OR (PD.ptitle LIKE ) OR (PD.pdesc LIKE ) OR (PD.fill_type LIKE ) OR (PD.summary LIKE ) OR (PD.features LIKE ) OR (CD.ctitle LIKE ) OR (B.btitle LIKE )) AND P.display = 1 GROUP BY P.pid ORDER BY PD.ptitle ASC ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316826 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam and LIKE operator error
What does the rendered query transaction look like? It should be somewhere in the error dump. Mike Little wrote: > hi guys, > > getting an error using the following syntax... > > WHERE ((P.product_code LIKE value="%tents%">) OR (PD.product_title LIKE cfsqltype="cf_sql_varchar" value="%tents%">) OR (PD.product_description LIKE > )) > > the error i get is... > > You have an error in your SQL syntax; check the manual that corresponds to > your MySQL server version for the right syntax to use near ') OR > (PD.product_title ' at line 9 > > this was working fine till i added the cfqueryparams. > > mike > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316825 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
Ok, makes sense. We use a CFC here that I built a long time ago that builds insert/update queries based upon the database's meta data. It puts in the cfqueryparams and does data validation prior to that. While the cfqueryparams were put in for those very reasons, the seen benefit by anyone using it is the fact they no longer have to write those queries. They just pass in typically the form structure, an action flag and the table name then it does the rest. I could see them still liking an SP that did it then the wrapper for the SP would need all the appropriate checks on the data coming in to hopefully avoid the possibility of a SQL injection attack. On Thu, Oct 30, 2008 at 2:57 PM, Adrian Lynch <[EMAIL PROTECTED]>wrote: > EXEC()ing a string won't produde the same execution plan as the base SQL > (<---<< a guess) and you lose cfqueryparam and cfprocparam's biggest > benefit, protecting against injection. > > Adrian > > -Original Message- > From: Aaron Rouse > Sent: 30 October 2008 19:52 > To: cf-talk > Subject: Re: cfqueryparam vs cfstoredproc? > > > I do you feel it would defeat the point? > > On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch > <[EMAIL PROTECTED]>wrote: > > > Exactly, which kinda defeats the point I feel. > > > > I've got a few ways that I might try but for now I'm back to writing SPs. > > > > If anyone's interested, I have the full DAO code here: > > > > http://adrianlynch.co.uk/post.cfm?postID=21 > > > > Adrian > > Building a database of ColdFusion errors at http://cferror.org/ > > > > -Original Message- > > From: [EMAIL PROTECTED] > > Sent: 30 October 2008 18:28 > > To: cf-talk > > Subject: RE: cfqueryparam vs cfstoredproc? > > > > > > exec() > > or sp_executesql > > > > You would need to pass in the arguments as a list to the procedure and > > then do the looping and building of a dynamic query with SQL. Then > > execute what you have created. > > > > Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you > > will have to take additional steps to paramaterize it. (requires > > sp_executesql) > > > > FYI: My advice assumes MS SQL. > > > > ~Brad > > > > Original Message > > Subject: RE: cfqueryparam vs cfstoredproc? > > From: "Adrian Lynch" <[EMAIL PROTECTED]> > > Date: Thu, October 30, 2008 1:06 pm > > To: cf-talk > > > > An open question then... > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314633 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam vs cfstoredproc?
EXEC()ing a string won't produde the same execution plan as the base SQL (<---<< a guess) and you lose cfqueryparam and cfprocparam's biggest benefit, protecting against injection. Adrian -Original Message- From: Aaron Rouse Sent: 30 October 2008 19:52 To: cf-talk Subject: Re: cfqueryparam vs cfstoredproc? I do you feel it would defeat the point? On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch <[EMAIL PROTECTED]>wrote: > Exactly, which kinda defeats the point I feel. > > I've got a few ways that I might try but for now I'm back to writing SPs. > > If anyone's interested, I have the full DAO code here: > > http://adrianlynch.co.uk/post.cfm?postID=21 > > Adrian > Building a database of ColdFusion errors at http://cferror.org/ > > -Original Message- > From: [EMAIL PROTECTED] > Sent: 30 October 2008 18:28 > To: cf-talk > Subject: RE: cfqueryparam vs cfstoredproc? > > > exec() > or sp_executesql > > You would need to pass in the arguments as a list to the procedure and > then do the looping and building of a dynamic query with SQL. Then > execute what you have created. > > Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you > will have to take additional steps to paramaterize it. (requires > sp_executesql) > > FYI: My advice assumes MS SQL. > > ~Brad > > Original Message > Subject: RE: cfqueryparam vs cfstoredproc? > From: "Adrian Lynch" <[EMAIL PROTECTED]> > Date: Thu, October 30, 2008 1:06 pm > To: cf-talk > > An open question then... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314632 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
erf ... I meant "Why do you feel it would defeat the point?" On Thu, Oct 30, 2008 at 2:51 PM, Aaron Rouse <[EMAIL PROTECTED]> wrote: > I do you feel it would defeat the point? > > > On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch <[EMAIL PROTECTED]>wrote: > >> Exactly, which kinda defeats the point I feel. >> >> I've got a few ways that I might try but for now I'm back to writing SPs. >> >> If anyone's interested, I have the full DAO code here: >> >> http://adrianlynch.co.uk/post.cfm?postID=21 >> >> Adrian >> Building a database of ColdFusion errors at http://cferror.org/ >> >> -Original Message- >> From: [EMAIL PROTECTED] >> Sent: 30 October 2008 18:28 >> To: cf-talk >> Subject: RE: cfqueryparam vs cfstoredproc? >> >> >> exec() >> or sp_executesql >> >> You would need to pass in the arguments as a list to the procedure and >> then do the looping and building of a dynamic query with SQL. Then >> execute what you have created. >> >> Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you >> will have to take additional steps to paramaterize it. (requires >> sp_executesql) >> >> FYI: My advice assumes MS SQL. >> >> ~Brad >> >> Original Message >> Subject: RE: cfqueryparam vs cfstoredproc? >> From: "Adrian Lynch" <[EMAIL PROTECTED]> >> Date: Thu, October 30, 2008 1:06 pm >> To: cf-talk >> >> An open question then... >> >> ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314631 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
I do you feel it would defeat the point? On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch <[EMAIL PROTECTED]>wrote: > Exactly, which kinda defeats the point I feel. > > I've got a few ways that I might try but for now I'm back to writing SPs. > > If anyone's interested, I have the full DAO code here: > > http://adrianlynch.co.uk/post.cfm?postID=21 > > Adrian > Building a database of ColdFusion errors at http://cferror.org/ > > -Original Message- > From: [EMAIL PROTECTED] > Sent: 30 October 2008 18:28 > To: cf-talk > Subject: RE: cfqueryparam vs cfstoredproc? > > > exec() > or sp_executesql > > You would need to pass in the arguments as a list to the procedure and > then do the looping and building of a dynamic query with SQL. Then > execute what you have created. > > Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you > will have to take additional steps to paramaterize it. (requires > sp_executesql) > > FYI: My advice assumes MS SQL. > > ~Brad > > Original Message > Subject: RE: cfqueryparam vs cfstoredproc? > From: "Adrian Lynch" <[EMAIL PROTECTED]> > Date: Thu, October 30, 2008 1:06 pm > To: cf-talk > > An open question then... > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314630 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam vs cfstoredproc?
Exactly, which kinda defeats the point I feel. I've got a few ways that I might try but for now I'm back to writing SPs. If anyone's interested, I have the full DAO code here: http://adrianlynch.co.uk/post.cfm?postID=21 Adrian Building a database of ColdFusion errors at http://cferror.org/ -Original Message- From: [EMAIL PROTECTED] Sent: 30 October 2008 18:28 To: cf-talk Subject: RE: cfqueryparam vs cfstoredproc? exec() or sp_executesql You would need to pass in the arguments as a list to the procedure and then do the looping and building of a dynamic query with SQL. Then execute what you have created. Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you will have to take additional steps to paramaterize it. (requires sp_executesql) FYI: My advice assumes MS SQL. ~Brad Original Message ---- Subject: RE: cfqueryparam vs cfstoredproc? From: "Adrian Lynch" <[EMAIL PROTECTED]> Date: Thu, October 30, 2008 1:06 pm To: cf-talk An open question then... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314629 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam vs cfstoredproc?
exec() or sp_executesql You would need to pass in the arguments as a list to the procedure and then do the looping and building of a dynamic query with SQL. Then execute what you have created. Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you will have to take additional steps to paramaterize it. (requires sp_executesql) FYI: My advice assumes MS SQL. ~Brad Original Message Subject: RE: cfqueryparam vs cfstoredproc? From: "Adrian Lynch" <[EMAIL PROTECTED]> Date: Thu, October 30, 2008 1:06 pm To: cf-talk An open question then... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314623 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam vs cfstoredproc?
An open question then... I have a function that takes optional arguments for each of the columns in a table like this: INSERT INTO [comment] ( #separator# [column1] #separator# [column2] ) VALUES ( #column1# #separator# ) SELECT SCOPE_IDENTITY() [commentID] This allows me to do an insert with any combination of columns using named arguments: I have similar ones for updating, selecting and deleting. So, how best to replicate this using stored procedures? The place I'm working at the moment won't allow cfquery so I can't use my beautiful code generator :O( Adrian Building a database of ColdFusion errors at http://cferror.org/ -Original Message- From: Craigsell Sent: 30 October 2008 17:45 To: cf-talk Subject: Re: cfqueryparam vs cfstoredproc? My 2 cents I use ORACLE stored procs exclusively (using a CFSTOREDPROC tag) and have found them to be great. There are things I can do in stored procs that would be difficult to do in a CFC. I can easily have multiple datasets returned in one call. And the CFPROCPARAM gives me the same benefits as CFQUERYPARAM. I'm a big believer in doing database things on the database and display stuff in the web server. I'll confess though that I don't use CF much anymore except for CFCs-- most everything I do is in Flex. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314622 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
My 2 cents I use ORACLE stored procs exclusively (using a CFSTOREDPROC tag) and have found them to be great. There are things I can do in stored procs that would be difficult to do in a CFC. I can easily have multiple datasets returned in one call. And the CFPROCPARAM gives me the same benefits as CFQUERYPARAM. I'm a big believer in doing database things on the database and display stuff in the web server. I'll confess though that I don't use CF much anymore except for CFCs-- most everything I do is in Flex. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314621 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam vs cfstoredproc?
Something of note... If you are using cfqueryparam all of the variables need to be bound. Leaving any variable "hanging out there" will not allow you to take advantage of the execution plan - even if it's a constant. This query SELECT col1,col2 FROMusers WHERE active = 1 AND userName = Will not pop the exec plan cache because the server will need to evaluate the "1" after active to type it as an int. To make it work it would need to be written as: SELECT col1,col2 FROMusers WHERE active = AND userName = Of course the top query is quite safe from injection.. It just has no chance of hitting the cache. In addition, server configuration issues on the SQL server will determine how effective it is at hitting the cache. By default it does a pretty good job, but it can need adjusting... Say when there are a few hundred databases for example. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Alan Rother [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2008 11:28 AM To: cf-talk Subject: Re: cfqueryparam vs cfstoredproc? Interesting... I thought the same thing until I ran these tests. I analyzed the results with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion, and watched them execute through SQL Profiler, all of them showed better execution times when I removed the CFQUERYPARAMs. Now, this was on a limited subset of my queries. I would still argue that using it is far better than not. Most of the Queries I have used it in I did see a performance improvement in. =] ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314620 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam vs cfstoredproc?
Interesting, indeed. Wonder if there's an issue of table scan vs index and how the initial execution plans are getting cached. Definitely something to keep your eye on! >Interesting... >I thought the same thing until I ran these tests. I analyzed the results >with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion, >and watched them execute through SQL Profiler, all of them showed better >execution times when I removed the CFQUERYPARAMs. Now, this was on a limited >subset of my queries. I would still argue that using it is far better than >not. Most of the Queries I have used it in I did see a performance >improvement in. > >=] ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314618 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
Interesting... I thought the same thing until I ran these tests. I analyzed the results with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion, and watched them execute through SQL Profiler, all of them showed better execution times when I removed the CFQUERYPARAMs. Now, this was on a limited subset of my queries. I would still argue that using it is far better than not. Most of the Queries I have used it in I did see a performance improvement in. =] On Thu, Oct 30, 2008 at 9:18 AM, Jason Fisher <[EMAIL PROTECTED]> wrote: > Alan, > > SQL Server will create an execution plan for each query that gets run, > where the plan is specific to the final Query definition. From SQL Server's > perspective, these are 2 different queries, so each gets its own plan: > > Query with both names: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = @param1 > >AND Fname = @param2 > >AND Lname = @param3 > > Query with last name only: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = @param1 > >AND Lname = @param2 > > So, every query I run with only a Last Name filter will re-use that 2nd > query plan, and that increases performance. Without using CFQUERYPARAM at > all, every instance of the query is 'new' and that should (in theory) kill > your performance, not boost it. In other words, the following queries would > have the same plan with params but are each 'new' and distinct without > params: > > Query with last name only: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = '1234' > >AND Lname = 'Smith' > > Query with last name only: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = '1234' > >AND Lname = 'Johnson' > > I'd be curious to see what the Analyzer had to say on the DB server side > about the query plans for running several hundred unique queries vs several > hundred recurrences of a few plans. > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314616 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
Alan, SQL Server will create an execution plan for each query that gets run, where the plan is specific to the final Query definition. From SQL Server's perspective, these are 2 different queries, so each gets its own plan: Query with both names: >SELECT ID, FName, LName, Email >FROM SomeTable >WHERE >IsActive = 1 >AND >ClientCode = @param1 >AND Fname = @param2 >AND Lname = @param3 Query with last name only: >SELECT ID, FName, LName, Email >FROM SomeTable >WHERE >IsActive = 1 >AND >ClientCode = @param1 >AND Lname = @param2 So, every query I run with only a Last Name filter will re-use that 2nd query plan, and that increases performance. Without using CFQUERYPARAM at all, every instance of the query is 'new' and that should (in theory) kill your performance, not boost it. In other words, the following queries would have the same plan with params but are each 'new' and distinct without params: Query with last name only: >SELECT ID, FName, LName, Email >FROM SomeTable >WHERE >IsActive = 1 >AND >ClientCode = '1234' >AND Lname = 'Smith' Query with last name only: >SELECT ID, FName, LName, Email >FROM SomeTable >WHERE >IsActive = 1 >AND >ClientCode = '1234' >AND Lname = 'Johnson' I'd be curious to see what the Analyzer had to say on the DB server side about the query plans for running several hundred unique queries vs several hundred recurrences of a few plans. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314615 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam vs cfstoredproc?
Let's hope you don't ever need to handle more than one result set. :)
Also, that requires you get the return code manually as well.
~Brad
Original Message
Subject: Re: cfqueryparam vs cfstoredproc?
From: "morgan l" <[EMAIL PROTECTED]>
We call stored procedures using cfqueryparam:
EXEC StoredProcName
@ParamName =
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314614
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam vs cfstoredproc?
Original Message Subject: cfqueryparam vs cfstoredproc? From: "Marie Taylore" <[EMAIL PROTECTED]> > I realize with stored procedures you have a lot more power in terms of SQL > scripting, This is not really true. You can put anything you want in a cfquery block. temp tables, CTE's, sp_commandshell, you name it. Procedures to not implicitly allow for any additional functionality other than the possibility of being called easily from other parts of your database. > but for basic queries, is CFQUERYPARAM just as fast as (or faster > than) running CFSTOREDPROC? There are no significant performance differences between running the same piece of sql as a paramaterized cfquery, or as a stored procedure. The biggest difference, is the amount of text that gets sent over the wire to the SQL server. "select * from ..." vs "execute sp_etc" > Stored Procedures - can contain advanced SQL & procedural code. Like above, your stored proc can't do anything your inline query can't > Encapsulate code outside of you application for a layer of abstraction. Now, you're talking. This is, in my opinion, one of the most useful features of stored procs. This is most readily apparent if your app does not use some form of data abstraction layer like DAOs. Additionally, if you have business logic in your SQL, (which is common though I recommend against it) placing that logic in a proc would make it possible for another process (Java, .NET, etc.) to reuse it at the database level. I have worked on applications where the basic API was comprised of hundreds of stored procedures full of business logic. > CFQUERYPARAM - allows non-DBAs to take advantage of pre-compiled queries, > providing speedier and more secured code. You can "encapsulate" much the > same way a stored procedure does with CFCs. Speedier than an ad-hoc query perhaps. Watch out for blanket statements about paramaterized performance though: http://www.codersrevolution.com/index.cfm/2008/7/26/cfqueryparam-its-not-just-for-security-also-when-NOT-to-use-it Wrapping data access in a CFC is my preferred method of abstracting and reusing SQL in my application, however that is really a CFC vs Proc thing and not a cfqueryparam vs proc conversation. > Would love to hear from others on the advantages/disadvantages of each I like stored procs for reusability and organization (SQL code is easier to read and edit in a SQL IDE as opposed to a CF IDE). I however, do not use them for security nor performance. ~Brad ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314612 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam vs cfstoredproc?
Marie, In my experience with SQL Server there is zero notable performance difference between well-formed SQL in a stored proc and the same well-formed SQL in a CFQUERY with CFQUERYPARAM: both gain from the built-in performance tuning of the data server. Also, note that you can run nearly any code directly between the CFQUERY tags that you could put in a stored proc. Yes, you can do multi-statement (just end each with ; like normal) and you can do cursors and you can declare database vars, all within a query in CFQUERY. As to which is better, in my opinion that depends primarily on who's responsible for writing the database calls. As a developer who also writes and optimizes the DB calls, I love having all my code in one searchable library = my CFML pages. If I need to change a data structure, then all changes, both code and DB side, can be found in a single codebase search and changed all at once and checked into one version control system (my CFM repository). On the other hand, if you have a separate person / group doing the DB work, it probably makes more sense to let them live in the stored proc world, especially if they're more comfortable there. HTH ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314610 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
The only issue I have run into with CFQUERYPARAM is that is can degrade performance on dynamic queries. This is an inherent issue in what CFQUERYPARAM does, it essentially makes your queries into stored procs, if you actually watch the traffic flow through a MS SQL Server for example, you will see your app is actually calling what appear to be stored procs. The performance issue manifest when your query has dynamic bits, such as the following example: SELECT ID, FName, LName, Email FROM SomeTable WHERE IsActive = 1 AND ClientCode = AND Fname = AND Lname = The first time CF processes this query, it creates some sort of memory based or temporary stored proc for it based on the structure of the query as it was run in this instance. It builds up an image of the query based on the CFQUERYPARAMS used. Now if the next time the query is executed one or more of the IF statements has a different result, thus including or excluding one or more, then CF has to recompile the temporary stored proc. This can cause a small performance loss. In some performance tuning I did on an app earlier this year, I had 1 query that was executed several thousand times in a long looping process. Nearly every time it was hit it was different and forced a recompile. I removed all of the CFQUERYPARAMs from it and it's average execution time went from 350ms to 10ms. Obviously, if this is a query that only executes occasionally, the difference between 350ms and 10 ms is nothing to worry about, but over millions of executions a day, it adds up. =] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314609 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
We call stored procedures using cfqueryparam: EXEC StoredProcName @ParamName = On Thu, Oct 30, 2008 at 10:49 AM, Marie Taylore <[EMAIL PROTECTED]>wrote: > Question... the more I read about CFQUERYPARAM the more it seems it > mitigates many of the problems that using stored procedures also solves. I > realize with stored procedures you have a lot more power in terms of SQL > scripting, but for basic queries, is CFQUERYPARAM just as fast as (or > faster > than) running CFSTOREDPROC? > > For a CFSTOREDPROC vs CFQUERYPARAM "debate" what would be the "better > thans" > on each side of the argument? > > A few I can think of off the top of my head would be: > > Stored Procedures - can contain advanced SQL & procedural code. > Encapsulate > code outside of you application for a layer of abstraction. > > CFQUERYPARAM - allows non-DBAs to take advantage of pre-compiled queries, > providing speedier and more secured code. You can "encapsulate" much the > same way a stored procedure does with CFCs. > > Would love to hear from others on the advantages/disadvantages of each > > Thanks! > > Marie > > keywords for searching: cfprocparam vs cfqueryparam, cfqueryparam vs > cfprocparam, cfprocresult, cfquery, cftransaction, cfupdate, cfinsert > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314608 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam vs cfstoredproc?
SQL in your CF code can be made dynamic more easily. The SP equivalent involves a SQL string and EXEC(). Not pretty. Adrian Building a database of ColdFusion errors at http://cferror.org/ -Original Message- From: Marie Taylore Sent: 30 October 2008 15:49 To: cf-talk Subject: cfqueryparam vs cfstoredproc? Question... the more I read about CFQUERYPARAM the more it seems it mitigates many of the problems that using stored procedures also solves. I realize with stored procedures you have a lot more power in terms of SQL scripting, but for basic queries, is CFQUERYPARAM just as fast as (or faster than) running CFSTOREDPROC? For a CFSTOREDPROC vs CFQUERYPARAM "debate" what would be the "better thans" on each side of the argument? A few I can think of off the top of my head would be: Stored Procedures - can contain advanced SQL & procedural code. Encapsulate code outside of you application for a layer of abstraction. CFQUERYPARAM - allows non-DBAs to take advantage of pre-compiled queries, providing speedier and more secured code. You can "encapsulate" much the same way a stored procedure does with CFCs. Would love to hear from others on the advantages/disadvantages of each Thanks! Marie keywords for searching: cfprocparam vs cfqueryparam, cfqueryparam vs cfprocparam, cfprocresult, cfquery, cftransaction, cfupdate, cfinsert ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314607 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQueryParam question
> The list="yes" parameter of CFQUERYPARAM will properly wrap the single quotes
> around each list element.
That's not quite right. What it will do is create a parameter for
every element in the list and map it to the database type you supply.
Even with character data, no single quotes are used when using
cfqueryparam. Indeed, it may be important to know that you are sending
a different kind of request to the db when you use cfqueryparam. Ie.
SELECT foo
FROM bar
WHERE foo IN ()
gets translated by ColdFusion, which then sends something like the
following to the database server:
SQL Statement: SELECT foo FROM bar WHERE foo IN (?,?,?)
SQL Parameters:
* param 1 (varchar) = 'Hello mum'
* param 2 (varchar) = 'Hello world'
* param 3 (varchar) = 'I love chocolate'
Without using cfqueryparam, you would send the following to the db:
SQL Statement: SELECT foo FROM bar WHERE foo IN ('Hello mum','Hello
world','I love chocolate').
Further, better and useful quick reading:
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
HTH
Dominic
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314446
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQueryParam question
Try this: Thanks, Eric Cobb Scott Stewart wrote: > I have a variable passed though a URL that looks like this > > index.cfm?a=1,2,3 > > The variable "a" is passed to a SQL statement WHERE clause as > part of an "IN" operator > > IE: WHERE b in (#url.a#). > > How would I encapsulate "url.a" in a CFQueryParam properly, is this a > case where I wouldn't define the cfsqltype? > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314441 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CFQueryParam question
cfqp has a list attribute. Adrian Building a database of ColdFusion errors at http://cferror.org/ -Original Message- From: Scott Stewart Sent: 28 October 2008 17:13 To: cf-talk Subject: CFQueryParam question I have a variable passed though a URL that looks like this index.cfm?a=1,2,3 The variable "a" is passed to a SQL statement WHERE clause as part of an "IN" operator IE: WHERE b in (#url.a#). How would I encapsulate "url.a" in a CFQueryParam properly, is this a case where I wouldn't define the cfsqltype? -- Scott Stewart ColdFusion Developer Office of Research Information Systems Research & Economic Development University of North Carolina at Chapel Hill Phone:(919)843-2408 Fax: (919)962-3600 Email: [EMAIL PROTECTED] ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314440 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQueryParam question
WHERE b IN ( ) The list="yes" parameter of CFQUERYPARAM will properly wrap the single quotes around each list element. -Jason >I have a variable passed though a URL that looks like this > >index.cfm?a=1,2,3 > >The variable "a" is passed to a SQL statement WHERE clause as >part of an "IN" operator > >IE: WHERE b in (#url.a#). > >How would I encapsulate "url.a" in a CFQueryParam properly, is this a >case where I wouldn't define the cfsqltype? > >-- >Scott Stewart >ColdFusion Developer > >Office of Research Information Systems >Research & Economic Development >University of North Carolina at Chapel Hill > >Phone:(919)843-2408 >Fax: (919)962-3600 >Email: [EMAIL PROTECTED] ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314438 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQueryParam question
Obviously you would need to change the sqltype to what your sql type is. On Tue, Oct 28, 2008 at 10:13 AM, Scott Stewart <[EMAIL PROTECTED]>wrote: > I have a variable passed though a URL that looks like this > > index.cfm?a=1,2,3 > > The variable "a" is passed to a SQL statement WHERE clause as > part of an "IN" operator > > IE: WHERE b in (#url.a#). > > How would I encapsulate "url.a" in a CFQueryParam properly, is this a > case where I wouldn't define the cfsqltype? > > -- > Scott Stewart > ColdFusion Developer > > Office of Research Information Systems > Research & Economic Development > University of North Carolina at Chapel Hill > > Phone:(919)843-2408 > Fax: (919)962-3600 > Email: [EMAIL PROTECTED] > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314439 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam within a cfc
Thanks for the suggestions. I drastically shorten the code/query to get my question across. However, you make very good points! Thanks! Mike -Original Message- From: Peter Boughton [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2008 5:51 AM To: cf-talk Subject: Re: cfqueryparam within a cfc >Add to that list, locally scope the query. > > > Very good point - probably the most critical change to make. I can't believe I missed that. :'( ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:313317 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam within a cfc
>Add to that list, locally scope the query. > > > Very good point - probably the most critical change to make. I can't believe I missed that. :'( ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:313316 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam within a cfc
Add to that list, locally scope the query. Adrian -Original Message- From: Peter Boughton [mailto:[EMAIL PROTECTED] Sent: 01 October 2008 00:01 To: cf-talk Subject: Re: cfqueryparam within a cfc > > > > > > SELECT * FROM TABLE WHERE field = cfsqltype="cf_sql_integer"> > > > > > Is this over-kill or good practice? Use it always - it's safer and simpler that way. Since you're asking about good practice, I want to pick on a couple of things in your example. ;) First, the "SELECT *" - bad! You shouldn't ever use "SELECT *" in code. (Name just the fields you need; even if you need them all, name them anyway - it also helps readability and I've heard a couple of times of obscure bugs caused by "SELECT *" use.) Second, that should be Arguments.field_value - always scope variables. (Again, scoping helps performance, helps readability, and helps avoid annoying bugs.) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:313315 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam within a cfc
> > > > > > SELECT * FROM TABLE WHERE field = cfsqltype="cf_sql_integer"> > > > > > Is this over-kill or good practice? Use it always - it's safer and simpler that way. Since you're asking about good practice, I want to pick on a couple of things in your example. ;) First, the "SELECT *" - bad! You shouldn't ever use "SELECT *" in code. (Name just the fields you need; even if you need them all, name them anyway - it also helps readability and I've heard a couple of times of obscure bugs caused by "SELECT *" use.) Second, that should be Arguments.field_value - always scope variables. (Again, scoping helps performance, helps readability, and helps avoid annoying bugs.) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:313305 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
