CF9 can protect SQL statements stored as string from SQL injection

Posted by Dan on Jul 15, 2009 @ 3:21 PM

One of the many new features in ColdFusion 9 is the ability to use every tag within a <cfscript /> block. Developers seem to either love or hate <cfscript /> and I've always falling into the love (well, maybe not love but I'm extremely fond of it.) I just find <cfscript /> a better method for writing business logic. So for me, the addition of being able to access any CF tag via <cfscript /> will be really nice. However, I wanted to point out one specific use case where the addition of being able to script any tag really comes in handy.

One of issue I've run into in the past with ColdFusion is that there's no good way to protect a SQL statement from SQL injections if your SQL is coming from a string variable. In most cases you're going to build dynamic SQL statements within a <cfquery /> block, occasionally I've found situations were this isn't ideal.

In these cases I've wanted to use a UDF to generate a block of SQL for me, because I can easily re-use the function to re-use common blocks of SQL.

The problem with building the statement via a UDF is that when your SQL is generated as a string, there's you can't use <cfqueryparam /> to secure your data (unless you were to save the string to disk, include it in the query and then clean up the temp file.)

This is where CF9's new functionality of being able to script any tag will become handy. ColdFusion 9 introduces a way to bind a token to a SQL parameter. This would allow you to build a SQL statement completely from a string and then bind tokens in the string to parameters.

The following example code comes from John Whish's cfquery in cfml with parameters post:

// create a query
q = new Query();

// don't need to set datasource if using this.datasource in Application.cfc 
q.setDatasource( "cfartgallery" );

// build the SQL statement 
q.setSQL( "select * from Art where artistid = :artistid and issold = :issold" );

// this is the equivalent of cfqueryparam 
q.addParam( name="artistid", value=2, cfsqltype="CF_SQL_INTEGER" );
q.addParam( name="issold", value=1, cfsqltype="CF_SQL_BIT" );

// run the query and get a query object
result = q.execute();

// dump query object
writeDump( result );

As you can see from the code, the setSQL() method takes a string of SQL to execute. You'll also notice two unique tokens in the SQL ":artistid" and ":issold". When you invoke the q.addParam() method, you're replacing the tokens with a binding SQL variable—which protects you from SQL injections.

So, the addition to the new Query() object ends up solving a problem that I've run into in the past to which there's never been a really good solution for in the past.

I've been meaning to post this since the CF9 beta became public, but work kept getting in the way. :)

Categories: HTML/ColdFusion

4 Comments

  • At first look I thought, he's doing nothing I can't do with CFQUERY. But, then it occurred to me that now you can have dynamic SQL plus bind parameters.

    Maybe it would be good to have a dynamic SQL statement in your example, just for clarification. ;-)

    Also, I think one should note that dynamic SQL plus bind parameters increase security, but, they do not prevent SQL injection entirely.

    Plus, bind paramters are often used to increase performance because they create pre-compiled statements. With dynamic SQL this is quite ineffective, because every statement results in a new execution plan.
  • That is pretty nice!

    I still prefer cfquery overall, but that really increases the odds of my using this when I discover the need for a query deep in a cfscript block.

    I definitely think I will use cfscript much more in CF9 (and I have already been a big fan of it).
  • How do you add a dynamic value instead of "2" for artist_id
  • @Patrick:

    You would just do:
    q.addParam( name="artistid", value=VARIABLE_NAME, cfsqltype="CF_SQL_INTEGER" );

    And substitute the string "VARIABLE_NAME" with the value you're binding.

Add Comment

Leave this field empty