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