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

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