subject:"CFQueryParam"


One of my table fields (and this occurs in many of my tables) is edited using 
CKEditor. This means the data will look like ptext data /p

I am using Access at the moment, converting to MySQL. I have not tried this 
with the MySQL database; it fails using the Access database. (But, I have a 
number of applications away from work which will continue using Access into the 
near future.)

My code in an insert query (CF10) is
cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar

The error is Application uses a value of the wrong type for the current 
operation.

Take out the p /p and it works fine, implying the paragraph tags are the 
problem. Take out the cfqueryparam  (i.e., '#CovNote#') and it works fine.

Any ideas?



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354407
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: cfqueryparam issue

2013-02-08 Thread Steve Milburn


Try to encode the string to base64 before adding it to the db. So it
becomes cfqueryparam value=#toBase64(conNote)#
cfsqltype=cf_sql_varchar /.  When you read the data back out of the db,
convert it back to a string like so: #toString(toBinary(field_name))#

HTH
Steve


On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu wrote:


 One of my table fields (and this occurs in many of my tables) is edited
 using CKEditor. This means the data will look like ptext data /p

 I am using Access at the moment, converting to MySQL. I have not tried
 this with the MySQL database; it fails using the Access database. (But, I
 have a number of applications away from work which will continue using
 Access into the near future.)

 My code in an insert query (CF10) is
 cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar

 The error is Application uses a value of the wrong type for the current
 operation.

 Take out the p /p and it works fine, implying the paragraph tags are
 the problem. Take out the cfqueryparam  (i.e., '#CovNote#') and it works
 fine.

 Any ideas?



 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354408
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: cfqueryparam issue

Thanks - but same error.

-Original Message-
From: Steve Milburn [mailto:scmilb...@gmail.com] 
Sent: Friday, February 08, 2013 11:56 AM
To: cf-talk
Subject: Re: cfqueryparam issue

Try to encode the string to base64 before adding it to the db. So it
becomes cfqueryparam value=#toBase64(conNote)#
cfsqltype=cf_sql_varchar /.  When you read the data back out of the db,
convert it back to a string like so: #toString(toBinary(field_name))#

HTH
Steve

On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu wrote:

 One of my table fields (and this occurs in many of my tables) is edited
 using CKEditor. This means the data will look like ptext data /p

 I am using Access at the moment, converting to MySQL. I have not tried
 this with the MySQL database; it fails using the Access database. (But, I
 have a number of applications away from work which will continue using
 Access into the near future.)

 My code in an insert query (CF10) is
 cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar

 The error is Application uses a value of the wrong type for the current
 operation.

 Take out the p /p and it works fine, implying the paragraph tags are
 the problem. Take out the cfqueryparam  (i.e., '#CovNote#') and it works
 fine.

 Any ideas?

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354409
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: cfqueryparam issue

2013-02-08 Thread Steve Milburn


What if you try to encode it prior to the cfqueryparam tag?

cfset tmp = toBase64(CovNote) /
cfqueryparam value=#tmp# cfsqltype=cf_sql_varchar /




On Fri, Feb 8, 2013 at 12:02 PM, Stephens, Larry V steph...@iu.edu wrote:


 Thanks - but same error.


 -Original Message-
 From: Steve Milburn [mailto:scmilb...@gmail.com]
 Sent: Friday, February 08, 2013 11:56 AM
 To: cf-talk
 Subject: Re: cfqueryparam issue


 Try to encode the string to base64 before adding it to the db. So it
 becomes cfqueryparam value=#toBase64(conNote)#
 cfsqltype=cf_sql_varchar /.  When you read the data back out of the db,
 convert it back to a string like so: #toString(toBinary(field_name))#

 HTH
 Steve


 On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu
 wrote:

 
  One of my table fields (and this occurs in many of my tables) is edited
  using CKEditor. This means the data will look like ptext data /p
 
  I am using Access at the moment, converting to MySQL. I have not tried
  this with the MySQL database; it fails using the Access database. (But, I
  have a number of applications away from work which will continue using
  Access into the near future.)
 
  My code in an insert query (CF10) is
  cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar
 
  The error is Application uses a value of the wrong type for the current
  operation.
 
  Take out the p /p and it works fine, implying the paragraph tags are
  the problem. Take out the cfqueryparam  (i.e., '#CovNote#') and it works
  fine.
 
  Any ideas?
 
 
 
 



 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354410
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: cfqueryparam issue


This gets stranger and stranger. It doesn't appear to have anything to do with 
the p tags. I've found all I have to do is remove some of the end of the 
message and it works. I played with several iterations and finally took off the 
last sentence and then it works okay.

Why the last sentence? Beats me. I worked up a program to look at the ASCII 
code of the entire string and everything fell into the 32 - 126 range except 
for some 10 (LF) and 9 (TAB) chars, and they don't seem to be a factor. No EOL 
shows up.

I'm at a loss.



 On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu
 wrote:

 
  One of my table fields (and this occurs in many of my tables) is edited
  using CKEditor. This means the data will look like ptext data /p
 
  I am using Access at the moment, converting to MySQL. I have not tried
  this with the MySQL database; it fails using the Access database. (But, I
  have a number of applications away from work which will continue using
  Access into the near future.)
 
  My code in an insert query (CF10) is
  cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar
 
  The error is Application uses a value of the wrong type for the current
  operation.
 
  Take out the p /p and it works fine, implying the paragraph tags are
  the problem. Take out the cfqueryparam  (i.e., '#CovNote#') and it works
  fine.
 
  Any ideas?
 
 
 
 



 



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354415
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: cfqueryparam issue

2013-02-08 Thread Cameron Childress


What is the datatype and length of the column in the Access database? This
sounds like a pretty standard data truncation problem.

-Cameron

On Fri, Feb 8, 2013 at 2:41 PM, Stephens, Larry V steph...@iu.edu wrote:

 This gets stranger and stranger. It doesn't appear to have anything to do
 with the p tags. I've found all I have to do is remove some of the end of
 the message and it works. I played with several iterations and finally took
 off the last sentence and then it works okay.

 Why the last sentence? Beats me. I worked up a program to look at the
 ASCII code of the entire string and everything fell into the 32 - 126 range
 except for some 10 (LF) and 9 (TAB) chars, and they don't seem to be a
 factor. No EOL shows up.

 I'm at a loss.


 
  On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu
  wrote:
 
  
   One of my table fields (and this occurs in many of my tables) is edited
   using CKEditor. This means the data will look like ptext data /p
  
   I am using Access at the moment, converting to MySQL. I have not tried
   this with the MySQL database; it fails using the Access database.
 (But, I
   have a number of applications away from work which will continue using
   Access into the near future.)
  
   My code in an insert query (CF10) is
   cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar
  
   The error is Application uses a value of the wrong type for the
 current
   operation.
  
   Take out the p /p and it works fine, implying the paragraph tags
 are
   the problem. Take out the cfqueryparam  (i.e., '#CovNote#') and it
 works
   fine.
  
   Any ideas?
  
  
  
  
 
 
 
 



 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354417
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: cfqueryparam issue

And, FWIW, converting it to base 64 doesn't get rid of whatever cfqueryparam is 
choking on. This is in the cfqueryparam tag as an insert without it works fine.

-Original Message-
From: Stephens, Larry V [mailto:steph...@iu.edu] 
Sent: Friday, February 08, 2013 2:41 PM
To: cf-talk
Subject: RE: cfqueryparam issue

This gets stranger and stranger. It doesn't appear to have anything to do with 
the p tags. I've found all I have to do is remove some of the end of the 
message and it works. I played with several iterations and finally took off the 
last sentence and then it works okay.

Why the last sentence? Beats me. I worked up a program to look at the ASCII 
code of the entire string and everything fell into the 32 - 126 range except 
for some 10 (LF) and 9 (TAB) chars, and they don't seem to be a factor. No EOL 
shows up.

I'm at a loss.

 On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu
 wrote:

  One of my table fields (and this occurs in many of my tables) is edited
  using CKEditor. This means the data will look like ptext data /p

  I am using Access at the moment, converting to MySQL. I have not tried
  this with the MySQL database; it fails using the Access database. (But, I
  have a number of applications away from work which will continue using
  Access into the near future.)

  My code in an insert query (CF10) is
  cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar

  The error is Application uses a value of the wrong type for the current
  operation.

  Take out the p /p and it works fine, implying the paragraph tags are
  the problem. Take out the cfqueryparam  (i.e., '#CovNote#') and it works
  fine.

  Any ideas?

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354418
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: cfqueryparam issue

2013-02-08 Thread Leigh


 What is the datatype and length of the column in the Access database? 

Also, which datasource type? I remember someone mentioning problems with Memo 
fields when using the unicode driver a ways back. They suggested using *_clob 
instead of *_longvarchar.

-Leigh



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354419
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: cfqueryparam issue

It's a memo field.

