Does cfsqltype include for cfqueryparam, which is still useful for sql intrusion protection?

Question

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

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

Answer 1

To get an idea of ​​how cfsqltype works under the hood, see the PreparedStatement Java / JDBC class: http://download.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html

You will notice various setInt, setDate, etc. methods. - I understand that cfsqltype is mapped to the corresponding method when creating a prepared statement.

If you specify a type, ColdFusion should be able to pass varaible to that type, and if it cannot, it will throw an exception before sending the request to the database.

When you omit cfsqltype, it probably calls either setObject or setString. The behavior of what happens next depends on the JDBC driver you are currently using. I have seen some cases where an omiting type can cause an error, even when you pass in real variables, what comes to mind works with date and date in MySQL.

Another thing to keep in mind is that if you omit cfsqltype on let, say an integer field, but you are not passing an integer value, ColdFusion may throw an exception before connecting to the database and sending the request if you specified cfsqltype, but without it, you lose the DB connection and runtime on the database server.

Answer 2

One of the benefits of cfqueryparam is type checking before the values ​​are sent to your database. For example, you specify cf_sql_integer , CF not only checks that the value is numeric, but also is an integer in a certain range. When you omit cfsqltype, CF will use cf_sql_char . That way, you obviously lose type checking for things like dates and numbers.

Personally, I think you should provide cfsqltype. However, even if you did not, using cfqueryparam means that CF uses binding variables. The lateral advantage of binding variables helps protect your queries from SQL injection. So in this sense, it is still good.

I think this is useful, but as a "check", not a "query SQL query" protection ".

Update: No, it is still applicable. Protection comes from using bind variables. Since CF will still use bind variables, even without any type, I believe that basic sql injection protection is still applied.

However, using cf_sql_char for anything other than a char column can cause your database to implicitly convert to the data type of the target column, sometimes producing unexpected results. So usually I would say that you should specify cfsqltype.