7;t it be better to validate the URL.user_id before getting to the
query?
-Randy
---Original Message---
From: Dave Watts
Date: 08/10/07 12:28:08
To: CF-Talk
Subject: RE: Is cfqueryparam worth it?
> the nice thing about this is that if "URL.userID" isn'
Thanks, Dave.
~|
Get the answers you are looking for on the ColdFusion Labs
Forum direct from active programmers and developers.
http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648
Archive:
http://w
> All of the examples in this thread show being
> used in a WHERE clause, and I understand the benefits of
> doing that now.
>
> Will I get the same benefit if I use them on an INSERT
> statement,
You won't get any significant performance benefit, because INSERT statements
don't really benefi
I think this has been asked answered, but I want to be clear on something about
.
All of the examples in this thread show being used in a WHERE
clause, and I understand the benefits of doing that now.
Will I get the same benefit if I use them on an INSERT statement, e.g.:
INSERT INTO user (fi
Correct, I meant val(), not int()
>>>the nice thing about this is that if "URL.userID" isn't an integer,
>int() returns 0
>
>Are you sure you're using the function int() ?
>I tried, and if the argument is not a number, the function does not
>return 0, but throw an error.
>And if the parameter is
>>the nice thing about this is that if "URL.userID" isn't an integer,
int() returns 0
Are you sure you're using the function int() ?
I tried, and if the argument is not a number, the function does not
return 0, but throw an error.
And if the parameter is not an integer, but a real number, it re
ata, and a transaction count that would make your head spin.
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Saturday, August 11, 2007 3:26 AM
To: CF-Talk
Subject: Re: Is cfqueryparam worth it?
Porter, Benjamin L. wrote:
>
> When you use cfqueryparam the sta
> Lol-- Splitting hairs on technical verbiage are we?
Not at all.
> Call it what you want, but I prefer to see my database as a
> sort of "server-superhero" who's spider sense tingles when I
> make more than "6 modifications to a temporary table" inside
> my stored procs.
You can prefer to se
Porter, Benjamin L. wrote:
>
> When you use cfqueryparam the statement that gets compiled uses
> sp_prepexec.
For which driver and which MS SQL Server version did you observe this?
Jochem
~|
Get involved in the latest ColdFusio
10, 2007 8:33 PM
To: CF-Talk
Subject: RE: Is cfqueryparam worth it?
> Given that the DB is likely to recompile the code if it
> senses that the current plan may not be optimal I assumed you
> were pointing out that there would be overhead in the
> generation of the new plan.
There are
> Given that the DB is likely to recompile the code if it
> senses that the current plan may not be optimal I assumed you
> were pointing out that there would be overhead in the
> generation of the new plan.
There are a set of specific things that will cause the database to recompile
the plan.
ords, usage of a non-optimal plan is not directly the fault of
cfqueryparam, but a side-effect of any manner of SQL execution allows
for caching.
~Brad
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Friday, August 10, 2007 6:23 PM
To: CF-Talk
Subject: RE: Is cfqueryparam
> From my research in the past on MS SQL Server, the DB will
> stop even in the middle of a proc and recompile because of
> any number of factors. For instance, a declaration of a temp
> table inside of a conditional statement will cause a recompile.
There are several factors that may cause the
n can be determined for a
statement without recompiling?
Please explain.
~Brad
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Friday, August 10, 2007 4:08 PM
To: CF-Talk
Subject: RE: Is cfqueryparam worth it?
> Still, in that case, the DB will simply perform a reco
> Still, in that case, the DB will simply perform a recompile
> to find a more suitable execution plan-- a step which would
> have had been performed anyway had there not have been a
> cached plan. So the worst case scenario seems to be you
> would break even. Doesn't seem like you can go wrong
> There are reasons not to use it. They come with perils. If the data
> being sent to the query does not come from the outside then the only
> risk to SQL injection comes from the developers working on the query,
> and they have other ways to cause havoc without having to write SQL
> injection atta
cfqueryparam worth it?
As many others have said, there is never a reason NOT to use
cfqueryparam.
You can still use your trick and cfqueryparam doesn't have to bomb:
M!ke
-Original Message-
From: Ben Mueller [mailto:[EMAIL PROTECTED]
Sent: Friday, August 10, 2007 12:01 PM
To: C
Benbig thread on this very subject all this past weekread the
archives...all questions answered in GREAT detail
Long story shortuse it...no reason not tolots of reason's for ;-)
Cheers
Bryan Stevenson B.Comm.
VP & Director of E-Commerce Development
Electric Edge Systems Group In
> You can work off the assumption that all of your queries will
> benefit. While I'm sure they exist, I've never seen a query
> that wasn't faster using bind variables.
I've seen plenty, actually. Not that I think that's a justification for not
using it.
Dave Watts, CTO, Fig Leaf Software
http:
that.
~Brad
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Friday, August 10, 2007 12:23 PM
To: CF-Talk
Subject: RE: Is cfqueryparam worth it?
> In general, the more complex the query (multi table joins,
> subqueries, that sort of thing), the more
All,
Thanks so much. I think I'm sold, especially if performance benefits are
really as much as double, by and large.
As far as why we want empty recordsets returned, 99.9% of the time, actual data
is returned from these queries, and so the .1% that a bogus value is passed in,
we find that ou
> In general, the more complex the query (multi table joins,
> subqueries, that sort of thing), the more complex the
> execution plan, and the greater performance benefit from
> having that execution plan cached.
On the other hand, the more complex the query, the more likely that an
execution p
> Okay, now I need to do some homework. Are there resources
> anywhere that can help tell me what queries would benefit
> from such things? My knowledge of SQL is decent, but isn't
> quite *that* deep. I would think all queries would benefit
> from that.
Basically, if different inputs would
In general, the more complex the query (multi table joins, subqueries, that
sort of thing), the more complex the execution plan, and the greater
performance benefit from having that execution plan cached. You also want to
factor in how often the query is executed as well since a cached execution
pl
You can work off the assumption that all of your queries will benefit. While
I'm sure they exist, I've never seen a query that wasn't faster using bind
variables.
On 8/10/07, Ben Mueller <[EMAIL PROTECTED]> wrote:
>
> Okay, now I need to do some homework. Are there resources anywhere that
> can h
Yes, a prepared statement using bind variables is going to be significantly
faster in virtually all cases. By significant, I mean twice as fast, if not
more. It depends on the database.
I can't imagine why you'd want to let an invalid value still execute the
query, but if you choose to do this you
> Dave, thanks for the reply. I won't bore you with details,
> but suffice it to say that we prefer getting empty recordsets
> back in some cases because it makes our overall error
> handling and application flow better--granted, perhaps to the
> detriment of application performance. However,
Okay, now I need to do some homework. Are there resources anywhere that can
help tell me what queries would benefit from such things? My knowledge of SQL
is decent, but isn't quite *that* deep. I would think all queries would
benefit from that.
Ben
>> Am I going to see a significant perfor
> Am I going to see a significant performance increase? For
> the sake of argument, pretend that sample query I included in
> my first post is heavily hit. I'm running SQL Server 2000
> (soon to upgrade to 2005).
The answer will depend on whether that particular query benefits from a
generic,
Dave, thanks for the reply. I won't bore you with details, but suffice it to
say that we prefer getting empty recordsets back in some cases because it makes
our overall error handling and application flow better--granted, perhaps to the
detriment of application performance. However, for us the
Ben,
Please take a look at the following post by Ben Forta. This should clear up
some "why" issues for you.
http://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignored
Dan Vega
http://www.danvega.org/blog/
On 8/10/07, Ben Mueller <[EMAIL PRO
Thanks, Dan. We know about SQL injection attacks, and that's why we have
things like this:
#int(URL.userID)#
If the value isn't an integer, then int() returns 0, and the query will not
throw an exception. Preventing injection attacks isn't enough of an incentive
to use cfqueryparam--at least
> the nice thing about this is that if "URL.userID" isn't an
> integer, int() returns 0, and the query executes and simply
> returns no records. For us, this is a far preferable method
> than what cfqueryparam would do, which is to bomb before
> executing the query.
You'd rather run a query u
Mike,
Thanks for the response. But the question is this: what benefits will I get
from saying this:
over just this:
#val(url.userid)#
Am I going to see a significant performance increase? For the sake of
argument, pretend that sample query I included in my first post is heavily hit.
I'
t: Is cfqueryparam worth it?
I'm trying to determine if cfqueryparam is really worth using.
For data validation, we tend to do something like this:
SELECT username
FROM user
where userID = #int(URL.userID)#
the nice thing about this is that if "URL.userID" isn't an integer,
int()
I'm trying to determine if cfqueryparam is really worth using.
For data validation, we tend to do something like this:
SELECT username
FROM user
where userID = #int(URL.userID)#
the nice thing about this is that if "URL.userID" isn't an integer, int()
returns 0, and the query executes and sim
36 matches
Mail list logo
