DeMystifying CFINSERT SQL Injection

Not much has been said as to the security of Adobe ColdFusion cfinsert and cfupdate tags. These functions transform input from a POST request into a database insert or update query. Essentially, a developer specifies a database table and creates a form field on a page. When a user submits those values, ColdFusion automatically creates a query to insert or update the corresponding values in the database. In essence, these tags give the developer the ability to execute SQL without writing SQL.

Looking through information available online and through Adobe, it is mentioned that these functions are vulnerable to SQL injection but there is no mention as to how. The majority of discussion pertaining to this potential security issue stopped abruptly around 2007. Even an article written by the 0x000000 hacker webzine in the summer of 2008 about ColdFusion security did not mention cfinsert or cfupdate vulnerabilities.

After looking in to the documentation of these tags, something just did not sit right. As a programmer, I generally desire very fine grained control over the flow of computation. As a security consultant, I generally fear an application which “writes the code for you.” It is very difficult for an application framework to draw the line between robust and secure.

Overview

For practical reasons, I decided to only look at cfinsert’s behavior with regards to Microsoft SQL Server 2000. The test lab consisted of two virtualized Microsoft Windows 2003 Server machines. One machine ran Microsoft IIS 6.0 with ColdFusion 8.x. The other machine ran Microsoft SQL Server. The reason for the breakout is Microsoft Windows does not take kindly to packet sniffing across loopback interfaces.

The general approach taken was to observe database commands sent from ColdFusion to SQL Server under three basic query scenarios. The scenarios identified were a regular (vulnerable) query, a parameterized query, and a cfinsert query. The first two queries where chosen to serve as a baseline against the potentially vulnerable cfinsert query. The tools used included Wireshark and the Microsoft TRACE tool, available as part of SQL Server. In the interest of simplicity, the database has no primary keys or relationships defined. Below are the observations of these commands.

The Results

Standard query with no protection

As expected, the standard ColdFusion cfquery function offers no protection from injection vulnerabilities. The TDS packet (RPC packet for SQL Server databases) sent inserted the user controlled parameters verbatim in to the SQL query allowing injection.

Standard query with parameterized values

The first time a parameterized statement is run after a ColdFusion server reboot, it will make use of the sp_prepexec command to dynamically create and execute a parameterized SQL statement in Microsoft SQL Server. Subsequent calls make use of the sp_execute command. User input is properly separated from the SQL logic. Therefore, typical SQL injection attacks are not possible.

cfinsert

This is the case that is of most interest to us and also the most complicated. The cfinsert (and cfupdate) statements work in the same way as the parameterized queries with one additional step. Before a call to either sp_prepexec or sp_execute is made, the sp_columns prepared statement is called.

The first statement executed is sp_columns N’tmptable’, NULL, NULL, N’%’, @ODBCVer = 3. One can only assume that ColdFusion is verifying names of the POST variables align with the column names in the table. By adding or modifying the names of POST variables to nonexistent columns, a call to the cfinsert query fails.

Conclusion

To answer the original question, there is no black and white answer as to whether cfupdate or cfinsert are injectable. If the application is not coded to strictly validate the POST data before use in cfinsert queries, you can add information to other columns the original developer never intended. Although this is not as severe as a more traditional SQL injection attack, depending on the data modified, this can still introduce a large amount of risk to the application. For example, when creating a new user and adding to an authentication table, I can specify additional fields and arbitrary information in the POST request that modify my account type or permissions.

Recommendations

My recommendation is to rewrite your cfinsert and cfupdate statements to use parameterized queries or prepared statements. Even if there are no columns for which a user should not be able to access at the moment, there is no guarantee that a database change will not introduce problems in the future. Futhermore, this allows the developer to introduce integrity checks which is a cornerstone of a good application security strategy. If this is not an option, Adobe ColdFusion also supplies a method to specify a whitelist of form fields to allow. This is essentially a requirement for using cfinsert of cfupdate securely. An example of using specifying a cfinsert whitelist is shown below:

<cfinsert datasource="database" tablename="table" formfields="columna, columnb, columnc"></cfinsert>