List:General Discussion« Previous MessageNext Message »
From:Don Cohen Date:June 17 2010 1:19am
Subject:RE: opening a server to generalized queries but not "too" far
View as plain text  
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
It won't because this url is not intended to be used in the browser
but in an application.
 > history, you are sending this information in plain text across the
 > internet, open to ANYONE on the way who wishes to packet sniff.
You might notice that the url started with https so this is also not
true. 

 > 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.
The user prompt relies on a browser, right?
This url is not being used by a browser.

 > 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.
No prompt in a browser that's not being used.
No plain text if I use https.

 > 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 were invented.
You want me to create a view for every user, right?
Or else you want me to create a user and a view every time a user
accesses the server and then delete them?
Either way seems ridiculously complicated compared to adding
 where user = <input>
The user only has to know about the columns I'm advertising, but
if he wants to look at (or look for) others I don't care.
How does the view simplify matters for him?  He still has to write
pretty much the same query.

 > 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
The advantage of the direct sql over xml is that they have access to
things like select max(col), where conditions, etc. so they can 
get back much less data and the results they want rather than having
to get back much more and then still have to compute the results
they want.
So far I don't see that my query allowing <colspec>,<wherespec>,
etc. is worse in any way than any of the other suggestions, and
I see ways in which it's better than all of them.

So far

 > 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?
First, why do you assume these are daily reports?
They are not.  This is an application that displays data that may
be changing much faster than that.  The user runs it, perhaps selects
the data/display he wants to see, then gets the data for that display
up to the minute.  Then if he wishes, he makes another choice.
If he wants to see a small summary of a large amount of data, this
is what databases are for.  I want the max or average to be computed
in the database.  I don't want him to retrieve the entire database
and then compute that at the client.

 > 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.
You want to tell me what select boxes to supply?
There are
- lots of different where conditions that might be used
- lots of FUNCTIONS of the data to apply
  (which might be inputs to aggregations and order by's)

 > > And this seems much easier.

 > Oh grasshopper... So Naïve. :)
Well then perhaps you should supply either some actual technical
arguments that show me what's wrong with what I propose, or some
references that contain those arguments.

 > 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
The sql manipulation is to be done by mysql - so I'm now finished
writing that part.

 > 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.
If what I have described is too complicated to use then so is mysql.
I expect most end users will run what I give them.
When they want something else, if they can't do it themselves.
I'll write the sql (or you can write it) for them.
Better than having to write the corresponding code.

I'm still hoping someone will address the original question...
Thread
opening a server to generalized queries but not "too" far(Don Cohen)16 Jun
  • Re: opening a server to generalized queries but not "too" farAdam Alkins16 Jun
    • Re: opening a server to generalized queries but not "too" far(Don Cohen)16 Jun
      • Re: opening a server to generalized queries but not "too" farAdam Alkins16 Jun
      • RE: opening a server to generalized queries but not "too" farDaevid Vincent16 Jun
        • RE: opening a server to generalized queries but not "too" far(Don Cohen)16 Jun
          • RE: opening a server to generalized queries but not "too" farWm Mussatto17 Jun
          • RE: opening a server to generalized queries but not "too" farDaevid Vincent17 Jun
            • RE: opening a server to generalized queries but not "too" far(Don Cohen)17 Jun
  • Re: opening a server to generalized queries but not "too" farRaj Shekhar18 Jun
    • Re: opening a server to generalized queries but not "too" far(Don Cohen)18 Jun