This code would fail my own code review. I don't allow any "url." or
"form." variables inside CFQUERY statements. This, however, might
pass.
<!--- Validate ID --->
<cfif #isdefined("id")# is "true">
<cfset theId=#val(htmleditformat(REreplacenocase(id,
"[*,;^>:?<|\]", "" , "ALL" )))#>
<cfelse>
<cfset theId=0>
</cfif>
<!--- update time of last visit for this user --->
<cfquery name="queryit" datasource="#dsn#">
update people
set updated = '#dateformat(now(), "MM/DD/YYYY")#'
where peopleid = <cfqueryparam value="#theid#"
cfsqltype="cf_sql_integer">
</cfquery>
(Note: I am not using CFQueryParam here as a validation tool. I
already know the variable is an integer. This query is from a header
and will fire every page load so I want successive queries to run from
cache on the Database server.)
Good Fortune,
Richard Walters,
Webmaster, Davita Laboratory Services
[EMAIL PROTECTED]
(800) 604-5227 x 3525
>>> [EMAIL PROTECTED] 04/12/02 01:44PM >>>
<cfqueryparam> does in fact prevent that code from running.
<cfqueryparam> creates a prepared statement with parameters. It then
compares what you've entered as a value with the datatype you've
specified and, if successful, binds the parameters with what you've
entered. So, if you entered:
select * from table where id = <cfqueryparam value="#url.id#"
cfsqltype="CF_SQL_DECIMAL">
and then in your url entered: id=12;drop table yourtable
It would through you an error.
As well, if you had:
select * from table where id = <cfqueryparam value="#url.id#"
cfsqltype="CF_SQL_VARCHAR">
It would create the equivalent SQL statement of:
select * from table where id = '12;drop table yourtable'
----- Original Message -----
From: [EMAIL PROTECTED]
Date: Friday, April 12, 2002 11:00 am
Subject: RE: Preventing SQL injection attacks...?
> let's say you have a text field that is 100 characters long. you
> can still
> get a "drop table tablename" appended to the sql statement or
> write an
> entire sql statment. Cfqueryparam was meant to speed up cfquery,
> not be to a
> cure all.
>
> Anthony Petruzzi
> Webmaster
> 954-321-4703
> [EMAIL PROTECTED]
> http://www.sheriff.org
>
>
> -----Original Message-----
> From: Zac Spitzer [mailto:[EMAIL PROTECTED]]
> Sent: Friday, April 12, 2002 1:06 PM
> To: CF-Talk
> Subject: Re: Preventing SQL injection attacks...?
>
>
> [EMAIL PROTECTED] wrote:
>
> >you can't forget that form fields also play a part in this. after
> reading>the informaiton provided in jeff's link, it did shine a
> light. although i
> >have been taught from the beginning to always use val() around
> numberic>values (thank Adam) and to use regex to validate text
> input (props
> Raymond).
> >if your anal and take the time to make sure that the information
that
> people
> >are passing you is in the extact fomrat you want, you shouldn't
> have a
> >problem. also, don't rely on javascript, i always do server-side
> validation>even after client side, just to make certain. i even go
> as far as putting
> as
> >much validation as i can into my stored procedures and triggers.
> although>SQL server doesn't support regular expressions , which
> sucks! anyone know a
> >way it could?
> >
> why not just use cfqueryparam, it validates and it makes your sql
> code
> run faster???
>
>
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
