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=arch...@jab.org
