CFArgument Usage

[Milosh-SzrBxI]

Hi all,

I had a quick question... are there any reasons not to use the (cfargument) tag to reference a table name? I have only used and seen it used within queries after the where clause.

Thanks!
Miles

[Dan_Bracuk]

Your question is not clear.

The cfargument tag is used to declare what arguments are accepted by a function. It can't be used in a query.

Are you sure you said what you meant?

[Milosh-SzrBxI]

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 ----------------------------

[Kapitaine]

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.

[Dan_Bracuk]

quote:

---

Originally posted by: Kapitaine
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.

---

It can be used in the select clause also if you are selecting a constant instead of a field. And then there are insert and update queries.

[Dan_Bracuk]

quote:

---

Originally posted by: Milosh
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 ----------------------------

---

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.

[Milosh-SzrBxI]

Thanks to all for your responses. =)

Mikey, that's great advice - I will definitely work trim() and possibly a combination of the others into the queries.

Dan, interesting info on using cfqueryparam on the select statement... and you're right, I have given this way too much thought, but it is a huge set of applications and this was an idea that I had several months ago. Sometimes the easiest way through the mountain is drilling straight through it! 😉

[BKBK]

Milosh,
Your order-by clause will cause an error when the if-condition is false. There will then be no column to sort by. You could modify it to something like

<cfif isdefined("arguments.sort") and len(trim(arguments.sort)) GT 0>
order by #arguments.sort#
</cfif>

Another minus point is the use of a variables scope within the function. I would go even further and pass the datasource, too, as an argument. Apart from that, I find your method of dynamic queries solid.

[Newsgroup_User]

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/

--
Adam