Skip to main content
WolfShade
WolfShade
Legend
September 19, 2012
Answered

Dynamic SQL - sanitize without CFQUERYPARAM?

  • September 19, 2012
  • 2 replies
  • 8295 views

Hello, everyone.

I'm trying to build a dynamic WHERE clause for a query, based upon a user form submit.  Since I can't use CFQUERYPARAM for the whole WHERE clause, is there a way to sanitize the WHERE clause? 

I cannot use Stored Procedures, and it's an Oracle database, not my familiar MS-SQL.

Thank you,

^_^

    This topic has been closed for replies.
    Correct answer 12Robots

    You could use cfqueryparam if you pieced together the WHERE clause INSIDE of your cfquery. That is a much better practice overall.

    <cfquery>

    SELECT colA,.. colF

    FROM table

    WHERE 1=1

    <cfif structkeyExists(FORM, "colA") AND len(FORM.colA)>

         AND colA = <cfqueryparam value="#FORM.colA#" cfsqltype="cf_sql_varchar" />

    </cfif>

    <cfif structkeyExists(FORM, "colB") AND len(FORM.coldB)>

         AND colB = <cfqueryparam value="#FORM.colB#" cfsqltype="cf_sql_varchar" />

    </cfif>

    <!--- etc, etc --->

    ORDER BY colA, colD

    </cfquery>

    2 replies

    12Robots
    12Robots
    Participating Frequently
    September 19, 2012

    Why can't you use cfqueryparam for a dynamic WHERE clause?  I do it all the time.

    Jason

    S
    Steve Sommers
    Legend
    September 19, 2012

    The main thing you have to do is escape all single quotes within string values and make sure number values are numbers by using val(), especially if the values come from the client. Lastly, you'll need to use preserveSingleQuotes(queryStr) when you do finally use your dynamic query string.

    WolfShade
    WolfShadeAuthor
    Legend
    September 19, 2012

    Thank you for the reply.

    I have PreserveSingleQuotes in place, already.  I am just worried about removing SQL commands from the user input.

    The query is to search for Training Programs, and the user can enter free text into input type="text" fields for searching against program name, program description, and program type.

    I do have JavaScript in place to remove all punctuation (including the semi-colon and apostrophe), and the database does not allow chained commands.  But I do not feel this is secure enough.

    Currently, based upon user input, the where clause is being pieced together based upon conditionals before the query is executed, so my current query looks like (pseudo code):

    SELECT colA,.. colF

    FROM table

    #PreserveSingleQuotes(whereClause)#

    ORDER BY colA, colD

    What is the best way to prevent SQLi since I cannot use cfqueryparam?

    Thank you,

    ^_^

    S
    Steve Sommers
    Legend
    September 19, 2012

    Like I said, escape single quotes and use val() for numeric values from the client. NEVER allow stuff like this in your where clause: where someString='#URL.someString#' or someNumber=#FORM.someNumber# . This must be coded like: where someString='#replace(URL.someString,"'","''","ALL")#' or someNumber=#val(FORM.someNumber)#. Really, you probably need more validation, but this is the minimum.

    But like Jason, I would lean toward redesigning to be able to use the cfqueryparam. It's a lot more idiot proof which will come in handy when some other programmer updates your work -- or you update your own work a few years from now and forget or overlook the gotchas.

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