-Original Message-
From: Cameron Childress [mailto:camer...@gmail.com] 
Sent: Friday, February 08, 2013 2:52 PM
To: cf-talk
Subject: Re: cfqueryparam issue

What is the datatype and length of the column in the Access database? This
sounds like a pretty standard data truncation problem.

-Cameron

On Fri, Feb 8, 2013 at 2:41 PM, Stephens, Larry V steph...@iu.edu wrote:

 This gets stranger and stranger. It doesn't appear to have anything to do
 with the p tags. I've found all I have to do is remove some of the end of
 the message and it works. I played with several iterations and finally took
 off the last sentence and then it works okay.

 Why the last sentence? Beats me. I worked up a program to look at the
 ASCII code of the entire string and everything fell into the 32 - 126 range
 except for some 10 (LF) and 9 (TAB) chars, and they don't seem to be a
 factor. No EOL shows up.

 I'm at a loss.

  On Fri, Feb 8, 2013 at 11:18 AM, Stephens, Larry V steph...@iu.edu
  wrote:

   One of my table fields (and this occurs in many of my tables) is edited
   using CKEditor. This means the data will look like ptext data /p

   I am using Access at the moment, converting to MySQL. I have not tried
   this with the MySQL database; it fails using the Access database.
 (But, I
   have a number of applications away from work which will continue using
   Access into the near future.)

   My code in an insert query (CF10) is
   cfqueryparam value=#CovNote# cfsqltype=cf_sql_longvarchar

   The error is Application uses a value of the wrong type for the
 current
   operation.

   Take out the p /p and it works fine, implying the paragraph tags
 are
   the problem. Take out the cfqueryparam  (i.e., '#CovNote#') and it
 works
   fine.

   Any ideas?

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354420
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: cfqueryparam issue

Thank you - that did it. Does this apply to MySQL, too? (I'm porting the thing 
over...)

-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com] 
Sent: Friday, February 08, 2013 3:07 PM
To: cf-talk
Subject: Re: cfqueryparam issue

 What is the datatype and length of the column in the Access database? 

Also, which datasource type? I remember someone mentioning problems with Memo 
fields when using the unicode driver a ways back. They suggested using *_clob 
instead of *_longvarchar.

-Leigh

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354421
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: cfqueryparam issue

2013-02-08 Thread Leigh


Well MySQL has very different data types (and unlike Access, it uses a pure 
jdbc driver). So this probably does not apply to MySQL.

-Leigh



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354422
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

convert to cfqueryparam

2012-08-31 Thread daniel kessler


I have a line of Oracle pl/sql (9i) where I'm trying to add cfqueryparam in CF7.

