Getting a single output from cfquery when query column is cfqueryparam variable

Question

I'm using ColdFusion and trying to make a function that will allow me to get the value of a specific column in a specific account (each account is its own record/row).

A function like this works fine:

<cffunction name="getColumnValueFromAccount" access="public" returntype="string" >
    <cfargument name="accountName" type="string" required="yes" />

    <cfquery name="getColumn" datasource="mydatasource">
        <!--- Note that the line below is 'hard-coded.' --->
        SELECT role_ExampleSystem
        FROM table_name
        WHERE (accountName = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value='#accountName#'>)
    </cfquery>

    <!--- It's easy to return the column value when you know what its name was. --->
    <cfreturn getColumn.role_ExampleSystem > 

</cffunction>

But what I actually want is a function that allows me to specify which column name to read from, and eliminate the need for making a bunch of nearly identical CF functions that just have a different hard-coded SELECT parameter. I'm thinking it should look something like this, but I'm having trouble actually reading the single string that I believe it should be returning.

<cffunction name="getColumnValueFromAccount" access="public" returntype="string" >
    <cfargument name="accountName" type="string" required="yes" />
    <!--- Trying to accept a column name as an argument --->
    <cfargument name="columnName" type="string" required="yes" />

    <cfquery name="getColumn" datasource="mydatasource">
        <!--- I'm trying to use cfqueryparam to add specify the column name to select. --->
        SELECT <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value='#columnName#'>
        FROM table_name
        WHERE (accountName = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value='#accountName#'>)
    </cfquery>

    <!--- This line doesn't work. --->
    <cfreturn getColumn[#columnName#] >

</cffunction>

I thought that you were able to use variables in a bracket notation like getColumn[#columnName#] or getColumn[columnName] because someone mentioned it in a comment. But when I've tried to use variables myself it has not worked as expected. I get this error:

The value returned from the getColumnValueFromAccount function is not of type string. If the component name is specified as a return type, it is possible that either a definition file for the component cannot be found or is not accessible.

Any idea what route I should take when I want to get the single result of a cfquery, but I am not using a hard-coded column name in the SELECT part of my query? Normally this process is very simple, but it seems that when your column name is a variable things become a bit different.

Answer 1

My Solution:

Based on Leigh's advice that I can't use cfqueryparam in the way I was trying to use it, and Mark A Kruger's answer I was able to modify my code and get it working. It now looks like this:

<cffunction name="getColumnValueFromAccount" access="public" returntype="string" >
    <cfargument name="accountName" type="string" required="yes" />
    <cfargument name="columnName" type="string" required="yes" />

    <cfquery name="getColumn" datasource="mydatasource">
        SELECT #columnName#
        FROM table_name
        WHERE (accountName = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value='#accountName#'>)
    </cfquery>

    <cfreturn getColumn[columnName][1] >

</cffunction>

It now correctly returns an account's role in a specified system i.e. if account accountName is an admin in ExampleSystem and I passed in role_ExampleSystem to the function as ColumnName the function will return admin as expected.

Warning: My solution could present a SQL injection risk if used improperly!

Using a ColdFusion variable in a SQL statement like this doesn't provide any protection from SQL injection so it would be a very bad idea to allow a user to enter the data that is used for the columnName here. In my case this function will only ever be called by other server-side functions written by me, and the data being used for columnName will be hard-coded in the server-side function. The accountName, on the other hand, is user-specified, so it is important that it is in a cfqueryparam.

An Alternate, Safer Solution:

Mark A Kruger mentioned that it might be a better idea just to select every column you might need and just read the one you're actually interested in. This seems like a pretty good idea. After all, getting every (relevant) column for a record is unlikely to be a much bigger database call than my single column example--unless your database has huge records with tons of columns. You might as well do it this way, and as a side benefit, you wouldn't need to worry about the SQL injections that using a regular coldfusion #variable# in a cfquery could open you up to.

Reading everyone's answers and comments was very enlightening. Hopefully this question and its answers helps other people who are interested in doing similar things in ColdFusion!

Answer 2

A query is a structure of arrays as in queryname[key][1].

It works in the first instance because of backward compatibility. From the beginning you could do queryname.columnname and CF would output the first row of the query for that column. Once you switch to object syntax it no longer works that way.

Try this in your cfreturn:

getColumn[columnName][1]

Note - you do not need the pound signs in your cfreturn example.