Thanks much to everyone for their advice.
After looking over the responses, I thought I'd better test this out
myself. Looks like Jochem is correct and cfqueryparam protects you for
both numeric and varchar strings where sql injection is attempted.
Here's what I did. Can anyone break this? Find a db platform where
this doesn't work?
Created a table called 'tabletest' in mysql
Field Config
--------------------
ID integer, autoincrement (Identity in ms sql)
Textfield varchar(255)
Populated with this data:
1,"blah blah"
2,"woo hoo"
3,"yadda yadda"
Created the following template. To validate that everything worked as
expected I:
1. didn't use cfqueryparam and instead used 'Where 0=0' to dump out all
data.
2. used cfqueryparam with cfsqltype=-cf_sql_numeric and set
variables.testID to a simple existing number. Used ID in the where
clause.
3. used cfqueryparam with cfsqltype=-cf_sql_varchar and set
variables.testID to a simple existing string. Used textfield in the
where clause
Using the ID field in the where clause I tried setting variables.TestID
to
a. 2; delete from tabletest where tabletest.ID=2;
b. 2; delete from tabletest where tabletest.ID=2
c. 2; drop table tabletest;
d. 2; drop table tabletest
In all cases an error was thrown by mySQL's ODBC driver
Next I switched to using the text field in the where clause and my
testID value was
a. yadda yadda; delete from tabletest where tabletest.textfield='woo
hoo';
b. yadda yadda; delete from tabletest where tabletest.ID=2
c. yadda yadda; drop table tabletest;
d. yadda yadda; drop table tabletest
And any other variation I could think of. In all cases no error was
thrown and zero records were returned.
<CFSET variables.TestID="woo hoo; delete from tabletest where
tabletest.ID=2;">
<CFQUERY
name="GetData"
dataSource="test">
SELECT
tabletest.ID,
tabletest.textfield
FROM tabletest
WHERE tabletest.textfield=<CFQUERYPARAM
CFSQLTYPE="CF_SQL_varchar" VALUE=#variables.testID#>
</CFQUERY>
<html><head><title></title></head><body>
<UL>
<CFOUTPUT QUERY="GetData">
<LI>#GetData.ID#. #GetData.Textfield#</LI>
</CFOUTPUT>
</UL>
</body></html>
--Matt Robertson--
MSB Designs, Inc.
http://mysecretbase.com
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
