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.

  • This would only be a mitigation of its impact but not of its occurrence.
    – Gumbo
    May 28 '15 at 5:10
  • SQLi is more than just deleting tables. Someone could use an SQLi to get all sorts of data out of your database, and use that data for various reasons. I saw SQLi used to log-in as Admin on a web application.
    – sir_k
    May 28 '15 at 8:16

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.

  • Thanks for the answer. It helped me realize why the issue is not as simple as I thought.
    – Mike
    May 27 '15 at 22:24

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.

  • I had imagined that the person accessing the website, say to buy a widget, always ran as a guest, but the guest account would have only read-only access to the tables it needed. But your answer and LvB's has made me realize that this would not work as you need to read the username/password table to login in the first place. Also buying the widget is going to cause rows to need to be written (to record the number of widgets ordered, where to send them, etc), so this guest would need write access to some tables. Thanks for the answer Steve and LvB.
    – Mike
    May 27 '15 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.