2

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.

5
  • This would only be a mitigation of its impact but not of its occurrence.
    – Gumbo
    Commented May 28, 2015 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
    Commented May 28, 2015 at 8:16
  • Get all sorts of what data? What data? What data are you possibly going to get access to, where the database prohibits you having that access? How are you going to read from a table for which your database role does not have select permissions? This makes absolutely no sense. Commented Aug 24, 2023 at 1:19
  • @DavidVentimiglia, How would you use the database if the user had no access to it? What would be the point of the database ? Commented Aug 24, 2023 at 12:48
  • Who ever said the user doesn't have access to the database? Certainly not me. I said the user might not have permissions on some tables. That doesn't mean they don't have access to other tables. Commented Aug 24, 2023 at 14:14

4 Answers 4

4

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.

13
  • Thanks for the answer. It helped me realize why the issue is not as simple as I thought.
    – Mike
    Commented May 27, 2015 at 22:24
  • 1
    @DavidVentimiglia Tables literally have nothing to do with SQL injection. And yes, a injection adds a new “sql command”. You know, things like “SELECT”,”UPDATE”,”DELETE”, etc. You seems to not understand what SQL or a database is.
    – LvB
    Commented Aug 24, 2023 at 7:21
  • 1
    you seem to fundamentally not understand SQL... SQL or Structured Query Language is a standardised way to make queries to retrieve data. it is often used to query RDBMS for their contents or to modify their contents. It does this by chaining 1 or more commands together into what's called a Query, and this query consists out of 1 or more Commands. it does not in any way require tables, or even a schema. all it needs is a working interpreter (such as a DBMS). And there have been SQLI attacks done on databases that had all their storage removed...
    – LvB
    Commented Aug 24, 2023 at 15:44
  • 1
    @DavidVentimiglia the mistake is with you, not the article: “Structured Query Language (SQL) … is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables. ” nothing about that says tables. It can be used on tables, but originally it was used for systems that did not even have tables.
    – LvB
    Commented Aug 25, 2023 at 6:20
  • 1
    “A model that tracks relationships in structured data.” That can be done in tables, but struct work equally well. Or even punchcards. And here is a tip, if SQL requires tables, why is this not an error: “SELECT 12 + 13;”
    – LvB
    Commented Aug 25, 2023 at 7:50
1

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.

3
  • 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
    Commented May 27, 2015 at 22:23
  • Rubbish. If it's rare that the database user context is inconsistent with the application context that is in part due to misinformation like that in these answers. Commented Aug 24, 2023 at 1:07
  • @Mike you have been misled. These answers were largely untrue 8 years ago and have only grown more untrue since then. Commented Aug 28, 2023 at 15:01
0

It's a matter of tradeoffs.

