ColdFusion Tip: User CFQUERYPARAM CFSQLTYPE to Format Your SQL Variables
March 9, 2012 in Web Dev
If you’ve ever tried to move data between two different flavors of databases, you know it can be painful, frustrating, time-consuming and often disappointing. But if you’re working in ColdFusion, CFQUERYPARAM’s CFSQLTTYPE parameter makes life a lot easier, particularly when moving large amounts of text.
After banging my head trying to convert MS SQL data types to ColdFusion data types to MySQL datatypes, I found a trick that works well AND enhances the security of our ColdFusion queries:
Use <cfqueryparam cfsqltype=”cf_sql_integer” value=”#myVar#” /> in your insert queries instead of trying to cast the perfect MS SQL or MySQL format. The “cfsqlytype” parameter has several types, including dates and varchars, so any type of data can be magically converted to the proper flavor of datatype and format without jumping through hoops. The “cfqueryparam” tag also provides protection against SQL injection and seems to process large requests much more quickly.
Bon appetit!