opening a server to generalized queries but not "too" far
opening a server to generalized queries but not "too" far
am 16.06.2010 21:17:01 von don-mysql8y
This seems like a topic that must have been studied, but I'm having
trouble figuring out what to search for in Google, since the usual
discussion of sql injection is not what I'm looking for here.
If anyone knows of references that discuss the issue, I'd like to
see them. I'm also interested in answers for other RDBMS's,
and I imagine that details of implementation may matter, but my
immediate primary interest is mysql used from php.
I want to allow web users to make a very wide variety of queries, but
limited to queries (no updates, redefinitions, etc), and limited to a
fixed set of tables - let's suppose one table with no joins, and
perhaps a few other restrictions.
I propose to send queries of the following form from php to the DB:
select from fixedtable
where group by order by
The user gets to supply all of the 's.
So, as an example, I want the user to be able to do
select max(col1) from fixedtable group by col2
The question is what I have to prohibit in order to prevent either
updates or access to other tables, or perhaps other things that
I should be worried about but haven't yet thought of.
So far I have identified at least one problem, which is subqueries
such as
select 1 from ... where exists (select 1 from othertable ...)
These can tell the attacker about other data he should not be able to
read. At the moment I plan to simply disallow inputs containing the
string "select" (case insensitive). Is there any way to get a select
statement to execute other statements, such as insert, delete, drop?
I believe that ";" is not a problem because a single request from php
to mysql containing multiple statements will result in a syntax error.
If I subject the inputs to mysql_real_escape_string then the user will
not be able to use quotes, which will prevent use of string constants.
What more could an attacker do if I don't escape the inputs?
Finally, suppose I want to limit access to the table to the rows
where col1=value1. If I just add that to what can an
attacker do to read other rows?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: opening a server to generalized queries but not "too" far
Sounds like you just want to GRANT access to specific tables (and with
limited commands), which is exactly what MySQL's privilege system does.
Refer to http://dev.mysql.com/doc/refman/5.1/en/grant.html
For example, you can
grant only SELECT privileges to a specific table for a specific user.
--
Adam Alkins || http://www.rasadam.com
On 16 June 2010 14:17, Don Cohen wrote:
>
> This seems like a topic that must have been studied, but I'm having
> trouble figuring out what to search for in Google, since the usual
> discussion of sql injection is not what I'm looking for here.
> If anyone knows of references that discuss the issue, I'd like to
> see them. I'm also interested in answers for other RDBMS's,
> and I imagine that details of implementation may matter, but my
> immediate primary interest is mysql used from php.
>
> I want to allow web users to make a very wide variety of queries, but
> limited to queries (no updates, redefinitions, etc), and limited to a
> fixed set of tables - let's suppose one table with no joins, and
> perhaps a few other restrictions.
>
> I propose to send queries of the following form from php to the DB:
> select from fixedtable
> where group by order by
> The user gets to supply all of the 's.
> So, as an example, I want the user to be able to do
> select max(col1) from fixedtable group by col2
>
> The question is what I have to prohibit in order to prevent either
> updates or access to other tables, or perhaps other things that
> I should be worried about but haven't yet thought of.
>
> So far I have identified at least one problem, which is subqueries
> such as
> select 1 from ... where exists (select 1 from othertable ...)
> These can tell the attacker about other data he should not be able to
> read. At the moment I plan to simply disallow inputs containing the
> string "select" (case insensitive). Is there any way to get a select
> statement to execute other statements, such as insert, delete, drop?
>
> I believe that ";" is not a problem because a single request from php
> to mysql containing multiple statements will result in a syntax error.
> If I subject the inputs to mysql_real_escape_string then the user will
> not be able to use quotes, which will prevent use of string constants.
> What more could an attacker do if I don't escape the inputs?
>
> Finally, suppose I want to limit access to the table to the rows
> where col1=value1. If I just add that to what can an
> attacker do to read other rows?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@adamalkins.com
>
>
--0016e65c70ea1fcf3304892aacd7--
Re: opening a server to generalized queries but not "too" far
am 16.06.2010 21:48:38 von don-mysql8y
Adam Alkins writes:
> Sounds like you just want to GRANT access to specific tables (and with
> limited commands), which is exactly what MySQL's privilege system does.
How about this part?
> > Finally, suppose I want to limit access to the table to the rows
> > where col1=value1. If I just add that to what can an
> > attacker do to read other rows?
The http request I have in mind will be something like
https://server.foo.com?user=john&password=wxyz&...
and the resulting query something like
select ... from table where user=john and ...
(I will first have verified the password.)
It seems I'd have to create a separate mysql user for each user in
my table, and perhaps also a separate separate table (or view?) for
that user to be allowed to read.
I suppose the php process could create the table/view, create the
user, then connect as the user to execute the query, then remove the
user.
Even if this turns out to be the best solution, I'm interested in
the answer to the original question.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: opening a server to generalized queries but not "too" far
MySQL doesn't have row level permissions, but this is what VIEWS are for. If
you only want access to specific rows, create a view with that subset of
data. You can create a function (privilege bound) to create the view to make
this more dynamic.
If you want direct access to the database, then you will need multiple MySQL
users. I don't quite get the purpose of what you're trying to do through a
PHP script; I see little reason why you can't use some client level security
to facilitate data access, especially if you're going to implement dynamic
creation of users in the PHP script itself (which would be rather weak
security imho).
--
Adam Alkins || http://www.rasadam.com
On 16 June 2010 14:48, Don Cohen wrote:
> Adam Alkins writes:
> > Sounds like you just want to GRANT access to specific tables (and with
> > limited commands), which is exactly what MySQL's privilege system does.
>
> How about this part?
> > > Finally, suppose I want to limit access to the table to the rows
> > > where col1=value1. If I just add that to what can an
> > > attacker do to read other rows?
>
> The http request I have in mind will be something like
> https://server.foo.com?user=john&password=wxyz&...
> and the resulting query something like
> select ... from table where user=john and ...
> (I will first have verified the password.)
>
> It seems I'd have to create a separate mysql user for each user in
> my table, and perhaps also a separate separate table (or view?) for
> that user to be allowed to read.
> I suppose the php process could create the table/view, create the
> user, then connect as the user to execute the query, then remove the
> user.
>
> Even if this turns out to be the best solution, I'm interested in
> the answer to the original question.
>
>
--0016364d1c4b79977404892b66b9--
RE: opening a server to generalized queries but not "too" far
am 16.06.2010 22:52:21 von Daevid Vincent
> -----Original Message-----
> From: Don Cohen [mailto:don-mysql8y@isis.cs3-inc.com]
>
> The http request I have in mind will be something like
> https://server.foo.com?user=john&password=wxyz&...
> and the resulting query something like
> select ... from table where user=john and ...
> (I will first have verified the password.)
For the love of God and all that is holy,
do NOT put the user/pass on the URL like that!!!!!!
Do something like this instead:
http://us.php.net/manual/en/features.http-auth.php
Or use "mod_auth_mysql" to maintain your 'authorized' users to your page.
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.
But also has he said, I don't see what you're trying to accomplish. If
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.
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
RE: opening a server to generalized queries but not "too" far
am 16.06.2010 23:47:39 von don-mysql8y
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?
> 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.
> 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.
> 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.
> 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.
And this seems much easier.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
RE: opening a server to generalized queries but not "too" far
am 17.06.2010 00:29:35 von mussatto
On Wed, June 16, 2010 14:47, Don Cohen wrote:
> 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?
The username and password shows up in logs on the server and in the
browser's cache since it is part of the page's "address". Anyone who has
access to either will get them. Remember, browser's cache history.
> > 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.
>
> > 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.
>
> > 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.
>
> > 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.
> And this seems much easier.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mussatto@csz.com
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
RE: opening a server to generalized queries but not "too" far
am 17.06.2010 02:54:18 von Daevid Vincent
=20
> -----Original Message-----
> From: Don Cohen [mailto:don-mysql8y@isis.cs3-inc.com]=20
> Sent: Wednesday, June 16, 2010 2:48 PM
> To: Daevid Vincent
> Cc: mysql@lists.mysql.com
> Subject: RE: opening a server to generalized queries but not "too" far
>=20
> Daevid Vincent writes:
>=20
> > For the love of God and all that is holy,=20
> > 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'=20
> 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.=20
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=20
> columns and joins you
> > want. This is also a good time to normalize the data and=20
> 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
were invented.
> > But also has he said, I don't see what you're trying to=20
> 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,=20
> then just give
> > them a read-only account to the mysql (or view) directly.=20
> Let them use
> > their own GUI tool like SQLYog or whatever -- it will be=20
> 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.
>=20
> > If you're trying to do some "reports", then just code up=20
> the reports and
> > use select boxes for the options you want someone to=20
> 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.=20
> And this seems much easier.
Oh grasshopper... So Na=EFve. :)
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=20
Daevid.
http://daevid.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
RE: opening a server to generalized queries but not "too" far
am 17.06.2010 03:19:57 von don-mysql8y
Daevid Vincent writes:
> > > For the love of God and all that is holy,=20
> > > 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.=20
> Mod_auth_mysql will serve as the user/password prompt and authentica=
tion
> between the user and the page you're creating. It is just an easy wa=
y for
> YOU to manage the users. If you have their user/password in a databa=
se --
> presumably you already do, then leverage that. That's all this apach=
e
> 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 d=
ata
> 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 =3D
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=20
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 ,,
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=EFve. :)
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...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
Re: opening a server to generalized queries but not "too" far
am 18.06.2010 00:25:27 von rajlist
In infinite wisdom don-mysql8y@isis.cs3-inc.com (Don Cohen) wrote:
> The question is what I have to prohibit in order to prevent either
> updates or access to other tables, or perhaps other things that
> I should be worried about but haven't yet thought of.
One option here might be to use "mysql proxy" as a man-in-the-middle and
filter out unwanted queries. You can find an example on how to do this
with mysql proxy on the mysql forge wiki (more stuff
http://forge.mysql.com/tools/search.php?t=tag&k=mysqlproxy)
(in case you do not know mysql proxy, see )
--
Raj Shekhar
-
If there's anything more important than my ego around, I want it
caught and shot now.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: opening a server to generalized queries but not "too" far
am 18.06.2010 18:42:45 von don-mysql8y
Raj Shekhar writes:
> One option here might be to use "mysql proxy" as a man-in-the-middle and
> filter out unwanted queries...
This seems more or less the same as what I'm doing now with php.
The same question applies there - what would you look for in your
filter?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org