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. :)
4 Comments
Comments for this entry have been disabled.