I am remediating some older ColdFusion code which uses an Oracle database. I have noticed many instances where a CFQueryParam has been implemented like this;
<cfqueryparam cfsqltype="varchar2" value="#someStringVariable#" />
Is this an undocumented CFSQL Type that I am unaware of?
If not why doesn't this throw an error at run time?
What validation of the data takes place if you enter an invalid CFSQLType?
I think it checks the type you have put in and then passes the type through. If no type is supplied or the type is not a correct one, it just defaults to a string check (i.e. varchar) - in this case it would work as that is what the field is.
The documentation tells you the order of the validation -
- For these types, a data value can be converted to a numeric value: CF_SQL_SMALLINT, CF_SQL_INTEGER, CF_SQL_REAL, CF_SQL_FLOAT, CF_SQL_DOUBLE, CF_SQL_TINYINT, CF_SQL_MONEY, CF_SQL_MONEY4, CF_SQL_DECIMAL, CF_SQL_NUMERIC, and CF_SQL_BIGINT
- For these types, a data value can be converted to a date supported by the target data source: CF_SQL_DATE, CF_SQL_TIME, CF_SQL_TIMESTAMP
- For all other types, if the maxLength attribute is used, a data value cannot exceed the maximum length specified.
ColdFusion debug output shows the bind variables as question marks and lists the values beneath the query, in order of usage.
The code you used is probably the equivalent of excluding the cfsqltype attribute. Although it works, its technically incorrect as its an invalid type
Thank you for your reply to my post.
What you said at the end "The code you used is probably the equivalent of excluding the cfsqltype attribute." is accurate, I was hoping someone might know for sure.
The datatype VARCHAR2 is an Oracle-specific datatype, I think. It's not in the list of supported parameter types that CF lists. But I'm not sure that really matters. My guess is that CF normally converts entries like "cf_sql_varchar" to whatever the database-specific equivalent happens to be. In the absence of being able to do that conversion, if it just gets passed through as-is, VARCHAR2 will work fine with Oracle since it's an Oracle datatype. I'm honestly not much of an Oracle guy, so I don't know what happens specifically with Oracle, but I wouldn't be surprised if it was specified intentionally because Oracle differentiates between VARCHAR2 and VARCHAR, and the programmer wanted to force the use of VARCHAR2 instead of VARCHAR. (The differentiation is really pretty minor, though.)
I don't know why CF doesn't throw an error with that value, but I can only assume that CF will let you pass through anything as long as the database itself will accept it. If you enter a CFSQLTYPE value that is neither supported by CF nor your database, your database would throw an error and the query wouldn't execute, so you wouldn't get very far. This whole CFQUERY and CFQUERYPARAM set is used to build a parameterized query in your database, and if the database doesn't support the parameters you're using, you'll know it. If you entered the wrong CFSQLTYPE datatype, for example a cf_sql_integer where you really wanted to use cf_sql_varchar, that'll cause a database error as well.
If you're really interested in seeing this at work, you should be able to view executed queries in Oracle and see what they look like. I don't know how to do that offhand, but I know that it can be done in SQL Server and other databases so I'm sure it can be done in Oracle also.
Dave Watts, CTO, Fig Leaf Software
Thanks for the reply.
I am a 17 year ColdFusion user and I am familiar with how Cfqueryparam works, I just had never seen a non-standard CFSQLType before and am still thinking it should cause an error.
it is my understanding that ColdFusion first performs a type check based on the CFSQLTypeÂ parameter and then declares the bind variables based on that type as well.
I don't believe there is a type check in ColdFusion for "varchar2" and my thought was that maybe thereÂ might be undocumented CFSQLTypes that I was unaware of.
Thanks also for the suggestion to monitor the sql sent to the db. Good call, I am investigating that now.
I would not be surprised if there's no type check for the standard CFSQLTYPE attribute values, either. I've never bothered to test that, though. I'm pretty confident that there are no specific undocumented values for CFSQLTYPE.
Dave Watts, CTO, Fig Leaf Software