August 14th, 2011, 08:43 PM
CFQUERY Datatypes and Quotes
In CFQUERY operations, can someone advise as to the following:
1. What data types REQUIRE single quotes for UPDATE and INSERT statements?
* We are using MS SQL 2008 R2 with CF9
2. Is it best practice to use CFQUERYPARAM for EVERY statement now days?
I searched everywhere but can't seem to find any type of reference sheet anywhere that I can use when building my statemnts.
Thanks in advance.
August 15th, 2011, 10:24 AM
What data types require quotes actually depends on the database server you're using, not CF. However, generally it is things like char, varchar, and dates/times. You can check by running the SQL directly against your RDBMS using whatever tool they provide (Oracle TSQL command line, MS SQL Query tool, etc.)
You should ALWAYS use CFQUERYPARAM for ANY data that is coming from the outside. So if you are hard-coding a value you could get away with not using CFQUERYPARAM, but anything supplied by the user in the FORM, URL, or SESSION scope must be a bind variable. So if you're not sure, I would just use CFQUERYPARAM for everything, that way there is no doubt.