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">
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.