SQL injection attacks work by the attacker putting valid SQL into the text that goes back from the page to the server; something like "Delete * from tblAccounts where 1=1" What I cannot understand is why this cannot be defeated by giving the account that a web user's post runs as read-only permissions. Not only that but read-only on only the tables you want him to read, and no access at all to any other tables. In the past one could give very granular permissions in databases, down to row level.
An SQL injection adds a new SQL command to the command the website uses to retrieve / update / add data to the Database. Since nearly all websites that use a database need to have some tables to write in the attacker can always add data there.
More importantly, most of the time the database holds information not available to all users of the website (like for example the user table with usernames email addresses and passwords (-hashes). a table the website itself uses (so needs access)
When security in depth is applied you do lock the database user the website uses for access to only those the website needs access to (as limited as needed) This does not eliminate the risk of an injection, just limits how far it can spread. but all tables available to the website are available to a injection user when SQL injection is not mitigated.
Your solution would only work in a very narrow set of circumstances. It's rare that the database user and the person viewing your website have exactly the same set of permissions, which is the scenario you're imagining.
Imagine this scenario:
select passwordhash from usernamePasswordtable where username=$username;
Even if you could limit this to a DB user that ONLY does logins, AND your database allows limiting access down to the row level (I've generally not seen this), you still have to open up access to the login user to all active users in the usernamePassword table. A SQL injection attack could easily allow an attacker to login as a different user, given they could put whatever they like into $username.
There's several other problems as well, the least of which is having to have a database user for each and every user of your website.
The only scenario where this might work is if your website is entirely public and everything in your database is already published on your website. Then running read-only might work.