List:General Discussion« Previous MessageNext Message »
From:Adam Alkins Date:June 16 2010 7:24pm
Subject:Re: opening a server to generalized queries but not "too" far
View as plain text  
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

<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 <don-mysql8y@stripped> 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 <colspec> from fixedtable
>   where <wherespec> group by <groupspec> order by <orderspec>
> The user gets to supply all of the <spec>'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 <wherespec> 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=1
>
>

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