1

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
  )

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.

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • 3
    Why not code both ways of doing it, wrap them with timers and then loop over it a few thousand times to see which one is faster? My guess is that it won't matter all that much. Also, please use `cfqueryparam` for database inputs. Not only will it protect the site from SQL injection, you can rewrite the `userdob` statement to be arguably more readable ``. The `null` attribute is a boolean that when true will send NULL instead of a value. – beloitdavisja Jan 20 '17 at 03:12
  • 1
    Neither. In our major applications, we trim user input in the onBlur event of the form field. We've manage to put this into the Application.cfm or cfc files so it's automatic. Plus, in your example, if dob stands for date of birth, attempting to store dates as strings is a much more serious problem than whitespace. – Dan Bracuk Jan 20 '17 at 03:46
  • Is there a place to see this trim `onBlur()` ? – James A Mohler Jan 20 '17 at 06:19
  • Our function was written over 10 years ago, but here is something more recent. http://stackoverflow.com/questions/498970/trim-string-in-javascript – Dan Bracuk Jan 20 '17 at 13:21
  • 1
    The onBlur solution will work, but remember that this runs client side. I always recommend any kind of data cleaning or validation be run on the server side in coordination with any client side code. The server will ensure any bad data from being passed through, and the client side code for better UX. – beloitdavisja Jan 20 '17 at 13:30

3 Answers3

2

Try to handle the trim on server side on ColdFusion. The data needs to be validated before it gets inserted to the database.

You would say, I have client side validation but a user can easily bypass those via several plugins etc. I would handle the trim at ColdFusion level.

One more thing as a tip, I personally always like to handle validation or setting variables or any conditional logic etc before actual insert. The insert should just insert values of the variables, all the preprocessing , validation should happen before you are in cfquery tag if thats possible.

HTH.

ah7866
  • 136
  • 3
2

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>

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]))>

More info here: http://blogs.coldfusion.com/post.cfm/security-enhancements-in-coldfusion-splendor-pbkdf2-and-antisamy

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

  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>
)
Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44
  • One question, if I run my Insert statement and all the data will be populated from different database (so there is no user input fields/variables). Do I need 'cfqueryparam' in that case? Also should I trim all the values in cfquery and put the trim around all data fields that I will use? This way I think that will be enough and I do not need coldfusion trim after that. Maybe I'm missing something, if you can provide any feed back that would help. Thank you! – espresso_coffee Jan 20 '17 at 23:09
  • You should always use `cfqueryparam`, it's just good practice and sometimes there's no telling where the data from the other tables was sourced. You should only do the `trim()` in the DB if you're not sure how the data was entered in the first place or if the DB column is using `char` instead of `varchar`. A `char` column will sometimes add padding to make up the max number of characters defined for it. – Adrian J. Moreno Jan 23 '17 at 02:21
1

Even when you use "#trim(someVar)#" within <cfquery>, you are still trimming in ColdFusion. So let me rephrase your question to:

"Should I trim values with ColdFusion before passing them to the Database Management System or should I pass the values untrimmed and let the database do the trimming?".

The best answer is: trim the values with ColdFusion before passing them to the database. This complies with at least 3 GRASP patterns (GRASP = General Responsibility Assignment Software Patterns):

  1. Information Expert: ColdFusion is the expert who knows what the variable is, where it comes from and what its value should be. As such ColdFusion should do the trimming.

  2. Low Coupling: Different database brands generally have different functions for trimming. Implementing any such function in cfquery would imply that ColdFusion knows too much about a particular database brand. This intimacy would increase coupling.

  3. Protected Variation: Suppose, in the SQL in cfquery, you implement the trimming function of one particular database brand. Then your code will break when you switch the database to a brand for which the trim function is defined differently.

BKBK
  • 484
  • 2
  • 9