Coldfusion更新查询在变量中使用语句时引发错误

Question

试图使用包含逗号分隔的列更新集的变量。该变量最终包含有效的SQL（或者更准确地说是有效的Coldfusion语法），但该查询在以下位置引发错误：[Macromedia][SQLServer JDBC Driver][SQLServer]语法错误'

我从要更新的初始列开始：

<cfset sqlString = myColumn & ' = <cfqueryparam value="#myValue#" cfsqltype="#myDataType#" null="#NOT len(trim(myValue))#">'>

然后循环使用一些表单元素：

<cfset sqlString = sqlString & ', ' & myColumn & ' = <cfqueryparam value="#myValue#" cfsqltype="#myDataType#" null="#NOT len(trim(myValue))#">'>

当然，我最终在sqlString中得到了一个完整的字符串，但为了可读性，它包含如下内容：

id = <cfqueryparam value="123" cfsqltype="cf_sql_integer">,
csp = <cfqueryparam value="5" cfsqltype="cf_sql_integer">,
Q185 = <cfqueryparam value="" cfsqltype="cf_sql_integer" null="YES">,
Q3 = <cfqueryparam value="" cfsqltype="cf_sql_integer" null="YES">,
Q177 = <cfqueryparam value="" cfsqltype="cf_sql_date" null="YES">

所以我尝试在这样的更新查询中使用它：

<cfquery name="update_answers" datasource="#application.datasource#">
   UPDATE answers
   SET    #PreserveSingleQuotes(sqlString)#                                                     
   WHERE  rec_id = #id#
</cfquery>

我已经尝试过了，但没有使用和不使用保护单引号函数。

如果我输出sqlString的内容并像这样直接粘贴到查询中，它工作正常：

<cfquery name="update_answers" datasource="#application.datasource#">
   UPDATE answers
          id = <cfqueryparam value="123" cfsqltype="cf_sql_integer">,
          csp = <cfqueryparam value="5" cfsqltype="cf_sql_integer">,
          Q185 = <cfqueryparam value="" cfsqltype="cf_sql_integer" null="YES">,
          Q3 = <cfqueryparam value="" cfsqltype="cf_sql_integer" null="YES">,
          Q177 = <cfqueryparam value="" cfsqltype="cf_sql_date" null="YES">                                                    
   WHERE  rec_id = #id#
</cfquery>

同样，为了可读性，我在这里显示换行符，但是如果我将sqlString内容粘贴到带有或不带有换行符的查询中并不重要；它会工作。

有什么想法吗？

Answer 1

您可以使用以下方法。几年前我写了这个例程，当时我需要动态地构建一个cfquerparams数组。

我还添加了“ReadQuery”功能，以防万一，您将来需要使用它。

它已经在Lucee 4.5（Windows 2008R2）上进行了全面测试

如果您需要有关调用sysntax的进一步说明，请让我知道，但我在底部添加了一个示例，以帮助您开始。

这些函数实际上是CFC服务的一部分，但是您可以以独立的方式使用它们：

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


<!--- FUNCTION UDF: update query --->

<!--- 

Notes:
Use for executing sql with simple update queries: 

Supported:

- query attributes: datasource

Unsupported:

- apart from 'datasource', no other query attributes are supported

--->

<cffunction name="UpdateQuery" returntype="struct" output="false" access="public" hint="function description: read query">
  <!--- arguments --->
  <cfargument name="dsn" required="yes" hint="argument description: dsn" />
  <cfargument name="tables" type="string" required="no" default="" hint="argument description: tables">
  <cfargument name="setparams" required="false" default="#ArrayNew(1)#" type="array" hint="argument description: set params">
  <cfargument name="whereparams" required="false" default="#ArrayNew(1)#" type="array" hint="argument description: where params">
  <!--- 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.setsql = "">
  <cfset local.wheresql = "">
  <cfset local.query = new Query()> 
    <cfset local.query.setAttributes(datasource=arguments.dsn)> 
    <cfset local.query.setAttributes(name="result")>
    <cfif ArrayLen(arguments.setparams)>
      <cfset local.setsql = BuildSQLParams(local.query,arguments.setparams)>
    </cfif>
    <cfif Len(Trim(local.setsql))>
      <cfset local.setsql = " SET " & local.setsql>
    </cfif>
    <cfif ArrayLen(arguments.whereparams)>
      <cfset local.wheresql = BuildSQLParams(local.query,arguments.whereparams)>
    </cfif>
    <cfif Len(Trim(local.wheresql))>
      <cfset local.wheresql = " WHERE " & local.wheresql>
    </cfif>
    <cfset local.execute = local.query.execute(sql="UPDATE #arguments.tables##local.setsql##local.wheresql#")>
    <cfset result.query = local.execute.getResult()> 
    <cfset result.metaInfo = local.execute.getPrefix()>
  </cfif>
<cfreturn result>
</cffunction>

<cfset sqlarray = ArrayNew(1)>
<cfset wherearray = ArrayNew(1)>

<cfset ArrayAppend(sqlarray,{'column'='id','operator'='=','sqltype'='integer','value'='123','andOr'=','})>

<cfset ArrayAppend(sqlarray,{'column'='csp','operator'='=','sqltype'='integer','value'='5','andOr'=','})>

...

<cfset ArrayAppend(wherearray,{'column'='rec_id','operator'='=','sqltype'='integer','value'=id,'andOr'=''}>

<cfset UpdateQuery(dsn=application.datasource,tables="answers",setparams=sqlarray,whereparams=wherearray)>

Answer 2

太长，读不下去了

CFQueryparam不能嵌套在字符串中。必须直接在

安全问题

虽然您可能只是出于绝望才尝试了PreserveSingleQuotes（），但除非您了解其后果，否则不要使用该函数，因为它基本上会在应用程序中创建一个很大的sql注入漏洞。

另外，使用这种动态sql语句时要非常小心。即使使用cfqueryparam保护所有参数，查询仍然容易受到sql注入的攻击，因为myColumn是用户提供的值。不幸的是，cfqueryparam无法保护对象名（表名、列名等），仅限文字（字符串、日期等）。因此，如果必须在原始sql中使用动态列名，请确保根据白名单验证它们，如果检测到无效列，请拒绝请求。