Mixup in the use of Scale vs MaxLength in QueryParam/ProcParam

[dhughes]

Hiya! I know you're way busy, but I think I've found a consistant mixup in your use of the Scale attribute, and wanted to mention it (and the bug related to it) privately rather than on the Reactor mailing list:

It appears that you are accidentally using Scale instead of MaxLength
-- that is, checking the number of decimal places (in string data!) rather than the actual length of the string. Here's the LiveDoc explaining the difference and suggested usage for both:
(http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/js/html/wwhelp.htm?href=00000317.htm)

Of course, you are one smart cookie, so perhaps you're just doing something wicked clever that I'm not catching! Here are some
examples:

/reactor/data/mssql/ObjectDao.cfc, line 16:

<cfprocparam cfsqltype="cf_sql_varchar" scale="384"

value="#arguments.Object.getName()#" />

So the Scale is set to 384 with a VarChar datatype containing an object name -- just the spot I'd expect to see this:

<cfprocparam cfsqltype="cf_sql_varchar" maxlength="384"

value="#arguments.Object.getName()#" />

I thought maybe you were using this to silently truncate rather than throwing an error -- but with CF7, at least, no truncation occurs, so an over-length string will have an error thrown from the DB (which means SQL injection attempts hit the DB, too), rather than being caught in CF.

Here's another example, from /reactor/xsl/dao.project.xls, lines 101-103:

<xsl:if test="@length > 0 and @cfSqlType != 'cf_sql_longvarchar'">

scale="<xsl:value-of select="@length" />"

</xsl:if>

There, you're writing the INSERT statement QueryParams; in this case, you're setting the Scale to whatever the *length* metadata is, seemingly just where you'd want MaxLength.

And that is where the bug that got me into this appears:

I have a Decimal field in an MSSQL2K database, with a Precision of 19, and a Scale of 10. Because it's a Decimal field, its "length"
metadata is zero, so the above code doesn't add the Scale attribute (since it would be scale="0") -- however, QueryParam defaults Scale to zero -- so my input values are being trunctated and rounded to the nearest integer, just as if the Scale attribute were added, anyhow.

I've spent an hour or so looking through the amazing work you've done grabbing metadata, and it looks like this is going to take a little more than just changing those Scale attributes to MaxLength (though it seems that would be a good start!):

I believe you'll also have to capture the Precision (number of digits
overall) and Scale (number of digits to the right of the decimal) values, and then add some XSL to build the Scale attribute if non-zero.

Here's the related code from ObjectDao.cfc (lines 51-54), which gets the *character* type field length:

CASE

WHEN ISNUMERIC(col.CHARACTER_MAXIMUM_LENGTH) = 1

THEN col.CHARACTER_MAXIMUM_LENGTH

ELSE 0

END as length,

Perhaps do something similar with the *numeric* precision (the "length" of a decimal) would serve to get the length metadata for both character and numeric data:

CASE

WHEN ISNUMERIC(col.CHARACTER_MAXIMUM_LENGTH) = 1

THEN col.CHARACTER_MAXIMUM_LENGTH

ELSE ISNUMERIC(col.NUMERIC_PRECISION) = 1

THEN col.NUMERIC_PRECISION

ELSE 0

END as length,

I'm not sure if the syntax is right on that, but you get the idea (and the field name is correct).

Here's something for Scale:

CASE

WHEN ISNUMERIC(col.NUMERIC_PRECISION_RADIX) = 1

THEN col.NUMERIC_PRECISION_RADIX

ELSE 0

END as scale,

There's also a field called NUMERIC_SCALE, but that's set to 16, while the *_RADIX value is set to the "10" that I entered when building the table, so it *appears* to be something else.

And of course, the other DB's are likely to be completely different -- and may not even have the same concept, for all I know, so asking those who contributed those DB's is a good idea, don't take my word for it!

So then you'd need to add "scale" and the related getters & setters to the reactor/core/Field.cfc, set that after the ReadFields query:

<cfset Field.setScale(qFields.scale) />

and then add the necessary XSL:

<xsl:if test="@scale > 0 and (@cfSqlType = 'cf_sql_decimal' or @cfSqlType = 'cf_sql_numeric')">

scale="<xsl:value-of select="@scale" />"

</xsl:if>

Again, not sure if my grouping syntax is correct, but you get the idea.

I was thinking of just sending you a couple of fixed files to save you time, but this seems to affect all the other DB types as well, and that scares me just a bit too much! I'll be happy to help with this in any way possible -- you have built an absolutely amazing tool for us, and I can't thank you enough!

=tracy

[anonymous]

Milestone Beta Candidate 2 (BC2) deleted