Syntax Error in INSERT INTO or SELECT Statement

Issue

You receive a syntax error when performing an INSERT or SELECT, but the syntax appears correct. An example of the error you may receive is shown below:

Error Occurred While Processing Request
Error Executing Database Query.
Syntax error in INSERT INTO statement.
 
The error occurred in D:\vhosts\domain.co.nz\httpdocs\insertdb.cfm: line 97

95 :       <cfelse>
96 :       NULL
97 :   </cfif>
98 :   )
99 :   </cfquery>

SQL 	          INSERT INTO table (category, type, address , password) VALUES 
                  ( 'Health and Fitness' , 'Company Name', 'Address', 'password123')

DATASOURCE        datasource
VENDORERRORCODE   3092

Cause

ColdFusion has a list of reserved keywords, which are typically part of the SQL language and are not normally used for names of columns or tables. In the example error above, password is a reserved keyword, so the insert fails. If you use these reserved keywords you must escape them. You may not have encountered this issue with earlier versions of ColdFusion, which allowed the use of some non-escaped reserved keywords.

A list of reserved keywords can be found at:
http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=00000316.htm

Solution

Escape reserved keyword for a column name or table name by enclosing them in square brackets.

Example:

INSERT INTO table (category, type, address, [password]) VALUES 
                  ( 'Health and Fitness', 'Company Name', 'Address', 'password123')

However a better solution is to avoid using reserved keywords, by using a naming convention for tables and columns which avoid conflicts.

The following link discusses naming conventions for database objects:
http://www.devx.com/dbzone/Article/10866