We use cfqueryparam in query parameters to secure database from malicious and un-authorized users (through SQL injection), perform data validation and get benefits of sql-bind property. We use cfqueryparam whether we do INSERT, UPDATE, DELETE OR SELECT.
One problem we might face when we have a column with INT, TIMESTAMP datatype and the column is not a required field which allows NULL values. If a user leaves a form field blank in UI (for which we have datatype of INT or TIMESTAMP in database) and the database is expecting INT or TIMESTAMP it will throw database error.
1. Database default like 0 (INT), 01/01/2000 (DATE/TIMESTAMP)
2. Conditional logic to set empty string to an INT like 0 or date like 01/01/2000
3. Conditional logic to use the NULL attribute of cfqueryparam.
Third option is little different that we might not have thought of, but probably the best solution out of all 3. Because we would not like to save 0 in an INTEGER column unless we know it’s really the exact value. The null attribute of cfqueryparam can take a value of ´yes/true’ or ´no/false’. And if we set null=´yes/true’ anything in value attribute will be ignored and NULL value will be passed to the database.