查询-字符串文字过长ORA错误

Question

我在Cold Fusion 11*中工作，收到以下错误：

ORA-01704: string literal too long.

首先，我收到了这个错误，并在查看了几个网站后通过更改我的代码来更正它：

<cfquery datasource="#dsn#">
update paragraphs
set paragraph_text = #input#
where  paragraph_id=#rs_d.paragraph_id#
</cfquery>

收件人：

<cfquery datasource="#dsn#">
update paragraphs
set paragraph_text = <cfqueryparam cfsqltype="CF_SQL_CLOB" value=#input#> 
where paragraph_id=#rs_d.paragraph_id#
</cfquery>

这个修复非常有效。现在我得到了同样的错误，但我没有使用cfquery，而是在使用它之前将sql查询构建成一个字符串

sql = "insert into log (LOG_ENTRY_ID, program_id, paragraph_id, action, userid,";
sql = sql & " paragraph_text_old, paragraph_text_new, comment_id, current_program_status, new_program_status)";
sql = sql & " values (1 ," & program_id & ",";
if (paragraph_id neq ""){
sql = sql & paragraph_id & ",";
}
else{
sql = sql & " null,";
}
sql = sql & "'" & action & "',";
sql = sql & userid & ", '";
sql = sql & DoubleSingleQuotes(paragraph_text_old) & "','";
sql = sql & DoubleSingleQuotes(paragraph_text_new) & "',";
if (comment_id neq ""){
sql = sql & comment_id & ",";
}
else{
sql = sql & " null,";
}
if (current_program_status neq ""){
sql = sql & "'" & current_program_status & "',";
}
else{
sql = sql & " null,";
}
if (new_program_status neq ""){
sql = sql & "'" & new_program_status & "'";
}
else{
sql = sql & " null";
}       
sql = sql & ")";
cfstmt(sql);

---终端功能

<cffunction name="cfstmt">
<cfargument name="sql">
<cfquery name="rs" datasource="#dsn#">
#PreserveSingleQuotes(sql)#
</cfquery>
</cffunction>

错误的原因是old_paragraph_text和new_paragrapht_text。我想知道是否可以像我包含的上一个问题/解决方案一样，包含一个cfqueryparam类型的解决方案来解决这个问题。我试着像第一个一样直接包含它，但我在代码中遇到了编译错误。任何想法或建议都会有所帮助，谢谢。

Answer 1

(注释太长)

希望限制代码，因此不确定null值在查询之外，我添加了案例语句

老实说，它并没有节省太多，因为代码本质上是在执行if/else。只是现在工作是在数据库端完成的，而不是在它所属的应用服务器端。

在使用过许多遗留应用程序后，我意识到它们经常包含你可能委婉地称之为"可疑"的代码；-)但是，您应该永远不要在SQL中使用原始客户端变量。除非有充分的理由，否则请始终使用cfqueryparam。

在内部，cfqueryparam使用绑定变量。两个最重要的好处是：

1. 绑定变量通过防止文字值作为sql命令执行，有助于防止sql注入。这样可以保护数据库不受包含恶意构建的SQL的值的影响。

2. 对于多次执行的查询，绑定变量还通过鼓励数据库重用执行计划来提高性能。否则，当查询参数改变时，数据库可能会选择生成新的执行计划，这是昂贵的。

CFQueryparam还有一些其他不错的特性，例如"null"属性。它可用于在满足某些条件时提交null值。

最后，就最佳实践而言，充分考虑所有变量也是一个好主意。例如，如果变量是在FORM范围内提交的，那么最终的查询可能看起来像这样。(根据需要修改cfsqltypes。)

<cfquery datasource="#variables.dsn#">
INSERT INTO into log (
program_id
, paragraph_id
, userid
, action
, paragraph_text_old
, paragraph_text_new
)
VALUES 
(
<cfqueryparam value="#FORM.program_id#" cfsqltype="CF_SQL_INTEGER">
, <cfqueryparam value="#FORM.paragraph_id#" cfsqltype="CF_SQL_INTEGER" null="#NOT IsNumeric(FORM.paragraph_id)#">
, <cfqueryparam value="#Session.userid#" cfsqltype="CF_SQL_INTEGER">
, 'Paragraph Updated' 
, <cfqueryparam cfsqltype="CF_SQL_CLOB" value="#rs_d.paragraph_text#">
, <cfqueryparam cfsqltype="CF_SQL_CLOB" value="#FORM.input#"> 
)
</cfquery>  

注意：除非某列指定了不同的默认值，否则当从INSERT列表中省略该列时，将自动插入NULL。

Answer 2

我建议重新安排您的逻辑以确定field null变量，然后在cfqueryparam的null属性中使用这些变量。类似这样的东西：

<cfscript>
fieldOneNull = conditionForNull ? true : false;
fieldTwoNull = conditionForNull ? true : false;
etc
</cfscript>
<cfquery>
insert into table
(field1, field2, etc)
values
(
<cfqueryparam cfsqltype="cf_sql_whatever" value="something" null="#fieldOneNull#">
, <cfqueryparam cfsqltype="cf_sql_whatever" value="something" null="#fieldTwoNull#">
, etc
)

Answer 3

感谢您的建议。我没有修改应用程序不同领域中使用的现有函数，而是在调用该函数以处理clobs的位置添加了一个新的insert语句。为了处理空字段，我使用了case语句。

<cfquery datasource="#dsn#">
insert into log (
LOG_ENTRY_ID, program_id, paragraph_id, userid, action, paragraph_text_old
, paragraph_text_new, comment_id, current_program_status, new_program_status
)
values (
null ,#program_id#,(case when #paragraph_id# = '' then null else #paragraph_id# end)
, #Session.userid# , 'Paragraph Updated'
, <cfqueryparam cfsqltype="CF_SQL_CLOB" value=#rs_d.paragraph_text#>
, <cfqueryparam cfsqltype="CF_SQL_CLOB" value=#input#> 
, null, null , null
)
</cfquery>