Webmaster Forum / ColdFusion / Advanced Techniques / November 2008

It is wise to use cfqueryparam, but you can't use it for things like dynamic
table names. I believe cfqueryparam can only be used in the where clause.

If you're gonna do queries like that, at least do some manual validation on
the string first, for example using trim() len() and replace() or a combination
thereof just to be sure that your database wont be destroyed!! Also use a CFTRY
and CFCATCH...and CFTRANSACTION if the database and query type calls for it.

Cheers,
Mikey.

[q][i]Originally posted by: [b][b]Milosh[/b][/b][/i]
Hi Dan,

I believe I am using the terminology correctly, but my apologies for the
confusion.  I am calling an argument within a component to name a table, which
is something that I haven't seen anyone do.  

This example should explain it better than words and terminology (see below)!  
=)

The reason I am questioning this is because there are things that work, and
there are things that work and work properly.  For instance, as a coder you
could easily put a couple of pound-signs after the where clause for companyID
and it would work fine.  But using cfqueryparam to eliminate SQL injection is
the proper way to do it, and that's what I want to ensure when doing my
component argument calling the table name.

I appreciate your input.  =)

Cheers,
Miles

-------------- start "retrieval" component snippet ----------------------------
    <cffunction name="getinfo" access="remote" returntype="query" output="false"
hint="gets tool info">
        <cfargument name="tablename" type="string" required="true" hint="table name">
        <cfargument name="companyID" type="string" required="false" hint="id">
        <cfargument name="sort" type="string" required="false" default="lastname"
hint="sort">

        <cfset var getinfo = "">
        <cfquery name="getinfo" datasource="#variables.dsn#">
            select *
                        <!--- start questionable code --->
            from [#arguments.tablename#]
                        <!--- end questionable code --->
            where
                companyID = <cfqueryparam value="#arguments.companyID#"
cfsqltype="cf_sql_integer" maxlength="50">
            order by
                <cfif isdefined("arguments.sort") and
len(arguments.sort)>#arguments.sort#</cfif>
        </cfquery>
       
        <cfreturn getinfo>
    </cffunction>

-------------- end "retrieval" component snippet ----------------------------

-------------- on the page itself ----------------------------
On the page itself, I would call this component by writing:

<!--- start query --->
<cfset getinfo = application.retrieval.getinfo(tablename="users",
companyID=session.companyID)>
<!--- end query --->

<!--- start output --->
<cfoutput query="users">
         ......output here.....
</cfoutput>
<!--- end output --->
-------------- end page itself ----------------------------[/q]
You may have given this situation too much thought.

Making the tablename an argument only makes sense when there is more than one
choice.  It's your app, you know whether or not that's true.

Before you get too far down the road of writing all these generic queries,
maybe have a look @ Reactor or Transfer (Reactor seems the better of the
two to me).

http://www.reactorframework.com/
http://www.transfer-orm.com/