This has been fixed. I did not elect to add the precision or scale to the cfqueryparam or cfprocparam tags. It doesn't seem to provide much of an advantage to justify the effort.
This is complete in revision 268. Doug -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tracy Logan Sent: Saturday, May 20, 2006 4:38 PM To: Reactor Mailing List Subject: [Reactor For CF] Reactor and CFQueryParam's "scale" attribute I've run into a pair of problems with Reactor; I wrote Doug about them, and he replied that they are known problems he'll be working on -- but since I wasn't able to find a reference to the problems here on the list, I thought it might save someone else some trouble if I mention them here. I believe I've done a good job of tracking through the problem, but of course there's always the chance that I'm just a bozo overlooking something, so feedback is very welcome! :*) The problems are both related to QueryParam and its Scale and MaxLength params; one problem causes decimals to be truncated, while the other problem allows overlength strings to hit the database instead of being trapped by CF. The decimal truncation is easy to check (though hard to fix!): Use Reactor to insert a decimal value (such as 1.23) into a field with a non-integer decimal datatype [such as decimal (3.2)]. The value will be rounded and inserted as an integer (such as 1.00). I'll provide sample code below. This happens because Reactor is not inserting the "scale" attribute (which sets the number of digits to the right of the decimal point) in the QueryParam statements it generates for Decimal datatypes (for good reason -- generating that attribute is definitely non-trivial!) Here's the generated QueryParm for the decimal(3,2) field in my sample code (from /reactor/project/truncationDemo/Dao/statDao.cfc): <cfqueryparam cfsqltype="cf_sql_decimal" value="#arguments.to.stat#" /> CF defaults the "scale" attribute to zero, so CF is just doing what it's told to do, rounding the supplied decimal (1.23) to zero digits to the right of the decimal (1.), and inserting that trucated value. To avoid this problem, a "scale" attribute matching the database must be added; we can test this by manually editing the ("do not edit me!") generated DAO to put in our desired scale value: <cfqueryparam cfsqltype="cf_sql_decimal" value="#arguments.to.stat#" scale="2" /> Doing this allows the full decimal to be inserted -- so "1.23" is now stored as "1.23". As I noted, this scale value is difficult to generate, and will of course be different for each RDBMS, so I have not been able to fix this; for now, I've switched the largish decimal(19,14) to varchar(21) -- the extra 2 characters are to hold the decimal point and sign, plus 19 digits. Another workaround would be to manually edit the generated DAO. Neither are very elegant solutions, so if you need decimal places, you'll have to decide for yourself which one sucks least (until Doug gets the real fix in place!) The other problem is also easy to test, and is much easier to fix. It, too, involves the "scale" attribute -- but this time, it's that the "scale" attribute is being used with varchar's, rather than the "maxlength" attribute. To test this, simply insert an overlength string; you'll see a database error, rather than a CF error; this means that the overlength string (which could, for example, contain a SQL injection attack) goes to the database where it can do its evil work, rather than being caught by CF. Here's the generated QueryParam for the varchar(10) field in my sample code (again, from statDAO.cfc): <cfqueryparam cfsqltype="cf_sql_varchar" scale="10" value="#arguments.to.name#" /> You can see that it has a "scale" attribute, and no "maxlength" attribute -- since "scale" applies only to decimal & numeric datatypes, it is ignored here, and since no maxlength attribute is given, any length of string is allowed. This means that a massive SQL string may be sent to the DB, allowing any sort of SQL injection to occur -- and negating the primary reason for using QueryParam in the first place. We can test a fix for this the same way as before, by manually editing (ouch!) the statDao.cfc file, changing the "scale" attribute to "maxlength": <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="10" value="#arguments.to.name#" /> Here's the original (dangerous) error: "Error Executing Database Query: [Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated." With the edited DAO, CF traps the overlength string, so we get a (much safer) CF error: "The cause of this output exception was that: coldfusion.tagext.sql.QueryParamTag$InvalidDataException: Invalid data Flexibility value exceeds MAXLENGTH setting 10." This problem does have a nice clean solution -- just replace scale= with maxlength=, in four places in the XSLT file that drives the DAO generation (/reactor/xsl/dao.xsl). Along with that, there are five other places where scale is used instead of maxlength, in actual QueryParam tags within the qFields query in the five RDBMS-specific ObjectDao.cfc files (/reactor/data/[rdbms type]/ObjectDAO.cfc). All five have cfsqltype set to cf_sql_varchar, so "scale" is ignored, and maxlength should be used instead, as the intent is clearly to match the maximum field name length of the RDBMS. Hopefully Doug will get the relatively simple fix for this out soon, so unless you're really worried about SQL injection attacks, there's probably no need to do this yourself. And finally, here's the test code to check this for yourself (all are run against the most recent version of Reactor as checked out via SVN yesterday): ===============MSSQL table creation script:=============== CREATE TABLE dbo.stat ( id int NOT NULL , name varchar (10) NOT NULL , stat decimal (3, 2) NOT NULL ) ON [PRIMARY] ===============Reactor.xml file (edit to suit):=============== <reactor> <config> <project value="truncationDemo" /> <dsn value="test" /> <type value="mssql" /> <mapping value="/truncationDemoModel" /> <mode value="development" /> </config> </reactor> ===============CF code:=============== <cfscript> orm = createObject("component", "reactor.ReactorFactory").init(expandPath("reactor.xml") ); stat = orm.createRecord("stat"); // first test stat.setID(1); stat.setName("Agility"); stat.setStat(1.981); writeOutput("Before save:" & stat.getName() & ": " & stat.getStat() & "<br />"); stat.save(); stat.load(); writeOutput("After save:" & stat.getName() & ": " & stat.getStat() & "<br />"); // second test stat.setID(2); stat.setName("Flexibility"); stat.setStat(0.789); writeOutput("Before save:" & stat.getName() & ": " & stat.getStat() & "<br />"); try { stat.save(); } catch (Database err){ writeOutput("Save has been aborted -- #err.Message# "#err.Detail#"" ); } catch (Any err){ writeOutput("Save aborted due to CF error "#err.Message#""); } </cfscript> This first test creates a Stat Record object, sets its ID, Name, and Stat values, displays those values, saves the record, then reloads the saved data and displays the [now rounded and truncated!] values. The second test is much the same, but with an overlength Name value; it then separately traps and displays either the CF or DB error. I've only tested these on MSSQL2000, but I imagine similar problems will occur in the other RDBMS's. =tracy -- Reactor for ColdFusion Mailing List -- [email protected] -- Archives at http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
