"Variables"
ColdFusion Variable Types:
- A variable is a named location in memory that holds a value. The "type" of variable refers to the type of data stored at that memory location. Some languages strongly resist converting numbers to strings (text) values and vice-versa; these languages are referred to as "strongly typed." ColdFusion is a "loosely typed" programming language.
- This means that variables are not (generally speaking) strongly associated with one data type or another; for example, a variable assigned a numeric value can be used as if it were a string variable. The length of text variables need not be defined before use, and I've created string variables several megabytes in length.
- The basic data types in ColdFusion (not that it's really all that important) are Numeric, String, Date/Time, and Boolean.
- There are others data types: arrays can be created; SQL queries, once executed, become a type of array; and structures were added in version 4.0. A List is almost another data type is ColdFusion, but it is actually just a string with some sort of delimiter, usually a comma. Special variables cannot be automatically converted to other types.
- A string can only be converted to a numeric if it looks like a number. "45.3" can be converted; "two" cannot be converted. Any number can be converted to a string. The two basic Boolean values are TRUE and FALSE; however YES and NO work well too. Non-zero numbers are considered TRUE; zero is considered FALSE. String values other than true, false, yes, no, or something easily converted to numeric, will cause an error to be displayed.
- Variables names are NOT case-sensitive, must start with a letter, and can include letters, numbers, and underscores ("_"). "Customer_Name" is valid; "2Customer" and "Customer Name" are not valid variable names. The value of a variable is displayed by expressing the variable in pound signs, within a CFOUTPUT block:
<CFOUTPUT>Welcome to our online system, #Customer_Name#!</CFOUTPUT> - All variable names that contain a single value exist in "variable scopes." (More about that in the next section.)
SQL Data Types:
- SQL (as implemented by ODBC) has three data types: string, numeric, and date/time (or timestamp.) SQL queries are sent to SQL-based databases via the <CFQUERY> tag.
SQL Strings:
- SQL strings begin and end with a single quote ('). If you want to embed a single quote in a query, you need to type two single quotes. (Programmers generically called this 'escaping' a reserved character.) Therefore, to express the value:
I don't get this. as an SQL string, you would type:
'I don''t get this.' - Within a CFQUERY, ColdFusion will replace single quotes in #variables# with double quotes automagically; however, ColdFusion will not do single quote escaping for functions. Therefore, any data that may contain single quotes should be manipulated outside of the CFQUERY statement.
| Wrong: | <CFQUERY...> SELECT * FROM Users WHERE UserName = '#ucase(User_Name)#'</CFQUERY> |
| Right: | <CFSET UserName = ucase(UserName)><CFQUERY...> SELECT * FROM Users WHERE UserName = '#User_Name#'</CFQUERY> |
SQL Numbers:
- Numbers are provided to SQL without any special consideration. However, ColdFusion programmers must be sure to handle numeric values passed into queries! Otherwise, the supposedly numeric value could be 'hijacked' by a malicious user and used to run other SQL commands. Say a link is created that passes a numeric row identifier "OrderID." OrderID is the primary key for the table "Orders" and is numeric. Although it's tempting to write a query to get the order from the database like this:
SELECT *FROM OrdersWHERE OrderID = #OrderID#- Someone could easily replace the numeric OrderID (e.g. "5") an the URL line (or alter the form, etc.) with a value followed by a malicious command, such as "http://myserver/MyPage.cfm?OrderID=5+delete+from+orders". The SQL passed to the database system would then look like this:
SELECT *FROM OrdersWHERE OrderID = 5 delete from orders - Most high-end database systems allow more than one SQL statement per query. Without very conservative database security settings, the above command would delete all orders from the system. You don't want to get that phone call. All numeric values passed by the user should be wrapped by the ColdFusion val() function, which returns the numeric value of the parameter supplied to it, or zero (0) if the value is not numeric.
- Therefore, val("5") is 5, but val("5 delete from orders") is 0. The code would be properly rewritten like this:
SELECT *FROM OrdersWHERE OrderID = #val(OrderID)#SQL Date and Date/Time values:
- ODBC was designed to provide one consistent application interface to many different DataBase Management Systems (DBMSes). There is a problem with providing one SQL front end to many DBMS back end systems...
Date/Time values, in ODBC, are provided in one of the following three formats:
- Timestamp: {ts '1999-11-01 15:14:23'}
- Date: {d '1999-11-01'}
- Time: {t '15:14:23.00001'}
For time (and timestamp) values, the seconds and fractional seconds portion are optional. Many SQL systems do not support fractional second timestamps; if you attempt to use a fractional second value with these systems, strange errors may occur.
An example SQL Insert showing all three data types:
<CFQUERY Name="InsertCustomer" DataSource="CustomerData"> INSERT INTO Customers ( CustomerName ,CustomerAge ,DateAdded ) VALUES ( 'John''s Bakery' ,30 ,{ts '1999-11-01 15:54:00'} )</CFQUERY>