Parameters in queryExecute

Hi, I have a simple function that searches for a user (see below).
using cfscript.

QryGetUser = 
SELECT tblUsers.ID,

I want to have the “username” parameter in the WHERE optional.
So traditionally I have:
Wrapped the where clause in IF statements to check if a Parameter is defined.

Where 1=1
AND field2=parameter2

So this works just fine but gets a little cumbersome and hard to read, however with optional parameters I figure I am going to have to build my SQL statement dynamically in any case. I have several parts to this question:

  1. Is there a cleaner solution apart from building the SQL string
  2. Would it be bad practice to pass the whole built SQL string as a parameter to this function, the entire select statement being built outside of the function
  3. Depending on the above answers would it then be an option to simply create a function to run any SQL command…

I tend to code like this:

    local.SQL = "
        select top 10
    if (something) {
        local.SQL &= "
            or [username] like (:userName)
    local.strSQL = {
        userName = {value="foo", cfsqltype="cf_sql_varchar"}
    return queryExecute(local.SQL, local.strSQL);

If that’s any help…

1 Like

yes that’s what I was playing about with. thanks…
I was worried about any security implications but there is little else you can do I guess?

This might help:

  <!-- FUNCTION UDF: build SQL params -->
  Use for building sql with simple read queries: 
  - value paramaterization
  - IN operator with or without paramaterization
  - ignore WHERE clause item, if a certain value or a value in a list of values, is matched: {...'ignore'='value'}
  - ignore value list custom delimeter: default {...'ignoredelimiter'=','}
  - use {...'sqltype'='number|string'} for non value paramaterization 
  - positional paramaters 'table column ?'
  - subqueries
  - named parameters, 'table column = :table column', due to a bug with the way Railo parses table prefixed columns sql
  <cffunction name="BuildSQLParams" access="public" returntype="string" hint="function description: build SQL params">
    <cfargument name="query" required="false" default="#StructNew()#" type="struct" hint="argument description: query">
    <cfargument name="params" required="false" default="#ArrayNew(1)#" type="array" hint="argument description: params">
    <cfset var result =  "">
    <cfset var local = StructNew()>
    <cfset local.keylist = "column,operator,sqltype,value">
    <cfset local.sqlarray = ArrayNew(1)>
    <cfif IsStruct(arguments.query) AND ArrayLen(arguments.params)>
      <cfloop from="1" to="#ArrayLen(arguments.params)#" index="local.i">
        <cfset local.param = arguments.params[local.i]>
        <cfif IsStruct(local.param) AND NOT StructIsEmpty(local.param)>
          <cfset local.isValidParam = false>
          <cfset local.counter = 0>
          <cfloop collection="#local.param#" item="local.key">
            <cfif ListFindNoCase(local.keylist,local.key)>
              <cfset local.counter = local.counter + 1>
          <cfif local.counter EQ ListLen(local.keylist)>
			<cfset local.isValidParam = true>
          <cfif local.isValidParam>
			<cfset local.ignore = false>
			<cfset local.ignoredelimiter = ",">
            <cfif StructKeyExists(param,'ignoredelimiter')>
              <cfset local.ignoredelimiter = param['ignoredelimiter']>
            <cfif StructKeyExists(param,'ignore')>
              <cfif ListLen(param['ignore'],local.ignoredelimiter)>
                <cfif ListFindNoCase(param['ignore'],param['value'],local.ignoredelimiter)>
                  <cfset local.ignore = true>
                <cfif param['ignore'] EQ param['value']>
                  <cfset local.ignore = true>
            <cfif NOT local.ignore>
			  <cfif NOT ListFindNoCase("number,string",param['sqltype'])>
                <cfif param['operator'] EQ "IN">
                  <cfset arguments.query.addParam(value=param['value'],cfsqltype="cf_sql_#param['sqltype']#",list="yes")>
                  <cfset arguments.query.addParam(value=param['value'],cfsqltype="cf_sql_#param['sqltype']#")>
            <cfif NOT local.ignore>
              <cfsavecontent variable="local.sql">
                  <cfif ListFindNoCase("number,string",param['sqltype'])>
                    <cfif param['sqltype'] EQ "number">
					<cfif param['operator'] EQ "IN">
                  <cfif StructKeyExists(param,'andOr')>
              <cfset result = result & local.sql>
    <cfif Len(Trim(result))>
	  <cfset result = REReplaceNoCase(result,"[\s]+"," ","ALL")>
      <cfset result = REReplaceNoCase(result,"[\s]+(AND|OR|,)[\s]*$","","ALL")>
      <cfset result = Trim(result)>
  <cfreturn result /> 

  <!-- FUNCTION UDF: read query -->
  Use for executing sql with simple read queries: 
  - query attributes: datasource
  - apart from 'datasource', no other query attributes are supported
  <cffunction name="ReadQuery" returntype="struct" output="false" access="public" hint="function description: read query">
    <!-- arguments -->
    <cfargument name="dsn" required="yes" hint="argument description: dsn">
    <cfargument name="columns" type="string" required="no" default="" hint="argument description: columns">
    <cfargument name="tables" type="string" required="no" default="" hint="argument description: tables">
    <cfargument name="params" required="false" default="#ArrayNew(1)#" type="array" hint="argument description: params">
    <cfargument name="groupby" type="string" required="no" default="" hint="argument description: group by">
    <cfargument name="orderby" type="string" required="no" default="" hint="argument description: order by">
    <cfargument name="sortorder" type="string" required="no" default="ASC" hint="argument description: sort order">
    <!-- local variables -->
    <cfset var result = StructNew()>
    <cfset var local = StructNew()>
    <!-- logic -->
    <cfset StructInsert(result,"query",QueryNew(''))>
    <cfset StructInsert(result,"metaInfo",StructNew())>
    <cfif Len(Trim(arguments.tables))>
	  <cfset local.wheresql = "">
      <cfset local.groupby = "">
      <cfset local.orderby = "">
      <cfif Len(Trim(arguments.groupby))>
        <cfset local.groupby = " GROUP BY " & arguments.groupby>
      <cfif Len(Trim(arguments.orderby))>
        <cfset local.orderby = " ORDER BY " & arguments.orderby & " " & arguments.sortorder>
	  <cfset local.query = new Query()> 
      <cfset local.query.setAttributes(datasource=arguments.dsn)> 
      <cfset local.query.setAttributes(name="result")>
      <cfif ArrayLen(arguments.params)>
        <cfset local.wheresql = BuildSQLParams(local.query,arguments.params)>
      <cfif Len(Trim(local.wheresql))>
        <cfset local.wheresql = " WHERE " & local.wheresql>
      <cfset local.execute = local.query.execute(sql="SELECT #arguments.columns# FROM #arguments.tables##local.wheresql##local.groupby##local.orderby#")>
      <cfset result.query = local.execute.getResult()> 
      <cfset result.metaInfo = local.execute.getPrefix()>
  <cfreturn result>


<cfset sqlarray = []>

<cfset ArrayAppend(sqlarray,{'column'='Foo','operator'='=','sqltype'='tinyint','value'='bar','andOr'='AND'})>

<cfset ArrayAppend(sqlarray,{'column'='Bar','operator'='=','sqltype'='tinyint','value'='foo','andOr'=''})>


<cfif ArrayLen(sqlarray)>

    <cfset query = ReadQuery(dsn=request.dsn,columns="Column1,Column2,Column3",tables="Foo INNER JOIN Bar ON = Bar.Id",params=sqlarray,groupby="",,sortorder='ASC').query>


FYI: the local scope in local.SQL = is only really needed in the first assignment, and is equivalent to var SQL =.

Subsequent references can simply use SQL since it is now in the local scope and that is the nearest scope inside a function.

Also, if you don’t need to write cross-engine code (i.e., if you don’t need it to run on ACF), then you can use the localMode=true either as an attribute on each function, or set it globally in `Application.cfc``s body:

this.localMode = true;  // new unscoped variables in functions will be local