I have:
#iif(len(trim(arguments.inStruct.a_person_id))eq 
0,DE(NULL),DE(#arguments.inStruct.a_person_id#))#,

a coworker suggested:
cfqueryparam value = #arguments.inStruct.a_person_id# CFSQLType = 
CF_SQL_NUMERIC null=#IIF(len(arguments.inStruct.a_person_id),0,1)#


But in reviewing it, I cannot tell if that is correct or not.  I've gone over 
the sql several times.  Also, I don't know DE().  

Any information on this is welcome.  Thank you. 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352378
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: convert to cfqueryparam

2012-08-31 Thread Dave Watts


 I have a line of Oracle pl/sql (9i) where I'm trying to add cfqueryparam in 
 CF7.

 I have:
 #iif(len(trim(arguments.inStruct.a_person_id))eq 
 0,DE(NULL),DE(#arguments.inStruct.a_person_id#))#,

 a coworker suggested:
 cfqueryparam value = #arguments.inStruct.a_person_id# CFSQLType = 
 CF_SQL_NUMERIC null=#IIF(len(arguments.inStruct.a_person_id),0,1)#

 But in reviewing it, I cannot tell if that is correct or not.  I've gone over 
 the sql several times.  Also, I don't know DE().

The DE function is kind of confusing, as it's only used within string
evaluation to ... delay that evaluation. So, in your starting case, DE
prevents its string argument from being evaluated as code. (You want
the literal value of arguments.inStruct.a_person_id, not to treat that
value as a CF expression from which you'd later get a literal value.)

Your coworker's suggestion may well be correct, but it can be
simplified. You don't need IIF in this case:

cfqueryparam ... null=#not len(arguments.inStruct.a_person_id)#

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite.

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352379
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: convert to cfqueryparam

2012-08-31 Thread daniel kessler


Ah I understand.  Great, thank you. 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:352380
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

MS SQL and cfqueryparam slow-down

2012-04-25 Thread Christopher Watson


We've been profiling our MS SQL traffic, and we're seeing the occasional spike 
in execution times related directly to the operations that CF perform in 
relation to the cfqueryparam tag in getting table column details. The problem 
crops up only on cfquery tags where cfqueryparam is used for values, and 
the specific operation to get column details hangs the process up for up to 45 
seconds. At this time, I'd like to ask if anyone else has seen this kind of 
behavior. Thank you! 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350833
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: MS SQL and cfqueryparam slow-down

2012-04-25 Thread Mike Stemle


It could be that you are not indexing the columns you're using variable data to 
search on. 

~ Mike Stemle, jr.

On Apr 25, 2012, at 12:18, Christopher Watson skyg...@gmail.com wrote:

 
 We've been profiling our MS SQL traffic, and we're seeing the occasional 
 spike in execution times related directly to the operations that CF perform 
 in relation to the cfqueryparam tag in getting table column details. The 
 problem crops up only on cfquery tags where cfqueryparam is used for 
 values, and the specific operation to get column details hangs the process up 
 for up to 45 seconds. At this time, I'd like to ask if anyone else has seen 
 this kind of behavior. Thank you! 
 
 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350834
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: MS SQL and cfqueryparam slow-down

2012-04-25 Thread Christopher Watson


Actually, the prolonged execution times are for simple tables where the proper 
indexing has been in place.

For assessment purposes, we have removed the cfqueryparam tags from that set 
of queries in our processes that manipulate the tables on which we are seeing 
the degradation in the profiler. We will look at the profiler data again in the 
morning to see if any of the same prolonged execution times exist. If not, then 
we'll stick with no cfqueryparam tags, since this is all related to tables 
and column data types and values where we have complete internalized control, 
and there really is no compelling reason to have the cfqueryparam tags in 
there. We'd LIKE to have them in there anyway, because of the other benefits 
realized. But if using them causes this degradation, and removing them does 
not, we'll opt for the latter.

 It could be that you are not indexing the columns you're using 
 variable data to search on. 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350837
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: MS SQL and cfqueryparam slow-down

2012-04-25 Thread Russ Michaels


Cfqueryparam has 2 purposes, to paramatise the params which adds your
injection security and to create a prepared statement for better execution
plan.
It seems the execution plan is where u need to look for problems.
But a workaround would be to use cfparam outside the cfquery instead if
cfqueryparam, which would do the same job as far as sql injection
protection from paramatisation goes.

Regards
Russ Michaels
From my mobile
On 25 Apr 2012 20:37, Christopher Watson skyg...@gmail.com wrote:


 Actually, the prolonged execution times are for simple tables where the
 proper indexing has been in place.

 For assessment purposes, we have removed the cfqueryparam tags from that
 set of queries in our processes that manipulate the tables on which we are
 seeing the degradation in the profiler. We will look at the profiler data
 again in the morning to see if any of the same prolonged execution times
 exist. If not, then we'll stick with no cfqueryparam tags, since this is
 all related to tables and column data types and values where we have
 complete internalized control, and there really is no compelling reason to
 have the cfqueryparam tags in there. We'd LIKE to have them in there
 anyway, because of the other benefits realized. But if using them causes
 this degradation, and removing them does not, we'll opt for the latter.

  It could be that you are not indexing the columns you're using
  variable data to search on.

 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350838
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: MS SQL and cfqueryparam slow-down

2012-04-25 Thread Brian Thornton


What is the ODBC driver in play here?

On Wed, Apr 25, 2012 at 4:21 PM, Russ Michaels r...@michaels.me.uk wrote:

 Cfqueryparam has 2 purposes, to paramatise the params which adds your
 injection security and to create a prepared statement for better execution
 plan.
 It seems the execution plan is where u need to look for problems.
 But a workaround would be to use cfparam outside the cfquery instead if
 cfqueryparam, which would do the same job as far as sql injection
 protection from paramatisation goes.

 Regards
 Russ Michaels
 From my mobile
 On 25 Apr 2012 20:37, Christopher Watson skyg...@gmail.com wrote:


 Actually, the prolonged execution times are for simple tables where the
 proper indexing has been in place.

 For assessment purposes, we have removed the cfqueryparam tags from that
 set of queries in our processes that manipulate the tables on which we are
 seeing the degradation in the profiler. We will look at the profiler data
 again in the morning to see if any of the same prolonged execution times
 exist. If not, then we'll stick with no cfqueryparam tags, since this is
 all related to tables and column data types and values where we have
 complete internalized control, and there really is no compelling reason to
 have the cfqueryparam tags in there. We'd LIKE to have them in there
 anyway, because of the other benefits realized. But if using them causes
 this degradation, and removing them does not, we'll opt for the latter.

  It could be that you are not indexing the columns you're using
  variable data to search on.



 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350839
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

cfqueryparam problem

2011-10-07 Thread Kris Sisk


I have this query in a remote accessible cfc:
cfquery datasource=staff name=rq
 SELECT *
 FROM intervention
 WHERE studentId = cfqueryparam value=#arguments.id# 
cfsqltype=cf_sql_integer  
 AND year=cfqueryparam value=#arguments.year# cfsqltype=cf_sql_integer
/cfquery

At issue is the second cfqueryparam. The arguments are passed in by a get 
request. When I pass in 2012 I get this error: [Macromedia][SQLServer JDBC 
Driver]Value can not be converted to requested type. Trying to pass it in as an 
integer gets the same error. The odd thing is that any other value passed in 
works right, but 2012 throws an error every time. Any suggestions? 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348002
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: cfqueryparam problem

2011-10-07 Thread Wil Genovese


Try this.

http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B270-3056B422E2F6FCAB



Wil Genovese
Sr. Web Application Developer/
Systems Administrator
CF Webtools
www.cfwebtools.com

wilg...@trunkful.com
www.trunkful.com

On Oct 7, 2011, at 3:56 PM, Kris Sisk wrote:

 
 I have this query in a remote accessible cfc:
 cfquery datasource=staff name=rq
 SELECT *
 FROM intervention
 WHERE studentId = cfqueryparam value=#arguments.id# 
 cfsqltype=cf_sql_integer  
 AND year=cfqueryparam value=#arguments.year# cfsqltype=cf_sql_integer
 /cfquery
 
 At issue is the second cfqueryparam. The arguments are passed in by a get 
 request. When I pass in 2012 I get this error: [Macromedia][SQLServer JDBC 
 Driver]Value can not be converted to requested type. Trying to pass it in as 
 an integer gets the same error. The odd thing is that any other value passed 
 in works right, but 2012 throws an error every time. Any suggestions? 
 
 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348003
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: cfqueryparam problem

2011-10-07 Thread Kris Sisk


That got it. Thanks, I was going nuts trying to figure it out.

 Try this.
 
 http://www.coldfusionjedi.com/index.
 cfm?mode=entryentry=7D417738-DF64-B270-3056B422E2F6FCAB
 
 
 
 Wil Genovese
 Sr. Web Application Developer/
 Systems Administrator
 CF Webtools
 www.cfwebtools.com
 
 wilg...@trunkful.com
 www.trunkful.com
 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348004
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: cfqueryparam problem

2011-10-07 Thread Wil Genovese


Cool - The only reason I knew about this was that one of our developers had 
just ran into the same issue here today and she spent time researching it.  She 
just told us about it a minute before you posted to HOF.

I think is is solar flare related - totally unrelated systems having the same 
error at the same time. Must be solar flares..  Where's my tin hat?






Wil Genovese
Sr. Web Application Developer/
Systems Administrator
CF Webtools
www.cfwebtools.com

wilg...@trunkful.com
www.trunkful.com

On Oct 7, 2011, at 4:11 PM, Kris Sisk wrote:

 
 That got it. Thanks, I was going nuts trying to figure it out.
 
 Try this.
 
 http://www.coldfusionjedi.com/index.
 cfm?mode=entryentry=7D417738-DF64-B270-3056B422E2F6FCAB
 
 
 
 Wil Genovese
 Sr. Web Application Developer/
 Systems Administrator
 CF Webtools
 www.cfwebtools.com
 
 wilg...@trunkful.com
 www.trunkful.com
 
 
 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:348005
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

cfqueryparam sqltype for text field?

2011-09-07 Thread Mike Kear


I am having an issue updating a record that has a text field
(MSSQLServer2005).   I'm using cfqueryparam and the docs are silent
about what is the correct type for a MSSQL Text field.   It's
rejecting the update, saying The data types text and varchar are
incompatible in the equal to operator

I've tried setting the sqltype to varchar,  and longvarchar but it
still gives the error. I've never had this problem updating
records with text fields before.

What's the correct sqltype to use for a MSSQLServer Text field type?

--
Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/mon

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347266
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: cfqueryparam sqltype for text field?

2011-09-07 Thread Mike Kear


OH wait.I've done it again slams forehead on the desk  the
problem was i was updating the record using the text field in the
WHERE clause.

Bad bad bad me.

Sorry about that, everyone.   We return you to normal programming.

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month


On Wed, Sep 7, 2011 at 5:46 PM, Mike Kear afpwebwo...@gmail.com wrote:
 I am having an issue updating a record that has a text field
 (MSSQLServer2005).   I'm using cfqueryparam and the docs are silent
 about what is the correct type for a MSSQL Text field.   It's
 rejecting the update, saying The data types text and varchar are
 incompatible in the equal to operator

 I've tried setting the sqltype to varchar,  and longvarchar but it
 still gives the error.     I've never had this problem updating
 records with text fields before.

 What's the correct sqltype to use for a MSSQLServer Text field type?

 --
 Cheers
 Mike Kear
 Windsor, NSW, Australia
 Adobe Certified Advanced ColdFusion Developer
 AFP Webworks
 http://afpwebworks.com
 ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month




~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:347267
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-04 Thread Jochem van Dieten


On Mon, May 2, 2011 at 11:48 AM, Mike Kear wrote:
 Thankfully I'd heeded good advice and
 used cfqueryparam on all the queries in that site and nothing they
 tried worked.    They were submitting urls with parameters like :
 /index.cfm?pid=111825pgm=../../../../../../../../../../proc/self/environguestprogID=2
  and many many variations.

That is not an attempts at SQL injection at all. That is an attempt to
test for a Linux vulnerability: http://lwn.net/Articles/191954/

Considering how long ago that vulnerability was fixed this was most
likely a generic scan, not something tailored to ColdFusion or your
sites.

Jochem

-- 
Jochem van Dieten
http://jochem.vandieten.net/

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344209
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

CFQUERYPARAM and caching

2011-05-03 Thread Jenny Gavin-Wear


Having read the blog and comments about CFQUERYPARAM and caching:-

http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B270-
3056B422E2F6FCAB

I am wondering if it is a viable option to leave Maintain connections
across client requests unchecked in a development environment?
No virus found in this outgoing message.
Checked by AVG - www.avg.com
Version: 9.0.894 / Virus Database: 271.1.1/3611 - Release Date: 05/02/11
15:51:00



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344169
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: CFQUERYPARAM and caching

2011-05-03 Thread Dave Watts


 Having read the blog and comments about CFQUERYPARAM and caching:-

 http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B270-
 3056B422E2F6FCAB

 I am wondering if it is a viable option to leave Maintain connections
 across client requests unchecked in a development environment?

Sure, that's a viable option in development. Less so in production, as
the creation of new database connections is expensive - that's why
JDBC provides connection pooling.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite.

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344170
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: CFQUERYPARAM and caching

2011-05-03 Thread Jenny Gavin-Wear

Awesome, thanks Dave.

-Original Message-
From: Dave Watts [mailto:dwa...@figleaf.com]
Sent: 03 May 2011 18:03
To: cf-talk
Subject: Re: CFQUERYPARAM and caching

 Having read the blog and comments about CFQUERYPARAM and caching:-

http://www.coldfusionjedi.com/index.cfm?mode=entryentry=7D417738-DF64-B270-
 3056B422E2F6FCAB

 I am wondering if it is a viable option to leave Maintain connections
 across client requests unchecked in a development environment?

Sure, that's a viable option in development. Less so in production, as
the creation of new database connections is expensive - that's why
JDBC provides connection pooling.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite.

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344171
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-03 Thread Bobby Hartsfield


Same here. I'm not aware of a way (in CF) to achieve caching of execution
plans without fully qualified, paramterized SQL. CFQUERYPARAM gets you the
latter when done right.

.:.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com



-Original Message-
From: Mark A. Kruger [mailto:mkru...@cfwebtools.com] 
Sent: Monday, May 02, 2011 4:28 PM
To: cf-talk
Subject: RE: Bless cfqueryparam - helped defend against a persistent hack
attempt


I almost always see a significant gain in query performance - at least on
all versions of MSSQL - as much as 20-30 percent in some cases.

Mark A. Kruger, MCSE, CFG
(402) 408-3733 ext 105
www.cfwebtools.com
www.coldfusionmuse.com
www.necfug.com


-Original Message-
From: Dave Watts [mailto:dwa...@figleaf.com] 
Sent: Monday, May 02, 2011 1:54 PM
To: cf-talk
Subject: Re: Bless cfqueryparam - helped defend against a persistent hack
attempt


  cfqueryparam creates bound sql parameters, which improve query
performance.

 This is purely theoretical, in practice, the gain in performance is
neglectible.
 I prefer have a query to take 11 ms and see the values submitted in case
of error, than having the same query take 10 ms, but no information in
errors.

In your practice, perhaps. In the practice of others, not so much.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite





~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344206
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-03 Thread Raymond Camden


You forgot a round in there too. ;)


On Mon, May 2, 2011 at 9:10 PM, Justin Scott leviat...@darktech.org wrote:
 of course).  If I have a numeric ID, I'm going to
 min(abs(val(trim(url.id))), 200) that variable before it ever
 makes it to the query (forces a positive in-range integer or zero; the
 two billion value can be modified if you're using larger numbers in
 your situation; an error can occur if the integer value is out of
 range so I force it in range as well).


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344208
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Bless cfqueryparam - helped defend against a persistent hack attempt


Last night I watched as someone made a pretty determined attempt to
attack one of my web sites.  Thankfully I'd heeded good advice and
used cfqueryparam on all the queries in that site and nothing they
tried worked.They were submitting urls with parameters like :
/index.cfm?pid=111825pgm=../../../../../../../../../../proc/self/environguestprogID=2
  and many many variations.Like most of us,  I get lots of hack
attempts but this was more persistent than any I'd seen before.

The site is still running happily now, after about 8 hours of this.  I
don't know why they persisted for that long with no result - I would
have thought there were other targets they could go for if they're
getting no result here.  I'm pleased I bothered to do all those things
at the time.   When I was building the site (which is only a small
hobby site), I remember saying to myself several times this is
overkill there's no need for all this paranoia.But I'm glad now
that I did all that.

The techniques I've used that worked for me in this case were a
combination of factors:

[A] ALL queries - every single one of them - have cfqueryparam
including the cf-sqltype parameter around any parameters sent to the
database.

[B] no feedback is given to the user about the nature of the error,
only an error-handling page with the generic statement that 'there is
an error - perhaps we're updating the database - check back soon'   so
they have no clue why their attempt failed.

[C] an email is sent to me with the exception struct, cgi vars, etc so
I can be aware of what they're doing - that way i can make sure I am
covering all the vulnerabilities.

I have a banning system on forms on this site,  so that forms
submitted with values that match certain parameters cause the ip
address of that user to be banned from the site.   I think perhaps
I'll need to expand that to include URL variables now.

I just wanted to pass on, that the warnings everyone hears about using
cfqueryparam are valid, and we should never let a chink appear in our
defences by listening to that voice in the back of your head,
specially when you're under time pressure this is overkill there's no
need for all this paranoia.

-- 
Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344116
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Claude Schnéegans


Right.
However about CFQUERYPARAM, keep in mind that:
- this will prevent SQL injection, not all form of attacks;
- CFQUERYPARAM makes all values look like parameter 1... in the error 
messages, instead of the real values, not really handful when debugging;
- CFQUERYPARAM makes all queries more difficult to write AND to read for 
programmers;
- disabling multiple statement execution in the database will prevent from SQL 
injection as well.
- ending a query on an error because of CFQUERYPARAM will prevent the 
injection, but will not give you any hint that it was really an attack.

Personally I prefer checking crucial form and url parameters and eventually ban 
the intruder before submiting the query.


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344119
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Dorioo



 - CFQUERYPARAM makes all values look like parameter 1... in the error
 messages, instead of the real values, not really handful when debugging;
 - CFQUERYPARAM makes all queries more difficult to write AND to read for
 programmers;


Are you implying that cfqueryparam _shouldn't_ be used for these reasons??

- Gabriel

On Mon, May 2, 2011 at 8:49 AM,  wrote:


 Right.
 However about CFQUERYPARAM, keep in mind that:
 - this will prevent SQL injection, not all form of attacks;
 - CFQUERYPARAM makes all values look like parameter 1... in the error
 messages, instead of the real values, not really handful when debugging;
 - CFQUERYPARAM makes all queries more difficult to write AND to read for
 programmers;
 - disabling multiple statement execution in the database will prevent from
 SQL injection as well.
 - ending a query on an error because of CFQUERYPARAM will prevent the
 injection, but will not give you any hint that it was really an attack.

 Personally I prefer checking crucial form and url parameters and eventually
 ban the intruder before submiting the query.


 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344120
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Claude Schnéegans


 Are you implying that cfqueryparam _shouldn't_ be used for these reasons??

No, I'm just saying that there are better and more efficient ways of fighting 
attacks.
I use cfqueryparam on some occasions, but not everywhere blindly.

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344121
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Steve 'Cutter' Blades


- CFQUERYPARAM makes all queries more difficult to write AND to read for 
programmers;

I couldn't disagree more with this statement. Aside from the protection 
benefits, the use of cfqueryparam creates bound sql parameters, which improve 
query performance. I don't disagree with your suggestion to check form and url 
parameters. In fact I encourage it heavily, and hope that folks will remember 
the 'type' attribute of the cfparam tag. That said, use of cfqueryparam is a 
must, IMO, and the more you use it the more it becomes second nature. Yes, your 
error messages may need some help, but a combination of usage and proper error 
handling (too often ignored) can go a long way towards saving your bacon.

Steve 'Cutter' Blades
Adobe Community Professional
Adobe Certified Expert
Advanced Macromedia ColdFusion MX 7 Developer

http://blog.cutterscrossing.com


Co-Author Learning Ext JS 3.2 Packt Publishing 2010
https://www.packtpub.com/learning-ext-js-3-2-for-building-dynamic-desktop-style-user-interfaces/book

The best way to predict the future is to help create it


On 5/2/2011 7:49 AM, =?ISO-8859-1?Q?Claude_Schn=E9egans wrote:
 Right.
 However about CFQUERYPARAM, keep in mind that:
 - this will prevent SQL injection, not all form of attacks;
 - CFQUERYPARAM makes all values look like parameter 1... in the error 
 messages, instead of the real values, not really handful when debugging;
 - CFQUERYPARAM makes all queries more difficult to write AND to read for 
 programmers;
 - disabling multiple statement execution in the database will prevent from 
 SQL injection as well.
 - ending a query on an error because of CFQUERYPARAM will prevent the 
 injection, but will not give you any hint that it was really an attack.

 Personally I prefer checking crucial form and url parameters and eventually 
 ban the intruder before submiting the query.


 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344122
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Gerald Guido


- CFQUERYPARAM makes all values look like parameter 1... in the error
messages, instead of the real values, not really handful when debugging;

There are parsers that will show the actual queries with values. My favorite
is the one that Ben Nadel wrote. It is a little JS bookmark widget.

- CFQUERYPARAM makes all queries more difficult to write AND to read for
programmers;

Opinion. Depends on the person. I have not problem with using them. Besides
the bulk of the SQL code for most of my CRUD's are machine generated.

G!

On Mon, May 2, 2011 at 8:49 AM,  wrote:


 Right.
 However about CFQUERYPARAM, keep in mind that:
 - this will prevent SQL injection, not all form of attacks;
 - CFQUERYPARAM makes all values look like parameter 1... in the error
 messages, instead of the real values, not really handful when debugging;
 - CFQUERYPARAM makes all queries more difficult to write AND to read for
 programmers;
 - disabling multiple statement execution in the database will prevent from
 SQL injection as well.
 - ending a query on an error because of CFQUERYPARAM will prevent the
 injection, but will not give you any hint that it was really an attack.

 Personally I prefer checking crucial form and url parameters and eventually
 ban the intruder before submiting the query.


 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344123
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Gerald Guido

In case anyone is interested. This is the JS bookmark widget that parses
Queryparam debugging info. Very handy.

http://www.bennadel.com/blog/459-Merging-ColdFusion-SQL-Debugging-And-Query-Params-With-Javascript.htm

HTH
G!

On Mon, May 2, 2011 at 9:14 AM, Gerald Guido gerald.gu...@gmail.com wrote:

- CFQUERYPARAM makes all values look like parameter 1... in the error
messages, instead of the real values, not really handful when debugging;

There are parsers that will show the actual queries with values. My
favorite is the one that Ben Nadel wrote. It is a little JS bookmark widget.

- CFQUERYPARAM makes all queries more difficult to write AND to read for
programmers;

Opinion. Depends on the person. I have not problem with using them. Besides
the bulk of the SQL code for most of my CRUD's are machine generated.

On Mon, May 2, 2011 at 8:49 AM, wrote:

Right.
However about CFQUERYPARAM, keep in mind that:
- this will prevent SQL injection, not all form of attacks;
- CFQUERYPARAM makes all values look like parameter 1... in the error
messages, instead of the real values, not really handful when debugging;
- CFQUERYPARAM makes all queries more difficult to write AND to read for
programmers;
- disabling multiple statement execution in the database will prevent from
SQL injection as well.
- ending a query on an error because of CFQUERYPARAM will prevent the
injection, but will not give you any hint that it was really an attack.

Personally I prefer checking crucial form and url parameters and
eventually ban the intruder before submiting the query.

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344124
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Dorioo


Ok. I'll respect your opinion.

But for anyone new to CF that may read this, I don't believe there is a
debate about using cfqueryparam. My impression is that the settled majority
best practice is to use it blindly, use it everywhere rather than not use
it, or use it sparingly, or not use it because of code aesthetic reasons.

- Gabriel

On Mon, May 2, 2011 at 9:04 AM,  wrote:


  Are you implying that cfqueryparam _shouldn't_ be used for these
 reasons??

 No, I'm just saying that there are better and more efficient ways of
 fighting attacks.
 I use cfqueryparam on some occasions, but not everywhere blindly.

 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344125
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt


Just for the record, I use other techniques to protect my sites too
but in this case, i was outlining what saved me in this case.
Clearly there are a bazillion ways nasty people can do damage or
exploit weaknesses in sites.

In this case, I was grateful that I hadn't given in to the temptation
to take the shortcut.   The fields that the hacker was trying to
inject into are integer fields.   If I hadn't had the cfqueryparam set
up for all variables to the query,  AND the cf-sqltype=integer
parameter as well,  the injection might have got through the other
defences I have set up.

But the cfqueryparam stopped the text injection getting into the
query, and the fact that i use error handling to trap errors, and give
no feedback as to why the  url failed,  meant it was difficult for the
hacker to figure out what he had to do to get around my defences.  As
it is, I got to watch over a period of 8 hours several hundred
attempts to exploit my database and do damage, without having to lift
a finger.


I use a code writer to write most of my queries - at least the CRUD
ones anyway,  so it's no difficulty to ensure all the queries have
cfqueryparam but at the time I wrote this site, I didnt have the code
writer ready yet.   I'm just glad i took the time back then to do the
job properly and not cut corners.

All I'm saying about this is:whenever you think it's not likely
someone will try to hack your site, and anyway you're up against a
deadline and cfqueryparam is a long-winded way to type out the simple
parameter you want to use,   THINK AGAIN!!   it is DEFINITELY worth
putting up all the defences you can, provided there isnt an undue
price to pay in terms of performance.  In fact, using cfqueryparam
improves performance of queries- that site of mine is a
database-driven site for a radio show,  and it just lopes along during
the show when load is higher.

If anyone is curious about what site it is, I'll be happy to tell you
and let you have a look for yourself,  but I'm reluctant to post the
url here since I've discussed so much about the security of the site.
Just send me an email off line and i'll tell you.

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month

On Mon, May 2, 2011 at 11:49 PM, Dorioo dor...@gmail.com wrote:

 Ok. I'll respect your opinion.

 But for anyone new to CF that may read this, I don't believe there is a
 debate about using cfqueryparam. My impression is that the settled majority
 best practice is to use it blindly, use it everywhere rather than not use
 it, or use it sparingly, or not use it because of code aesthetic reasons.

 - Gabriel

 On Mon, May 2, 2011 at 9:04 AM,  wrote:


  Are you implying that cfqueryparam _shouldn't_ be used for these
 reasons??

 No, I'm just saying that there are better and more efficient ways of
 fighting attacks.
 I use cfqueryparam on some occasions, but not everywhere blindly.



 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344126
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt


As an interesting aside, the same ip address started trying to probe
my mail server a few minutes ago, and my version of cfformprotect
picked it up and added the ip address to the banned ips. Now that
same ip address is banned from using any forms on any site I
administer.

Again, they get no feedback that they've been banned.Just seems
like they've successfully submitted a form, but in fact their
submission just gets dumped in the bitbucket and nothing gets done.
it doesn't even get to the database.  The reason for not letting them
know they're banned is they can submit forms till the cows come home
and nothing will happen.   It means they will be less likely to try to
find other holes in the defences to exploit.


Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344127
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Gerald Guido


Mike, out of curiosity, what seems to be motivating these attacks? Malware
injections? Hacker cred? Revenge or grudge? Or just to vandalize and do
damage? It seems like a LOT of effort. I am also surprised that they are
using the same IP for so long and not changing proxies.

Thanx
G!


On Mon, May 2, 2011 at 10:42 AM, Mike Kear afpwebwo...@gmail.com wrote:


 As an interesting aside, the same ip address started trying to probe
 my mail server a few minutes ago, and my version of cfformprotect
 picked it up and added the ip address to the banned ips. Now that
 same ip address is banned from using any forms on any site I
 administer.

 Again, they get no feedback that they've been banned.Just seems
 like they've successfully submitted a form, but in fact their
 submission just gets dumped in the bitbucket and nothing gets done.
 it doesn't even get to the database.  The reason for not letting them
 know they're banned is they can submit forms till the cows come home
 and nothing will happen.   It means they will be less likely to try to
 find other holes in the defences to exploit.


 Cheers
 Mike Kear
 Windsor, NSW, Australia
 Adobe Certified Advanced ColdFusion Developer
 AFP Webworks
 http://afpwebworks.com
 ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month

 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344129
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt


I dont know what the motivation is.   All I've seen is repeated
attempts to inject stuff into the database - never getting anywhere.
So that means all the attempts so far are simply attempts to get at
passwords or schema.   I'm supposing once they get a look at the
database they'll be able to figure out what's there that they want.
If they're after credit card or user info I have bad news for them ...
 it's not a commercial site.  There AREN'T any customer details
because there are no customers LOL

I get attempts on the email server every day.  At least a hundred
attempts a day over all the sites I manage.   I dont see them now
because I have automated the defences.  Once an ip address is banned,
they can submit hundreds of forms a minute and i wont see any of them
because the first line of the form processing is to check if the
user's ip address is banned, and only proceed further if it's not.

I'm guessing the reason for these probes is to find mail servers that
are open to exploitation, so they can send spams through them.

Cheers
Mike Kear
Windsor, NSW, Australia
Adobe Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion 9 Enterprise, PHP, ASP, ASP.NET hosting from AUD$15/month


On Tue, May 3, 2011 at 1:09 AM, Gerald Guido gerald.gu...@gmail.com wrote:

 Mike, out of curiosity, what seems to be motivating these attacks? Malware
 injections? Hacker cred? Revenge or grudge? Or just to vandalize and do
 damage? It seems like a LOT of effort. I am also surprised that they are
 using the same IP for so long and not changing proxies.

 Thanx
 G!

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344130
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Dave Watts


 However about CFQUERYPARAM, keep in mind that:
 - this will prevent SQL injection, not all form of attacks;
 - CFQUERYPARAM makes all values look like parameter 1... in the error 
 messages, instead of the real values, not really handful when debugging;
 - CFQUERYPARAM makes all queries more difficult to write AND to read for 
 programmers;
 - disabling multiple statement execution in the database will prevent from 
 SQL injection as well.
 - ending a query on an error because of CFQUERYPARAM will prevent the 
 injection, but will not give you any hint that it was really an attack.

 Personally I prefer checking crucial form and url parameters and eventually 
 ban the intruder before submiting the query.

This is silly contrarianism. Yes, CFQUERYPARAM only prevents SQL
injection attacks. That's not a criticism. I've been using it for
years, and I haven't found it significantly more difficult to read
errors or debug query problems. Also, it is not the case that all SQL
injection attacks require the ability to execute multiple statements.
SQL injection attacks can be written within a single statement,
although the attacker does lose some flexibility within that
constraint. Hell, the Wikipedia page on SQL injection attacks lists as
its first attack (an information disclosure attack) a single
statement. Finally, how you trap errors is entirely up to you.

CFQUERYPARAM is definitely more likely to be successful at preventing
SQL injection attacks than your checking crucial form and URL
parameters. CFQUERYPARAM, by defining a separation between data and
executable code, prevents them entirely. It prevents known SQL
injection attacks, and is guaranteed to prevent as-yet-undiscovered
SQL injection attacks in the future. You have no guarantee that you
can prevent attacks entirely by attempting to filter them out
individually.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite.

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344131
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Dave Watts


 The site is still running happily now, after about 8 hours of this.  I
 don't know why they persisted for that long with no result - I would
 have thought there were other targets they could go for if they're
 getting no result here.

Chances are, it was an automated attack - the attacker might not have
even know that your site was being attacked. Time is on the attacker's
side here.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344132
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Claude Schnéegans


 cfqueryparam creates bound sql parameters, which improve query performance.

This is purely theoretical, in practice, the gain in performance is neglectible.
I prefer have a query to take 11 ms and see the values submitted in case of 
error, than having the same query take 10 ms, but no information in errors.

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344134
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Matt Quackenbush


And we prefer to hack Schneegans apps.  :D

On Mon, May 2, 2011 at 1:10 PM,  wrote:


  cfqueryparam creates bound sql parameters, which improve query
 performance.

 This is purely theoretical, in practice, the gain in performance is
 neglectible.
 I prefer have a query to take 11 ms and see the values submitted in case of
 error, than having the same query take 10 ms, but no information in errors.

 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344135
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Judah McAuley


On Mon, May 2, 2011 at 11:10 AM,   wrote:

  cfqueryparam creates bound sql parameters, which improve query performance.

 This is purely theoretical, in practice, the gain in performance is 
 neglectible.
 I prefer have a query to take 11 ms and see the values submitted in case of 
 error, than having the same query take 10 ms, but no information in errors.

The gains are not purely theoretical but like most things, it depends
on your situation. If you are using very simple queries, the benefit
very well may be negligible. If you are using more complex queries
that require more time to compute the query plan and are under load
the benefits are much more obvious.

If you are optimizing for errors, you are doing it wrong. It isn't
that difficult to do proper error handling and debugging with cfquery,
you just have to plan for it. I wrote up a quick blog article on the
subject last year.
http://judah.posterous.com/debugging-with-prepared-statements-with-cfque

If you provide a result attribute on your cfquery you'll get a struct
returned that includes not only the results but also the parameterized
query that was run, the parameters that were passed in and whether or
not the query was cached.

Hope that helps,
Juda

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344137
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Dave Watts


  cfqueryparam creates bound sql parameters, which improve query performance.

 This is purely theoretical, in practice, the gain in performance is 
 neglectible.
 I prefer have a query to take 11 ms and see the values submitted in case of 
 error, than having the same query take 10 ms, but no information in errors.

In your practice, perhaps. In the practice of others, not so much.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344140
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Mark A. Kruger


I almost always see a significant gain in query performance - at least on
all versions of MSSQL - as much as 20-30 percent in some cases.

Mark A. Kruger, MCSE, CFG
(402) 408-3733 ext 105
www.cfwebtools.com
www.coldfusionmuse.com
www.necfug.com


-Original Message-
From: Dave Watts [mailto:dwa...@figleaf.com] 
Sent: Monday, May 02, 2011 1:54 PM
To: cf-talk
Subject: Re: Bless cfqueryparam - helped defend against a persistent hack
attempt


  cfqueryparam creates bound sql parameters, which improve query
performance.

 This is purely theoretical, in practice, the gain in performance is
neglectible.
 I prefer have a query to take 11 ms and see the values submitted in case
of error, than having the same query take 10 ms, but no information in
errors.

In your practice, perhaps. In the practice of others, not so much.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344142
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Eric Cobb


I generally see a pretty significant gain on Oracle as well.

Thanks,

Eric Cobb
http://www.cfgears.com
Help me make a difference this summer - http://bit.ly/i8dJvQ



On 5/2/2011 3:27 PM, Mark A. Kruger wrote:
 I almost always see a significant gain in query performance - at least on
 all versions of MSSQL - as much as 20-30 percent in some cases.

 Mark A. Kruger, MCSE, CFG
 (402) 408-3733 ext 105
 www.cfwebtools.com
 www.coldfusionmuse.com
 www.necfug.com


 -Original Message-
 From: Dave Watts [mailto:dwa...@figleaf.com]
 Sent: Monday, May 02, 2011 1:54 PM
 To: cf-talk
 Subject: Re: Blesscfqueryparam - helped defend against a persistent hack
 attempt


 cfqueryparam creates bound sql parameters, which improve query
 performance.
 This is purely theoretical, in practice, the gain in performance is
 neglectible.
 I prefer have a query to take 11 ms and see the values submitted in case
 of error, than having the same query take 10 ms, but no information in
 errors.

 In your practice, perhaps. In the practice of others, not so much.

 Dave Watts, CTO, Fig Leaf Software
 http://www.figleaf.com/
 http://training.figleaf.com/

 Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
 GSA Schedule, and provides the highest caliber vendor-authorized
 instruction at our training centers, online, or onsite



 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344143
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread James Holmes


Sure, your MS Access DB doesn't cache execution plans, but real databases do.

On Tuesday, 3 May 2011,   wrote:

  cfqueryparam creates bound sql parameters, which improve query performance.

 This is purely theoretical, in practice, the gain in performance is 
 neglectible.
 I prefer have a query to take 11 ms and see the values submitted in case of 
 error, than having the same query take 10 ms, but no information in errors.

-- 
--
WSS4CF - WS-Security framework for CF
http://wss4cf.riaforge.org

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344150
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread David


On Mon, May 2, 2011 at 2:10 PM,  wrote:


  cfqueryparam creates bound sql parameters, which improve query
 performance.

 This is purely theoretical, in practice, the gain in performance is
 neglectible.
 I prefer have a query to take 11 ms and see the values submitted in case of
 error, than having the same query take 10 ms, but no information in errors.


no information in errors -- Untrue. The error/catch object has one key
SQL with the sql statement containing the (param 1) etc placeholders,
and another key where with all the parameters, types, and values.


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344151
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Bless cfqueryparam - helped defend against a persistent hack attempt

2011-05-02 Thread Justin Scott


 Yes, your error messages may need some help, but a combination
 of usage and proper error handling (too often ignored) can go a long
 way towards saving your bacon.

Perhaps I'm going a little overboard, but my personal philosophy is
that it shouldn't be possible for an end-user to generate a ColdFusion
error message.  Proper error handling is an absolute must so you get
notified of errors that do occur, but I will generally massage the
incoming data before passing it into a query (which uses cfqueryparam,
of course).  If I have a numeric ID, I'm going to
min(abs(val(trim(url.id))), 200) that variable before it ever
makes it to the query (forces a positive in-range integer or zero; the
two billion value can be modified if you're using larger numbers in
your situation; an error can occur if the integer value is out of
range so I force it in range as well).

There is something to be said for getting notified when an attack is
in progress, but I'd lean on the side of logging unusual requests and
sending a notice when the app sees greater than X unusual requests
within a certain time period so I can check it out.  It's very rare
that the error notification on my sites ever need to kick in, and it
becomes an event when they do rather than thousands of error emails
getting generated from an automated attack.

Kudos to the original poster for making sure they used some protection
though.  Attackers don't care what your deadline looked like when the
app was written and their automated scanning tools don't care how big
your site is, so you have to assume they're going to probe long and
hard regardless of the size of the site.


-Justin

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344152
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Storing decimal parts of a second cfqueryparam

2011-02-28 Thread Pete Ruckelshaus


OK, just not my night with decimal values.

Trying to store distances (for throwing and jumping events), which are
measured in feet and inches.  In order to maintain proper sort order, I
decided to convert feet and inches (with fractions of an inch as decimal
value) to inches with fractions of an inch as decimal values.

Again, database is SQL Server 2008, and I'm using cfqueryparam.  Data type
for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL
as the cfsqltype value.  I can see in the debug output that the decimal
portion of the value is part of the value to be inserted, for instance,
825.25, but looking at the database table, the stored value is  825.
 Here's the debug output for that query:

*insertResult* (Datasource=track, Time=2ms, Records=1) in
C:\inetpub\wwwroot\track\enter_results.cfm @ 21:59:59.059

INSERT INTO 
tblEventResults(eventid, athleteid, time, distance,
competition)
VALUES  (?,
 ?,
 ?,
 ?,
 ?)


Query Parameter Value(s) -
Parameter #1(CF_SQL_INTEGER) = 1
Parameter #2(CF_SQL_INTEGER) = 1
Parameter #3(CF_SQL_TIMESTAMP) =
Parameter #4(CF_SQL_DECIMAL) = 825.25
Parameter #5(CF_SQL_BIT) = NO

Should I be using a different precision value for the decimal datatype?  Or
am I just doing something idiotic?

Thanks,

Pete

On Sat, Feb 26, 2011 at 4:59 PM, Pete Ruckelshaus pruckelsh...@gmail.comwrote:

 Hey, that did it!  Thanks!

 Pete


 On Sat, Feb 26, 2011 at 4:43 PM, Charlie Griefer 
 charlie.grie...@gmail.com wrote:


 Hi Pete:

 Have you tried cf_sql_timestamp as the cfsqltype (as opposed to
 cf_sql_time)?
 --
 Charlie Griefer
 http://charlie.griefer.com

 I have failed as much as I have succeeded. But I love my life. I love my
 wife. And I wish you my kind of success.
 On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote:
 
  I'm building an app for keeping track of high school track meet results
  using CF9 and MS SQL Server 2008. I am using the time(7) data type in
 the
  database, and when doing the insert query, I'm using cfqueryparam with
 a
  sqltype of cf_sql_time. However, when entering a time value of, for
  example, 00:01:22.05, the value that actually gets inserted drops the
  decimal part of the seconds. When I do the insert query without using
  cfqueryparam, it works fine. I prefer using cfqueryparam for obvious
  reasons and am always hesitant to not use it, so is there a way to get
 it to
  work and still have it retain the decimal portion of the second?
 
  Thanks,
 
  Pete
 
 
 

 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342602
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Storing decimal parts of a second cfqueryparam

2011-02-28 Thread Charlie Griefer


I think your code is fine, but take a look at the precision value for the 
column in SQL Server. Ensure that it's set to 2.

-- 
Charlie Griefer
http://charlie.griefer.com

I have failed as much as I have succeeded. But I love my life. I love my wife. 
And I wish you my kind of success.
On Saturday, February 26, 2011 at 8:08 PM, Pete Ruckelshaus wrote: 
 
 OK, just not my night with decimal values.
 
 Trying to store distances (for throwing and jumping events), which are
 measured in feet and inches. In order to maintain proper sort order, I
 decided to convert feet and inches (with fractions of an inch as decimal
 value) to inches with fractions of an inch as decimal values.
 
 Again, database is SQL Server 2008, and I'm using cfqueryparam. Data type
 for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL
 as the cfsqltype value. I can see in the debug output that the decimal
 portion of the value is part of the value to be inserted, for instance,
 825.25, but looking at the database table, the stored value is 825.
  Here's the debug output for that query:
 
 *insertResult* (Datasource=track, Time=2ms, Records=1) in
 C:\inetpub\wwwroot\track\enter_results.cfm @ 21:59:59.059
 
  INSERT INTO tblEventResults(eventid, athleteid, time, distance,
 competition)
  VALUES (?,
  ?,
  ?,
  ?,
  ?)
 
 
 Query Parameter Value(s) -
 Parameter #1(CF_SQL_INTEGER) = 1
 Parameter #2(CF_SQL_INTEGER) = 1
 Parameter #3(CF_SQL_TIMESTAMP) =
 Parameter #4(CF_SQL_DECIMAL) = 825.25
 Parameter #5(CF_SQL_BIT) = NO
 
 Should I be using a different precision value for the decimal datatype? Or
 am I just doing something idiotic?
 
 Thanks,
 
 Pete
 
 On Sat, Feb 26, 2011 at 4:59 PM, Pete Ruckelshaus 
 pruckelsh...@gmail.comwrote:
 
  Hey, that did it! Thanks!
  
  Pete
  
  
  On Sat, Feb 26, 2011 at 4:43 PM, Charlie Griefer 
  charlie.grie...@gmail.com wrote:
  
   
   Hi Pete:
   
   Have you tried cf_sql_timestamp as the cfsqltype (as opposed to
   cf_sql_time)?
   --
   Charlie Griefer
   http://charlie.griefer.com
   
   I have failed as much as I have succeeded. But I love my life. I love my
   wife. And I wish you my kind of success.
   On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote:

I'm building an app for keeping track of high school track meet results
using CF9 and MS SQL Server 2008. I am using the time(7) data type in
   the
database, and when doing the insert query, I'm using cfqueryparam with
   a
sqltype of cf_sql_time. However, when entering a time value of, for
example, 00:01:22.05, the value that actually gets inserted drops the
decimal part of the seconds. When I do the insert query without using
cfqueryparam, it works fine. I prefer using cfqueryparam for obvious
reasons and am always hesitant to not use it, so is there a way to get
   it to
work and still have it retain the decimal portion of the second?

Thanks,

Pete
 
 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342611
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Storing decimal parts of a second cfqueryparam

2011-02-28 Thread Roger Austin


On 2/26/2011 10:08 PM, Pete Ruckelshaus wrote:

 OK, just not my night with decimal values.

 Trying to store distances (for throwing and jumping events), which are
 measured in feet and inches.  In order to maintain proper sort order, I
 decided to convert feet and inches (with fractions of an inch as decimal
 value) to inches with fractions of an inch as decimal values.

 Again, database is SQL Server 2008, and I'm using cfqueryparam.  Data type
 for the column in question is decimal(18, 4), and I'm using CF_SQL_DECIMAL
 as the cfsqltype value.  I can see in the debug output that the decimal
 portion of the value is part of the value to be inserted, for instance,
 825.25, but looking at the database table, the stored value is  825.

Why not store two fields; one for feet and one for inches. That seems
like the easiest way to do it other than just use one float for both.
The decimal issue is probably more a database issue than a CF issue.

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342617
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Storing decimal parts of a second cfqueryparam

2011-02-28 Thread Pete Ruckelshaus


Primarily because it's easier and more accurate to sort on a single overall
value (inches).

Of course, if it were up to me, we'd be using metric :)

On Mon, Feb 28, 2011 at 7:12 PM, Roger Austin raust...@nc.rr.com wrote:


 On 2/26/2011 10:08 PM, Pete Ruckelshaus wrote:
 
  OK, just not my night with decimal values.
 
  Trying to store distances (for throwing and jumping events), which are
  measured in feet and inches.  In order to maintain proper sort order, I
  decided to convert feet and inches (with fractions of an inch as decimal
  value) to inches with fractions of an inch as decimal values.
 
  Again, database is SQL Server 2008, and I'm using cfqueryparam.  Data
 type
  for the column in question is decimal(18, 4), and I'm using
 CF_SQL_DECIMAL
  as the cfsqltype value.  I can see in the debug output that the decimal
  portion of the value is part of the value to be inserted, for instance,
  825.25, but looking at the database table, the stored value is  825.

 Why not store two fields; one for feet and one for inches. That seems
 like the easiest way to do it other than just use one float for both.
 The decimal issue is probably more a database issue than a CF issue.

 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342629
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Storing decimal parts of a second cfqueryparam

2011-02-28 Thread James Holmes


You need to set the scale attribute in the cfqueryparam tag.

http://cfquickdocs.com/cf9/?getDoc=cfqueryparam

--
WSS4CF - WS-Security framework for CF
http://wss4cf.riaforge.org/



On 1 March 2011 10:43, Pete Ruckelshaus pruckelsh...@gmail.com wrote:

 Primarily because it's easier and more accurate to sort on a single overall
 value (inches).

 Of course, if it were up to me, we'd be using metric :)

 On Mon, Feb 28, 2011 at 7:12 PM, Roger Austin raust...@nc.rr.com wrote:


 On 2/26/2011 10:08 PM, Pete Ruckelshaus wrote:
 
  OK, just not my night with decimal values.
 
  Trying to store distances (for throwing and jumping events), which are
  measured in feet and inches.  In order to maintain proper sort order, I
  decided to convert feet and inches (with fractions of an inch as decimal
  value) to inches with fractions of an inch as decimal values.
 
  Again, database is SQL Server 2008, and I'm using cfqueryparam.  Data
 type
  for the column in question is decimal(18, 4), and I'm using
 CF_SQL_DECIMAL
  as the cfsqltype value.  I can see in the debug output that the decimal
  portion of the value is part of the value to be inserted, for instance,
  825.25, but looking at the database table, the stored value is  825.

 Why not store two fields; one for feet and one for inches. That seems
 like the easiest way to do it other than just use one float for both.
 The decimal issue is probably more a database issue than a CF issue.



 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342631
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Storing decimal parts of a second cfqueryparam

2011-02-26 Thread Pete Ruckelshaus


I'm building an app for keeping track of high school track meet results
using CF9 and MS SQL Server 2008.  I am using the time(7) data type in the
database, and when doing the insert query, I'm using cfqueryparam with a
sqltype of cf_sql_time.  However, when entering a time value of, for
example, 00:01:22.05, the value that actually gets inserted drops the
decimal part of the seconds.  When I do the insert query without using
cfqueryparam, it works fine.  I prefer using cfqueryparam for obvious
reasons and am always hesitant to not use it, so is there a way to get it to
work and still have it retain the decimal portion of the second?

Thanks,

Pete


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342599
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Storing decimal parts of a second cfqueryparam

2011-02-26 Thread Charlie Griefer


Hi Pete:

Have you tried cf_sql_timestamp as the cfsqltype (as opposed to cf_sql_time)? 
-- 
Charlie Griefer
http://charlie.griefer.com

I have failed as much as I have succeeded. But I love my life. I love my wife. 
And I wish you my kind of success.
On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote: 
 
 I'm building an app for keeping track of high school track meet results
 using CF9 and MS SQL Server 2008. I am using the time(7) data type in the
 database, and when doing the insert query, I'm using cfqueryparam with a
 sqltype of cf_sql_time. However, when entering a time value of, for
 example, 00:01:22.05, the value that actually gets inserted drops the
 decimal part of the seconds. When I do the insert query without using
 cfqueryparam, it works fine. I prefer using cfqueryparam for obvious
 reasons and am always hesitant to not use it, so is there a way to get it to
 work and still have it retain the decimal portion of the second?
 
 Thanks,
 
 Pete
 
 
 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342600
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Storing decimal parts of a second cfqueryparam

2011-02-26 Thread Pete Ruckelshaus


Hey, that did it!  Thanks!

Pete

On Sat, Feb 26, 2011 at 4:43 PM, Charlie Griefer
charlie.grie...@gmail.comwrote:


 Hi Pete:

 Have you tried cf_sql_timestamp as the cfsqltype (as opposed to
 cf_sql_time)?
 --
 Charlie Griefer
 http://charlie.griefer.com

 I have failed as much as I have succeeded. But I love my life. I love my
 wife. And I wish you my kind of success.
 On Saturday, February 26, 2011 at 2:33 PM, Pete Ruckelshaus wrote:
 
  I'm building an app for keeping track of high school track meet results
  using CF9 and MS SQL Server 2008. I am using the time(7) data type in
 the
  database, and when doing the insert query, I'm using cfqueryparam with
 a
  sqltype of cf_sql_time. However, when entering a time value of, for
  example, 00:01:22.05, the value that actually gets inserted drops the
  decimal part of the seconds. When I do the insert query without using
  cfqueryparam, it works fine. I prefer using cfqueryparam for obvious
  reasons and am always hesitant to not use it, so is there a way to get it
 to
  work and still have it retain the decimal portion of the second?
 
  Thanks,
 
  Pete
 
 
 

 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342601
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Best Practive for Updating using cfqueryparam

2011-01-04 Thread David Moore


What is the best way to write the following update loop using cfqueryparam?

  cfset loopcount=0
  cfloop index=UpdateRecord list=#FORM.RecordsToUpdate#
cfset loopcount = loopcount+1
cfset NewOrder=#ListGetAt(FORM.DisplayOrder, loopcount)#
cfquery name=updDisplayOrder datasource=#DSN#
UPDATE Pages 
SET Pages.DisplayOrder = #NewOrder# 
WHERE Pages.PageID = #PageIndex#
/cfquery
  /cfloop 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340424
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Best Practive for Updating using cfqueryparam

2011-01-04 Thread David Moore


What is the best way to write the following update loop using cfqueryparam?

  cfset loopcount=0
  cfloop index=UpdateRecord list=#FORM.RecordsToUpdate#
cfset loopcount = loopcount+1
cfset NewOrder=#ListGetAt(FORM.DisplayOrder, loopcount)#
cfquery name=updDisplayOrder datasource=#DSN#
UPDATE Pages 
SET Pages.DisplayOrder = #NewOrder# 
WHERE Pages.PageID = #UpdateRecord#
/cfquery
  /cfloop

or 

  cfset loopcount=0
  cfloop index=UpdateRecord list=#FORM.RecordsToUpdate#
cfset loopcount = loopcount+1
 cfquery name=updDisplayOrder datasource=#DSN#
UPDATE Pages 
SET Pages.DisplayOrder = cfqueryparam cfsqltype=cf_sql_integer 
value=#ListGetAt(FORM.DisplayOrder, loopcount)#
WHERE Pages.PageID = #UpdateRecord#
/cfquery
  /cfloop

David Moore
UpstateWeb 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340426
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: Best Practive for Updating using cfqueryparam

2011-01-04 Thread Andrew Scott


Just add the queryparam where it is supposed to go on the set.

Also where are people learning to write code like this?

cfset NewOrder=#ListGetAt(FORM.DisplayOrder, loopcount)#

The correct way is

cfset NewOrder = ListGetAt(FORM.DisplayOrder, loopcount) /

Notice that the hashes has been removed!


Regards,
Andrew Scott
http://www.andyscott.id.au/


 -Original Message-
 From: David Moore [mailto:dgmoor...@hotmail.com]
 Sent: Wednesday, 5 January 2011 9:11 AM
 To: cf-talk
 Subject: Best Practive for Updating using cfqueryparam
 
 
 What is the best way to write the following update loop using
cfqueryparam?
 
   cfset loopcount=0
   cfloop index=UpdateRecord list=#FORM.RecordsToUpdate#
 cfset loopcount = loopcount+1
 cfset NewOrder=#ListGetAt(FORM.DisplayOrder, loopcount)#
 cfquery name=updDisplayOrder datasource=#DSN# UPDATE Pages
 SET Pages.DisplayOrder = #NewOrder#
 WHERE Pages.PageID = #PageIndex#
 /cfquery
   /cfloop
 
 ~~
 ~~~|
 Order the Adobe Coldfusion Anthology now!
 http://www.amazon.com/Adobe-Coldfusion-
 Anthology/dp/1430272155/?tag=houseoffusion
 Archive: http://www.houseoffusion.com/groups/cf-
 talk/message.cfm/messageid:340424
 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
 Unsubscribe: http://www.houseoffusion.com/groups/cf-
 talk/unsubscribe.cfm


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340427
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Best Practive for Updating using cfqueryparam

2011-01-04 Thread Steve Bryant


cfset loopcount=0
cfloop index=UpdateRecord list=#FORM.RecordsToUpdate#
cfset loopcount = loopcount+1
cfquery name=updDisplayOrder datasource=#DSN#
UPDATE Pages 
SET Pages.DisplayOrder = cfqueryparam cfsqltype=cf_sql_integer 
value=#ListGetAt(FORM.DisplayOrder, loopcount)#
WHERE Pages.PageID = cfqueryparam cfsqltype=cf_sql_integer 
value=#UpdateRecord#
/cfquery
/cfloop

OR (assuming I understand correctly):

cfset loopcount=0
cfloop index=UpdateRecord list=#FORM.RecordsToUpdate#
cfset loopcount = loopcount+1
cfquery datasource=#DSN#
UPDATE Pages 
SET Pages.DisplayOrder = cfqueryparam cfsqltype=cf_sql_integer 
value=#loopcount#
WHERE Pages.PageID = cfqueryparam cfsqltype=cf_sql_integer 
value=#UpdateRecord#
/cfquery
/cfloop


Or (couldn't help myself here, sorry):

cfset Application.DataMgr.saveSortOrder(Pages ,DisplayOrder 
,FORM.RecordsToUpdate)
http://www.bryantwebconsulting.com/docs/datamgr/change-sort-order.cfm

Hope that helps,

Steve
 What is the best way to write the following update loop using 
 cfqueryparam?
 
  
 cfset loopcount=0
  
 cfloop index=UpdateRecord list=#FORM.RecordsToUpdate#

 cfset loopcount = loopcount+1

 cfset NewOrder=#ListGetAt(FORM.DisplayOrder, loopcount)#

 cfquery name=updDisplayOrder datasource=#DSN#

 UPDATE Pages 

 SET Pages.DisplayOrder = #NewOrder# 

 WHERE Pages.PageID = #UpdateRecord#

 /cfquery
  
 /cfloop
 
 or 
 
  
 cfset loopcount=0
  
 cfloop index=UpdateRecord list=#FORM.RecordsToUpdate#

 cfset loopcount = loopcount+1
 
 cfquery name=updDisplayOrder datasource=#DSN#

 UPDATE Pages 

 SET Pages.DisplayOrder = cfqueryparam cfsqltype=cf_sql_integer 
 value=#ListGetAt(FORM.DisplayOrder, loopcount)#

 WHERE Pages.PageID = #UpdateRecord#

 /cfquery
  
 /cfloop
 
 David Moore
 UpstateWeb 


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340434
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Working with cfqueryparam

2010-11-15 Thread Scott Brady


It probably shouldn't be used for columns that don't allow null values.

On Sun, Nov 14, 2010 at 11:52 AM, David Moore dgmoor...@hotmail.com wrote:



 Should this be written in on every cfqueryparam as a failsafe or should
 other steps be taken so that is not needed?
 cribe.cfm http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm




-- 
-
Scott Brady
http://www.scottbrady.net/


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339228
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Working with cfqueryparam


  null=#Trim(FORM.Contributed) EQ ''#

 I like this.

I find this easier to read:

null=#yesNoFormat(len(trim(form.contributed)))#

just because the nested quotes can be confusing.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on
GSA Schedule, and provides the highest caliber vendor-authorized
instruction at our training centers, online, or onsite. 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339198
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Working with cfqueryparam


Dave,

I find this easier to read:
null=#yesNoFormat(len(trim(form.contributed)))#

I really like this in that is seems to be truer to the actual use of the 
variable as CF intended it to be used. 

To confirm what you are proposing, if form.contributed is NULL it will produce 
the following:

null=yes

Otherwise, if form.contribute has some information it will produce:

null=no

Should this be written in on every cfqueryparam as a failsafe or should other 
steps be taken so that is not needed?


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339199
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Working with cfqueryparam


I really appreciate everyone's input. This kind of documentation is very 
helpful for a lot of people. I have found CF-Talk to be the best CF resource 
available. Maybe some of the experts on this list can provide other examples of 
cfqueryparam uses that have been helpful, but not mentioned here. 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339202
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Working with cfqueryparam

2010-11-14 Thread Roger Austin


On 11/14/2010 1:52 PM, David Moore wrote:
 I find this easier to read:
 null=#yesNoFormat(len(trim(form.contributed)))#

What about using null=#isDate(form.contributed)# ?
It looks like you are assuming a valid date or an empty
string. What if someone puts in foo into the form field?
Perhaps you are doing something else to validate the field
that I am not following.
-- 
LinkedIn: http://www.linkedin.com/pub/8/a4/60
Twitter:  http://twitter.com/RogerTheGeek
Blog: http://rogerthegeek.wordpress.com/
MissHunt: http://www.misshunt.com/ (Home of the Clean/Dirty Magnet)

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339203
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Working with cfqueryparam