Thanks!
CFQUERYPARAM enabled me to reduce that pesky overhead using client vars in
a database to maintain state from 32ms to 16ms.....
Anything up your sleeve to reduce my SQL7 1,000ms-2,000ms full-text
searches by 50% ?
best, paul
At 11:52 AM 12/16/02 -0600, you wrote:
>While it's true that CFQUERYPARAM escapes characters, there's a bit more
>going on under the covers. When you use CFQUERYPARAM you are assigning a
>"type" to the variable that's passed to the RDBMS. This type information
>insulates the item from such things as the infamous SQL query injection
>attack. If the code that is rendered from using cfqueryparam where written
>out in SQL it would look like this:
>
>-------------------------------
>--declarations
>DECLARE @item1 char(50)
>DECLARE @item2 char(15)
>
>--assignments (these actually come from the "input")
>SELECT @itme1 = 'blah'
>SELECT @item2 = ') truncate table Mytable'
>
>-- insert
>Insert into MyTable (item1, item2)
> values (@item1, @item2)
>--------------------------------------
>
>Note that in item2, the hacker has tried to pass a command to kill your
>table. But because the variable @item2 is predefined as a character string
>it can't be done. SQL will treat whatever is in @item2 as character data -
>no matter how pernicious <g>. Incidentally, this is also why CFQUERYPARAM
>is so much faster on MS SQL. the SQL server caches execution plans for
>re-use. When you pass in a query that looks typical:
>
>insert into mytable (item1, item2)
> values ('blah','blah')
>
>the execution plan is more likely to be unique - and therefore not in the
>cache - because the 2 "value" items are part of the plan. Additionaly, SQL
>must "lookup" the type to create the execution plan. So every insert
>requires a new execution plan. When you use cfqeuryparam however, sql can
>find an execution plan that is cached. That's because the actual plan will
>not contain specific values but placeholders that are typed:
>
>insert into mytable (item1,item2)
> values(@item1 char(10),@item2 char(40))
>
>This use of a saved execution plan reduces the "prepare" part of the SQL
>process saving overhead. On a busy server this can cause an increase in
>performance that is exponential. At least that's been my experience.
>
>-Mark
>
>
>-----Original Message-----
>From: Craig Dudley [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 16, 2002 11:05 AM
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>
>cfqueryparam will escape potentially dangerous characters for you. This
>is ESSENTIAL if you don't want to have your database dropped by some
>nasty hacker type person.
>
>Search through the archives for SQL injection attacks, you'll soon see
>why.
>
>It will also negate the need to escape quotes and other things manually,
>which is quite handy too.
>
>Trust me, cfqueryparam is your friend ;-)
>
>Craig.
>
>-----Original Message-----
>From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
>Sent: 16 December 2002 16:51
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>Hi, Craig, and thanks for the reply.
>
>When you say they "make things a lot more secure."
>What exactly do you mean? Boy, that's a lot of extra typing
>over the typical CFINSERT syntax...
>
>Rick
>
>
>-----Original Message-----
>From: Craig Dudley [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 16, 2002 11:10 AM
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>
>Standard SQL inserts will work fine on most if not all RDBMS's
>
>Eg.
>
>insert into tablename
>(int_col1,varchar_col2)
>values
>(<cfqueryparam cfsqltype="CF_SQL_INTEGER"
>value="#form.val1#">,<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
>value="#form.val2#">)
>
>Do try to use the cfqueryparams, they make things a lot more secure.
>
>-----Original Message-----
>From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
>Sent: 16 December 2002 16:02
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>Hi, Matt.
>
>I, too, learned about the CFUPDATE problem from personal experience
>and from the Allaire forums when I first started using CF (with Access
>at
>that time).
>I stopped using it and went to the CFQUERY...Set... approach. That's
>worked
>fine.
>
>I haven't had any problems with CFINSERT, but if that may be problematic
>in
>the future
>I may as well go ahead and change my coding habits now.
>
>How is the INSERT coded for mySQL and CF?
>Example?
>
>Thanks,
>
>Rick
>
>
>-----Original Message-----
>From: Matt Robertson [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 16, 2002 1:11 AM
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>
>Rick,
>
>I noticed you mention that you use CFINSERT. From your earlier postings
>I
>know you are on CF 4.5x, as I am.
>
>You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow
>sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure,
>and
>maybe 4.0x). In threads on the subject over at the (then) Allaire
>forums no
>specific cause was ever traced. It just happens. Sometimes.
>
>In one of those threads I believe it was Paul Hastings who advised me to
>'just say no to cfinsert/cfupdate' and it ranks as some of the best CF
>advice I ever got. On the surface those tags appear to be handy
>shortcuts,
>but they black-box your SQL, take away the otherwise granular control
>you
>should have and make debugging ... difficult.
>
>I suggest you follow the same advice -- you'll probably find out you
>have
>to, anyway. While you're at it go for the double bonus and implement
>cfqueryparam.
>
>Happy Monday (early) :)
>
>---------------------------------------
>Matt Robertson, MSB Designs, Inc.
>http://mysecretbase.com - Retail
>http://foohbar.org - ColdFusion Tools
>---------------------------------------
>
>
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
