• May 28th, 2008
• Posted by dantheman
• Programming Goodness
• No Comments

When using Cold Fusion with dynamic forms that feed a SQL query, it is always a good idea to use the <cfqueryparam> tag to help stop sql injection.

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

cfqueryparam

Verifies the data type of a query parameter and, for DBMSs that support bind variables, enables ColdFusion to use bind variables in the SQL statement. Bind variable usage enhances performance when executing a cfquery statement multiple times.

This tag is nested within a cfquery tag, embedded in a query SQL statement. If you specify optional parameters, this tag performs data validation.

Note from The Hahn:
I thought it worth providing a sample I use for cfqueryparam since null values are a bit wacky.

<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="14" value="#FORM.field#" null="#YesNoFormat(NOT Len(Trim(FORM.field)))#">

This checks the data type and if the field is blank inserts a null. The “null” attribute of the cf tag uses yes/no for some reason, thankfully CF has that little YesNoFormat function. Note that an error in length will throw an ugly error message, you should do some server side checking of your own and display a custom error message.

Thanks to Dan for bringing this up and for linking keywords!