Re: Dynamic Renaming of uploaded file
This is to both Jason and Azad. Can I use a self generated ID for renaming the files instead of the function createuuid? TIA You don't want to use the '' inside the CFFILE tag. Try this, assuming #dest# is a variable holding a full file folder path ending with '\': cfif structKeyExists(form, upload) cfloop index=i from=1 to=#Session.numberoffields# step=1 cfset variables.filename = Session.ExtraProductImgName i / cffile action=UPLOAD destination=#dest# nameconflict=makeunique filefield=#variables.filename# / cffile action=RENAME source=#dest##cffile.serverfile# destination=#dest##createuuid()#.#cffile.serverFileExt# / /cfloop /cfif ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321564 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Question about hack
Hi Mark, I only mentioned cfexecute because of the permissions set on our specific case. Your info seems most likely. I did notice that there was a cfm file created with a call to cfexecute on the webroot so this should be a check as well. best regards Donnie On 4/13/09, Nick Gleason n.glea...@citysoft.com wrote: Donnie, Mark, Our research so far seems to support marks's analysis of this problem. There are still some unknowns here so that may change. But, changing your FTP accounts and setting your FTP server to ban IPs after a certain number of failed login attempts will prevent most brute force attempts on FTP. Our server admin didn't do that which appears to have been a mistake. Nick . -Original Message- From: Mark Kruger [mailto:mkru...@cfwebtools.com] Sent: Monday, April 13, 2009 1:14 PM To: cf-talk Subject: RE: Question about hack Donnie, I believe this is the same attack I have been helping another customer with and it does not appear to be related to CF. Instead, it appears to start with a malware install of some kind on the server (and possibly a root kit) and then progress to the creation of accounts and the changing of file permissions. Another theory gaining weight (and illustrating that we don't know much yet) is that this attack is an agent on a client computer that piggybacks onto FTP - which explains a few things but not everything. I'm guessing some combination at this point. Anyway, I agree that cfexecute is a dangerous tag that needs to be controlled, but it does not appear to be the cuprit. All of this advice is good, but the only place that CF comes into play on this particular hack happens to be the propensity to use index.cfm as the home page script. The attack targets index.* files and affects (on the server I am working with) Index.cfm, index.html and index.php etc. -Mark ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321565 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Dynamic Renaming of uploaded file
Absolutely. cfset myCustomVar = whateverABC1299X / ... cffile ... destination=#dest##myCustomVar#.#cffile.serverFileExt# / ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321566 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Server Monitoring
Good Day, Can't come up with a better title, so here is what I want to do. I have two servers, one DB and one web. My DB server is having MASSIVE issues at the moment. CPUs blowing up, and the server shutting down randomly, at night. I would like to use the webserver (with CF8) to monitor the status of the DB server, and was wondering what everyone thought was the best method? I was thinking of checking to see if the domain server (which is controlled by the DB server) was in existence, although I am not sure if that is possible with CF. The other thing I could test would be the connection to the database, but I can't seem to find the references to accessing the admin tools in CF8, where I thought that would be possible, so any resource direction would be much appreciated. Thanks, Rob ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321567 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Server Monitoring
This isn't a CF kind of way to do what you are asking, but it might be what you are looking for. We have used Nagios to monitor, log and troubleshoot servers in both of our datacenters and it can be very useful if you just can't sit in front of a machine 24/7. http://www.nagios.org/ http://nagios.sourceforge.net/docs/3_0/monitoring-windows.html Hope that helps, Dave On Apr 14, 2009, at 9:27 AM, Rob Parkhill wrote: Good Day, Can't come up with a better title, so here is what I want to do. I have two servers, one DB and one web. My DB server is having MASSIVE issues at the moment. CPUs blowing up, and the server shutting down randomly, at night. I would like to use the webserver (with CF8) to monitor the status of the DB server, and was wondering what everyone thought was the best method? I was thinking of checking to see if the domain server (which is controlled by the DB server) was in existence, although I am not sure if that is possible with CF. The other thing I could test would be the connection to the database, but I can't seem to find the references to accessing the admin tools in CF8, where I thought that would be possible, so any resource direction would be much appreciated. Thanks, Rob ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321568 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Java now available on Google App engine - how long til CFML?
How long til we get CFML on there? http://jeffgladnick.instantspot.com/blog FWIW there's been a discussion on the Open BlueDragon list about running a modified version of OpenBD on the Google App Engine for Java. http://groups.google.com/group/openbd/browse_thread/thread/a9da36320b64d5d6?hl=en This may serve as a viable option for those who want to give CF on the Google app engine a try. regards, larry ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321569 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Server Monitoring
Hi - you dono't say what db you are running - I am guessing SQL Server? I can think of a couple of ways from the CF point of view: 1.Create a cfm file that connects to an instance on your db server. E-mail the results of SELECT GETDATE() AS [CurrentDateTime] to you. Set this up as a scheduled task. 2. Use an external monitoring tool (There are thousands, one we use is host-tracker.com) to hit your test cfm file above, and skip the scheduled task and e-mail step. 3. Use a CF tool like FusionReactor or SeeFusion or a system tool like Nagios to monitor all layers of your setup. 4. Figure out your SQL issue! Maybe CF is causing your issue? FR or SF above may help in that regard. Good luck! On Tue, Apr 14, 2009 at 10:27, Rob Parkhill robert.parkh...@gmail.com wrote: Good Day, Can't come up with a better title, so here is what I want to do. I have two servers, one DB and one web. My DB server is having MASSIVE issues at the moment. CPUs blowing up, and the server shutting down randomly, at night. I would like to use the webserver (with CF8) to monitor the status of the DB server, and was wondering what everyone thought was the best method? I was thinking of checking to see if the domain server (which is controlled by the DB server) was in existence, although I am not sure if that is possible with CF. The other thing I could test would be the connection to the database, but I can't seem to find the references to accessing the admin tools in CF8, where I thought that would be possible, so any resource direction would be much appreciated. Thanks, Rob ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321570 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Server Monitoring
If your goal is to diagnose a mystery database issue that is a definite problem, I would use a database monitoring tool, such as MS SQL Profiler. Are you aware of the professional database monitoring tools, such as the ones Quest and Idera make? Where Web site monitoring of the database helps is to ensure that the Web server can connect to the database server, but that doesn't sound like the problem you are describing. What database are you running and what version of CF are you running? I ask about the CF version because CF8 has the query profiling feature built into it, although I would still favor a database query analysis tool if you know the problem is the database. -Mike Chabot On Tue, Apr 14, 2009 at 10:27 AM, Rob Parkhill robert.parkh...@gmail.com wrote: Good Day, Can't come up with a better title, so here is what I want to do. I have two servers, one DB and one web. My DB server is having MASSIVE issues at the moment. CPUs blowing up, and the server shutting down randomly, at night. I would like to use the webserver (with CF8) to monitor the status of the DB server, and was wondering what everyone thought was the best method? I was thinking of checking to see if the domain server (which is controlled by the DB server) was in existence, although I am not sure if that is possible with CF. The other thing I could test would be the connection to the database, but I can't seem to find the references to accessing the admin tools in CF8, where I thought that would be possible, so any resource direction would be much appreciated. Thanks, Rob ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321571 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Server Monitoring
Oops. You did mention that you run CF8. Have you explored the monitoring feature built into CF8 yet? -Mike Chabot On Tue, Apr 14, 2009 at 10:04 AM, Mike Chabot mcha...@gmail.com wrote: If your goal is to diagnose a mystery database issue that is a definite problem, I would use a database monitoring tool, such as MS SQL Profiler. Are you aware of the professional database monitoring tools, such as the ones Quest and Idera make? Where Web site monitoring of the database helps is to ensure that the Web server can connect to the database server, but that doesn't sound like the problem you are describing. What database are you running and what version of CF are you running? I ask about the CF version because CF8 has the query profiling feature built into it, although I would still favor a database query analysis tool if you know the problem is the database. -Mike Chabot On Tue, Apr 14, 2009 at 10:27 AM, Rob Parkhill robert.parkh...@gmail.com wrote: Good Day, Can't come up with a better title, so here is what I want to do. I have two servers, one DB and one web. My DB server is having MASSIVE issues at the moment. CPUs blowing up, and the server shutting down randomly, at night. I would like to use the webserver (with CF8) to monitor the status of the DB server, and was wondering what everyone thought was the best method? I was thinking of checking to see if the domain server (which is controlled by the DB server) was in existence, although I am not sure if that is possible with CF. The other thing I could test would be the connection to the database, but I can't seem to find the references to accessing the admin tools in CF8, where I thought that would be possible, so any resource direction would be much appreciated. Thanks, Rob ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321572 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Server Monitoring
I second Nagios. You can monitor the port required and get alerts by email or sms (if available) On 4/14/09, Mike Chabot mcha...@gmail.com wrote: Oops. You did mention that you run CF8. Have you explored the monitoring feature built into CF8 yet? -Mike Chabot On Tue, Apr 14, 2009 at 10:04 AM, Mike Chabot mcha...@gmail.com wrote: If your goal is to diagnose a mystery database issue that is a definite problem, I would use a database monitoring tool, such as MS SQL Profiler. Are you aware of the professional database monitoring tools, such as the ones Quest and Idera make? Where Web site monitoring of the database helps is to ensure that the Web server can connect to the database server, but that doesn't sound like the problem you are describing. What database are you running and what version of CF are you running? I ask about the CF version because CF8 has the query profiling feature built into it, although I would still favor a database query analysis tool if you know the problem is the database. -Mike Chabot On Tue, Apr 14, 2009 at 10:27 AM, Rob Parkhill robert.parkh...@gmail.com wrote: Good Day, Can't come up with a better title, so here is what I want to do. I have two servers, one DB and one web. My DB server is having MASSIVE issues at the moment. CPUs blowing up, and the server shutting down randomly, at night. I would like to use the webserver (with CF8) to monitor the status of the DB server, and was wondering what everyone thought was the best method? I was thinking of checking to see if the domain server (which is controlled by the DB server) was in existence, although I am not sure if that is possible with CF. The other thing I could test would be the connection to the database, but I can't seem to find the references to accessing the admin tools in CF8, where I thought that would be possible, so any resource direction would be much appreciated. Thanks, Rob ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321573 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: How to decode e-mail subject ?
Thank you Paul If anyone is interested: A bit dirty but this worked for me..: cfset codedstring==?utf-8?B?W1ZpZGVyZXNlbmR0IGZyYSBXUyBTdXBwb3J0XSBQYXlFeCDDuG5za2VyIGRl?= =?utf-8?B?ZyBnb2QgcMOlc2tl?= cfset cleanstring = cfset a=#replacenocase(codedstring, '=?utf-8?B?','', 'all')# cfloop list=#a# index=lid delimiters= cfset cleanstring='#cleanstring#' '#toString(toBinary(LID))#' /cfloop cfoutput#cleanstring#/cfoutput Helge -Original Message- From: Paul Hastings [mailto:p...@sustainablegis.com] Sent: 8. april 2009 03:15 To: cf-talk Subject: Re: How to decode e-mail subject ? WebSite CFTalk wrote: But still, how do I convert it into something readable ? cfscript y='W1ZpZGVyZXNlbmR0IGZyYSBXUyBTdXBwb3J0XSBQYXlFeCDDuG5za2VyIGRl'; x=toString(toBinary(y)); writeOutput(x); /cfscript you should get something like: [Videresendt fra WS Support] PayEx ønsker de i'll leave parsing the MIME encoded subject to you... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321574 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: coldfusion framework - i forget the name...
thank chow-lee, kinda interesting. im a framework-less kinda guy, but i have my own system i use, and its all cfc's and im interested in this one. tw On Mon, Apr 13, 2009 at 10:30 PM, Jake Pilgrim jpilg...@snapfitness.com wrote: Yes! Thanks! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321575 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: coldfusion framework - i forget the name...
I'd love to hear a writeup, or even a few sentence review of your experience with this one Jake. It looks really interesting. -Original Message- From: Ras Tafari [mailto:rastaf...@gmail.com] Sent: Tuesday, April 14, 2009 10:26 AM To: cf-talk Subject: Re: coldfusion framework - i forget the name... thank chow-lee, kinda interesting. im a framework-less kinda guy, but i have my own system i use, and its all cfc's and im interested in this one. tw On Mon, Apr 13, 2009 at 10:30 PM, Jake Pilgrim jpilg...@snapfitness.com wrote: Yes! Thanks! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321576 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Question about hack
this sounds like a good idea. when a hack is taking place would the home page not be available? is this because they are running multiple scripts which takes all the resources? can you explain this a little more as i feel like it is a good idea, and would like to understand how to implement it thanks To test if I have been hacked: I run an automated task from my home computer that requests my home page every 15 minutes. I use a URL variable that tells my home page to display a footer (which only appears when this particular url variable is present) which shows my name, address, phone, email address and a few other fields taken from my members table. I then compare what is displayed to what I know belongs there. (The only part that changes is the banner ad, which I ignore) IF the page isn't available, or if any of the text changes, I send an alert to my cell phone. When I was hacked last year, every table in my database had a javascrpt inserted into it. This will alert me if that happen again. I do this for my 3 most important web sites. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321577 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Question about hack
this sounds like a good idea. when a hack is taking place would the home page not be available? is this because they are running multiple scripts which takes all the resources? Many automated attacks deface your existing pages, or append additional content to those pages. Not all do, of course. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321578 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CFQUERY Timeout for SQL2005 Fails
Here's my test: cfquery name=test datasource=dsn timeout=1 select top 2 * from table /cfquery cfoutput#cfquery.ExecutionTime#/cfoutput I'm getting 3000+ for this value. This shouldn't be, right? Because I have the timeout parameter set for a second? The driver I'm using is the one that's supplied with CFMX7.02, Microsoft SQL Server. The only way I can make this work is if I use cfsetting requesttimeout=1 enablecfoutputonly=No, but I don't want to do that. What is going on? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321579 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Question about hack
thanks for the info this sounds like a good idea. when a hack is taking place would the home page not be available? is this because they are running multiple scripts which takes all the resources? Many automated attacks deface your existing pages, or append additional content to those pages. Not all do, of course. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321580 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
expert sql challenge
Hi we have a relational database and one task is taking far too long but we cannot see any way to improve it, although i do feel there is a way to massively improve it... so would like some expert help on this we have a normal table which has a One-to-Many table coming off of it... table 1 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1| 123456789,234567891,21342 mr client 2| 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321581 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
What DB are you using? If it's MySQL, you can use this: select client.name, group_concat(clientphone.number) as phone numbers from client left outer join clientphone on client.id = clientphone.clientId group by client.name If it's MS SQL Server you can use a subquery with FOR XML PATH and STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb On Tue, Apr 14, 2009 at 12:38 PM, Richard White rich...@j7is.co.uk wrote: Hi we have a relational database and one task is taking far too long but we cannot see any way to improve it, although i do feel there is a way to massively improve it... so would like some expert help on this we have a normal table which has a One-to-Many table coming off of it... table 1 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1 | 123456789,234567891,21342 mr client 2 | 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321582 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
The easiest way is use a JOIN to get a query that will have a phone number on each row, then use the 'group' attribute of cfoutput to display them correctly. On Tue, Apr 14, 2009 at 3:38 PM, Richard White rich...@j7is.co.uk wrote: Hi we have a relational database and one task is taking far too long but we cannot see any way to improve it, although i do feel there is a way to massively improve it... so would like some expert help on this we have a normal table which has a One-to-Many table coming off of it... table 1 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1| 123456789,234567891,21342 mr client 2| 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321583 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
Have you looked into StoredProcs? Push the load off the web server onto the SQL Server (or Oracle whichever) On Tue, Apr 14, 2009 at 3:38 PM, Richard White rich...@j7is.co.uk wrote: Hi we have a relational database and one task is taking far too long but we cannot see any way to improve it, although i do feel there is a way to massively improve it... so would like some expert help on this we have a normal table which has a One-to-Many table coming off of it... table 1 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1| 123456789,234567891,21342 mr client 2| 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321584 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
There isn't really an efficient way to get a comma separated list in one column with your DB query. However, there is a far more efficient method that uses a single query and groups the output: cfquery name=qryClients datasource=myDsn SELECT c.clientId, c.firstname, c.lastname, t.number FROM client c LEFT JOIN clientPhoneNumber t ON t.clientId = c.clientId /cfquery ... cfoutput query=qryClients group=clientId #firstname# #lastname# numbers: cfoutput#number#/cfoutput /cfoutput ... Its a shame you can't do group on the cfloop tag but its a wonderful thing that saves you in these kinds of cases. Dominic 2009/4/14 Richard White rich...@j7is.co.uk: Hi we have a relational database and one task is taking far too long but we cannot see any way to improve it, although i do feel there is a way to massively improve it... so would like some expert help on this we have a normal table which has a One-to-Many table coming off of it... table 1 is client details (one to one table), and table 2 is client telephone numbers (one to many table and has the clientid as a foreign key) we need to process a query that contains all the client details that have the telephone numbers put into one cell and separated by commas for example, this is the output query that we need client details | telephone numbers mr client 1 | 123456789,234567891,21342 mr client 2 | 583736245,828262u82 we have no idea if there is a way to ask SQL to combine the one to many telephone numbers into one cell and seperate them by commas for now we are getting all clients. then in a seperate query we are getting all telephone numbers. we then add a column to the clients query. then we run an outer loop to loop through all clients, and an inner loop that runs through all the telephone numbers, and appending the telephone number to the client if the client ids in both queries match. this seems a very long way around it but are not sure if there is a better way we would appreciate any suggestions to improve this thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321585 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
thanks for all your replies. barney, yes we are using mysql and didnt even know about the qroup_concat. it works like a dream and where this task was taking 23 seconds to complete it is now taking a matter of milliseconds :) fantastic and thanks once again for all your replies :) What DB are you using? If it's MySQL, you can use this: select client.name, group_concat(clientphone.number) as phone numbers from client left outer join clientphone on client.id = clientphone.clientId group by client.name If it's MS SQL Server you can use a subquery with FOR XML PATH and STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321586 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: expert sql challenge
Nice. So group_concat works sort of like ColdFusion's valuelist method. Very nice indeed! -Original Message- From: Richard White [mailto:rich...@j7is.co.uk] Sent: Tuesday, April 14, 2009 2:59 PM To: cf-talk Subject: Re: expert sql challenge thanks for all your replies. barney, yes we are using mysql and didnt even know about the qroup_concat. it works like a dream and where this task was taking 23 seconds to complete it is now taking a matter of milliseconds :) fantastic and thanks once again for all your replies :) What DB are you using? If it's MySQL, you can use this: select client.name, group_concat(clientphone.number) as phone numbers from client left outer join clientphone on client.id = clientphone.clientId group by client.name If it's MS SQL Server you can use a subquery with FOR XML PATH and STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321587 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
cf.Objective is Giving Away (2)Two CF 8 Standard Licenses to New Registrants
cf.Objective is Giving Away (2)Two CF 8 Standard Licenses to New Registrants If you register* between April 13 to the start of the conference you will be entered to win one of two licenses that are being given away only for these last minute registrants. Jason Delmore has donated these licenses to cf.Objective. All attendees are still in the drawing for the 5 licenses that Adobe donated for a combination of CF Standard and Flex Builder Pro. *Only applies to new paid registrations. Click https://www.bestmeetings.com/registration/cfobjective/cfobjective.cfm Here to Register for the Only Enterprise Level ColdFusion Conference! Hotel Extends Group Room Rate to Monday April 20! The Hotel has extended the date to Monday April 20th that you can get the cf.Objective group room rate of $164 per night. This includes free internet access in your room. This also includes the Government rate of $130. http://minneapolis.hyatt.com/groupbooking/msprm5cfo2009 Click here to reserve your hotel room For government rate call 1-800-233-1234 Jim Louis Best Meetings Inc. cf.Objective() 2009 May 14-16, 2009, Minneapolis, MN The world's ONLY enterprise engineering conference for ColdFusion www.cfobjective.com http://www.cfobjective.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321588 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
Is there a MSSQL version of that puppy? On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com wrote: Nice. So group_concat works sort of like ColdFusion's valuelist method. Very nice indeed! -Original Message- From: Richard White [mailto:rich...@j7is.co.uk] Sent: Tuesday, April 14, 2009 2:59 PM To: cf-talk Subject: Re: expert sql challenge thanks for all your replies. barney, yes we are using mysql and didnt even know about the qroup_concat. it works like a dream and where this task was taking 23 seconds to complete it is now taking a matter of milliseconds :) fantastic and thanks once again for all your replies :) What DB are you using? If it's MySQL, you can use this: select client.name, group_concat(clientphone.number) as phone numbers from client left outer join clientphone on client.id = clientphone.clientId group by client.name If it's MS SQL Server you can use a subquery with FOR XML PATH and STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321589 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQUERY Timeout for SQL2005 Fails
Actually, I was wrong -- even cfsetting requesttimeout=1 enablecfoutputonly=No doesn't work, because it still waits for the cfquery to finish before it outputs the timeout error. Is there any solution for this? The database we're relying on is unstable and we need to be able to kill the query sooner. Thanks, - Sung Here's my test: cfquery name=test datasource=dsn timeout=1 select top 2 * from table /cfquery cfoutput#cfquery.ExecutionTime#/cfoutput I'm getting 3000+ for this value. This shouldn't be, right? Because I have the timeout parameter set for a second? The driver I'm using is the one that's supplied with CFMX7.02, Microsoft SQL Server. The only way I can make this work is if I use cfsetting requesttimeout=1 enablecfoutputonly=No, but I don't want to do that. What is going on? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321590 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
how about adding WITH (NOLOCK) If your queries are just selecting data, i'd use WITH (NOLOCK) that way you don't need to wait for transactions to finish if that table gets heavy inserts cfquery name=test datasource=dsn timeout=1 select top 2 * WITH (NOLOCK) from table /cfquery On Tue, Apr 14, 2009 at 4:58 PM, Sung Woo s...@cornell.edu wrote: Actually, I was wrong -- even cfsetting requesttimeout=1 enablecfoutputonly=No doesn't work, because it still waits for the cfquery to finish before it outputs the timeout error. Is there any solution for this? The database we're relying on is unstable and we need to be able to kill the query sooner. Thanks, - Sung ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321591 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: expert sql challenge
Here is an Oracle UDF (for lack of a better description) that I just found. create or replace function join ( p_cursor sys_refcursor, p_del varchar2 := ',' ) return varchar2 is l_value varchar2(32767); l_result varchar2(32767); begin loop fetch p_cursor into l_value; exit when p_cursor%notfound; if l_result is not null then l_result := l_result || p_del; end if; l_result := l_result || l_value; end loop; return l_result; end join; Syntax: join(cursor(select name form users)). If you eant to change the default delim (which is a comma) you would do this: join(cursor(select name form users), '|') On Tue, Apr 14, 2009 at 3:31 PM, C. Hatton Humphrey chumph...@gmail.comwrote: Is there a MSSQL version of that puppy? On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com wrote: Nice. So group_concat works sort of like ColdFusion's valuelist method. Very nice indeed! -Original Message- From: Richard White [mailto:rich...@j7is.co.uk] Sent: Tuesday, April 14, 2009 2:59 PM To: cf-talk Subject: Re: expert sql challenge thanks for all your replies. barney, yes we are using mysql and didnt even know about the qroup_concat. it works like a dream and where this task was taking 23 seconds to complete it is now taking a matter of milliseconds :) fantastic and thanks once again for all your replies :) What DB are you using? If it's MySQL, you can use this: select client.name, group_concat(clientphone.number) as phone numbers from client left outer join clientphone on client.id = clientphone.clientId group by client.name If it's MS SQL Server you can use a subquery with FOR XML PATH and STUFF to approximate the same functionality. Don't know about other platforms. At the very least, pull a single recordset with the same JOIN as above, but no GROUP BY, and then you can do the rollup in a single CFOUTPUT loop. That'll save you a lot of trips to the DB, and therefor a lot of wasted time. cheers, barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321592 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method. Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to complete
it is now taking a matter of milliseconds :)
fantastic and thanks once again for all your replies :)
What DB are you using? If it's MySQL, you can use this:
select client.name, group_concat(clientphone.number) as phone numbers
from client
left outer join clientphone on client.id = clientphone.clientId group
by client.name
If it's MS SQL Server you can use a subquery with FOR XML PATH and
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321593
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
Hi Casey, What happened today is that the SQL server basically locked up. CF kept waiting for the request to come back, but it never did because it just hung infinititely. It seems as if CF waits forever in a case like this, so it's vital that I can kill the connection from the CFQUERY. The real query I run returns usually within milliseconds, about 75ms at most. So this is something that is out of the ordinary, but I want to prevent it from happening in the future. Is there a way? Thanks. how about adding WITH (NOLOCK) If your queries are just selecting data, i'd use WITH (NOLOCK) that way you don't need to wait for transactions to finish if that table gets heavy inserts cfquery name=test datasource=dsn timeout=1 select top 2 * WITH (NOLOCK) from table /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321594 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Question about hack
Mark, I can confirm that there has been FTP related 'sploits going around. I received a message from a hosting company warning that: There is a potential security exploit within the FTP software that we use on your account. Just a 411 G! On Mon, Apr 13, 2009 at 1:13 PM, Mark Kruger mkru...@cfwebtools.com wrote: Donnie, I believe this is the same attack I have been helping another customer with and it does not appear to be related to CF. Instead, it appears to start with a malware install of some kind on the server (and possibly a root kit) and then progress to the creation of accounts and the changing of file permissions. Another theory gaining weight (and illustrating that we don't know much yet) is that this attack is an agent on a client computer that piggybacks onto FTP - which explains a few things but not everything. I'm guessing some combination at this point. Anyway, I agree that cfexecute is a dangerous tag that needs to be controlled, but it does not appear to be the cuprit. All of this advice is good, but the only place that CF comes into play on this particular hack happens to be the propensity to use index.cfm as the home page script. The attack targets index.* files and affects (on the server I am working with) Index.cfm, index.html and index.php etc. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Donnie Bachan (Gmail) [mailto:donnie.bac...@gmail.com] Sent: Monday, April 13, 2009 8:30 AM To: cf-talk Subject: Re: Question about hack Hi Nick, I know this post is a bit late but to your original question, that attack is as a result of incorrect file/iis permissions and is not an XSS attack. I would even bet that you are on a shared server (at HMS) since one of my client sites had this exact same problem. The attacker would have gained access to the file system (possibly via FTP) and executed code that injected the code into all index.* files on the server (not just your hosting account). We have had a lot of problems trying to get this sorted out. It appears that the issue was with security related to the windows script host and/or CFEXECUTE. The only thing you can do to prevent this is work with your hosting provider to secure the system or move to a VPS or dedicated account and make sure your FTP accounts are secure. HTH Donnie Bachan Nitendo Vinces - By Striving You Shall Conquer == The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. On Mon, Apr 13, 2009 at 1:30 PM, Richard White rich...@j7is.co.uk wrote: hi dave, i have scripts that write to the file system as well. what would i need to do to secure them, do you have a link that i could read in relation to this as i am a little lost as to what to do thanks We are having to scrub our files to remove the injected code (which is being written directly to the files as the result of the hack allowing FULL CONTROL for the Everyone user on the machine. Have you determined a solution for removing/preventing this? First, audit your code to find any scripts that can write to the filesystem. Second, audit your code to find any scripts that pass unfiltered user input to the database. Third, fix that code. Fourth, configure filesystem permissions properly to prevent CF or your database from writing to the web server's webroot. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321595 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: expert sql challenge
Barney, that TSQL is brilliant. I've never used the Stuff function and
had only used FOR XML PATH when generating xml. I had to go look at
the books online and sure enough they have an example turning results
into a value list in the same way that you are although they do the
select as data(). I'm not familiar with the data() function and why
they would use it but your query doesn't Would you mind elaborating?
And for those interested in what I'm talking about his Barney's use of
FOR XML PATH, the msdn reference is here:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
Thanks,
Judah
On Tue, Apr 14, 2009 at 1:20 PM, Barney Boisvert bboisv...@gmail.com wrote:
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method. Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to complete
it is now taking a matter of milliseconds :)
fantastic and thanks once again for all your replies :)
What DB are you using? If it's MySQL, you can use this:
select client.name, group_concat(clientphone.number) as phone numbers
from client
left outer join clientphone on client.id = clientphone.clientId group
by client.name
If it's MS SQL Server you can use a subquery with FOR XML PATH and
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321596
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
SOT: data catalogs
Hey All, WowLONG time no post! ;-) We've got a large client with MANY (over 100) data sources (including spreadsheets on PCsyou know the story). They want to have a central catalog of what data can be found where. Of course it's not just basic details like payroll data can be accessed in the xyz database), but rather a more in depth description of that data, what it may or may not be good for, comments on quality or aggregation, and so on. I've done some preliminary searching for business data catalog (as well as business data catalogue for us Canucks on the list). The results are greatly infested with a Microsoft tool that ties in with SharePoint (and I will read up on it). Does anyone have any other ideas? suggested search terms? know of such an application? Of course it may be that we simply have to build the application to meet their requirements, but I like to see what else is out there. BTWopen source is fine by me. TIA 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: br...@electricedgesystems.com web: www.electricedgesystems.com Notice: This message, including any attachments, is confidential and may contain information that is privileged or exempt from disclosure. It is intended only for the person to whom it is addressed unless expressly authorized otherwise by the sender. If you are not an authorized recipient, please notify the sender immediately and permanently destroy all copies of this message and attachments. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321597 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Question about hack
Thanks... I'll add that to my list. I have a pretty hefty blog post coming out on this tomorrow (or hopefully tomorrow :). -mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Gerald Guido [mailto:gerald.gu...@gmail.com] Sent: Tuesday, April 14, 2009 4:08 PM To: cf-talk Subject: Re: Question about hack Mark, I can confirm that there has been FTP related 'sploits going around. I received a message from a hosting company warning that: There is a potential security exploit within the FTP software that we use on your account. Just a 411 G! On Mon, Apr 13, 2009 at 1:13 PM, Mark Kruger mkru...@cfwebtools.com wrote: Donnie, I believe this is the same attack I have been helping another customer with and it does not appear to be related to CF. Instead, it appears to start with a malware install of some kind on the server (and possibly a root kit) and then progress to the creation of accounts and the changing of file permissions. Another theory gaining weight (and illustrating that we don't know much yet) is that this attack is an agent on a client computer that piggybacks onto FTP - which explains a few things but not everything. I'm guessing some combination at this point. Anyway, I agree that cfexecute is a dangerous tag that needs to be controlled, but it does not appear to be the cuprit. All of this advice is good, but the only place that CF comes into play on this particular hack happens to be the propensity to use index.cfm as the home page script. The attack targets index.* files and affects (on the server I am working with) Index.cfm, index.html and index.php etc. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Donnie Bachan (Gmail) [mailto:donnie.bac...@gmail.com] Sent: Monday, April 13, 2009 8:30 AM To: cf-talk Subject: Re: Question about hack Hi Nick, I know this post is a bit late but to your original question, that attack is as a result of incorrect file/iis permissions and is not an XSS attack. I would even bet that you are on a shared server (at HMS) since one of my client sites had this exact same problem. The attacker would have gained access to the file system (possibly via FTP) and executed code that injected the code into all index.* files on the server (not just your hosting account). We have had a lot of problems trying to get this sorted out. It appears that the issue was with security related to the windows script host and/or CFEXECUTE. The only thing you can do to prevent this is work with your hosting provider to secure the system or move to a VPS or dedicated account and make sure your FTP accounts are secure. HTH Donnie Bachan Nitendo Vinces - By Striving You Shall Conquer == The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. On Mon, Apr 13, 2009 at 1:30 PM, Richard White rich...@j7is.co.uk wrote: hi dave, i have scripts that write to the file system as well. what would i need to do to secure them, do you have a link that i could read in relation to this as i am a little lost as to what to do thanks We are having to scrub our files to remove the injected code (which is being written directly to the files as the result of the hack allowing FULL CONTROL for the Everyone user on the machine. Have you determined a solution for removing/preventing this? First, audit your code to find any scripts that can write to the filesystem. Second, audit your code to find any scripts that pass unfiltered user input to the database. Third, fix that code. Fourth, configure filesystem permissions properly to prevent CF or your database from writing to the web server's webroot. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive:
Re: expert sql challenge
I don't know about the data function. I try to avoid SQL Server when
I can. ;) We use it for some projects with MySQL's query language
wasn't rich enough to express certain types of queries, but in general
I use MySQL when possible.
Did they have an example of a query equivalent to mine that uses the
data function? Because it seems like that'd probably be more
efficient than going through an XML process.
cheers,
barneyb
On Tue, Apr 14, 2009 at 3:13 PM, Judah McAuley ju...@wiredotter.com wrote:
Barney, that TSQL is brilliant. I've never used the Stuff function and
had only used FOR XML PATH when generating xml. I had to go look at
the books online and sure enough they have an example turning results
into a value list in the same way that you are although they do the
select as data(). I'm not familiar with the data() function and why
they would use it but your query doesn't Would you mind elaborating?
And for those interested in what I'm talking about his Barney's use of
FOR XML PATH, the msdn reference is here:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
Thanks,
Judah
On Tue, Apr 14, 2009 at 1:20 PM, Barney Boisvert bboisv...@gmail.com wrote:
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method.
Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to complete
it is now taking a matter of milliseconds :)
fantastic and thanks once again for all your replies :)
What DB are you using? If it's MySQL, you can use this:
select client.name, group_concat(clientphone.number) as phone numbers
from client
left outer join clientphone on client.id = clientphone.clientId group
by client.name
If it's MS SQL Server you can use a subquery with FOR XML PATH and
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321599
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: expert sql challenge
On Tue, Apr 14, 2009 at 3:47 PM, Barney Boisvert bboisv...@gmail.com wrote:
Did they have an example of a query equivalent to mine that uses the
data function? Because it seems like that'd probably be more
efficient than going through an XML process.
MSDN actually used almost exactly what you wrote. If you go to the MSDN page:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
And look down for C. Generating a value list using PATH mode it
shows their example.
The only real difference is that they were using AS data() which I
am not familiar with. It might be because the result returned from the
subquery in their example is being put into a tsql variable and then
used in another xml path query.
Here is the subquery in their example:
(SELECT ProductID as data()
FROM Production.Product
WHERE Production.Product.ProductModelID =
Production.ProductModel.ProductModelID
FOR XML PATH ('')) as @ProductIDs
Cheers
Judah
On Tue, Apr 14, 2009 at 3:13 PM, Judah McAuley ju...@wiredotter.com wrote:
Barney, that TSQL is brilliant. I've never used the Stuff function and
had only used FOR XML PATH when generating xml. I had to go look at
the books online and sure enough they have an example turning results
into a value list in the same way that you are although they do the
select as data(). I'm not familiar with the data() function and why
they would use it but your query doesn't Would you mind elaborating?
And for those interested in what I'm talking about his Barney's use of
FOR XML PATH, the msdn reference is here:
http://msdn.microsoft.com/en-us/library/ms189885(SQL.90).aspx
Thanks,
Judah
On Tue, Apr 14, 2009 at 1:20 PM, Barney Boisvert bboisv...@gmail.com wrote:
Here you go. Note that they're not really semantically equivalent,
they just happen to return the same results. The approach for
obtaining the result is completely different, and the MS SQL one,
while less direct for the actual comma separate list creation, is far
more expressive for what you're actually trying to accomplish (i.e.
uses a correlated subquery instead of a grouped join).
MySQL:
select name, group_concat(phone) as phones
from client
inner join phone on client.id = phone.clientId
group by name
MS SQL Server:
select name,
stuff((
select distinct ',' + phone
from phone
where clientId = client.id
for xml path ('')
), 1, 1, '') as phones
from client
cheers,
barneyb
On Tue, Apr 14, 2009 at 12:31 PM, C. Hatton Humphrey
chumph...@gmail.com wrote:
Is there a MSSQL version of that puppy?
On Tue, Apr 14, 2009 at 3:19 PM, Andy Matthews li...@commadelimited.com
wrote:
Nice. So group_concat works sort of like ColdFusion's valuelist method.
Very
nice indeed!
-Original Message-
From: Richard White [mailto:rich...@j7is.co.uk]
Sent: Tuesday, April 14, 2009 2:59 PM
To: cf-talk
Subject: Re: expert sql challenge
thanks for all your replies.
barney, yes we are using mysql and didnt even know about the qroup_concat.
it works like a dream and where this task was taking 23 seconds to
complete
it is now taking a matter of milliseconds :)
fantastic and thanks once again for all your replies :)
What DB are you using? If it's MySQL, you can use this:
select client.name, group_concat(clientphone.number) as phone numbers
from client
left outer join clientphone on client.id = clientphone.clientId group
by client.name
If it's MS SQL Server you can use a subquery with FOR XML PATH and
STUFF to approximate the same functionality. Don't know about other
platforms.
At the very least, pull a single recordset with the same JOIN as above,
but no GROUP BY, and then you can do the rollup in a single CFOUTPUT
loop. That'll save you a lot of trips to the DB, and therefor a lot of
wasted time.
cheers,
barneyb
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321600
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Question about hack
No - if you are hacked, the home page is available, but it includes a javascript that does bad things to the visitors. The most common way is a sql injection attack, where they insert the javascript into some fields in the database, (in my case, they appended the javascript to all vchar fields in every table) so when you display information on the website from the database, you inadvertently are also adding that javascript to the page. The recent attack that is being talked about has the attacker editing the index.cfm page and directly adding javascript to it. In both types of attacks, the home page is available and you might not notice anything just by looking at it. So my idea to detect it is to set up a cfhttp call to the index.cfm page. I add a url parameter that signifies that the page should also display my own personal information from one of the tables. I do this because I know I won't change the information in the table, and if it does change, there was a problem. So the first time I do the cfhttp call, I save the page, then all subsequent calls get compared to it. If it changes, or is not available, I send an alert to my cell phone. I do this as an automated task from a different server so I can test if the website is up also. One problem I had was my banner ad changes.. so I put a comment around the banner ad that says start banner end banner, and snip that section out before comparing it. At 12:34 PM 4/14/2009, you wrote: this sounds like a good idea. when a hack is taking place would the home page not be available? is this because they are running multiple scripts which takes all the resources? can you explain this a little more as i feel like it is a good idea, and would like to understand how to implement it thanks To test if I have been hacked: I run an automated task from my home computer that requests my home page every 15 minutes. I use a URL variable that tells my home page to display a footer (which only appears when this particular url variable is present) which shows my name, address, phone, email address and a few other fields taken from my members table. I then compare what is displayed to what I know belongs there. (The only part that changes is the banner ad, which I ignore) IF the page isn't available, or if any of the text changes, I send an alert to my cell phone. When I was hacked last year, every table in my database had a javascrpt inserted into it. This will alert me if that happen again. I do this for my 3 most important web sites. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321601 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Question about hack
thanks for the info, at least we know what to look for now. we will also try to setup something similar, thanks again No - if you are hacked, the home page is available, but it includes a javascript that does bad things to the visitors. The most common way is a sql injection attack, where they insert the javascript into some fields in the database, (in my case, they appended the javascript to all vchar fields in every table) so when you display information on the website from the database, you inadvertently are also adding that javascript to the page. The recent attack that is being talked about has the attacker editing the index.cfm page and directly adding javascript to it. In both types of attacks, the home page is available and you might not notice anything just by looking at it. So my idea to detect it is to set up a cfhttp call to the index.cfm page. I add a url parameter that signifies that the page should also display my own personal information from one of the tables. I do this because I know I won't change the information in the table, and if it does change, there was a problem. So the first time I do the cfhttp call, I save the page, then all subsequent calls get compared to it. If it changes, or is not available, I send an alert to my cell phone. I do this as an automated task from a different server so I can test if the website is up also. One problem I had was my banner ad changes.. so I put a comment around the banner ad that says start banner end banner, and snip that section out before comparing it. At 12:34 PM 4/14/2009, you wrote: ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321602 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
