I've inherited a php web app that is very insecure, with a history of sql injection. I can't fix the scripts immediately, I rather need them to be running to have the website running, and there are too many php scripts to deal with from the php end first. I do, however, have full control over the server and the software on the server, including full control over the mysql database and it's users.
Let's estimate it at something like 300 scripts overall, 40 semi-private scripts, and 20 private/secure scripts.
So my question is how best to go about securing the data, with the implicit assumption that sql injection from the php side (e.g. somewhere in that list of 300 scripts) is inevitable?
My first-draft plan is to create multiple tiers of different permissioned users in the mysql database. In this way I can secure the data & scripts in most need of securing first ("private/secure" category), then the second tier of database tables & scripts ("semi-private"), and finally deal with the security of the rest of the php app overall (with the result of finally securing the database tables that essentially deal with "public" information, e.g. stuff that even just viewing the homepage requires).
So, 3 database users (public, semi-private, and secure), with a different user connecting for each of three different groups of scripts (the secure scripts, the semi-private scripts, and the public scripts). In this way, I can prevent all access to "secure" from "public" or from "semi-private", and to "semi-private" from "public". Are there other alternatives that I should look into? If a tiered access system is the way to go, what approaches are best?
The simple answer is "You can't really secure against SQL injection at the database level". Once they're connected to the database with a query in hand your DB is going to execute it -- if someone has injected nastiness into that SQL the best you can hope to do is mitigate the damage they can do.
In terms of damage mitigation what you already described is a great approach: restrict each script to using a database user account that is limited to the minimum access that script requires (Select, Insert, Update, Delete & only to the specific subset of tables that script must touch).
This doesn't secure your database from SQL injection -- It just limits the amount of data someone can steal (or destroy) based on the script they compromised. A determined attacker will probably attack the "interesting" scripts first, which have the juicy data they want in the first place.
In terms of unforeseen problems, the separation into user roles may provide only a minimal security gain (if you have lots of complex cross-table queries and scripts that do multiple things) at the risk of sever breakage (especially if you have lots of complex cross-table queries of scripts that do multiple things).
It's worth noting that auditing your database at this level will probably take as much time (if not more) than replacing all your current calls with proper parameterized queries that make SQL injection attacks either difficult or impossible.
I would recommend you look at PHP-IDS http://phpids.org/ . Its not going to be a substitute for securing the scripts, and it won't prevent all attacks but it is a really fantastic bandaid if you must apply a security bandaid. It will frustrate convenience hackers and get them to move on to the next target. It won't stop anyone determined.
It will make a best effort guess at :
Combined with mod_security it will help immensely.
Your strategy of partitioning is 100% correct. Think beyond the SQL Injections though because if they are there there is likely other classes of attacks. Privilege elevations for instance or session hijacking? You may find partitioning is harder than first imagined depending on the nature of how the site is designed.
Ultimately biting the bullet and just re-coding it is what needs to happen.
mod_security for apache or an IDS/IPS hardware firewall modules will do analysis of requests, blocking potential SQL injections. But I'm sure other commenters will explain the risks in details.
You need GreenSQL. I've never used it myself - in a production environment, anyway, but it acts as a query for MySQL and filters out dangerous queries that indicate SQL injection.
You can (if you have the talent, time, or resources) write a database proxy that sits between the database and PHP scripts, and filters out questionable SQL queries. If all or most of the PHP scripts are calling the same database API libraries, then I'd recommend implementing the filters there. Be sure to create patch files (using diff) so if your PHP DB API gets updated you can reapply your changes.
You could use Mysql Proxy to verify/modify all requests on the fly. It might be hard to prevent data mining of the D, byt you could at least remove all DROP for example.