RE: cfc question

[Chad Gray]

Thank Matt, Jim and Dan.

I see where you guys are going with this.

As far as security im not sure how using the array is any better.

I suppose once things are broken out using the array I could then validate the 
data coming in with <cfqueryparam>.

This would keep people from tacking on sub-queries and injecting their own SQL 
statements.

Thanks for the help!
Chad



-----Original Message-----
From: Matt Quackenbush [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 18, 2007 11:25 AM
To: CF-Talk
Subject: Re: cfc question

Chad,

<cfquery> is indeed escaping your single quotes.  You can get around this in
a couple of ways.  The first is to use preserveSingleQuotes() in your
function, like so:

WHERE #preserveSingleQuotes(arguments.criteria)#

While that is a very easy "solution", you will be opening yourself up to all
sorts of security risks.  Therefore, I would strongly urge that you do *not*
use this "solution".

Another solution would be to add an argument that would accept an array, or
an array of structs that would hold your criteria.  Here is a quick example
of what I mean.

<!--- calling page --->
<cfscript>
   myArray = arrayNew(1);
   myArray[1] = structNew();
   myArray[1].field = "FileName";
   myArray[1].value = "PLATES.zjf";
   myArray[1].quote = true;
   myArray[2].field = "FileModificationDate";
   myArray[2].value = "09/30/2005 12:34:01 PM";
   myArray[2].quote = true;
</cfscript>

<!--- function --->
<cfargument name="criteria" type="array" />

<cfquery>
   WHERE 0 = 0
   <cfloop from="1" to="#arrayLen(arguments.criteria)#" index="i">
      AND #arguments.criteria[i].field# =
         <cfif arguments.criteria[i].quote>
            '#arguments.criteria[i].value#'
         <cfelse>
            #arguments.criteria[i].value#
         </cfif>
   </cfloop>
</cfquery>

Hope that helps.


Matt


I have a CRUD CFC that has this function and I load the CFC into the
> application scope:
>
> <cffunction name="readZJF" access="public" returntype="query">
>         <cfargument name="criteria" type="string" required="yes">
>
>         <cfquery datasource="dsn" name="readZJF">
>         SELECT *
>         FROM FlexoDirectConfigFiles
>         WHERE #arguments.criteria#
>         </cfquery>
>
>         <cfreturn readZJF>
> </cffunction>
>
> I want to be able to pass in the "WHERE" statement into it.  So it could
> be:
> FileName = 'PLATES.zjf' AND FileModificationDate = '09/30/2005 12:34:01
> PM'
>
> So I run something like this to call the function:
>
> <cfset DoesFileAlreadyExist = application.ZJF.readZJF("FileName =
> '#getZJF_files.name#' AND FileModificationDate =
> '#getZJF_files.dateLastModified#'")>
>
> What I get back is an error:
> [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '
> PLATES.zjf'.
>
> Then I look at the SQL that was run in debugging.
> SELECT *
> FROM FlexoDirectConfigFiles
> WHERE FileName = ''PLATES.zjf'' AND FileModificationDate = ''09/30/2005
> 12:34:01 PM''
>
> Why did it take my single quotes and add another single quote?  Is it
> trying to escape the single quote?  How do I keep it from doing this?
>




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & 
Flex 2
Free Trial 
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284000
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4