Restrictive permissions can be a useful mitigation for SQL injection, though not a prevention or complete mitigation. As the other answers have explained, SQLi attacks can often target exactly the same sorts of things that the webapp legitimately needs, such as retrieving password hashes, creating user accounts, editing account data, deleting content that users request to remove, and so on. However, restrictive database permissions can still be useful in limiting the damage that any given SQLi can cause (to use the example given in the question, SQLi there might be able to read account details possibly including PII and/or password hashes, but if it was a readonly connection limited to that table only, wouldn't be able to do things like delete accounts or read user data directly).

What you're proposing is straightforwardly an example of the "principle of least privilege", which is the design principle that any actor or component in a system should only have the permissions/privileges to do things that said actor/component has a legitimate need to do. So, a login service might need to be able to read user credentials and write to a session / refresh-token table, but not much else. Or you could even have those components be separate, such that a SQLi on login wouldn't let you forge a session token for an arbitrary user, and a SQLi on access token refresh wouldn't let you dump user password hashes.

Unfortunately, least privilege is kind of expensive (in the sense of "a lot of thinking and a lot of work") to do correctly. It's still a good idea to consider it, but in light of the ability to completely prevent SQLi with a modest level of effort, it arguably makes little sense to spend a bunch of time on creating a huge number of limited-access DB accounts (and connections for each) and then making sure each query is executed only by the appropriate account.

Besides, if you're really following the principle of least privilege, you'd remove the ability for the webapp to execute arbitrary queries - or even severely restricted ones, like "SELECT operations on a specific table" - altogether, and instead you'd create a bunch of stored procedures and limit each account to a specific one of those, or at the very least prevent the use of raw queries directly without going through a prepared statement step (where presumably the webapp code would prepare all the relevant statements at startup, rather than generating them as needed). Then the only variation that any DB user would have in what it could do would be altering the parameters/arguments to that procedure/statement, which all DB engines will prevent being used to break from data (e.g. user content) to code (SQL keywords/operators) and most will furthermore provide strict type checking and possibly other validation.

The thing is, once you've done that, you've already solved SQLi for your app! Technically it is possible - if you screw up hard enough - to have SQLi with prepared statements (allow the app to prepare statements from strings other than hardcoded constants, specifically strings built using concatenation with user-supplied content) or stored procedures (have the procedure itself perform string concatenation on either its parameters or user-sourced data in the DB, producing a new string which is then evaluated as a query), but if you ignore these massive failure points - or indeed just use any other sort of parameterized queries and absolutely no manual string concatenation - then you don't have to worry about SQLi. I'm aware that it's easier said than done, especially with a large and legacy codebase (though some static analysis tools are remarkably good at finding concatenation that turns into a query, which can help a lot) but I promise you, it's easier than the "make every DB operation use a least-privilege DB user that can't do much even if there's an injection vulnerability". Or you can take a reduced-but-not-least privilege approach and restrict only by type of operation (e.g. SELECT/INSERT/UPDATE/DELETE) which would still mitigate SQPi at least a little, but unless your app was written that way in the first place you'd still have to update 100% of your database code to use the appropriate connection object in each case, and if you're doing that, you should just parameterize your queries and avoid concatenation on all your DB code anyhow.

7
  • No. Other answers have explained it poorly and have been misleading if in those answers it was said that SQL injection targets the same functions a webapp needs and implied that there are no mechanisms within the database to prevent this. Naturally, it depends on the database, but the major relational players (Oracle, SQL Server, PostgrreSQL) have had most of the important mechanisms (data types, constraints, functions, views, domains, policies, roles, permissions) for decades. Commented Aug 24, 2023 at 14:17
  • 1
    @DavidVentimiglia Respectfully, I think you should read more carefully. I neither said that SQLi targets the exact same functions (although sometimes it does, actually! Like if you want to change an admin's password to one you know), nor that you can't mitigate SQLi using DB features (in fact I said literally the exact opposite of that). The thing I was referring to other users pointing out is stuff like "you can't prevent SQLi by just making your connection read-only, some SQLi only needs read" and "you can't make the whole DB read-only to the app, apps need to change DB contents".
    – CBHacking
    Commented Aug 24, 2023 at 22:27
  • you literally wrote, "SQLi attacks can often target exactly the same sorts of things that the webapp legitimately needs." If that doesn't mean that you said that "SQLi targets the exact same functions" then I guess we'll just have to chalk that up to the inherent ambiguity in human communication. Likewise with the word "mitigate" which AFAIK means "to lessen" but not "to prevent." If you're saying database mechanisms only lessen but do not prevent SQLi, then I'm saying almost the opposite. I'm saying they literally prevent SQLi. Commented Aug 24, 2023 at 23:12
  • "same sorts of things" Yes, like the ability to read and write user data, create/edit/delete user accounts, modify the list of authorized users (in the webapp not the DB sense) for various data, etc. "Exact same functions" like what? The vast majority of DB interaction is done with a single function on the app side. connection.query() and standard SQL operations (SELECT/INSERT/UPDATE/DELETE) on the DB side. If you're using user-defined functions / stored procedures, then you are already doing what I said you should be doing to prevent SQLi, rather than mitigate it, you know?
    – CBHacking
    Commented Aug 25, 2023 at 0:26
  • Also, you seem to be conflating the OP's question about database user permissions with "database mechanisms", which I was originally willing to excuse as inexact wording on your part but at this point you've thrown away the benefit of the doubt. Nobody - least of all me - is claiming you can't prevent SQLi using database mechanisms in general - I give multiple examples of ways to do that exact thing in this post - while database user permissions - the thing the OP is asking about, which are a small subset of "database mechanisms", can mitigate but not (in the general case) prevent SQLi.
    – CBHacking
    Commented Aug 25, 2023 at 0:30
0

You understand it correctly, if you'll allow the generalization from standard SQL grant statements (i.e. "permissions") to "all of the mechanisms offered by the RDBMS." RDBMS's depart from standard SQL and differ in the features they offer, but commonly-used RDBMS's like SQL Server, Oracle, and PostgreSQL have broad overlap on these mechanisms:

  • intrinsic data types
  • user-defined data types
  • constraints
  • views
  • row access policies
  • connection properties
  • functions and procedures

There's some justification in generalizing your question in this way, in the part where you state

In the past one could give very granular permissions in databases, down to row level.

If you generalize your question to encompass non-standard but common RDBMS features like those above and combine them with standard SQL permissions then you can go a long way toward protecting your database from malicious activity and freeing yourself from the bugaboo of "SQL injection" and the folk wisdom of sanitizing your inputs and using prepared statements.

1
  • Don't believe me? Try your luck with this toy PostgreSQL database I created. Connect to "postgres://chadria:8f8d3c20f07e7a7f81e7c23c9b9c0bf3@ep-jolly-lake-95593588.us-east-2.aws.neon.tech/neondb" with whatever client you like. What tables, views, functions, etc. are there? What are the cardinalities of the tables? What security measures are deployed and can you defeat them? Some of these will be easy, others less so. Don't worry. You can't do any real world damage. Commented Aug 25, 2023 at 22:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .