Skip to main content
U
uzeruzer
Participating Frequently
August 24, 2009
Question

Secure parsing of data in MySQL query?

  • August 24, 2009
  • 3 replies
  • 3056 views

I am quite new to CF, but want to make sure the variables that get put in a query are secure and dont leave me open to hacks. I made a simple guestbook since I'm learning.

Here is my CFC

<cfcomponent>
    <!--- GET COMMENTS --->
    <cffunction name="get_comments" access="public" returntype="query">
        <cfquery name="q_comments" datasource="guestbook">
            SELECT name, location, comment, timestamp
            FROM comments
            ORDER BY timestamp DESC
        </cfquery>
        <cfreturn q_comments>
    </cffunction>
   
    <!--- INSERT COMMENT --->
    <cffunction name="insert_comment" access="public" returntype="void">
        <cfargument name="form_data" type="struct" required="yes">
        <cfquery datasource="guestbook">
            INSERT INTO comments
            (name, location, comment)
            VALUES
            ('#form_data.name#', '#form_data.location#', '#form_data.comment#')
        </cfquery>
    </cffunction>
</cfcomponent>

Here is index.cfm

<cfif IsDefined("form.submit")><!--- one field is sufficient --->
    <!--- define the struct to be passed to the function --->
    <cfset data.comment = trim(form.comment)>
    <cfset data.name = trim(form.name)>
    <cfset data.location = trim(form.location)>
     <cfif comment NEQ "">        
          <cfif name EQ "">
               <cfset name = "Anonymous">
          </cfif>
          <cfinvoke
                  component="guestbook.cfc.db"
                  method="insert_comment">
                  <cfinvokeargument name="form_data" value="#data#"/>
          </cfinvoke>
     <cfelse>
          <cfset message="Comment is required!">
     </cfif>
</cfif>

<cfform method="post" name="guestbook">
<label>Name </label><cfinput type="text" name="name" size="50" maxlength="50">
<label>Location </label><cfinput type="text" name="location" size="50" maxlength="50">
<label>Comment </label><textarea name="comment" cols="65" rows="10" wrap="virtual"></textarea>
<cfinput type="submit" name="submit" value="Submit">
</cfform>

This topic has been closed for replies.

3 replies

T
TLC-IT
Inspiring
August 25, 2009

When you use <cfqueryparam>, ColdFusion automagically inserts "placeholders" into the SQL text, and supplies corresponding "parameter values" for each.

The SQL server, therefore, recognizes the presence of the placeholders in the SQL query, and at the appropriate time (i.e. after finishing the task of parsing the SQL text...) it inserts the parameter-values.  At this point, the textual content of the parameter cannot be mis-construed as part of the SQL text.  (The essence of an "SQL injection" attack.)

As noted, you still need to be mindful of the presence of unwanted material in what has been stored.  You need to filter-out that material altogether, or modify it so that it becomes "harmless," and you need to "HTML escape" the character sequences as you output your web-pages based on this material so that the user's browser cannot interpret as HTML content.  (A "cross-side scripting" attack might well be called an "HTML injection" attack, taking place on the subsequent user's system.)

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
August 25, 2009

The user seems to interact with your database via the insert-query, so I would sanitize that with cfqueryparam, as follows

<cfquery datasource="guestbook">
    INSERT INTO comments
    (name, location, comment)
    VALUES
        (<cfqueryparam value="#form_data.name#" cfsqltype="CF_SQL_VARCHAR">,
    <cfqueryparam value="#form_data.location#" cfsqltype="CF_SQL_VARCHAR">,
    <cfqueryparam value="#form_data.comment#" cfsqltype="CF_SQL_VARCHAR">)
</cfquery>

Also, you can configure Coldfusion to detect any attempts at cross-site scripting. Use the attribute scriptProtect in the cfapplication tag in Application.cfm or the setting this.scriptProtect, if you have Application.cfc instead. The value to set it to is "all". Alternatively, you could set it to any comma-delimited list of scopes that includes form, for example, "form,url,cookie".

Remember you first have to enable the use of scriptProtect in the Coldfusion Administrator. You can test scriptProtect  yourself by submitting something like <script>hack</script> in a form.

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
August 24, 2009

You should add cfqueryparam to the variables you are using in your cfquery SQL statements.  This will help you avoid SQL injection attacks.  See the links below for more information.

cfqueryparam tag
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html#1102474

"Enhancing security with cfqueryparam"
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=queryDB_1.html#1127430

"SQL Injection" on wikipedia

http://en.wikipedia.org/wiki/Sql_injection

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
August 24, 2009

In addition you may want to remove an potential unwanted text from you input before storing it in your database.  This will reduce your vulnerability to cross site scripting (XSS) attacks.
For more on XSS see http://www.12robots.com/index.cfm/2008/8/4/Persistent-XSS-Attacks-and-countermeausures-in-ColdFusion.  To remove HTML from a variable you can use the StripHtml function available at http://www.cflib.org/udf/stripHTML .

Take a look at the security topics at Jason Dean's website http://www.12robots.com.  He has done several very good ColdFusion related security blog entries.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices