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
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?