Hey Ian, My first thoughts on this would be to ensure that YOU keep total control over the actually SQL that is being run, if you're working with large sets of data (more than a few GB) than the performance problems which arise from poorly written SQL could likely cause you all kinds of beef ;-) and that's just assuming that people aren't hitting you with malicious code (which they will) Not only that but also having to keep an eye on the queries people are running to ensure your indexes and statistics are configured in a manner which keeps things efficient is also quite a task.... bleh, it'd be messy I'm sure.
I'd suggest developing some form of data access API in the form of a web service or possibly with your own front end on it if that's what the use case needs which allows users access to data but means you can retain control over the actual SQL which is executed at runtime. I don't know the full details of your use case at the moment but if I was in your shoes my first thought would jump to publishing a webservice API which allows people access to the data. I think if you let users write SQL to work on your data you'll likely just end up wanted to kill yourself, genuinely! The performance and security risks involved are just so huge. Rob -----Original Message----- From: Ian Skinner [mailto:h...@ilsweb.com] Sent: 23 April 2009 15:35 To: cf-talk Subject: YIKES! I must let internet users write SQL queries for our database! Did I get your attention? Luckily these are read-only queries, but still! I have a meeting this afternoon to discuss reworking/developing an interface to allow anonymous, anybody in the world, users to develop add hock queries on a respectably large database. This database gets about 2.5 million transactions, and growing, a year and has been around since 1973. It is public data, so we need to provide public access to it. We would like it to be self service public access so the public can stop bothering us for the data and we can spend more time on fun projects :) There is currently a form based interface, but it has serious limitations and is no longer serving its role well. So this meeting is to discuss what to do about this and how much time and effort it might take. I am soliciting you all for ideas I may be overlooking on possible solutions and|or gotcha's for something like this. So fire away please. Thank You Ian ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:321855 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
