我应该修剪SQL或ColdFusion中的值吗? [英] Should I trim values in SQL or ColdFusion?

问题描述

我已经完成了许多插入/更新操作，而我一直在脑海中浮现的一个问题是，我应该在哪里修剪要插入或更新的值.在这种情况下，我将ColdFusion用作服务器端编程语言，而SQL Microsoft是我的数据库语言.因此，如果我要进行插入，是否应该修剪ColdFusion或SQL中的值?两者都必须做吗?有什么更有效的?我想知道如果遵循一些建议的步骤是否可以提高效率.这是我插入代码之一的示例:

I have done many inserts/updates and question that always was on the top of my mind is where I should trim the values that I'm inserting or updating. In this case I use ColdFusion as my server-side programming language and SQL Microsoft is my database language. So if I'm doing insert, should I trim the values in ColdFusion or SQL? Do I have to do in both? What is more efficient? I was wondering if this can improve efficiency if I follow some recommended steps. Here is example that I have in one of my Insert codes:

<cfset userphone = trim(user_phonenum)>
<cfset userdob = trim(user_dob)>

INSERT INTO  UserTest
  ( mm_phone,
    mm_dob
  )
VALUES
  (  
     '#trim(userphone)#',
      CASE WHEN LTRIM(RTRIM('#userdob#')) = '' THEN NULL ELSE LTRIM(RTRIM('#userdob#')) END
  )

在上面的代码中，我使用了ColdFusion装饰，在其中我设置了两个值 userphone 和 userdob .我应该在SQL Insert语句中的下面还是下面进行修剪?有什么更好，更有效的方法?如果有人可以帮忙，请告诉我.谢谢.

In the code above I have used ColdFusion trim where I set both values userphone and userdob. Should I do the trim there or down below in SQL Insert statement? What is better and more efficient? If anyone can help please let me know. Thank you.

推荐答案

您可以使用 Application.cfc 文件中的 onRequestStart()自动修剪所有表单字段.这段代码在每个HTTP POST的顶部运行.

You can automatically trim all form fields by using onRequestStart() inside of your Application.cfc file. This code runs at the top of every HTTP POST.

<cffunction name="onRequestStart" returnType="boolean">
    <cfargument type="String" name="targetPage" required=true/>

    <cfif CGI.REQUEST_METHOD IS "POST">

        <cfloop collection= "#form#"  item="local.field">
            <cfset form[local.field] = trim(form[local.field])>
        </cfloop>

    </cfif>

    <cfreturn true>
</cffunction>

如果您使用的是ColdFusion 11或更高版本，还可以使用本机的AntiSamy函数 getSafeHTML()清理表单数据.这将删除恶意的XSS攻击代码.

If you're using ColdFusion 11 or later, you can also scrub the form data using the native AntiSamy function getSafeHTML(). This removes malicious XSS attack code.

< cfset form [local.field] = trim(getSafeHTML(form [local.field]))>

此处的更多信息: http://blogs.coldfusion.com/post.cfm/security-enhancements-in-coldfusion-splendor-pbkdf2-and-antiisamy

然后，如果您的查询只是通过CF代码完成的，那么您应该

Then, if your query is just done via the CF code, then you should

1. 将变量的作用域限定为 form 作用域.
2. 使用 cfqueryparam 防止SQL注入攻击.

1. Scope your variables to the form scope.
2. Use cfqueryparam to protect against SQL Injection attacks.

INSERT INTO  UserTest (
    mm_phone
    , mm_dob
)
VALUES (
    <cfqueryparam value="#form.userphone#" cfsqltype="cf_sql_varchar" />
    <cfif len(form.userdob) EQ 0>
        , <cfqueryparam cfsqltype="cf_sql_date" null="true" />
    <cfelse>
        , <cfqueryparam value="#form.userdob#" cfsqltype="cf_sql_date" />
    </cfif>
)

这篇关于我应该修剪SQL或ColdFusion中的值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！