any ideas

I would like to leave my Query open to take in any number of where statements. IE: <cfset arguments.clause = "JobID = 1"> <cfquery name="getJobs" datasource="#application.dsn#"> SELECT * FROM Jobs WHERE 1=1   <cfif len(arguments.clause)>AND #arguments.clause#</cfif> </cfquery> Of course the problem with doing this is SQL injection. Anyone have a good way of doing this and keep safe from SQL injection? Thanks! Chad

<cfqueryparam> the variables in the where statement ??? I would like to leave my Query open to take in any number of where statements. IE: <cfset arguments.clause = "JobID = 1"> <cfquery name="getJobs" datasource="#application.dsn#"> SELECT * FROM Jobs WHERE 1=1   <cfif len(arguments.clause)>AND #arguments.clause#</cfif> </cfquery> Of course the problem with doing this is SQL injection. Anyone have a good way of doing this and keep safe from SQL injection? Thanks! Chad

Also.. if you are using SQL server... Create a user in SQL server with the least amount of rights.  NEVER USE THE SA ACCOUNT! Allow only select. Deny delete, insert, update if it is not needed.  (Technically, you should never delete a record.  Just mark it "no show" or something of that nature.) Use the created SQL server username for the datasource in ColdFusion Administrator. I would like to leave my Query open to take in any number of where statements. IE: <cfset arguments.clause = "JobID = 1"> <cfquery name="getJobs" datasource="#application.dsn#"> SELECT * FROM Jobs WHERE 1=1   <cfif len(arguments.clause)>AND #arguments.clause#</cfif> </cfquery> Of course the problem with doing this is SQL injection. Anyone have a good way of doing this and keep safe from SQL injection? Thanks! Chad

You are effectively throwing open the door to any hacker, then asking how to stop them from coming in and stealing your stereo. :) Why do you want to do this? Perhaps there is a better way? On Wed, May 14, 2008 at 1:44 PM, Chad Gray <cgray@careyweb.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

I have done this with a function for type that keeps my cfqueryparams intact... Instead of building the where clause as a string (usually a kind of a tricky proposition and not a great idea) put your logic inside the cfquery and do something like this. <cfquery ...>   SELECT ....      <!---  makes general "and" possilbe --->   WHERE 1 = 1      <cfif NOT isEmpty(var1)>     AND  colname = <cfqueryparam cfsqltype="#funcToFigureOutType(var1)#" value="#var1#"/>   </cfif>   <cfif NOT isEmpty(var2)>     AND  colname = <cfqueryparam cfsqltype="#funcToFigureOutType(var2)#" value="#var2#"/>   </cfif> </cfquery> This keeps my vars bound and prevents attack while allowing for a dynamic query... You could just as easily loop through a list of stuff using the same approach. Of course you might need to handle nulls, handle decimals etc... It can get dicey :) Another approach is to scrub out key words that might be used in an attack. There are some Regex functions out there and a function at cflib.org to remove malicious SQL - although I think it is somewhat incomplete. Of the 2 approaches I always fall back to <cfqueryparam...>. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com You are effectively throwing open the door to any hacker, then asking how to stop them from coming in and stealing your stereo. :) Why do you want to do this? Perhaps there is a better way? On Wed, May 14, 2008 at 1:44 PM, Chad Gray <cgray@careyweb.com> wrote: > I would like to leave my Query open to take in any number of where statements. ----- Excess quoted text cut - see Original Post for more -----

>Anyone have a good way of doing this and keep safe from SQL injection? Do you have any information at all about the field types/columns being included? There are certainly ways to handle this if you just have something like a string of words you want to search on or known phrases, such that you can split them out into cfqueryparams. If it's just a random where clause, that would be a lot more challenging, if not impossible, to make hacker-safe. --- Mary Jo

Something like this maybe? <cfscript>   dataObject[1][1] = "myField"; // field name   dataObject[1][2] = "myValue"; // value   dataObject[1][3] = "myDataType" // data type </cfscript> <cfloop from="1" to="#arrayLen(dataObject)#" index="i">   and #dataObject[i][1]# = <cfqueryparam value="#dataObject[i][2]" cfsqltype="#dataObject[i][3]"> </cfloop> Thats off the dome so it could be wrong some. Mary Jo Sminkey wrote: ----- Excess quoted text cut - see Original Post for more -----

> (Technically, you should never delete a record.  Just mark it > "no show" or something of that nature.) That's not true. There are cases where you want to "soft delete" records, and (many other) cases where you want to actually delete the record. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Training: Adobe/Google/Paperthin Certified Partners http://training.figleaf.com/ WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers! http://www.webmaniacsconference.com/