MS SQL's Casting Causes Consternation
If you are using MSSQL Server an Cfqueryparam, you should be aware of the old implicit datatype conversion. This helpful feature uses an order of precedence to accommodate code that is written to pass in a sql_variant data type. To put it another way, lets say you are comparing a column of the type "varchar" to the the number 1 or 2. If it was me, I would write "WHERE myCol = '1' OR myCol = '2'" - or perhaps use an "IN" clause. This code will always work. It is saying "compare a character '2' to the character column myCol". Since they are both of the same type there is no conflict - but what happens if you mismatch the type?
Sometimes Mismatching Works
Let's say you create a "type" that you set to an int and use a type of 1 or 2. Your where clause is going to say "WHERE myType = 2". Let's also suppose that after the fact you reconsider what you might want to put in the "type" column so you change the type to a string. Your original query still works - why? It turns out that MS SQL helps you out and simply converts your column to a type of int before comparing it.
Did you catch that? A query that looks like this:
SELECT *
FROM myItems
WHERE myType = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="1">
</cfquery>
SELECT *
FROM myItems
WHERE myType = 1
</cfquery>
SELECT *
FROM myItems
WHERE CAST(myType AS INT) = 1
</cfquery>
The Catch
The problem is that this will actually work as long as there are only castable values in the column. In other words, as long as 1,2,3 and 4 are stored in the column the query will run. As soon as you put an "A" or some other alpha string in the column the query will error out. This is why it is important to get the cfqueryparam data type correct in the first place. Such an error could lie hidden during development but rear it's ugly head after launch as new values are put into the column.
4 Comments
-
Any <cfquery> tags should be wrapped in a try/catch because they have to do with Input/Output, right?
(3rd attempt to post due to LylaCaptcha) -
Phil,
Try / Catch blocks should be used judiciously in conjuction with expected and unexpected errors. The use of cftransaction is probably more important that try/catch - except that glaring DB errors sometimes expose information germane to security. There is not "extra" reason that cfquery should get special attention from your error trapping. I'm not sure what you mean by "input/output"... a cfquery get's data from the database server and may take input from the user or process... cfqueryparam is a necessity to stop sql injection attack... but I'm not following your point exactly :) -
No problem. It was first thing in the morning and I just dashed off a quick response.
My thought was to allow SQL Server to catch some of the obscure validations. If a user types 'A' where a number is suppose to be, and it somehow gets past the field validation, I don't have a problem with them getting a generic SQL Server error message, provided it's presented in the application through the use of <cfset ErrorMessage = cfcatch.detail> (trimmed of such unnessesary strings like [Macromedia], [SQLServer JDBC Driver], and [SQLServer] to make it more readable).
Oh, please consider
Simplifying the captcha graphic in Lyla Captcha (and BlogCFC) at
http://carehart.org/blog/client/index.cfm/2006/8/1... -
Phil,
Ah... I see what you mean. You could simply "catch" errors where a user is sending a character into a numeric field. Clever - but the point of the post was to make sure you are using the correct data type in your cfqueryparam - so that an inadvertant implicit cast doesn't slip by you.
I would think that validation would still be necessary (of user input) outside of your try / catch. checking date formats and ranges - or numeric values, or email formats and the like... there will always be a place for custom validation routines I fear :)
As for the kaptcha... that's a feature of Rays blogging software.... I'll mention it to him. The big issue I have with it is that I cannot tell capital letters from small caps.. the font isn't clear enough on that point and the size isn't enough of a clue (sigh).
Sites to Watch
Aggregators
RSS
