If you code in cold fusion you should be using cfqueryparam when you are inputting data into your back end database, that’s a given as it is pretty effective against sql injection hacks, but when you do this you run the risk of throwing errors that you never worried about previously. The two most common problems are maxlength violations, and null value errors in numeric fields, to handle these two issues in your Extjs project I suggest the following approach:
Client Side Considerations:
Always use the xtype:numberfield, or the Ext.form.field.Number input control.
Make sure that you set a valid minValue, or it will default to negative infinity, and you may also need to set a maxValue if you do not want the user to enter a number greater than the ceiling on the data type.
Remember that decimals are allowed by default, so if you want to store the input in an integer field make sure you set allowDecimals to false.
Decide if this is a required field, if so set allowBlank to false to make sure that the user enters a number before submitting the field.
Server Side Considerations:
When you always want a value in the database field (for example a price or quantity field) — Always use cold fusion’s val() function around your input variables as it will convert the numeric characters at the beginning of a string to a number, but, will replace the value with a zero if there are leading characters, meaning the end result is you will always get a valid number in your number field. See the code below.
UPDATE some_table SET some_currency_value = <cfqueryparam cfsqltype="cf_sql_float" value="#val(some_user_input_data)#" />
In the example above if some_user_input_data was set to XYZ6563 then the code above would set the value zero. If the numbers were in front of the letters then the letters would be removed and the value entered into database would be 6563 and XYZ would be left off.
When you may actually want a null in the database field – If you do not want to use the val() function but still want to prevent invalid data input from causing cold fusion errors on the back end of your application you can use the IsValid() function coupled with the null attribute in cfqueryparam and ensure that your invalid values insert a null into the field.
Here’s how and why.
UPDATE some_table SET some_int_value = <cfqueryparam cfsqltype="cf_sql_integer" value="# some_user_input_data #" null="#not IsValid('integer', some_user_input_data)#" />
The null attribute is looking for a yes or a no, a 1 or a 0 to tell it to ignore the value in the value field and just put the NULL into the database using not in front will reverse the 1 or zero that the Isvalid function returns thus telling cold fusion to place a NULL in the database when the value passed was not a valid integer. You can take a look at more features of the IsValid function here.
There you have it, some tips on keeping your number field values in place and ensuring that the integrity of your back end data stays sound.