在Coldfusion查询中找不到语法错误

Question

我不明白为什么会出现这个错误。我使用cfform将数据从一个html页面发送到下一个。请参见下文。我注意到的另一件事是，angle_changes字符串的前两个字符被截断了。它应该是"0a0a0a0a"，但"0a0a0a"在错误消息中被传递。

以下是spatialforaging.cfm:中的相关html/Javascript代码

<!---all of these get passed from a previous page using cfoutput, except for angle_changes--->
<cfform action="field_transition.cfm" method="post" name="field_form"> 
<cfinput type="hidden" id="angle_changes" name="angle_changes" value="">
<cfinput type="hidden" id="subject_id" name="subject_id" value=#subject_id#>
<cfinput type="hidden" id="times_switched_away" name="times_switched_away" value=#times_switched_away#>
<cfinput type="hidden" id="total_time_unfocused" name="total_time_unfocused" value=#total_time_unfocused#>
<cfinput type="hidden" id="completed_fields" name="completed_fields" value="">
</cfform> 

编写脚本

//these values get changed earlier in the script
document.getElementById("times_switched_away").value = times_switched_away;
document.getElementById("total_time_unfocused").value = total_time_unfocused;
document.getElementById("completed_fields").value = completed_fields.toString();

//angleChanges is an array containing integers which is created elsewhere in the script
var angleChangesFormString = document.getElementById("angle_changes").value;
//adding each angle change to a string to add to database
for (i=0; i < angleChanges.length; i++) {
angleChangesFormString += angleChanges[i].toString();
angleChangesFormString += "a";
}

angleChangesFormString = angleChangesFormString.replace("undefined","");
document.getElementById("angle_changes").value = angleChangesFormString;
//this does print the correct value of '0a0a0a0a' to alert
alert(document.getElementById("angle_changes").value);
document.getElementById("field_form").submit();

以下是来自field_transition.cfm 的查询

<cfquery datasource="exmind">
update dbo.sf
set completed_fields = #completed_fields#
where subject_id = #subject_id#
</cfquery>
<cfquery datasource="exmind">
update dbo.sf
set times_switched_away = #times_switched_away#, total_time_unfocused = #total_time_unfocused#
where subject_id = #subject_id#
</cfquery>
<!---the first two queries work fine and update the database properly--->
<!---but this one gives the error message below.--->
<!---I do not see a syntax error, it is formatted exactly like the two queries above which work fine--->
<cfquery datasource="exmind">
update dbo.sf
set angle_changes = #angle_changes#
where subject_id = #subject_id#
</cfquery>

错误消息：

执行数据库查询时出错。

[MMacromedia][SQLServer JDBC驱动程序][SQLServer]附近的语法不正确"a0a0a0a"。

错误发生在C:REDACTED/field_transition.cfm：第64行中

62:更新dbo.sf

63:设置角度更改=#angle_changes#

64：其中subject_id=#subject_id#

65:

66:

供应商错误代码102

SQLSTATE HY000

SQL更新dbo.sf设置angle_changes=0a0a0a0a，其中subject_id=523550

DATASOURCE exmind

Answer 1

我认为问题在于您没有使用<cfqueryparam>。

您尝试更新的字段看起来像一个字符串字段。

<cfquery datasource="exmind">
update dbo.sf
set angle_changes = <cfqueryparam value="#angle_changes#" cfsqltype="cf_sql_varchar">
where subject_id = <cfqueryparam value="#angle_changes#" cfsqltype="cf_sql_integer">
</cfquery>

当尝试更新varchar/char/text数据库字段时，查询应该看起来像下面的set angle_changes = '0a0a0a0a'(带引号(。<cfqueryparam>以更好的方式类似。

<cfqueryparam>通过防止SQL注入来提高代码的安全性。