Is not including the cfsqltype for cfqueryparam still useful for sql injection protection?

Question

Can someone explain if not including the cfsqltype for cfqueryparam is still useful for SQL injection protection? And also what actually happens with cfqueryparam with the cfsqltype and w/o cfsqltype.

<!--- without cfsqltype--->
<cfqueryparam value="#someValue#"> 

<!--- with cfsqltype--->
<cfqueryparam value="#someValue#" cfsqltype="cf_sql_char">  

Answer 1

To get a good idea of what cfsqltype is likley doing under the hood take a look at the Java / JDBC PreparedStatement class: http://download.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html

You will notice various setInt, setDate, etc. methods - my understanding is that the cfsqltype is matched up with a corresponding method when it creates the prepared statement.

If you specify a type then ColdFusion needs to be able to cast the variable into that type, and if it can't it will throw an exception before sending the query to the database.

When you omit the cfsqltype it probably calls either setObject or setString. The behavior of what happens next is dependent on the JDBC driver you are using at this point. I've seen some cases where omiting the type can cause an error even when you are passing in valid variables, one that comes to mind is working with date and datetime on MySQL.

Another thing to consider is that if you omit the cfsqltype on let's say an integer field, but you pass a non integer value, ColdFusion could have thrown the exception before connecting to the database and sending the query if you specified the cfsqltype, but without it you wasting the DB connection and execution time on the database server.

Answer 2

One of the benefits of cfqueryparam is type checking before values are sent to your database. For example, you specify cf_sql_integer, CF not only verifies the value is numeric, but that it is a whole number within a specific range. When you omit the cfsqltype, CF will use cf_sql_char. So you obviously lose type checking for things like dates and numbers.

Personally, I think you should supply a cfsqltype. However, even if you did not, using cfqueryparam means CF uses bind variables. A side benefit of bind variables is helping to protect your queries against sql injection. So in that sense, it is still a good thing.

I think it is useful but as "validation" not "sql query injection protection".

Update: No, it still applies. The protection comes from using bind variables. Since CF will still use bind variables, even without a type, I believe the basic sql injection protection still applies.

That said, using cf_sql_char on something other than a char column may force your database to perform an implicit conversion to the data type of the target column, sometimes producing unexpected results. So generally I would say you should specify a cfsqltype.