Re: SOLVED Re: MySQL - CF: Access Denied when using password
well i see the Blue Bus has arrived! ~Dave the disruptor~ "Some people just don't appreciate how difficult it is to dispense wisdom and abuse at the same time." From: Will Tomlinson <[EMAIL PROTECTED]> Sent: Tuesday, August 30, 2005 9:54 PM To: CF-Talk Subject: Re: SOLVED Re: MySQL - CF: Access Denied when using password >LOL...never shoulda named ya the "Disruptor"... I believe it's really the "disruptured" Bryan. :) ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216942 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOLVED Re: MySQL - CF: Access Denied when using password
>LOL...never shoulda named ya the "Disruptor"... I believe it's really the "disruptured" Bryan. :) ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216940 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOLVED Re: MySQL - CF: Access Denied when using password
" LOL...never shoulda named ya the "Disruptor"..." yeah but ya did and now i have an image to uphold and even you as the creator are not a immune! ~Dave the disruptor~ "Some people just don't appreciate how difficult it is to dispense wisdom and abuse at the same time." ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216933 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOLVED Re: MySQL - CF: Access Denied when using password
LOL...never shoulda named ya the "Disruptor"... Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216827 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOLVED Re: MySQL - CF: Access Denied when using password
i was just messin with ya man ;) you were askin 4 it on another thread but i didnt have time to get ya!!! haha ok so lets be neutral and change the line too: "hopefully it will be on postgresql soon.." ~Dave the disruptor~ "Some people just don't appreciate how difficult it is to dispense wisdom and abuse at the same time." From: "Bryan Stevenson" <[EMAIL PROTECTED]> Sent: Tuesday, August 30, 2005 1:22 PM To: CF-Talk Subject: Re: SOLVED Re: MySQL - CF: Access Denied when using password >" Thankfully this project will end up on MS SQL Server ;-)" > hopefully not!!! > > while ms sql server (and as crystal tech likes to call it mysql server > 2000, great job of training them techs ct!!! very knowledgable) is a > decent product, some of us and our clients choose to use better platforms > (that we can depend on) and if everyone would stop using ms junk for a few > weeks maybe it would force them to build a decent os to run it on. > > I'd rather leave the worms for fishing, thanx.. > > Rock on MySQL > > ;-) > > ~Dave the disruptor~ Check the archives for my very valid issues with MySQL (*ahem* allowing NULLs in NOT NULL fields for starters).as for MS SQL Server...it has never let me down...always worked as advertised (not that I like M$ in the least) That's it from me on the subject... Cheers Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216812 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOLVED Re: MySQL - CF: Access Denied when using password
>" Thankfully this project will end up on MS SQL Server ;-)" > hopefully not!!! > > while ms sql server (and as crystal tech likes to call it mysql server > 2000, great job of training them techs ct!!! very knowledgable) is a > decent product, some of us and our clients choose to use better platforms > (that we can depend on) and if everyone would stop using ms junk for a few > weeks maybe it would force them to build a decent os to run it on. > > I'd rather leave the worms for fishing, thanx.. > > Rock on MySQL > > ;-) > > ~Dave the disruptor~ Check the archives for my very valid issues with MySQL (*ahem* allowing NULLs in NOT NULL fields for starters).as for MS SQL Server...it has never let me down...always worked as advertised (not that I like M$ in the least) That's it from me on the subject... Cheers Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216810 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOLVED Re: MySQL - CF: Access Denied when using password
" Thankfully this project will end up on MS SQL Server ;-)" hopefully not!!! while ms sql server (and as crystal tech likes to call it mysql server 2000, great job of training them techs ct!!! very knowledgable) is a decent product, some of us and our clients choose to use better platforms (that we can depend on) and if everyone would stop using ms junk for a few weeks maybe it would force them to build a decent os to run it on. I'd rather leave the worms for fishing, thanx.. Rock on MySQL ;-) ~Dave the disruptor~ "Some people just don't appreciate how difficult it is to dispense wisdom and abuse at the same time." From: "Bryan Stevenson" <[EMAIL PROTECTED]> Sent: Tuesday, August 30, 2005 12:51 PM To: CF-Talk Subject: Re: SOLVED Re: MySQL - CF: Access Denied when using password > Yup, I posted this originally and got the problem solved by downloading > the > jdbc "connector/j" from mysql.org - I had to use connector version 3.0 > though, maybe because I am on CFMX6.1 -- the 3.1 version (recommended) > didn't work for me. .and for me the JDBC connector works for verifying the datasource, but then all queries using CFQUERYPARAM then fail to retrieve any data.remove the CFQUERYPARAM and then data is retrievednot exactly a great solution on my end. Anybody run into that one? Thankfully this project will end up on MS SQL Server ;-) Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216803 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOLVED Re: MySQL - CF: Access Denied when using password
> Yup, I posted this originally and got the problem solved by downloading > the > jdbc "connector/j" from mysql.org - I had to use connector version 3.0 > though, maybe because I am on CFMX6.1 -- the 3.1 version (recommended) > didn't work for me. and for me the JDBC connector works for verifying the datasource, but then all queries using CFQUERYPARAM then fail to retrieve any data.remove the CFQUERYPARAM and then data is retrievednot exactly a great solution on my end. Anybody run into that one? Thankfully this project will end up on MS SQL Server ;-) Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216793 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
SOLVED Re: MySQL - CF: Access Denied when using password
Yup, I posted this originally and got the problem solved by downloading the jdbc "connector/j" from mysql.org - I had to use connector version 3.0 though, maybe because I am on CFMX6.1 -- the 3.1 version (recommended) didn't work for me. - Original Message - From: "John Paul Ashenfelter" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Tuesday, August 30, 2005 6:56 AM Subject: Re: MySQL - CF: Access Denied when using password > On 8/29/05, Bryan Stevenson <[EMAIL PROTECTED]> wrote: >> >> CF 7 comes with a MySQL 3.1 driverif you're using 4.1, there is a >> different authentication mechanism so username/passwords don't >> flyjust >> use without the user/pass. > > > Actually, it's a 3.23.x series driver. And you're fine if you're using > MySQL > 4.1.0 -- the auth mechanism changed in 4.1.1. > > Running without a password is really a poor choice, especially when there > are a number of more sensible options: > > 1) Use the oldpassword option in your my.ini or pass as a command line > parameter at startup > 2) Use the OLD_PASSWORD() function on the password for the user defined in > the CFMX datasource, which will generate a hash that the MySQL 3.23.x > driver > that ships with ColdFusion understands > 3) Use the MySQL 4.1.x series driver as an "Other" datasource in CFMX, > which > you'll *have* to do to get access to the 4.1 and 5.0 branch-specific > functionality. > > You can also dload the newer JDBC driver for 4.x and up at > mysql.org<http://mysql.org>or .com >> or wherever the home of that miserable excuse for a database is (no I'm >> not >> jaded at all...hehe) ;-) > > > How's this significantly different from the whole Windows vs SQL Server > login difference with MS-SQL? Other than the fact that you've had to know > the difference between those two types of logins for so long to get > CF-MSSQL > datasources to work that it's second nature by now :) > > HTH >> >> Cheers >> >> Bryan Stevenson B.Comm. >> VP & Director of E-Commerce Development >> Electric Edge Systems Group Inc. >> phone: 250.480.0642 >> fax: 250.480.1264 >> cell: 250.920.8830 >> e-mail: [EMAIL PROTECTED] >> web: www.electricedgesystems.com <http://www.electricedgesystems.com> >> - Original Message - >> From: "Josh Nathanson" <[EMAIL PROTECTED]> >> To: "CF-Talk" >> Sent: Saturday, August 27, 2005 2:22 PM >> Subject: Re: MySQL - CF: Access Denied when using password >> >> >> > Thanks Dave, but I have set up a dummy user locally with the same >> username >> > and password as that of the live application, so I don't think that is >> the >> > issue... >> > >> > It works absolutely great as long as I don't try to use a password. For >> > some reason that is what's throwing it off. >> > >> > I have a bunch of apps running remotely on CF/MySQL, and I am trying to >> > move >> > from MS Access to MySQL locally, so I'd like to be able to do that >> without >> > rewriting all the code, or dealing with the commenting workaround you >> > suggested. >> > >> > Is there some rule against a localhost user other than root connecting >> to >> > MySQL? >> > >> > I am no expert so I might be missing something really obvious, but this >> > also >> > stumped my trusty web host who is something of a CF guru. I emailed him >> > my >> > MySQL user and db tables and he couldn't find anything wrong that would >> be >> > causing the error. >> > >> > >> > >> > >> > >> > >> > - Original Message - >> > From: "dave" <[EMAIL PROTECTED]> >> > To: "CF-Talk" >> > Sent: Saturday, August 27, 2005 1:07 PM >> > Subject: re: MySQL - CF: Access Denied when using password >> > >> > >> >> that would be because your local MySQL install doesnt have the same >> pw's >> >> as your live one therefor you are trying to give it a username and >> >> password it doesn't have and getting the warning. >> >> >> >> Not sure how others do it but in my Application.cfm or cfc (shut it >> will) >> >> I set 2 sets of connection variable, 1 live and 1 local and just >> comment >> >> out the one i dont need at the time. >> >> >> >> like >&
Re: MySQL - CF: Access Denied when using password
On 8/29/05, Bryan Stevenson <[EMAIL PROTECTED]> wrote: > > CF 7 comes with a MySQL 3.1 driverif you're using 4.1, there is a > different authentication mechanism so username/passwords don't flyjust > use without the user/pass. Actually, it's a 3.23.x series driver. And you're fine if you're using MySQL 4.1.0 -- the auth mechanism changed in 4.1.1. Running without a password is really a poor choice, especially when there are a number of more sensible options: 1) Use the oldpassword option in your my.ini or pass as a command line parameter at startup 2) Use the OLD_PASSWORD() function on the password for the user defined in the CFMX datasource, which will generate a hash that the MySQL 3.23.x driver that ships with ColdFusion understands 3) Use the MySQL 4.1.x series driver as an "Other" datasource in CFMX, which you'll *have* to do to get access to the 4.1 and 5.0 branch-specific functionality. You can also dload the newer JDBC driver for 4.x and up at mysql.org<http://mysql.org>or .com > or wherever the home of that miserable excuse for a database is (no I'm > not > jaded at all...hehe) ;-) How's this significantly different from the whole Windows vs SQL Server login difference with MS-SQL? Other than the fact that you've had to know the difference between those two types of logins for so long to get CF-MSSQL datasources to work that it's second nature by now :) HTH > > Cheers > > Bryan Stevenson B.Comm. > VP & Director of E-Commerce Development > Electric Edge Systems Group Inc. > phone: 250.480.0642 > fax: 250.480.1264 > cell: 250.920.8830 > e-mail: [EMAIL PROTECTED] > web: www.electricedgesystems.com <http://www.electricedgesystems.com> > - Original Message - > From: "Josh Nathanson" <[EMAIL PROTECTED]> > To: "CF-Talk" > Sent: Saturday, August 27, 2005 2:22 PM > Subject: Re: MySQL - CF: Access Denied when using password > > > > Thanks Dave, but I have set up a dummy user locally with the same > username > > and password as that of the live application, so I don't think that is > the > > issue... > > > > It works absolutely great as long as I don't try to use a password. For > > some reason that is what's throwing it off. > > > > I have a bunch of apps running remotely on CF/MySQL, and I am trying to > > move > > from MS Access to MySQL locally, so I'd like to be able to do that > without > > rewriting all the code, or dealing with the commenting workaround you > > suggested. > > > > Is there some rule against a localhost user other than root connecting > to > > MySQL? > > > > I am no expert so I might be missing something really obvious, but this > > also > > stumped my trusty web host who is something of a CF guru. I emailed him > > my > > MySQL user and db tables and he couldn't find anything wrong that would > be > > causing the error. > > > > > > > > > > > > > > - Original Message - > > From: "dave" <[EMAIL PROTECTED]> > > To: "CF-Talk" > > Sent: Saturday, August 27, 2005 1:07 PM > > Subject: re: MySQL - CF: Access Denied when using password > > > > > >> that would be because your local MySQL install doesnt have the same > pw's > >> as your live one therefor you are trying to give it a username and > >> password it doesn't have and getting the warning. > >> > >> Not sure how others do it but in my Application.cfm or cfc (shut it > will) > >> I set 2 sets of connection variable, 1 live and 1 local and just > comment > >> out the one i dont need at the time. > >> > >> like > >> > >> > >> > >> > >> ---> > >> > >> > >> > >> > >> > >> > >> ~Dave the disruptor~ > >> "Some people just don't appreciate how difficult it is to dispense > wisdom > >> and abuse at the same time." > >> > >> > >> From: "Josh Nathanson" <[EMAIL PROTECTED]> > >> Sent: Saturday, August 27, 2005 1:49 PM > >> To: CF-Talk > >> Subject: MySQL - CF: Access Denied when using password > >> > >> Hello all, > >> > >> I have set up MySQL on my local box, everything works fine until I try > to > >> use a password when creating a datasource in CF Administrator. I get > the > >> "1045 Access Denied" er
RE: MySQL - CF: Access Denied when using password
uh oh now you've done it. Prepare for a wall of fire -Original Message- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] Sent: Monday, August 29, 2005 12:37 PM To: CF-Talk Subject: Re: MySQL - CF: Access Denied when using password CF 7 comes with a MySQL 3.1 driverif you're using 4.1, there is a different authentication mechanism so username/passwords don't flyjust use without the user/pass. You can also dload the newer JDBC driver for 4.x and up at mysql.org or .com or wherever the home of that miserable excuse for a database is (no I'm not jaded at all...hehe) ;-) HTH Cheers Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com - Original Message - From: "Josh Nathanson" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Saturday, August 27, 2005 2:22 PM Subject: Re: MySQL - CF: Access Denied when using password > Thanks Dave, but I have set up a dummy user locally with the same username > and password as that of the live application, so I don't think that is the > issue... > > It works absolutely great as long as I don't try to use a password. For > some reason that is what's throwing it off. > > I have a bunch of apps running remotely on CF/MySQL, and I am trying to > move > from MS Access to MySQL locally, so I'd like to be able to do that without > rewriting all the code, or dealing with the commenting workaround you > suggested. > > Is there some rule against a localhost user other than root connecting to > MySQL? > > I am no expert so I might be missing something really obvious, but this > also > stumped my trusty web host who is something of a CF guru. I emailed him > my > MySQL user and db tables and he couldn't find anything wrong that would be > causing the error. > > > > > > > - Original Message - > From: "dave" <[EMAIL PROTECTED]> > To: "CF-Talk" > Sent: Saturday, August 27, 2005 1:07 PM > Subject: re: MySQL - CF: Access Denied when using password > > >> that would be because your local MySQL install doesnt have the same pw's >> as your live one therefor you are trying to give it a username and >> password it doesn't have and getting the warning. >> >> Not sure how others do it but in my Application.cfm or cfc (shut it will) >> I set 2 sets of connection variable, 1 live and 1 local and just comment >> out the one i dont need at the time. >> >> like >> >> >> >> >> ---> >> >> >> >> >> >> >> ~Dave the disruptor~ >> "Some people just don't appreciate how difficult it is to dispense wisdom >> and abuse at the same time." >> >> >> From: "Josh Nathanson" <[EMAIL PROTECTED]> >> Sent: Saturday, August 27, 2005 1:49 PM >> To: CF-Talk >> Subject: MySQL - CF: Access Denied when using password >> >> Hello all, >> >> I have set up MySQL on my local box, everything works fine until I try to >> use a password when creating a datasource in CF Administrator. I get the >> "1045 Access Denied" error. I am able to create the datasource fine if I >> don't use a password, so I know the issue is not caused by incorrect >> privileges in the mysql.user or mysql.db tables. Also if I do set up the >> datasource without a password, and try to use a password in a cfquery, I >> get the same error. >> >> My platform: Windows XP SP2/CFMX 6.1/MySQL 4.1 >> >> Thanks for any assistance! >> >> >> >> > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216671 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: MySQL - CF: Access Denied when using password
CF 7 comes with a MySQL 3.1 driverif you're using 4.1, there is a different authentication mechanism so username/passwords don't flyjust use without the user/pass. You can also dload the newer JDBC driver for 4.x and up at mysql.org or .com or wherever the home of that miserable excuse for a database is (no I'm not jaded at all...hehe) ;-) HTH Cheers Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com - Original Message - From: "Josh Nathanson" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Saturday, August 27, 2005 2:22 PM Subject: Re: MySQL - CF: Access Denied when using password > Thanks Dave, but I have set up a dummy user locally with the same username > and password as that of the live application, so I don't think that is the > issue... > > It works absolutely great as long as I don't try to use a password. For > some reason that is what's throwing it off. > > I have a bunch of apps running remotely on CF/MySQL, and I am trying to > move > from MS Access to MySQL locally, so I'd like to be able to do that without > rewriting all the code, or dealing with the commenting workaround you > suggested. > > Is there some rule against a localhost user other than root connecting to > MySQL? > > I am no expert so I might be missing something really obvious, but this > also > stumped my trusty web host who is something of a CF guru. I emailed him > my > MySQL user and db tables and he couldn't find anything wrong that would be > causing the error. > > > > > > > - Original Message - > From: "dave" <[EMAIL PROTECTED]> > To: "CF-Talk" > Sent: Saturday, August 27, 2005 1:07 PM > Subject: re: MySQL - CF: Access Denied when using password > > >> that would be because your local MySQL install doesnt have the same pw's >> as your live one therefor you are trying to give it a username and >> password it doesn't have and getting the warning. >> >> Not sure how others do it but in my Application.cfm or cfc (shut it will) >> I set 2 sets of connection variable, 1 live and 1 local and just comment >> out the one i dont need at the time. >> >> like >> >> >> >> >> ---> >> >> >> >> >> >> >> ~Dave the disruptor~ >> "Some people just don't appreciate how difficult it is to dispense wisdom >> and abuse at the same time." >> >> >> From: "Josh Nathanson" <[EMAIL PROTECTED]> >> Sent: Saturday, August 27, 2005 1:49 PM >> To: CF-Talk >> Subject: MySQL - CF: Access Denied when using password >> >> Hello all, >> >> I have set up MySQL on my local box, everything works fine until I try to >> use a password when creating a datasource in CF Administrator. I get the >> "1045 Access Denied" error. I am able to create the datasource fine if I >> don't use a password, so I know the issue is not caused by incorrect >> privileges in the mysql.user or mysql.db tables. Also if I do set up the >> datasource without a password, and try to use a password in a cfquery, I >> get the same error. >> >> My platform: Windows XP SP2/CFMX 6.1/MySQL 4.1 >> >> Thanks for any assistance! >> >> >> >> > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216670 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: MySQL - CF: Access Denied when using password SOLVED
Thanks all, I downloaded the MySQL Connector/J 3.0 and it worked. First I downloaded the newer version 3.1 and that did not work, perhaps because I am on CFMX 6.1 rather than 7. Thanks again!! - Original Message - From: "Jeff Fleitz" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Sunday, August 28, 2005 7:05 AM Subject: Re: MySQL - CF: Access Denied when using password > Sorry, meant Fusion Authority blog. > -- > v/r, > > Jeff Fleitz > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216631 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: MySQL - CF: Access Denied when using password
The password algorithm was changed in MySQL 4.1. That is why you are having problems. Go here: http://dev.mysql.com/doc/mysql/en/old-client.html or here: http://rucus.ru.ac.za/docs/mysql/Old-client.html and you will find your solution. HTH Mike ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216628 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: MySQL - CF: Access Denied when using password
Sorry, meant Fusion Authority blog. -- v/r, Jeff Fleitz ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216620 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: MySQL - CF: Access Denied when using password
Hi Josh, I just got through going through this myself. There is a tech note regarding this issue (courtesy of the House of Fusion blog). Once I followed the instructions it appears to be working fine for me. I am using CFMX7, but the tech note is for MX, so it should work. http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=6ef0253&pss=rss_coldfusion_6ef0253 On 8/27/05, Josh Nathanson <[EMAIL PROTECTED]> wrote: > Hello all, > > I have set up MySQL on my local box, everything works fine until I try to use > a password when creating a datasource in CF Administrator. I get the "1045 > Access Denied" error. I am able to create the datasource fine if I don't use > a password, so I know the issue is not caused by incorrect privileges in the > mysql.user or mysql.db tables. Also if I do set up the datasource without a > password, and try to use a password in a cfquery, I get the same error. > > My platform: Windows XP SP2/CFMX 6.1/MySQL 4.1 > > Thanks for any assistance! > > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216618 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: MySQL - CF: Access Denied when using password
I experienced the same kind of problem a long time ago when I was using MySQL. Ran into it with SQL server I think too. Don't ask me why, but I believe what I did that worked was to go straight thru my Windows Control panel to setup the datasource. Control Panel > Administrative Tools > Data Sources > Click the system DSN tab. Mine is showing the MySQL driver as being ODBC MySQL 3.51 ODBC. Maybe you can try it like that? Then go thru CF admin afterwards. Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216614 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: MySQL - CF: Access Denied when using password
> Hello all, > > I have set up MySQL on my local box, everything works fine until I try > to use a password when creating a datasource in CF Administrator. I > get the "1045 Access Denied" error. I am able to create the > datasource fine if I don't use a password, so I know the issue is not > caused by incorrect privileges in the mysql.user or mysql.db tables. > Also if I do set up the datasource without a password, and try to use > a password in a cfquery, I get the same error. > > My platform: Windows XP SP2/CFMX 6.1/MySQL 4.1 > > Thanks for any assistance! > > I use SQL server (not sure how similar this is to MySQL) and there were two options when setting it up either use windows authentication or combined windows and sql authentication I chose windows authentication which means I don't have to pass the username and password as windows does that. Maybe that is what is happening in your case? I just modify my application template (on my PC) to reflect no username and password (as on the hosted server they are required). request.login=""; request.password=""; Incidently I found the whole process of setting up SQL Server locally a total nightmare but eventually I figured it out! Andrew. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216612 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: MySQL - CF: Access Denied when using password
i dunno if there is a better work around, i have never really bothered to look, the way i do it takes about 17.4 seconds to do so it's easy enough. Seems as tho there was someting about a local mysql system that just didnt let you use it but it sure looks like thats your fix and as long as you set it in the Application scope its there and only needs changed on one page. Technically, you can do quite a few things with this like in the application.cfm or CFC you can do some logic, like determine if its being called from localhost or from the live server and have it feed it the correct settings. ~Dave the disruptor~ "Some people just don't appreciate how difficult it is to dispense wisdom and abuse at the same time." From: "Josh Nathanson" <[EMAIL PROTECTED]> Sent: Saturday, August 27, 2005 5:24 PM To: CF-Talk Subject: Re: MySQL - CF: Access Denied when using password Thanks Dave, but I have set up a dummy user locally with the same username and password as that of the live application, so I don't think that is the issue... It works absolutely great as long as I don't try to use a password. For some reason that is what's throwing it off. I have a bunch of apps running remotely on CF/MySQL, and I am trying to move from MS Access to MySQL locally, so I'd like to be able to do that without rewriting all the code, or dealing with the commenting workaround you suggested. Is there some rule against a localhost user other than root connecting to MySQL? I am no expert so I might be missing something really obvious, but this also stumped my trusty web host who is something of a CF guru. I emailed him my MySQL user and db tables and he couldn't find anything wrong that would be causing the error. - Original Message - From: "dave" To: "CF-Talk" Sent: Saturday, August 27, 2005 1:07 PM Subject: re: MySQL - CF: Access Denied when using password > that would be because your local MySQL install doesnt have the same pw's > as your live one therefor you are trying to give it a username and > password it doesn't have and getting the warning. > > Not sure how others do it but in my Application.cfm or cfc (shut it will) > I set 2 sets of connection variable, 1 live and 1 local and just comment > out the one i dont need at the time. > > like > > > > > ---> > > > > > > > ~Dave the disruptor~ > "Some people just don't appreciate how difficult it is to dispense wisdom > and abuse at the same time." > > > From: "Josh Nathanson" > Sent: Saturday, August 27, 2005 1:49 PM > To: CF-Talk > Subject: MySQL - CF: Access Denied when using password > > Hello all, > > I have set up MySQL on my local box, everything works fine until I try to > use a password when creating a datasource in CF Administrator. I get the > "1045 Access Denied" error. I am able to create the datasource fine if I > don't use a password, so I know the issue is not caused by incorrect > privileges in the mysql.user or mysql.db tables. Also if I do set up the > datasource without a password, and try to use a password in a cfquery, I > get the same error. > > My platform: Windows XP SP2/CFMX 6.1/MySQL 4.1 > > Thanks for any assistance! > > > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216601 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: MySQL - CF: Access Denied when using password
Thanks Dave, but I have set up a dummy user locally with the same username and password as that of the live application, so I don't think that is the issue... It works absolutely great as long as I don't try to use a password. For some reason that is what's throwing it off. I have a bunch of apps running remotely on CF/MySQL, and I am trying to move from MS Access to MySQL locally, so I'd like to be able to do that without rewriting all the code, or dealing with the commenting workaround you suggested. Is there some rule against a localhost user other than root connecting to MySQL? I am no expert so I might be missing something really obvious, but this also stumped my trusty web host who is something of a CF guru. I emailed him my MySQL user and db tables and he couldn't find anything wrong that would be causing the error. - Original Message - From: "dave" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Saturday, August 27, 2005 1:07 PM Subject: re: MySQL - CF: Access Denied when using password > that would be because your local MySQL install doesnt have the same pw's > as your live one therefor you are trying to give it a username and > password it doesn't have and getting the warning. > > Not sure how others do it but in my Application.cfm or cfc (shut it will) > I set 2 sets of connection variable, 1 live and 1 local and just comment > out the one i dont need at the time. > > like > > > > > ---> > > > > > > > ~Dave the disruptor~ > "Some people just don't appreciate how difficult it is to dispense wisdom > and abuse at the same time." > > > From: "Josh Nathanson" <[EMAIL PROTECTED]> > Sent: Saturday, August 27, 2005 1:49 PM > To: CF-Talk > Subject: MySQL - CF: Access Denied when using password > > Hello all, > > I have set up MySQL on my local box, everything works fine until I try to > use a password when creating a datasource in CF Administrator. I get the > "1045 Access Denied" error. I am able to create the datasource fine if I > don't use a password, so I know the issue is not caused by incorrect > privileges in the mysql.user or mysql.db tables. Also if I do set up the > datasource without a password, and try to use a password in a cfquery, I > get the same error. > > My platform: Windows XP SP2/CFMX 6.1/MySQL 4.1 > > Thanks for any assistance! > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216599 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
re: MySQL - CF: Access Denied when using password
that would be because your local MySQL install doesnt have the same pw's as your live one therefor you are trying to give it a username and password it doesn't have and getting the warning. Not sure how others do it but in my Application.cfm or cfc (shut it will) I set 2 sets of connection variable, 1 live and 1 local and just comment out the one i dont need at the time. like ---> ~Dave the disruptor~ "Some people just don't appreciate how difficult it is to dispense wisdom and abuse at the same time." From: "Josh Nathanson" <[EMAIL PROTECTED]> Sent: Saturday, August 27, 2005 1:49 PM To: CF-Talk Subject: MySQL - CF: Access Denied when using password Hello all, I have set up MySQL on my local box, everything works fine until I try to use a password when creating a datasource in CF Administrator. I get the "1045 Access Denied" error. I am able to create the datasource fine if I don't use a password, so I know the issue is not caused by incorrect privileges in the mysql.user or mysql.db tables. Also if I do set up the datasource without a password, and try to use a password in a cfquery, I get the same error. My platform: Windows XP SP2/CFMX 6.1/MySQL 4.1 Thanks for any assistance! ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216594 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
SQL7 Full-Text (was RE: mySQL & CF)
Catalogs built once. In each of the 2 full-text searches below, only one
database column is indexed and searched in each of the 2 tables. Tables
indexed every day.
Typical full-text query template follows.
best, paul
=
?,{}/\]+", " ", "ALL")>
SELECT N_Word FROM NoiseWords (nolock)
WHERE N_Word = '#III#'
SELECT ID
FROM afulltable (nolock)
WHERE City = '#REQUEST.GetClientDataCity.City#'
AND ( CONTAINS(fulltext,'#VARIABLES_SEARCHTERM#'))
WHERE CONTAINS(fulltext,'#VARIABLES_SEARCHTERM#')
UPDATE ClientData
SET SearchList = '#ValueList(VeritySearchListings.ID)#'
WHERE CFI = #VAL(URL.CFI)#
AND CFT = #VAL(URL.CFT)#
SELECT ID,Class_ID,Class_Name,Pages
FROM YellowPages (nolock)
WHERE CONTAINS(Class_Name,'#VARIABLES_SEARCHTERM#')
ORDER BY Class_Name
UPDATE ClientData
SET HeadingList = '#ValueList(REQUEST.GetHeadings.ID)#'
WHERE CFI = #VAL(URL.CFI)#
AND CFT = #VAL(URL.CFT)#
==
At 08:26 PM 12/16/02 -0600, you wrote:
>hehe... maybe - can you post some of your code? How are your catalogs built
>and how often?
>
>-Mark
>
>-Original Message-----
>From: paul smith [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 16, 2002 8:10 PM
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>
>Thanks!
>
>CFQUERYPARAM enabled me to reduce that pesky overhead using client vars in
>a database to maintain state from 32ms to 16ms.
>
>Anything up your sleeve to reduce my SQL7 1,000ms-2,000ms full-text
>searches by 50% ?
>
>best, paul
>
>At 11:52 AM 12/16/02 -0600, you wrote:
> >While it's true that CFQUERYPARAM escapes characters, there's a bit more
> >going on under the covers. When you use CFQUERYPARAM you are assigning a
> >"type" to the variable that's passed to the RDBMS. This type information
> >insulates the item from such things as the infamous SQL query injection
> >attack. If the code that is rendered from using cfqueryparam where written
> >out in SQL it would look like this:
> >
> >---
> >--declarations
> >DECLARE @item1 char(50)
> >DECLARE @item2 char(15)
> >
> >--assignments (these actually come from the "input")
> >SELECT @itme1 = 'blah'
> >SELECT @item2 = ') truncate table Mytable'
> >
> >-- insert
> >Insert into MyTable (item1, item2)
> > values (@item1, @item2)
> >--
> >
> >Note that in item2, the hacker has tried to pass a command to kill your
> >table. But because the variable @item2 is predefined as a character string
> >it can't be done. SQL will treat whatever is in @item2 as character data -
> >no matter how pernicious . Incidentally, this is also why CFQUERYPARAM
> >is so much faster on MS SQL. the SQL server caches execution plans for
> >re-use. When you pass in a query that looks typical:
> >
> >insert into mytable (item1, item2)
> > values ('blah','blah')
> >
> >the execution plan is more likely to be unique - and therefore not in the
> >cache - because the 2 "value" items are part of the plan. Additionaly, SQL
> >must "lookup" the type to create the execution plan. So every insert
> >requires a new execution plan. When you use cfqeuryparam however, sql can
> >find an execution plan that is cached. That's because the actual plan will
> >not contain specific values but placeholders that are typed:
> >
> >insert into mytable (item1,item2)
> > values(@item1 char(10),@item2 char(40))
> >
> >This use of a saved execution plan reduces the "prepare" part of the SQL
> >process saving overhead. On a busy server this can cause an increase in
> >performance that is exponential. At least that's been my experience.
> >
> >-Mark
> >
> >
> >-Original Mes
RE: mySQL & CF
hehe... maybe - can you post some of your code? How are your catalogs built
and how often?
-Mark
-Original Message-
From: paul smith [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 8:10 PM
To: CF-Talk
Subject: RE: mySQL & CF
Thanks!
CFQUERYPARAM enabled me to reduce that pesky overhead using client vars in
a database to maintain state from 32ms to 16ms.
Anything up your sleeve to reduce my SQL7 1,000ms-2,000ms full-text
searches by 50% ?
best, paul
At 11:52 AM 12/16/02 -0600, you wrote:
>While it's true that CFQUERYPARAM escapes characters, there's a bit more
>going on under the covers. When you use CFQUERYPARAM you are assigning a
>"type" to the variable that's passed to the RDBMS. This type information
>insulates the item from such things as the infamous SQL query injection
>attack. If the code that is rendered from using cfqueryparam where written
>out in SQL it would look like this:
>
>---
>--declarations
>DECLARE @item1 char(50)
>DECLARE @item2 char(15)
>
>--assignments (these actually come from the "input")
>SELECT @itme1 = 'blah'
>SELECT @item2 = ') truncate table Mytable'
>
>-- insert
>Insert into MyTable (item1, item2)
> values (@item1, @item2)
>--
>
>Note that in item2, the hacker has tried to pass a command to kill your
>table. But because the variable @item2 is predefined as a character string
>it can't be done. SQL will treat whatever is in @item2 as character data -
>no matter how pernicious . Incidentally, this is also why CFQUERYPARAM
>is so much faster on MS SQL. the SQL server caches execution plans for
>re-use. When you pass in a query that looks typical:
>
>insert into mytable (item1, item2)
> values ('blah','blah')
>
>the execution plan is more likely to be unique - and therefore not in the
>cache - because the 2 "value" items are part of the plan. Additionaly, SQL
>must "lookup" the type to create the execution plan. So every insert
>requires a new execution plan. When you use cfqeuryparam however, sql can
>find an execution plan that is cached. That's because the actual plan will
>not contain specific values but placeholders that are typed:
>
>insert into mytable (item1,item2)
> values(@item1 char(10),@item2 char(40))
>
>This use of a saved execution plan reduces the "prepare" part of the SQL
>process saving overhead. On a busy server this can cause an increase in
>performance that is exponential. At least that's been my experience.
>
>-Mark
>
>
>-Original Message-
>From: Craig Dudley [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 16, 2002 11:05 AM
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>
>cfqueryparam will escape potentially dangerous characters for you. This
>is ESSENTIAL if you don't want to have your database dropped by some
>nasty hacker type person.
>
>Search through the archives for SQL injection attacks, you'll soon see
>why.
>
>It will also negate the need to escape quotes and other things manually,
>which is quite handy too.
>
>Trust me, cfqueryparam is your friend ;-)
>
>Craig.
>
>-Original Message-
>From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
>Sent: 16 December 2002 16:51
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>Hi, Craig, and thanks for the reply.
>
>When you say they "make things a lot more secure."
>What exactly do you mean? Boy, that's a lot of extra typing
>over the typical CFINSERT syntax...
>
>Rick
>
>
>-Original Message-
>From: Craig Dudley [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 16, 2002 11:10 AM
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>
>Standard SQL inserts will work fine on most if not all RDBMS's
>
>Eg.
>
>insert into tablename
>(int_col1,varchar_col2)
>values
>(value="#form.val1#">,value="#form.val2#">)
>
>Do try to use the cfqueryparams, they make things a lot more secure.
>
>-Original Message-
>From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
>Sent: 16 December 2002 16:02
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>Hi, Matt.
>
>I, too, learned about the CFUPDATE problem from personal experience
>and from the Allaire forums when I first started using CF (with Access
>at
>that time).
>I stopped using it and went to the CFQUERY...Set... approach. That's
>worked
>fine.
>
>I haven't had any problems with CFINSERT, but if that may be problematic
>in
>the future
>I may as well go ahead and change my coding habits now.
>
RE: mySQL & CF
Thanks!
CFQUERYPARAM enabled me to reduce that pesky overhead using client vars in
a database to maintain state from 32ms to 16ms.
Anything up your sleeve to reduce my SQL7 1,000ms-2,000ms full-text
searches by 50% ?
best, paul
At 11:52 AM 12/16/02 -0600, you wrote:
>While it's true that CFQUERYPARAM escapes characters, there's a bit more
>going on under the covers. When you use CFQUERYPARAM you are assigning a
>"type" to the variable that's passed to the RDBMS. This type information
>insulates the item from such things as the infamous SQL query injection
>attack. If the code that is rendered from using cfqueryparam where written
>out in SQL it would look like this:
>
>---
>--declarations
>DECLARE @item1 char(50)
>DECLARE @item2 char(15)
>
>--assignments (these actually come from the "input")
>SELECT @itme1 = 'blah'
>SELECT @item2 = ') truncate table Mytable'
>
>-- insert
>Insert into MyTable (item1, item2)
> values (@item1, @item2)
>--
>
>Note that in item2, the hacker has tried to pass a command to kill your
>table. But because the variable @item2 is predefined as a character string
>it can't be done. SQL will treat whatever is in @item2 as character data -
>no matter how pernicious . Incidentally, this is also why CFQUERYPARAM
>is so much faster on MS SQL. the SQL server caches execution plans for
>re-use. When you pass in a query that looks typical:
>
>insert into mytable (item1, item2)
> values ('blah','blah')
>
>the execution plan is more likely to be unique - and therefore not in the
>cache - because the 2 "value" items are part of the plan. Additionaly, SQL
>must "lookup" the type to create the execution plan. So every insert
>requires a new execution plan. When you use cfqeuryparam however, sql can
>find an execution plan that is cached. That's because the actual plan will
>not contain specific values but placeholders that are typed:
>
>insert into mytable (item1,item2)
> values(@item1 char(10),@item2 char(40))
>
>This use of a saved execution plan reduces the "prepare" part of the SQL
>process saving overhead. On a busy server this can cause an increase in
>performance that is exponential. At least that's been my experience.
>
>-Mark
>
>
>-Original Message-
>From: Craig Dudley [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 16, 2002 11:05 AM
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>
>cfqueryparam will escape potentially dangerous characters for you. This
>is ESSENTIAL if you don't want to have your database dropped by some
>nasty hacker type person.
>
>Search through the archives for SQL injection attacks, you'll soon see
>why.
>
>It will also negate the need to escape quotes and other things manually,
>which is quite handy too.
>
>Trust me, cfqueryparam is your friend ;-)
>
>Craig.
>
>-Original Message-
>From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
>Sent: 16 December 2002 16:51
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>Hi, Craig, and thanks for the reply.
>
>When you say they "make things a lot more secure."
>What exactly do you mean? Boy, that's a lot of extra typing
>over the typical CFINSERT syntax...
>
>Rick
>
>
>-Original Message-
>From: Craig Dudley [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 16, 2002 11:10 AM
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>
>Standard SQL inserts will work fine on most if not all RDBMS's
>
>Eg.
>
>insert into tablename
>(int_col1,varchar_col2)
>values
>(value="#form.val1#">,value="#form.val2#">)
>
>Do try to use the cfqueryparams, they make things a lot more secure.
>
>-Original Message-
>From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
>Sent: 16 December 2002 16:02
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>Hi, Matt.
>
>I, too, learned about the CFUPDATE problem from personal experience
>and from the Allaire forums when I first started using CF (with Access
>at
>that time).
>I stopped using it and went to the CFQUERY...Set... approach. That's
>worked
>fine.
>
>I haven't had any problems with CFINSERT, but if that may be problematic
>in
>the future
>I may as well go ahead and change my coding habits now.
>
>How is the INSERT coded for mySQL and CF?
>Example?
>
>Thanks,
>
>Rick
>
>
>-Original Message-
>From: Matt Robertson [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 16, 2002 1:11 AM
>To: CF-Talk
>Sub
RE: mySQL & CF
Thanks for the insights, Mark!
Rick
-Original Message-
From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 12:53 PM
To: CF-Talk
Subject: RE: mySQL & CF
While it's true that CFQUERYPARAM escapes characters, there's a bit more
going on under the covers. When you use CFQUERYPARAM you are assigning a
"type" to the variable that's passed to the RDBMS. This type information
insulates the item from such things as the infamous SQL query injection
attack. If the code that is rendered from using cfqueryparam where written
out in SQL it would look like this:
---
--declarations
DECLARE @item1 char(50)
DECLARE @item2 char(15)
--assignments (these actually come from the "input")
SELECT @itme1 = 'blah'
SELECT @item2 = ') truncate table Mytable'
-- insert
Insert into MyTable (item1, item2)
values (@item1, @item2)
--
Note that in item2, the hacker has tried to pass a command to kill your
table. But because the variable @item2 is predefined as a character string
it can't be done. SQL will treat whatever is in @item2 as character data -
no matter how pernicious . Incidentally, this is also why CFQUERYPARAM
is so much faster on MS SQL. the SQL server caches execution plans for
re-use. When you pass in a query that looks typical:
insert into mytable (item1, item2)
values ('blah','blah')
the execution plan is more likely to be unique - and therefore not in the
cache - because the 2 "value" items are part of the plan. Additionaly, SQL
must "lookup" the type to create the execution plan. So every insert
requires a new execution plan. When you use cfqeuryparam however, sql can
find an execution plan that is cached. That's because the actual plan will
not contain specific values but placeholders that are typed:
insert into mytable (item1,item2)
values(@item1 char(10),@item2 char(40))
This use of a saved execution plan reduces the "prepare" part of the SQL
process saving overhead. On a busy server this can cause an increase in
performance that is exponential. At least that's been my experience.
-Mark
-Original Message-----
From: Craig Dudley [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 11:05 AM
To: CF-Talk
Subject: RE: mySQL & CF
cfqueryparam will escape potentially dangerous characters for you. This
is ESSENTIAL if you don't want to have your database dropped by some
nasty hacker type person.
Search through the archives for SQL injection attacks, you'll soon see
why.
It will also negate the need to escape quotes and other things manually,
which is quite handy too.
Trust me, cfqueryparam is your friend ;-)
Craig.
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
Sent: 16 December 2002 16:51
To: CF-Talk
Subject: RE: mySQL & CF
Hi, Craig, and thanks for the reply.
When you say they "make things a lot more secure."
What exactly do you mean? Boy, that's a lot of extra typing
over the typical CFINSERT syntax...
Rick
-Original Message-
From: Craig Dudley [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 11:10 AM
To: CF-Talk
Subject: RE: mySQL & CF
Standard SQL inserts will work fine on most if not all RDBMS's
Eg.
insert into tablename
(int_col1,varchar_col2)
values
(,)
Do try to use the cfqueryparams, they make things a lot more secure.
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
Sent: 16 December 2002 16:02
To: CF-Talk
Subject: RE: mySQL & CF
Hi, Matt.
I, too, learned about the CFUPDATE problem from personal experience
and from the Allaire forums when I first started using CF (with Access
at
that time).
I stopped using it and went to the CFQUERY...Set... approach. That's
worked
fine.
I haven't had any problems with CFINSERT, but if that may be problematic
in
the future
I may as well go ahead and change my coding habits now.
How is the INSERT coded for mySQL and CF?
Example?
Thanks,
Rick
-Original Message-
From: Matt Robertson [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 1:11 AM
To: CF-Talk
Subject: RE: mySQL & CF
Rick,
I noticed you mention that you use CFINSERT. From your earlier postings
I
know you are on CF 4.5x, as I am.
You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow
sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure,
and
maybe 4.0x). In threads on the subject over at the (then) Allaire
forums no
specific cause was ever traced. It just happens. Sometimes.
In one of those threads I believe it was Paul Hastings who advised me to
'just say no to cfinsert/cfupdate' and it ranks as some of the best CF
advice I ever got. On the surface those tags appear to be handy
shortcuts,
but they black-box your
RE: mySQL & CF
While it's true that CFQUERYPARAM escapes characters, there's a bit more
going on under the covers. When you use CFQUERYPARAM you are assigning a
"type" to the variable that's passed to the RDBMS. This type information
insulates the item from such things as the infamous SQL query injection
attack. If the code that is rendered from using cfqueryparam where written
out in SQL it would look like this:
---
--declarations
DECLARE @item1 char(50)
DECLARE @item2 char(15)
--assignments (these actually come from the "input")
SELECT @itme1 = 'blah'
SELECT @item2 = ') truncate table Mytable'
-- insert
Insert into MyTable (item1, item2)
values (@item1, @item2)
--
Note that in item2, the hacker has tried to pass a command to kill your
table. But because the variable @item2 is predefined as a character string
it can't be done. SQL will treat whatever is in @item2 as character data -
no matter how pernicious . Incidentally, this is also why CFQUERYPARAM
is so much faster on MS SQL. the SQL server caches execution plans for
re-use. When you pass in a query that looks typical:
insert into mytable (item1, item2)
values ('blah','blah')
the execution plan is more likely to be unique - and therefore not in the
cache - because the 2 "value" items are part of the plan. Additionaly, SQL
must "lookup" the type to create the execution plan. So every insert
requires a new execution plan. When you use cfqeuryparam however, sql can
find an execution plan that is cached. That's because the actual plan will
not contain specific values but placeholders that are typed:
insert into mytable (item1,item2)
values(@item1 char(10),@item2 char(40))
This use of a saved execution plan reduces the "prepare" part of the SQL
process saving overhead. On a busy server this can cause an increase in
performance that is exponential. At least that's been my experience.
-Mark
-Original Message-----
From: Craig Dudley [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 11:05 AM
To: CF-Talk
Subject: RE: mySQL & CF
cfqueryparam will escape potentially dangerous characters for you. This
is ESSENTIAL if you don't want to have your database dropped by some
nasty hacker type person.
Search through the archives for SQL injection attacks, you'll soon see
why.
It will also negate the need to escape quotes and other things manually,
which is quite handy too.
Trust me, cfqueryparam is your friend ;-)
Craig.
-----Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
Sent: 16 December 2002 16:51
To: CF-Talk
Subject: RE: mySQL & CF
Hi, Craig, and thanks for the reply.
When you say they "make things a lot more secure."
What exactly do you mean? Boy, that's a lot of extra typing
over the typical CFINSERT syntax...
Rick
-Original Message-
From: Craig Dudley [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 11:10 AM
To: CF-Talk
Subject: RE: mySQL & CF
Standard SQL inserts will work fine on most if not all RDBMS's
Eg.
insert into tablename
(int_col1,varchar_col2)
values
(,)
Do try to use the cfqueryparams, they make things a lot more secure.
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
Sent: 16 December 2002 16:02
To: CF-Talk
Subject: RE: mySQL & CF
Hi, Matt.
I, too, learned about the CFUPDATE problem from personal experience
and from the Allaire forums when I first started using CF (with Access
at
that time).
I stopped using it and went to the CFQUERY...Set... approach. That's
worked
fine.
I haven't had any problems with CFINSERT, but if that may be problematic
in
the future
I may as well go ahead and change my coding habits now.
How is the INSERT coded for mySQL and CF?
Example?
Thanks,
Rick
-Original Message-
From: Matt Robertson [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 1:11 AM
To: CF-Talk
Subject: RE: mySQL & CF
Rick,
I noticed you mention that you use CFINSERT. From your earlier postings
I
know you are on CF 4.5x, as I am.
You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow
sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure,
and
maybe 4.0x). In threads on the subject over at the (then) Allaire
forums no
specific cause was ever traced. It just happens. Sometimes.
In one of those threads I believe it was Paul Hastings who advised me to
'just say no to cfinsert/cfupdate' and it ranks as some of the best CF
advice I ever got. On the surface those tags appear to be handy
shortcuts,
but they black-box your SQL, take away the otherwise granular control
you
should have and make debugging ... difficult.
I suggest you follow the same advice -- you'll probably find out you
have
to, anyway. While yo
Re: mySQL & CF (url injection info)
Cary Gordon wrote: > CFQueryParam gives you an easy way to validate the query params. Some > clever, bored hackers have figured out how to do things like pass a drop > table query through the header. It is an even easier exploit if you are > passing query parameters through the url string. a simple example .. passing the column name instead of a value can get very damaging delete from comments where comment_id = #comment_id# which when #comment_id# = comment_id means delete from comments where comment_id=comment_id or as comment_id=comment_id is always true actually becomes delete from comments neat huh? or with users update user set password='#password#' where user_id=#user_id# nice way to reset all passwords on the site z z ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: mySQL & CF
Thanks, Craig! Rick -Original Message- From: Craig Dudley [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 12:05 PM To: CF-Talk Subject: RE: mySQL & CF cfqueryparam will escape potentially dangerous characters for you. This is ESSENTIAL if you don't want to have your database dropped by some nasty hacker type person. Search through the archives for SQL injection attacks, you'll soon see why. It will also negate the need to escape quotes and other things manually, which is quite handy too. Trust me, cfqueryparam is your friend ;-) Craig. -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: 16 December 2002 16:51 To: CF-Talk Subject: RE: mySQL & CF Hi, Craig, and thanks for the reply. When you say they "make things a lot more secure." What exactly do you mean? Boy, that's a lot of extra typing over the typical CFINSERT syntax... Rick -Original Message- From: Craig Dudley [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 11:10 AM To: CF-Talk Subject: RE: mySQL & CF Standard SQL inserts will work fine on most if not all RDBMS's Eg. insert into tablename (int_col1,varchar_col2) values (,) Do try to use the cfqueryparams, they make things a lot more secure. -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: 16 December 2002 16:02 To: CF-Talk Subject: RE: mySQL & CF Hi, Matt. I, too, learned about the CFUPDATE problem from personal experience and from the Allaire forums when I first started using CF (with Access at that time). I stopped using it and went to the CFQUERY...Set... approach. That's worked fine. I haven't had any problems with CFINSERT, but if that may be problematic in the future I may as well go ahead and change my coding habits now. How is the INSERT coded for mySQL and CF? Example? Thanks, Rick -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:11 AM To: CF-Talk Subject: RE: mySQL & CF Rick, I noticed you mention that you use CFINSERT. From your earlier postings I know you are on CF 4.5x, as I am. You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure, and maybe 4.0x). In threads on the subject over at the (then) Allaire forums no specific cause was ever traced. It just happens. Sometimes. In one of those threads I believe it was Paul Hastings who advised me to 'just say no to cfinsert/cfupdate' and it ranks as some of the best CF advice I ever got. On the surface those tags appear to be handy shortcuts, but they black-box your SQL, take away the otherwise granular control you should have and make debugging ... difficult. I suggest you follow the same advice -- you'll probably find out you have to, anyway. While you're at it go for the double bonus and implement cfqueryparam. Happy Monday (early) :) --- Matt Robertson, MSB Designs, Inc. http://mysecretbase.com - Retail http://foohbar.org - ColdFusion Tools --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: mySQL & CF
Thanks, Cary! Rick -Original Message- From: Cary Gordon [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 12:07 PM To: CF-Talk Subject: RE: mySQL & CF CFQueryParam gives you an easy way to validate the query params. Some clever, bored hackers have figured out how to do things like pass a drop table query through the header. It is an even easier exploit if you are passing query parameters through the url string. At 11:51 AM 12/16/2002 -0500, you wrote: >Hi, Craig, and thanks for the reply. > >When you say they "make things a lot more secure." >What exactly do you mean? Boy, that's a lot of extra typing >over the typical CFINSERT syntax... > >Rick > > >-Original Message- >From: Craig Dudley [mailto:[EMAIL PROTECTED]] >Sent: Monday, December 16, 2002 11:10 AM >To: CF-Talk >Subject: RE: mySQL & CF > > >Standard SQL inserts will work fine on most if not all RDBMS's > >Eg. > >insert into tablename >(int_col1,varchar_col2) >values >(value="#form.val1#">,value="#form.val2#">) > >Do try to use the cfqueryparams, they make things a lot more secure. > >-----Original Message- >From: Rick Faircloth [mailto:[EMAIL PROTECTED]] >Sent: 16 December 2002 16:02 >To: CF-Talk >Subject: RE: mySQL & CF > >Hi, Matt. > >I, too, learned about the CFUPDATE problem from personal experience >and from the Allaire forums when I first started using CF (with Access >at >that time). >I stopped using it and went to the CFQUERY...Set... approach. That's >worked >fine. > >I haven't had any problems with CFINSERT, but if that may be problematic >in >the future >I may as well go ahead and change my coding habits now. > >How is the INSERT coded for mySQL and CF? >Example? > >Thanks, > >Rick > > >-Original Message- >From: Matt Robertson [mailto:[EMAIL PROTECTED]] >Sent: Monday, December 16, 2002 1:11 AM >To: CF-Talk >Subject: RE: mySQL & CF > > >Rick, > >I noticed you mention that you use CFINSERT. From your earlier postings >I >know you are on CF 4.5x, as I am. > >You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow >sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure, >and >maybe 4.0x). In threads on the subject over at the (then) Allaire >forums no >specific cause was ever traced. It just happens. Sometimes. > >In one of those threads I believe it was Paul Hastings who advised me to >'just say no to cfinsert/cfupdate' and it ranks as some of the best CF >advice I ever got. On the surface those tags appear to be handy >shortcuts, >but they black-box your SQL, take away the otherwise granular control >you >should have and make debugging ... difficult. > >I suggest you follow the same advice -- you'll probably find out you >have >to, anyway. While you're at it go for the double bonus and implement >cfqueryparam. > >Happy Monday (early) :) > >--- >Matt Robertson, MSB Designs, Inc. >http://mysecretbase.com - Retail >http://foohbar.org - ColdFusion Tools >--- > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: mySQL & CF
cfqueryparam will escape potentially dangerous characters for you. This is ESSENTIAL if you don't want to have your database dropped by some nasty hacker type person. Search through the archives for SQL injection attacks, you'll soon see why. It will also negate the need to escape quotes and other things manually, which is quite handy too. Trust me, cfqueryparam is your friend ;-) Craig. -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: 16 December 2002 16:51 To: CF-Talk Subject: RE: mySQL & CF Hi, Craig, and thanks for the reply. When you say they "make things a lot more secure." What exactly do you mean? Boy, that's a lot of extra typing over the typical CFINSERT syntax... Rick -Original Message- From: Craig Dudley [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 11:10 AM To: CF-Talk Subject: RE: mySQL & CF Standard SQL inserts will work fine on most if not all RDBMS's Eg. insert into tablename (int_col1,varchar_col2) values (,) Do try to use the cfqueryparams, they make things a lot more secure. -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: 16 December 2002 16:02 To: CF-Talk Subject: RE: mySQL & CF Hi, Matt. I, too, learned about the CFUPDATE problem from personal experience and from the Allaire forums when I first started using CF (with Access at that time). I stopped using it and went to the CFQUERY...Set... approach. That's worked fine. I haven't had any problems with CFINSERT, but if that may be problematic in the future I may as well go ahead and change my coding habits now. How is the INSERT coded for mySQL and CF? Example? Thanks, Rick -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:11 AM To: CF-Talk Subject: RE: mySQL & CF Rick, I noticed you mention that you use CFINSERT. From your earlier postings I know you are on CF 4.5x, as I am. You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure, and maybe 4.0x). In threads on the subject over at the (then) Allaire forums no specific cause was ever traced. It just happens. Sometimes. In one of those threads I believe it was Paul Hastings who advised me to 'just say no to cfinsert/cfupdate' and it ranks as some of the best CF advice I ever got. On the surface those tags appear to be handy shortcuts, but they black-box your SQL, take away the otherwise granular control you should have and make debugging ... difficult. I suggest you follow the same advice -- you'll probably find out you have to, anyway. While you're at it go for the double bonus and implement cfqueryparam. Happy Monday (early) :) --- Matt Robertson, MSB Designs, Inc. http://mysecretbase.com - Retail http://foohbar.org - ColdFusion Tools --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: mySQL & CF
CFQueryParam gives you an easy way to validate the query params. Some clever, bored hackers have figured out how to do things like pass a drop table query through the header. It is an even easier exploit if you are passing query parameters through the url string. At 11:51 AM 12/16/2002 -0500, you wrote: >Hi, Craig, and thanks for the reply. > >When you say they "make things a lot more secure." >What exactly do you mean? Boy, that's a lot of extra typing >over the typical CFINSERT syntax... > >Rick > > >-Original Message- >From: Craig Dudley [mailto:[EMAIL PROTECTED]] >Sent: Monday, December 16, 2002 11:10 AM >To: CF-Talk >Subject: RE: mySQL & CF > > >Standard SQL inserts will work fine on most if not all RDBMS's > >Eg. > >insert into tablename >(int_col1,varchar_col2) >values >(value="#form.val1#">,value="#form.val2#">) > >Do try to use the cfqueryparams, they make things a lot more secure. > >-Original Message- >From: Rick Faircloth [mailto:[EMAIL PROTECTED]] >Sent: 16 December 2002 16:02 >To: CF-Talk >Subject: RE: mySQL & CF > >Hi, Matt. > >I, too, learned about the CFUPDATE problem from personal experience >and from the Allaire forums when I first started using CF (with Access >at >that time). >I stopped using it and went to the CFQUERY...Set... approach. That's >worked >fine. > >I haven't had any problems with CFINSERT, but if that may be problematic >in >the future >I may as well go ahead and change my coding habits now. > >How is the INSERT coded for mySQL and CF? >Example? > >Thanks, > >Rick > > >-Original Message- >From: Matt Robertson [mailto:[EMAIL PROTECTED]] >Sent: Monday, December 16, 2002 1:11 AM >To: CF-Talk >Subject: RE: mySQL & CF > > >Rick, > >I noticed you mention that you use CFINSERT. From your earlier postings >I >know you are on CF 4.5x, as I am. > >You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow >sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure, >and >maybe 4.0x). In threads on the subject over at the (then) Allaire >forums no >specific cause was ever traced. It just happens. Sometimes. > >In one of those threads I believe it was Paul Hastings who advised me to >'just say no to cfinsert/cfupdate' and it ranks as some of the best CF >advice I ever got. On the surface those tags appear to be handy >shortcuts, >but they black-box your SQL, take away the otherwise granular control >you >should have and make debugging ... difficult. > >I suggest you follow the same advice -- you'll probably find out you >have >to, anyway. While you're at it go for the double bonus and implement >cfqueryparam. > >Happy Monday (early) :) > >--- >Matt Robertson, MSB Designs, Inc. >http://mysecretbase.com - Retail >http://foohbar.org - ColdFusion Tools >--- > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: mySQL & CF
Hi, Craig, and thanks for the reply. When you say they "make things a lot more secure." What exactly do you mean? Boy, that's a lot of extra typing over the typical CFINSERT syntax... Rick -Original Message- From: Craig Dudley [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 11:10 AM To: CF-Talk Subject: RE: mySQL & CF Standard SQL inserts will work fine on most if not all RDBMS's Eg. insert into tablename (int_col1,varchar_col2) values (,) Do try to use the cfqueryparams, they make things a lot more secure. -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: 16 December 2002 16:02 To: CF-Talk Subject: RE: mySQL & CF Hi, Matt. I, too, learned about the CFUPDATE problem from personal experience and from the Allaire forums when I first started using CF (with Access at that time). I stopped using it and went to the CFQUERY...Set... approach. That's worked fine. I haven't had any problems with CFINSERT, but if that may be problematic in the future I may as well go ahead and change my coding habits now. How is the INSERT coded for mySQL and CF? Example? Thanks, Rick -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:11 AM To: CF-Talk Subject: RE: mySQL & CF Rick, I noticed you mention that you use CFINSERT. From your earlier postings I know you are on CF 4.5x, as I am. You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure, and maybe 4.0x). In threads on the subject over at the (then) Allaire forums no specific cause was ever traced. It just happens. Sometimes. In one of those threads I believe it was Paul Hastings who advised me to 'just say no to cfinsert/cfupdate' and it ranks as some of the best CF advice I ever got. On the surface those tags appear to be handy shortcuts, but they black-box your SQL, take away the otherwise granular control you should have and make debugging ... difficult. I suggest you follow the same advice -- you'll probably find out you have to, anyway. While you're at it go for the double bonus and implement cfqueryparam. Happy Monday (early) :) --- Matt Robertson, MSB Designs, Inc. http://mysecretbase.com - Retail http://foohbar.org - ColdFusion Tools --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: mySQL & CF
Hi, Jon.
>With mysql, you need to watch out for illegal characters. That means if
>value1 = "This won't work" you need to replace it with "This won\'t work"
Will I have to run some code to parse the strings to find illegal characters
and insert a "\" before them?
Will using CFPARAM (as Craig suggested in another response to this thread)
and redefining the form.variables (which is where the strings to insert will
come from)
take care of the illegal character problem?
Thanks for your help...
Rick
-Original Message-
From: jon roig [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 11:08 AM
To: CF-Talk
Subject: RE: mySQL & CF
For mysql -- or any sql, really -- you code it as follows:
INSERT INTO yourTable (field1, field2) VALUES ('value1', 'value2')
With mysql, you need to watch out for illegal characters. That means if
value1 = "This won't work" you need to replace it with "This won\'t work"
In other words, you need to escape the quotes.
Good luck and enjoy mysql. For what it is -- a quick, simple little db --
it's really quite nice.
-- jon
-
jon roig
senior manager, online production
epilepsy foundation
phone: 215.850.0710
site: http://www.epilepsyfoundation.org
email: [EMAIL PROTECTED]
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 11:02 AM
To: CF-Talk
Subject: RE: mySQL & CF
Hi, Matt.
I, too, learned about the CFUPDATE problem from personal experience
and from the Allaire forums when I first started using CF (with Access at
that time).
I stopped using it and went to the CFQUERY...Set... approach. That's worked
fine.
I haven't had any problems with CFINSERT, but if that may be problematic in
the future
I may as well go ahead and change my coding habits now.
How is the INSERT coded for mySQL and CF?
Example?
Thanks,
Rick
-Original Message-
From: Matt Robertson [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 1:11 AM
To: CF-Talk
Subject: RE: mySQL & CF
Rick,
I noticed you mention that you use CFINSERT. From your earlier postings I
know you are on CF 4.5x, as I am.
You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow
sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure, and
maybe 4.0x). In threads on the subject over at the (then) Allaire forums no
specific cause was ever traced. It just happens. Sometimes.
In one of those threads I believe it was Paul Hastings who advised me to
'just say no to cfinsert/cfupdate' and it ranks as some of the best CF
advice I ever got. On the surface those tags appear to be handy shortcuts,
but they black-box your SQL, take away the otherwise granular control you
should have and make debugging ... difficult.
I suggest you follow the same advice -- you'll probably find out you have
to, anyway. While you're at it go for the double bonus and implement
cfqueryparam.
Happy Monday (early) :)
---
Matt Robertson, MSB Designs, Inc.
http://mysecretbase.com - Retail
http://foohbar.org - ColdFusion Tools
---
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
RE: mySQL & CF
Standard SQL inserts will work fine on most if not all RDBMS's Eg. insert into tablename (int_col1,varchar_col2) values (,) Do try to use the cfqueryparams, they make things a lot more secure. -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: 16 December 2002 16:02 To: CF-Talk Subject: RE: mySQL & CF Hi, Matt. I, too, learned about the CFUPDATE problem from personal experience and from the Allaire forums when I first started using CF (with Access at that time). I stopped using it and went to the CFQUERY...Set... approach. That's worked fine. I haven't had any problems with CFINSERT, but if that may be problematic in the future I may as well go ahead and change my coding habits now. How is the INSERT coded for mySQL and CF? Example? Thanks, Rick -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:11 AM To: CF-Talk Subject: RE: mySQL & CF Rick, I noticed you mention that you use CFINSERT. From your earlier postings I know you are on CF 4.5x, as I am. You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure, and maybe 4.0x). In threads on the subject over at the (then) Allaire forums no specific cause was ever traced. It just happens. Sometimes. In one of those threads I believe it was Paul Hastings who advised me to 'just say no to cfinsert/cfupdate' and it ranks as some of the best CF advice I ever got. On the surface those tags appear to be handy shortcuts, but they black-box your SQL, take away the otherwise granular control you should have and make debugging ... difficult. I suggest you follow the same advice -- you'll probably find out you have to, anyway. While you're at it go for the double bonus and implement cfqueryparam. Happy Monday (early) :) --- Matt Robertson, MSB Designs, Inc. http://mysecretbase.com - Retail http://foohbar.org - ColdFusion Tools --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: mySQL & CF
For mysql -- or any sql, really -- you code it as follows:
INSERT INTO yourTable (field1, field2) VALUES ('value1', 'value2')
With mysql, you need to watch out for illegal characters. That means if
value1 = "This won't work" you need to replace it with "This won\'t work"
In other words, you need to escape the quotes.
Good luck and enjoy mysql. For what it is -- a quick, simple little db --
it's really quite nice.
-- jon
-
jon roig
senior manager, online production
epilepsy foundation
phone: 215.850.0710
site: http://www.epilepsyfoundation.org
email: [EMAIL PROTECTED]
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 11:02 AM
To: CF-Talk
Subject: RE: mySQL & CF
Hi, Matt.
I, too, learned about the CFUPDATE problem from personal experience
and from the Allaire forums when I first started using CF (with Access at
that time).
I stopped using it and went to the CFQUERY...Set... approach. That's worked
fine.
I haven't had any problems with CFINSERT, but if that may be problematic in
the future
I may as well go ahead and change my coding habits now.
How is the INSERT coded for mySQL and CF?
Example?
Thanks,
Rick
-Original Message-
From: Matt Robertson [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 1:11 AM
To: CF-Talk
Subject: RE: mySQL & CF
Rick,
I noticed you mention that you use CFINSERT. From your earlier postings I
know you are on CF 4.5x, as I am.
You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow
sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure, and
maybe 4.0x). In threads on the subject over at the (then) Allaire forums no
specific cause was ever traced. It just happens. Sometimes.
In one of those threads I believe it was Paul Hastings who advised me to
'just say no to cfinsert/cfupdate' and it ranks as some of the best CF
advice I ever got. On the surface those tags appear to be handy shortcuts,
but they black-box your SQL, take away the otherwise granular control you
should have and make debugging ... difficult.
I suggest you follow the same advice -- you'll probably find out you have
to, anyway. While you're at it go for the double bonus and implement
cfqueryparam.
Happy Monday (early) :)
---
Matt Robertson, MSB Designs, Inc.
http://mysecretbase.com - Retail
http://foohbar.org - ColdFusion Tools
---
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
RE: mySQL & CF
Thanks for the tip, Luis...I'll read up on ENUM. Rick -Original Message- From: Luis Lebron [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 9:26 AM To: CF-Talk Subject: RE: mySQL & CF For Yes and No I use and ENUM field and specify 'Yes','No' as the acceptable choices. Luis -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 15, 2002 6:40 PM To: CF-Talk Subject: mySQL & CF Hi, all. I'm learning to use mySQL with CF instead of Access... Access had a yes/no field type...in mySQL, I'm using tinyint...is that appropriate? In Access, a value of "Yes" passed in a checkbox formfield put a 1 value in the field with a CFINSERT... Now, the value has been changed to 1 when checked, because mySQL doesn't like "Yes" for tinyint When inserting checkbox values into a mySQL db, I'm using 1 for "Yes" or "True"... but if the checkbox is unchecked, of course, no value is passed and the field (up to this point) has been left blank or null. Should I have it do a zerofill instead of being empty or null? The mySQL fields that were left blank or null when the CFINSERT occured, appropriately left the checkboxes unchecked when I used and update page... Best procedure for handling checkbox values in mySQL? Thanks, Rick ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: mySQL & CF
Hi, Matt. I, too, learned about the CFUPDATE problem from personal experience and from the Allaire forums when I first started using CF (with Access at that time). I stopped using it and went to the CFQUERY...Set... approach. That's worked fine. I haven't had any problems with CFINSERT, but if that may be problematic in the future I may as well go ahead and change my coding habits now. How is the INSERT coded for mySQL and CF? Example? Thanks, Rick -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:11 AM To: CF-Talk Subject: RE: mySQL & CF Rick, I noticed you mention that you use CFINSERT. From your earlier postings I know you are on CF 4.5x, as I am. You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure, and maybe 4.0x). In threads on the subject over at the (then) Allaire forums no specific cause was ever traced. It just happens. Sometimes. In one of those threads I believe it was Paul Hastings who advised me to 'just say no to cfinsert/cfupdate' and it ranks as some of the best CF advice I ever got. On the surface those tags appear to be handy shortcuts, but they black-box your SQL, take away the otherwise granular control you should have and make debugging ... difficult. I suggest you follow the same advice -- you'll probably find out you have to, anyway. While you're at it go for the double bonus and implement cfqueryparam. Happy Monday (early) :) --- Matt Robertson, MSB Designs, Inc. http://mysecretbase.com - Retail http://foohbar.org - ColdFusion Tools --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: mySQL & CF
For Yes and No I use and ENUM field and specify 'Yes','No' as the acceptable choices. Luis -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 15, 2002 6:40 PM To: CF-Talk Subject: mySQL & CF Hi, all. I'm learning to use mySQL with CF instead of Access... Access had a yes/no field type...in mySQL, I'm using tinyint...is that appropriate? In Access, a value of "Yes" passed in a checkbox formfield put a 1 value in the field with a CFINSERT... Now, the value has been changed to 1 when checked, because mySQL doesn't like "Yes" for tinyint When inserting checkbox values into a mySQL db, I'm using 1 for "Yes" or "True"... but if the checkbox is unchecked, of course, no value is passed and the field (up to this point) has been left blank or null. Should I have it do a zerofill instead of being empty or null? The mySQL fields that were left blank or null when the CFINSERT occured, appropriately left the checkboxes unchecked when I used and update page... Best procedure for handling checkbox values in mySQL? Thanks, Rick ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: mySQL & CF
Rick, I noticed you mention that you use CFINSERT. From your earlier postings I know you are on CF 4.5x, as I am. You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure, and maybe 4.0x). In threads on the subject over at the (then) Allaire forums no specific cause was ever traced. It just happens. Sometimes. In one of those threads I believe it was Paul Hastings who advised me to 'just say no to cfinsert/cfupdate' and it ranks as some of the best CF advice I ever got. On the surface those tags appear to be handy shortcuts, but they black-box your SQL, take away the otherwise granular control you should have and make debugging ... difficult. I suggest you follow the same advice -- you'll probably find out you have to, anyway. While you're at it go for the double bonus and implement cfqueryparam. Happy Monday (early) :) --- Matt Robertson, MSB Designs, Inc. http://mysecretbase.com - Retail http://foohbar.org - ColdFusion Tools --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: mySQL & CF
That's the way I do it. -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 15, 2002 8:15 PM To: CF-Talk Subject: RE: mySQL & CF Thanks, Mark. I guess the zerofill option is there to prevent a null, if desired? Rick -Original Message- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 15, 2002 8:37 PM To: CF-Talk Subject: RE: mySQL & CF Rick, You should put in a zero in my opinion. 1 = "true" and 0 = "false". Using tinyint in this way is fairly common on most DB platforms. -Mark -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 15, 2002 6:40 PM To: CF-Talk Subject: mySQL & CF Hi, all. I'm learning to use mySQL with CF instead of Access... Access had a yes/no field type...in mySQL, I'm using tinyint...is that appropriate? In Access, a value of "Yes" passed in a checkbox formfield put a 1 value in the field with a CFINSERT... Now, the value has been changed to 1 when checked, because mySQL doesn't like "Yes" for tinyint When inserting checkbox values into a mySQL db, I'm using 1 for "Yes" or "True"... but if the checkbox is unchecked, of course, no value is passed and the field (up to this point) has been left blank or null. Should I have it do a zerofill instead of being empty or null? The mySQL fields that were left blank or null when the CFINSERT occured, appropriately left the checkboxes unchecked when I used and update page... Best procedure for handling checkbox values in mySQL? Thanks, Rick ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: mySQL & CF
Thanks, Mark. I guess the zerofill option is there to prevent a null, if desired? Rick -Original Message- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 15, 2002 8:37 PM To: CF-Talk Subject: RE: mySQL & CF Rick, You should put in a zero in my opinion. 1 = "true" and 0 = "false". Using tinyint in this way is fairly common on most DB platforms. -Mark -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 15, 2002 6:40 PM To: CF-Talk Subject: mySQL & CF Hi, all. I'm learning to use mySQL with CF instead of Access... Access had a yes/no field type...in mySQL, I'm using tinyint...is that appropriate? In Access, a value of "Yes" passed in a checkbox formfield put a 1 value in the field with a CFINSERT... Now, the value has been changed to 1 when checked, because mySQL doesn't like "Yes" for tinyint When inserting checkbox values into a mySQL db, I'm using 1 for "Yes" or "True"... but if the checkbox is unchecked, of course, no value is passed and the field (up to this point) has been left blank or null. Should I have it do a zerofill instead of being empty or null? The mySQL fields that were left blank or null when the CFINSERT occured, appropriately left the checkboxes unchecked when I used and update page... Best procedure for handling checkbox values in mySQL? Thanks, Rick ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: mySQL & CF
Rick, You should put in a zero in my opinion. 1 = "true" and 0 = "false". Using tinyint in this way is fairly common on most DB platforms. -Mark -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 15, 2002 6:40 PM To: CF-Talk Subject: mySQL & CF Hi, all. I'm learning to use mySQL with CF instead of Access... Access had a yes/no field type...in mySQL, I'm using tinyint...is that appropriate? In Access, a value of "Yes" passed in a checkbox formfield put a 1 value in the field with a CFINSERT... Now, the value has been changed to 1 when checked, because mySQL doesn't like "Yes" for tinyint When inserting checkbox values into a mySQL db, I'm using 1 for "Yes" or "True"... but if the checkbox is unchecked, of course, no value is passed and the field (up to this point) has been left blank or null. Should I have it do a zerofill instead of being empty or null? The mySQL fields that were left blank or null when the CFINSERT occured, appropriately left the checkboxes unchecked when I used and update page... Best procedure for handling checkbox values in mySQL? Thanks, Rick ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: MySQL & CF
That did the trick. Thanks On Thu, 31 Oct 2002 10:11:32 -0800, Matt Robertson wrote: > This will do it for you. Watch out for any unintended line breaks that > might make it into this msg. > > Notice that its not necessary to use tickmarks on a text field. Use > cfsqltype=CF_SQL_LONGVARCHAR for memo fields. These sql types can be > used on Access, mySQL, Oracle and MS SQL without causing any trouble. > Haven't tested for a universal date type, yet. > > DATASOURCE="#request.SiteDSN#"> >UPDATE myFile >SET >myFile.myPath= value=#form.myPath#> >WHERE > myFile.IDNUM= value=#blahblah#> > > > Cheers, > > --Matt Robertson-- > MSB Designs, Inc. > http://mysecretbase.com > > > > -Original Message- > From: FlashGuy [mailto:flashmx@;rogers.com] > Sent: Thursday, October 31, 2002 10:01 AM > To: CF-Talk > Subject: Re: MySQL & CF > > > I see...OK...trying the database. Must be wrong syntax. > I'll keep trying. > > On Thu, 31 Oct 2002 11:53:38 -0600, Paul Giesenhagen wrote: > > > \ is an escape character .. .Matt Robertson gave me a good tip .. use > > for your insert and let CF escape all the correct > characters. > > > > Hope this helps. > > > > Paul Giesenhagen > > QuillDesign > > > > - Original Message - > > From: "FlashGuy" <[EMAIL PROTECTED]> > > To: "CF-Talk" <[EMAIL PROTECTED]> > > Sent: Thursday, October 31, 2002 11:46 AM > > Subject: OT: MySQL & CF > > > > > > > Hi, > > > > > > Something weird is happening. Before I switched my databases from > Access > > to MySQL my app was working OK. > > > Now when I insert data into my SQL database one of the fields is > getting > > mucked up and I don't know why? Here is an example of a entry how is > > *should* appear in the > > > database: > > > > > > Should be: > > > > > > env_var = book1 > > > destination = D:\data\book1 > > > > > > This is what getting input into my SQL database: > > > > > > env_var = book1 > > > destination = D:|ata|ook1 > > > > > > Its like the "\b" is getting interpreted into a wacko ASCII > character? > > > > > > Both fields in my table are varchar(255) types > > > > > > > > > --- > > > Colonel Nathan R. Jessop > > > Commanding Officer > > > Marine Ground Forces > > > Guatanamo Bay, Cuba > > > --- > > > > > > > > > > > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: MySQL & CF
This will do it for you. Watch out for any unintended line breaks that might make it into this msg. Notice that its not necessary to use tickmarks on a text field. Use cfsqltype=CF_SQL_LONGVARCHAR for memo fields. These sql types can be used on Access, mySQL, Oracle and MS SQL without causing any trouble. Haven't tested for a universal date type, yet. UPDATE myFile SET myFile.myPath= WHERE myFile.IDNUM= Cheers, --Matt Robertson-- MSB Designs, Inc. http://mysecretbase.com -Original Message- From: FlashGuy [mailto:flashmx@;rogers.com] Sent: Thursday, October 31, 2002 10:01 AM To: CF-Talk Subject: Re: MySQL & CF I see...OK...trying the \ is an escape character .. .Matt Robertson gave me a good tip .. use > for your insert and let CF escape all the correct characters. > > Hope this helps. > > Paul Giesenhagen > QuillDesign > > - Original Message - > From: "FlashGuy" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, October 31, 2002 11:46 AM > Subject: OT: MySQL & CF > > > > Hi, > > > > Something weird is happening. Before I switched my databases from Access > to MySQL my app was working OK. > > Now when I insert data into my SQL database one of the fields is getting > mucked up and I don't know why? Here is an example of a entry how is > *should* appear in the > > database: > > > > Should be: > > > > env_var = book1 > > destination = D:\data\book1 > > > > This is what getting input into my SQL database: > > > > env_var = book1 > > destination = D:|ata|ook1 > > > > Its like the "\b" is getting interpreted into a wacko ASCII character? > > > > Both fields in my table are varchar(255) types > > > > > > --- > > Colonel Nathan R. Jessop > > Commanding Officer > > Marine Ground Forces > > Guatanamo Bay, Cuba > > --- > > > > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: MySQL & CF
well \ is a C type escape character. So like a new line is \n or backspace is \b if you double the \ that is the escape character for \ so D:\\data\\book1 should do it. -Original Message- From: FlashGuy [mailto:flashmx@;rogers.com] Sent: Thursday, October 31, 2002 9:47 AM To: CF-Talk Subject: OT: MySQL & CF Hi, Something weird is happening. Before I switched my databases from Access to MySQL my app was working OK. Now when I insert data into my SQL database one of the fields is getting mucked up and I don't know why? Here is an example of a entry how is *should* appear in the database: Should be: env_var = book1 destination = D:\data\book1 This is what getting input into my SQL database: env_var = book1 destination = D:|ata|ook1 Its like the "\b" is getting interpreted into a wacko ASCII character? Both fields in my table are varchar(255) types --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: MySQL & CF
I see...OK...trying the \ is an escape character .. .Matt Robertson gave me a good tip .. use > for your insert and let CF escape all the correct characters. > > Hope this helps. > > Paul Giesenhagen > QuillDesign > > - Original Message - > From: "FlashGuy" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, October 31, 2002 11:46 AM > Subject: OT: MySQL & CF > > > > Hi, > > > > Something weird is happening. Before I switched my databases from Access > to MySQL my app was working OK. > > Now when I insert data into my SQL database one of the fields is getting > mucked up and I don't know why? Here is an example of a entry how is > *should* appear in the > > database: > > > > Should be: > > > > env_var = book1 > > destination = D:\data\book1 > > > > This is what getting input into my SQL database: > > > > env_var = book1 > > destination = D:|ata|ook1 > > > > Its like the "\b" is getting interpreted into a wacko ASCII character? > > > > Both fields in my table are varchar(255) types > > > > > > --- > > Colonel Nathan R. Jessop > > Commanding Officer > > Marine Ground Forces > > Guatanamo Bay, Cuba > > --- > > > > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: MySQL & CF
\ is an escape character .. .Matt Robertson gave me a good tip .. use for your insert and let CF escape all the correct characters. Hope this helps. Paul Giesenhagen QuillDesign - Original Message - From: "FlashGuy" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, October 31, 2002 11:46 AM Subject: OT: MySQL & CF > Hi, > > Something weird is happening. Before I switched my databases from Access to MySQL my app was working OK. > Now when I insert data into my SQL database one of the fields is getting mucked up and I don't know why? Here is an example of a entry how is *should* appear in the > database: > > Should be: > > env_var = book1 > destination = D:\data\book1 > > This is what getting input into my SQL database: > > env_var = book1 > destination = D:|ata|ook1 > > Its like the "\b" is getting interpreted into a wacko ASCII character? > > Both fields in my table are varchar(255) types > > > --- > Colonel Nathan R. Jessop > Commanding Officer > Marine Ground Forces > Guatanamo Bay, Cuba > --- > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: MySql & CF?
Just remember MySQL does not support cftransaction and subselects. You have to use inner joins. -Original Message- From: W Luke [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, February 06, 2001 3:47 PM To: CF-Talk Subject:MySql & CF? Hi, I've been asked to do some modifications for a site which is using MySql as it's Database, and running CF for Linux. I, locally, am running CF for Windows and have always used Access - can anyone tell me whether my Windows CF will be able to connect to the Windows version of MySql? Rgds Will ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: MySql & CF?
W Luke <[EMAIL PROTECTED]> wrote: > can anyone tell me whether my Windows > CF will be able to connect to the Windows version of MySql? Simplest thing to do is use myODBC (availabe at the mySQL site) to add the mySQL databases as ODBC datasources under Windows. -- Zac Belado email: [EMAIL PROTECTED] WWW: http://www.pixelgeek.com/ ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: MySql & CF?
yes, just download the mysql odbc drivers from www.mysql.com (myodbc) -Original Message- From: W Luke [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 06, 2001 10:47 AM To: CF-Talk Subject: MySql & CF? Hi, I've been asked to do some modifications for a site which is using MySql as it's Database, and running CF for Linux. I, locally, am running CF for Windows and have always used Access - can anyone tell me whether my Windows CF will be able to connect to the Windows version of MySql? Rgds Will ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: MySql & CF?
At 15:46 2/6/01 -, you wrote: >Hi, > >I've been asked to do some modifications for a site which is using MySql as >it's Database, and running CF for Linux. I, locally, am running CF for >Windows and have always used Access - can anyone tell me whether my Windows >CF will be able to connect to the Windows version of MySql? If they have CF running on Linux, why would they be running the database on a Windows box? We connect to a linux/Mysql database from a Windows/ColdFusion box with no problems. We are running 4.1 on that box, I think we had to install MyODBC (the MySQL driver for Windows) to do it, but 4.5.1 might come with a MySQL driver (I know CF-4.5.1-Linux does, don't know about the Win version). Ryan ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: [MySql & CF?]
yes it will "W Luke" <[EMAIL PROTECTED]> wrote: Hi, I've been asked to do some modifications for a site which is using MySql as it's Database, and running CF for Linux. I, locally, am running CF for Windows and have always used Access - can anyone tell me whether my Windows CF will be able to connect to the Windows version of MySql? Rgds Will ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: [mySQL,cf,W2K: possible?]
What you need to do is create a system DSN under your odbc control pannel with all the pertinent information. Once that is done then ColdFusion recognizes that DS as a valid one under the coldfusion administrator... --- Chris Straight Web Applications Developer University of Oregon Bookstore, inc. 895 E. 13th Ave Eugene, OR 97401 (541) 346-4331 http://www.uobookstore.com -Original Message- From: Mark Davies [mailto:[EMAIL PROTECTED]] Sent: Sunday, August 13, 2000 8:58 PM To: [EMAIL PROTECTED] Subject: RE: [mySQL,cf,W2K: possible?] thanks, I've got myodbc installed but can you tell me which ODBC driver I use? It isn't showing MYODBC -- should it? OR do I use a MS driver? Mark ___ nyc: 917-922-5279 cell nyc: 212-431-9002 work uk: 07957-138858 fax: 530-239-8059 [EMAIL PROTECTED] > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Sunday, August 13, 2000 3:39 PM > To: [EMAIL PROTECTED] > Subject: Re: [mySQL,cf,W2K: possible?] > > > yes. you need MyODBC. > > "Mark Davies" <[EMAIL PROTECTED]> wrote: > I'm trying to use mySQL instead of MSAcccess on my W2K laptop for CF > development. Is this possible? I've got everything up and running, but I > can't seem to define the datasource in the CF app server. Is it possible? > > Any help much appreciated. > > Mark > [EMAIL PROTECTED] > > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with > 'unsubscribe' in the > body. > > > > Get free email and a permanent address at http://www.netaddress.com/?N=1 > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: [mySQL,cf,W2K: possible?]
thanks, I've got myodbc installed but can you tell me which ODBC driver I use? It isn't showing MYODBC -- should it? OR do I use a MS driver? Mark ___ nyc: 917-922-5279 cell nyc: 212-431-9002 work uk: 07957-138858 fax: 530-239-8059 [EMAIL PROTECTED] > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Sunday, August 13, 2000 3:39 PM > To: [EMAIL PROTECTED] > Subject: Re: [mySQL,cf,W2K: possible?] > > > yes. you need MyODBC. > > "Mark Davies" <[EMAIL PROTECTED]> wrote: > I'm trying to use mySQL instead of MSAcccess on my W2K laptop for CF > development. Is this possible? I've got everything up and running, but I > can't seem to define the datasource in the CF app server. Is it possible? > > Any help much appreciated. > > Mark > [EMAIL PROTECTED] > > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with > 'unsubscribe' in the > body. > > > > Get free email and a permanent address at http://www.netaddress.com/?N=1 > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: [mySQL,cf,W2K: possible?]
yes. you need MyODBC. "Mark Davies" <[EMAIL PROTECTED]> wrote: I'm trying to use mySQL instead of MSAcccess on my W2K laptop for CF development. Is this possible? I've got everything up and running, but I can't seem to define the datasource in the CF app server. Is it possible? Any help much appreciated. Mark [EMAIL PROTECTED] -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. Get free email and a permanent address at http://www.netaddress.com/?N=1 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: mySQL & CF & BLOB
> Anyone using CF and mySQL and accessing BLOB data? I converted an Access2K > db to Mysql and all memo fields were converted to BLOB datatypes. Now when I > pull the information and display it I get ASCII garbage and not the text. > Someone in mySQL support told me to change it to a longvarchar datatype but > that would require reconverting the database a second time which I would > like to avoid. > > Anyone run into this problem? Is there any way around it ? Change your BLOB types to TEXT, TINYBLOB to TINYTEXT and MEDIUMBLOB to MEDIUMTEXT with ALTER TABLE sentences. That does the trick. Erki -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
