hehe... maybe - can you post some of your code? How are your catalogs built and how often?
-Mark -----Original Message----- From: paul smith [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 8:10 PM To: CF-Talk Subject: RE: mySQL & CF 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 Get the mailserver that powers this list at http://www.coolfusion.com
