2 Replies Latest reply on Aug 13, 2007 7:39 AM by Newsgroup_User

    cfqueryparam question

    murpg
      I have a general ColdFusion security question. If I am using cfqueryparam and the cfl_sqltype is varchar can SQL injection code get passed to my database. I had someone tell me that the quotes get striped off. I don't beleive that is true. Can someone give me the breakdown on that please.
        • 1. Re: cfqueryparam question
          iKnowKungFoo Level 1
          cfqueryparam will strip out any sql injection attempts regardless of cf_sqltype, but it does more than that.

          There are a couple of current threads on CF-Talk that have been discussing this.

          Is cfqueryparam worth it?

          cfquery: quotes vs queryparam
          • 2. Re: cfqueryparam question
            Level 7
            murpg wrote:
            > I have a general ColdFusion security question. If I am using cfqueryparam and
            > the cfl_sqltype is varchar can SQL injection code get passed to my database. I
            > had someone tell me that the quotes get striped off. I don't beleive that is
            > true. Can someone give me the breakdown on that please.
            >

            And it is not true because <cfqueryparam> passes the data as a 'bind'
            parameter to the database management systems that can use them. That is
            what you want to read up on to get a full understanding of the process.

            Basically it separates data variables and tells the database that these
            are data variables and will NEVER EVER contain executable SQL so don't
            even bother trying to parse it.

            Simple example

            <cfquery ...>
            SELECT aField
            FROM aTable
            WHERE bField = #someValue#
            </cfquery>

            In this use case ColdFusion process the entire block between the
            <cfquery...> tags into a single string and sends it the database.
            ColdFusion nor the database has any idea what is data and what is SQL
            commands so it will parse any it finds.

            <cfquery...>
            SELECT aField
            FROM aTable
            WHERE bField = <cfqueryparam value="#someValue#"
            cf_sql_type="someSQLtype">
            </cfquery>

            In this case ColdFusion will seperate the query param and send it
            seperatly as the above mentioned bind parameter which both CF and modern
            database management systems understand to be data and will never process
            any SQL syntax it might contain. It the pareses the rest of the SQL
            into a string that it sends to the database to be parced, using the
            binded parameters.

            HTH
            Ian