RE: my cfqueryparam grievance
Maybe it's happening in code. Do a search for the variable and make sure you don't find something like: Stranger things have happened :OD Adrian > -Original Message- > From: Qing Xia [mailto:txiasum...@gmail.com] > Sent: 14 May 2009 21:33 > To: cf-talk > Subject: Re: my cfqueryparam grievance > > > Ah yes! You are right--that ID value, after being returned in the query > recordset, does get set in cookie scope, and that is where my > cfqueryparam > tag gets it from, in cookie scope. > > So, yeah, it is possible that users could have manipulated that cookie > value... But then, with so many users (i must have had a couple dozen > error > messages at least, and they are from different legitimate users) all > generating the same error message, it seems unlikely that they all > changed > their cookie to the same value. > > On Thu, May 14, 2009 at 4:01 PM, Adrian Lynch > wrote: > > > > > I thought you said it was a cookie value? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322533 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: my cfqueryparam grievance
Ah yes! You are right--that ID value, after being returned in the query recordset, does get set in cookie scope, and that is where my cfqueryparam tag gets it from, in cookie scope. So, yeah, it is possible that users could have manipulated that cookie value... But then, with so many users (i must have had a couple dozen error messages at least, and they are from different legitimate users) all generating the same error message, it seems unlikely that they all changed their cookie to the same value. On Thu, May 14, 2009 at 4:01 PM, Adrian Lynch wrote: > > I thought you said it was a cookie value? > > > -Original Message- > > From: Qing Xia [mailto:txiasum...@gmail.com] > > Sent: 14 May 2009 20:43 > > To: cf-talk > > Subject: Re: my cfqueryparam grievance > > > > > > I wish--but the value is a ID value passed back in a query recordset > > and > > there is no way how users can manually pass it in. > > > > On Thu, May 14, 2009 at 3:39 PM, Adrian Lynch > > wrote: > > > > > > > > The user changed it maybe? > > > > > > Adrian > > > > > > > -----Original Message- > > > > From: Qing Xia [mailto:txiasum...@gmail.com] > > > > Sent: 14 May 2009 20:30 > > > > To: cf-talk > > > > Subject: Re: my cfqueryparam grievance > > > > > > > > But still, it is interesting to ponder whatever happened to my > > data, > > > > and > > > > why, of all things, everything got to be 521636a. Just another > > thing > > > > to > > > > think about on the metro. > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322532 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: my cfqueryparam grievance
I thought you said it was a cookie value? > -Original Message- > From: Qing Xia [mailto:txiasum...@gmail.com] > Sent: 14 May 2009 20:43 > To: cf-talk > Subject: Re: my cfqueryparam grievance > > > I wish--but the value is a ID value passed back in a query recordset > and > there is no way how users can manually pass it in. > > On Thu, May 14, 2009 at 3:39 PM, Adrian Lynch > wrote: > > > > > The user changed it maybe? > > > > Adrian > > > > > -Original Message- > > > From: Qing Xia [mailto:txiasum...@gmail.com] > > > Sent: 14 May 2009 20:30 > > > To: cf-talk > > > Subject: Re: my cfqueryparam grievance > > > > > > But still, it is interesting to ponder whatever happened to my > data, > > > and > > > why, of all things, everything got to be 521636a. Just another > thing > > > to > > > think about on the metro. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322526 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: my cfqueryparam grievance
I wish--but the value is a ID value passed back in a query recordset and there is no way how users can manually pass it in. On Thu, May 14, 2009 at 3:39 PM, Adrian Lynch wrote: > > The user changed it maybe? > > Adrian > > > -Original Message- > > From: Qing Xia [mailto:txiasum...@gmail.com] > > Sent: 14 May 2009 20:30 > > To: cf-talk > > Subject: Re: my cfqueryparam grievance > > > > But still, it is interesting to ponder whatever happened to my data, > > and > > why, of all things, everything got to be 521636a. Just another thing > > to > > think about on the metro. > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322522 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: my cfqueryparam grievance
The user changed it maybe? Adrian > -Original Message- > From: Qing Xia [mailto:txiasum...@gmail.com] > Sent: 14 May 2009 20:30 > To: cf-talk > Subject: Re: my cfqueryparam grievance > > But still, it is interesting to ponder whatever happened to my data, > and > why, of all things, everything got to be 521636a. Just another thing > to > think about on the metro. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322521 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: my cfqueryparam grievance
LOL I am glad that you asked about the VARCHAR auto increment, Dave. It is an interesting story (everybody grab some hot cocoa, please): Well, (most) iMIS tables have a VARCHAR identity field which need to auto-increment like an integer. To achieve that, there is table which stores the "next" ID value for these tables. So, whenever an INSERT is done on an iMIS table, this special table with the "next" ID values have to be updated to reflect what the "next" value is for the table which just got the INSERT statement. To make life more interesting, some iMIS tables have triggers on them so that the special table gets automatically updated, but others don't. Make sense? Probably not. But well, that is how it is done. As for the cached SQL statement, I think I understand what you mean now. So it is basically like changing the accepted parameters of a CF function--it used to take INT1 and INT2, now it wants VARCHAR1 and INT2, and that CF function happens to be in APPLICATION scope or other persistent scope. So things break until you can refresh the function. Hmm... but I still can't imagine how it happened to my code since our table schema has not changed. Well, I guess my lessons here are: 1) Don't try to be cute with CFqueryparam, always use the same data type as the receiving table column; 2) Make sure the column which holds the same data has the same data type across tables. But still, it is interesting to ponder whatever happened to my data, and why, of all things, everything got to be 521636a. Just another thing to think about on the metro. On Thu, May 14, 2009 at 2:39 PM, Dave Watts wrote: > > > 2. The incoming data is supposed to be for an ID column, which is of > VARCHAR > > data type in the database, and it auto increments. It is not designed to > be > > hexadecimal, so I can't imagine the translated value standing for any > > hexadecimal either--well, at least there is no instruction on either the > CF > > or MSSQL side which tells it to convert into hexadecimal. > > > > Adding to the confusion of course, is that this ID column is sometimes > > VARCHAR and sometimes INT in different tables--probably not the best > > practice. We have an archaic system (iMIS) and it has this ID as > VARCHAR, > > but elsewhere, in built-in-house systems sometimes it is INT. > > What does it mean for a VARCHAR to autoincrement? > > > Regarding Brad's comment: I am intrigued by the theory of corrupted > cached > > SQL statement... What exactly does that mean? How does corrupted > statements > > get cached? And if it was corrupted, how/why did it run before with no > > issues? Did something happen that made the corrupted cache stop working? > > You run a valid SQL command, you change the schema in such a way that > it would no longer be valid, and you attempt to rerun the same command > using a cached execution plan. > > Dave Watts, CTO, Fig Leaf Software > http://www.figleaf.com/ > > Fig Leaf Software provides the highest caliber vendor-authorized > instruction at our training centers in Washington DC, Atlanta, > Chicago, Baltimore, Northern Virginia, or on-site at your location. > Visit http://training.figleaf.com/ for more informati > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322520 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: my cfqueryparam grievance
> 2. The incoming data is supposed to be for an ID column, which is of VARCHAR > data type in the database, and it auto increments. It is not designed to be > hexadecimal, so I can't imagine the translated value standing for any > hexadecimal either--well, at least there is no instruction on either the CF > or MSSQL side which tells it to convert into hexadecimal. > > Adding to the confusion of course, is that this ID column is sometimes > VARCHAR and sometimes INT in different tables--probably not the best > practice. We have an archaic system (iMIS) and it has this ID as VARCHAR, > but elsewhere, in built-in-house systems sometimes it is INT. What does it mean for a VARCHAR to autoincrement? > Regarding Brad's comment: I am intrigued by the theory of corrupted cached > SQL statement... What exactly does that mean? How does corrupted statements > get cached? And if it was corrupted, how/why did it run before with no > issues? Did something happen that made the corrupted cache stop working? You run a valid SQL command, you change the schema in such a way that it would no longer be valid, and you attempt to rerun the same command using a cached execution plan. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more informati ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322517 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: my cfqueryparam grievance
Hello folks: Thanks to all who replied--I really appreciate your thoughts and insights on the issue. Here is the latest development on my fascinating CFQueryparam issue: 1. I have gone through the error messages on this issue, and none of them has bad data coming in. Which is to say, all the incoming data for cookie.theID conforms to somehting like "123456", 6-digit integers. So, in all instances, something happened with the cfqueryparam tag, where incoming values all got somehow translated into "521636a". 2. The incoming data is supposed to be for an ID column, which is of VARCHAR data type in the database, and it auto increments. It is not designed to be hexadecimal, so I can't imagine the translated value standing for any hexadecimal either--well, at least there is no instruction on either the CF or MSSQL side which tells it to convert into hexadecimal. Adding to the confusion of course, is that this ID column is sometimes VARCHAR and sometimes INT in different tables--probably not the best practice. We have an archaic system (iMIS) and it has this ID as VARCHAR, but elsewhere, in built-in-house systems sometimes it is INT. Regarding Brad's comment: I am intrigued by the theory of corrupted cached SQL statement... What exactly does that mean? How does corrupted statements get cached? And if it was corrupted, how/why did it run before with no issues? Did something happen that made the corrupted cache stop working? Thanks guys for your thoughts! On Tue, May 12, 2009 at 5:23 PM, Jason Fisher wrote: > > Ummm, not sure why your cookie.theID would shift, but I would say > absolutely that the CF_SQL_TYPE is designed to match the database column > data type, not the incoming variable parameter. The entire point of the > CF_SQL_TYPE is to let the JDBC driver handle the data pass-through for you > in a way that is safe and that the database server understands, regardless > of what RDBMS you are using. > > So, I would agree that there are 2 separate issues: what's gone on with > your data, independent of the CFQUERY and then ensuring that all > CFQUERYPARAMs are designed to match your database rather than your > application data, which in this case just happens to be integer. > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322513 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: my cfqueryparam grievance
Ummm, not sure why your cookie.theID would shift, but I would say absolutely that the CF_SQL_TYPE is designed to match the database column data type, not the incoming variable parameter. The entire point of the CF_SQL_TYPE is to let the JDBC driver handle the data pass-through for you in a way that is safe and that the database server understands, regardless of what RDBMS you are using. So, I would agree that there are 2 separate issues: what's gone on with your data, independent of the CFQUERY and then ensuring that all CFQUERYPARAMs are designed to match your database rather than your application data, which in this case just happens to be integer. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322449 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: my cfqueryparam grievance
Start logging the value of COOKIE.theID. Just save it somewhere and see if it's ever an unexpected value. Adrian > -Original Message- > From: Qing Xia [mailto:txiasum...@gmail.com] > Sent: 12 May 2009 20:16 > To: cf-talk > Subject: my cfqueryparam grievance > > > Hello folks, > > I had a pretty strange experience with CFQueryParam today. Basically, > I > have an innocent-looking query: > > *SELECT* username, password > *FROM* someTable > *WHERE* someID = "#cookie.theID#" maxlength="6"> > > > This query had worked just fine for nearly a year (since I > cfqueryparamed it > last summer) until this morning, when it broke. The error messages > read: > > *Message:* > Error Executing Database Query. > 12 May 2009 01:49:21 PM EDT > Diagnostics: > Error Executing Database Query. [Macromedia][SQLServer JDBC > Driver][SQLServer]Syntax error converting the varchar value '521636a' > to a > column of data type int. The error occurred on line 35. > > That error points to the WHERE statement, and it complains the > cookie.theID > is not an integer-but it is!!! *Even the error messages themselves > which > contains a CFDump on Cookie scope say it is an integer, of 6 digits!!!* > Even > more strangely, the error messages always say ".converting the varchar > value > '521636a'." , no matter what the actual cookie.theID value is. Since > this > query is called from a template that logs users in, I got dozens of > error > reports, all with this identical error message, even though the actual > theID > values were very different. > > When I switched the cfqueryparam cfsqltype to varchar, the error went > away!! Of course, the actual data type of the id table column IS > varchar, > but I had purposefully made it integer in the cfqueryparam, for code > "readability" and more control over the incoming value. L > > The moral of the story, of course, is that the cfsqltype should > probably > always match the receiving database table column's data type. But I am > just > curious. why were my values being converted to "521636a".? 6-digit > integer/varchar values are certainly not out of bounds for SQL int data > type, so I simply cannot fathom why conversion was done and why it was > necessary. > > A ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322445 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: my cfqueryparam grievance
It might have been a problem on the SQL server side if a corrupted prepared statement got cached. Changing the cfsqltype effectively cleared the old statement since a new one needed to be recompiled. Can't say I've ever seen what you described but I have seen structure changes a database break cached prepared statements before. ~Brad Original Message Subject: my cfqueryparam grievance From: Qing Xia Date: Tue, May 12, 2009 2:16 pm To: cf-talk Hello folks, I had a pretty strange experience with CFQueryParam today. Basically, I have an innocent-looking query: *SELECT* username, password *FROM* someTable *WHERE* someID = This query had worked just fine for nearly a year (since I cfqueryparamed it last summer) until this morning, when it broke. The error messages read: *Message:* Error Executing Database Query. 12 May 2009 01:49:21 PM EDT Diagnostics: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value '521636a' to a column of data type int. The error occurred on line 35. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322441 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: my cfqueryparam grievance
Qing Xia wrote: > I simply cannot fathom why conversion was done and why it was > necessary. Presumably not necessary, definitely undesirable as you describe it. But seeing that value 521636a makes me wonder if something is trying to interpret the value as a hexadecimal for some reason? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322440 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
my cfqueryparam grievance
Hello folks, I had a pretty strange experience with CFQueryParam today. Basically, I have an innocent-looking query: *SELECT* username, password *FROM* someTable *WHERE* someID = This query had worked just fine for nearly a year (since I cfqueryparamed it last summer) until this morning, when it broke. The error messages read: *Message:* Error Executing Database Query. 12 May 2009 01:49:21 PM EDT Diagnostics: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value '521636a' to a column of data type int. The error occurred on line 35. That error points to the WHERE statement, and it complains the cookie.theID is not an integerbut it is!!! *Even the error messages themselves which contains a CFDump on Cookie scope say it is an integer, of 6 digits!!!* Even more strangely, the error messages always say converting the varchar value 521636a , no matter what the actual cookie.theID value is. Since this query is called from a template that logs users in, I got dozens of error reports, all with this identical error message, even though the actual theID values were very different. When I switched the cfqueryparam cfsqltype to varchar, the error went away!! Of course, the actual data type of the id table column IS varchar, but I had purposefully made it integer in the cfqueryparam, for code readability and more control over the incoming value. L The moral of the story, of course, is that the cfsqltype should probably always match the receiving database table columns data type. But I am just curious why were my values being converted to 521636a ? 6-digit integer/varchar values are certainly not out of bounds for SQL int data type, so I simply cannot fathom why conversion was done and why it was necessary. A ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322436 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
