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
