1

Here in the company I work, we have a support tool that, among other things, provides a page that allows the user to run SELECT queries. It should prevent the user from running UPDATE, INSERT, DELETE, DROP, etc. Besides that, every select statement is accepted.

The way it works is by executing

SELECT * FROM (<query>)

so any statement besides a SELECT should fail due to a syntax error.

In my opinion, this approach is not enough to prevent an attack since anything could change the out-query and break the security. I affirmed that along with that solution it should also check the syntax of the inside query. My colleagues asked me to prove that the current solution is unsafe.

To test it, I tried to write something like

SELECT * from dual); DROP table users --

But it failed because of the ; character that is not accepted by the SQL connector.

So, is there any way to append a modification statement in a query like that?

By the way, it is Oracle SQL.

EDIT:

Just to put it more clear: I know this is not a good approach. But I must prove it to my colleagues to justify a code modification. Theoretical answers are good, but I think a real injection would be more efficient.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Eduardo Hoefel
  • 127
  • 4
  • 3
    A good approach for handling SQL injections involves parameters. Does your approach use parameters? – Gordon Linoff Dec 04 '18 at 12:11
  • If you don't trust these users then don't let them write their own code. As long as you're executing whatever code they write, it's possible for them to exploit that. At the very least the account they use to access the data should have no write access to anything. – David Dec 04 '18 at 12:13
  • Parameters is a great way, indeed. If your users can only perform `SELECT` queries, you can set the DB user as read-only too. – Cid Dec 04 '18 at 12:13
  • 2
    @GordonLinoff: I cannot think of a way to use parameters since the user should be able to run full queries. As far as I know, parameters can only replace values. – Eduardo Hoefel Dec 04 '18 at 12:14
  • 2
    Parameterized query will do your stuff. Again if you want to be full proof, Create a user and assign a role that only have read permission, and execute user query using that user. – Ayan_84 Dec 04 '18 at 12:14
  • 1
    If the users should be able to run full queries, you can work around users' permission – Cid Dec 04 '18 at 12:16
  • Grant nothing but `GRANT SELECT ON ... TO ...`. However, a bad guy may gather information which he is not intended to get. – Wernfried Domscheit Dec 04 '18 at 13:58
  • If this is a real requirement, I would be reasonably comfortable allowing this as long as they can only connect to an account that owns no objects, has no privileges to create objects, and only has `select` privilege on views that they absolutely require. This way, the only SQL injection vulnerability is a user being able to query views that their *front end software* tries to hide from them, or being able to work around data security predicates that their software tries to add. – Jeffrey Kemp Dec 05 '18 at 02:52
  • In addition, if the requirement includes data security (i.e. some users are not allowed to see *all* the data), I would use Virtual Private Database (aka Row Level Security) to ensure they cannot see data they're not allowed to. – Jeffrey Kemp Dec 05 '18 at 02:55

2 Answers2

2

The protection is based on the idea/assumption that "update queries" are never going to produce a result table (which is what it would take to make it a valid sub-expression to your SELECT FROM (...) ).

Proprietary engines with proprietary language extensions might undermine that assumption. And although admittedly it still seems unlikely, in the world of proprietary extensions there really is some crazy stuff flying around so don't assume too lightly.

Maybe also beware of expression compilers that coerce "does not return a table" into "an empty table of some kind". You know. Because any system must do anything it can to make the user action succeed instead of fail/crash/...

And maybe also consider that if "query whatever you like" is really the function that is needed, then your DBMS most likely already has some tool or component that actually allows that ... (and is even designed specifically for the purpose).

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
1

I'm going to assume that it's deemed acceptable for users to see any data accessible from that account (as that is what this seems designed to do).

It's also fairly trivial to perform a Denial of Service with this, either with an inefficient query, or with select for update, which could be used to lock critical tables.

Oracle is a feature rich DB, and that means there is likely a variety of ways to run DML from within a query. You would need to find an inline PL/SQL function that allow you to perform DML or have other side effects. It will depend on the specific schema as to what packages are available - the XML DB packages have some mechanisms to run arbitrary SQL, the UTL_HTTP packages can often be used to launch network attacks, and the java functionality is quite powerful.

The correct way to protect against this is to use the DB security mechanisms - run this against a read-only schema (one with query privs only on the tables).

Egret
  • 739
  • 3
  • 8