Parameters in queryExecute

dev
1

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

QryGetUser = 
Queryexecute("
SELECT tblUsers.ID,
tblUsers.OrganisaztionID,
tblUsers.Username,
tblUsers.Password,
tblUsers.AccessLevel
FROM 
tblUsers
where 
tblUsers.Username=?
", 
[username],{datasource="myds"});

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.
e.g.

Where 1=1
AND
If(isdefined(“parameter2”)
{
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…
2

I tend to code like this:

    local.SQL = "
        select top 10
            [Id],
            [name]
        from
            [table]
        where
            1=1
    ";
    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
3

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?

5

This might help:

  <!-- FUNCTION UDF: build SQL params -->
  
  <!-- 
  
  Notes:
  Use for building sql with simple read queries: 
  
  Supported:
  
  - INNER JOINS
  - 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 ?'
  
  Unsupported:
  
  - 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>
          </cfloop>
          <cfif local.counter EQ ListLen(local.keylist)>
			<cfset local.isValidParam = true>
          </cfif>
          <cfif local.isValidParam>
			<cfset local.ignore = false>
			<cfset local.ignoredelimiter = ",">
            <cfif StructKeyExists(param,'ignoredelimiter')>
              <cfset local.ignoredelimiter = param['ignoredelimiter']>
            </cfif>
            <cfif StructKeyExists(param,'ignore')>
              <cfif ListLen(param['ignore'],local.ignoredelimiter)>
                <cfif ListFindNoCase(param['ignore'],param['value'],local.ignoredelimiter)>
                  <cfset local.ignore = true>
                </cfif>
              <cfelse>
                <cfif param['ignore'] EQ param['value']>
                  <cfset local.ignore = true>
                </cfif>
              </cfif>
            </cfif>
            <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")>
                <cfelse>
                  <cfset arguments.query.addParam(value=param['value'],cfsqltype="cf_sql_#param['sqltype']#")>
                </cfif>
              </cfif>
            </cfif>
            <cfif NOT local.ignore>
              <cfsavecontent variable="local.sql">
                <cfoutput>
                  #param['column']# 
                  #param['operator']# 
                  <cfif ListFindNoCase("number,string",param['sqltype'])>
                    <cfif param['sqltype'] EQ "number">
                      #param['value']# 
                    <cfelse>
                      '#param['value']#' 
                    </cfif>
                  <cfelse>
					<cfif param['operator'] EQ "IN">
                      (?) 
                    <cfelse>
                      ?
                    </cfif>
                  </cfif>
                  <cfif StructKeyExists(param,'andOr')>
                    #param['andOr']# 
                  </cfif>
                </cfoutput>
              </cfsavecontent>
              <cfset result = result & local.sql>
            </cfif>
          </cfif>
        </cfif>
      </cfloop>
    </cfif>	  
    <cfif Len(Trim(result))>
	  <cfset result = REReplaceNoCase(result,"[\s]+"," ","ALL")>
      <cfset result = REReplaceNoCase(result,"[\s]+(AND|OR|,)[\s]*$","","ALL")>
      <cfset result = Trim(result)>
    </cfif>
  <cfreturn result /> 
  </cffunction>

  <!-- FUNCTION UDF: read query -->
  
  <!-- 
  
  Notes:
  Use for executing sql with simple read queries: 
  
  Supported:
  
  - query attributes: datasource
  
  Unsupported:
  
  - 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>
      <cfif Len(Trim(arguments.orderby))>
        <cfset local.orderby = " ORDER BY " & arguments.orderby & " " & arguments.sortorder>
      </cfif>
	  <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>
      <cfif Len(Trim(local.wheresql))>
        <cfset local.wheresql = " WHERE " & local.wheresql>
      </cfif>
      <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()>
    </cfif>
  <cfreturn result>
  </cffunction>

IMPLEMENTATION:

<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 Foo.id = Bar.Id",params=sqlarray,groupby="Foo.id",orderby=Foo.id,sortorder='ASC').query>

</cfif>
6

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