> -----Original Message-----
> From: Don Cohen [mailto:don-mysql8y@stripped]
> Sent: Wednesday, June 16, 2010 2:48 PM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: RE: opening a server to generalized queries but not "too" far
> Daevid Vincent writes:
> > For the love of God and all that is holy,
> > do NOT put the user/pass on the URL like that!!!!!!
> What's so unholy (or even unwise) about it?
Oh my goodness, where to begin...
Well barring the fact that it'll be in the user's cache and browser
history, you are sending this information in plain text across the
internet, open to ANYONE on the way who wishes to packet sniff.
> > Or use "mod_auth_mysql" to maintain your 'authorized'
> users to your page.
> Why is this so much better?
> In my case it's worse cause I don't want this to be interactive.
> I want to install something on a user's machine that will access his
> data without him having to do anything. The url is to be used by
> a program, not a person on a browser.
One has nothing to do with the other.
Mod_auth_mysql will serve as the user/password prompt and authentication
between the user and the page you're creating. It is just an easy way for
YOU to manage the users. If you have their user/password in a database --
presumably you already do, then leverage that. That's all this apache
module will do.
It's no different than you having to use $_GET['user'] and
$_GET['password'] from your URL, querying the DB and granting some data
back, except you've removed the horrific thought of forcing the user to
expose their vitals in plain text in the URL/GET/POST/whatever.
> > And as Adam beat me to, use a VIEW to expose ONLY the
> columns and joins you
> > want. This is also a good time to normalize the data and
> column names so
> > that you don't expose what their internal names are.
> So far I don't like this solution.
Interesting. Perhaps you don't understand it? A view seems to suit your
needs very well from what you've explained. It allows a READ ONLY access to
your data. It prevents exposing your column/table names. It normalizes your
data so your customers get it without having to jump through hoops and
knowing intimate details about your schema. Pretty much this is why VIEWs
> > But also has he said, I don't see what you're trying to
> accomplish. If
> I'm trying to let a large number of users each access his own data
> and not everyone else's in a very flexible way, in particular,
> allowing selection using functions, aggregations, filters.
Right. And for YOU to code all that up in some fancy web GUI is going to be
a nightmare and will never be all things to all people that you desire. So
you either give people a GUI front end with select boxes and parameters, OR
you give them some direct SQL access to a VIEW, OR you give them some
XML/JSON/Excel/CVS/whatever output that they can then pull into their tool
of choice to manipulate. Why not provide daily SQL dumps of their
normalized data to your users and let them run their reports -- if they're
trying to run SQL queries themselves?
> > someone is technically literate to format SQL statements,
> then just give
> > them a read-only account to the mysql (or view) directly.
> Let them use
> > their own GUI tool like SQLYog or whatever -- it will be
> far more robust
> > than anything you can write yourself.
> In this case there may be a lot of users but the queries are likely to
> be written by a small number.
> > If you're trying to do some "reports", then just code up
> the reports and
> > use select boxes for the options you want someone to
> choose. Use jQuery and
> > table_sorter plugin and you're done.
> I can't predict what options will be needed.
Well you don't have to. You just make select boxes and such for each field
that someone would want to select on and various filters. I bet you can
cover 80% of your users with that if not all of them.
> And this seems much easier.
Oh grasshopper... So Naïve. :)
Here's what I think will happen -- and you can tell me "nah nah nah nah" in
2 years when you finish writing this uber-tool that does all this fancy SQL
magic manipulation. It will either still fall short of what you wanted it
to do b/c it's too complicated to code, OR it will be too complicated to
use and nobody will understand it enough to use it.
But hey, more power to ya Don if you want to tackle it, go ahead. I would
suggest maybe looking at http://www.phpmyadmin.net