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-mysq...@isis.cs3-inc.com> 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=my...@adamalkins.com > >
