How bad is allow edit a database field containing an sql from a form?

Question

I have a table in a database (Mysql) which contains a field which stores sql strings on it. I mean complete queries as text (always select statements). To modify one of that queries is a "pain in the ass" because is needed first to connect to a vpn, then access to the database and finally modify the field with some software like Mysql Workbench or any other.

I'm developing in a website a control panel only accesible by site admins. In that control panel I'm thinking in create a form to modify that sql field. It can do the admins life easier to manage it.

I'm usually very aware of being careful with sql injections in all my forms, GET and POST requests, etc. I know this could be dangerous even being a restricted panel only for admins.

First question is: If I finally decide to create it and an admin user falls under control of a hacker, how bad would it be for me? Is there a possibility to "control" the sql statements to be stored to allow only select statements?

I know that some basic techniques like replace some dangerous reserved words for nothing, like DROP for example, are useless... the hackers can use simple tricks like DRDROPOP which after replacing gets DROP anyway and that kind of dirty tricks... or maybe they can ofuscante the sqls using Unicode, etc...

Second question: Is there any good guide to try to control and filter as much as possible sql "tricks" or is useless because ALWAYS is there a method to penetrate launching other "non-select" statements?

Answer 1

If you really have to do this, I'd suggest a couple things:

1. Run your user supplied queries as a separate read-only database user. Most DBMS have permission system where you can GRANT the user permission to only be able to run SELECT queries and you can restrict which table that that user can run against.

2. If any of your tables contains a mix of sensitive and non-sensitive data (e.g users table contains password hashes), you might want to create a VIEW on that table to hide the sensitive data. You'd GRANT the user privilege for this VIEW instead of the underlying table. Alternatively, you can give the user access to call selected Stored Procedure/Function that filters them without giving permission to the underlying table. In some cases, you can also use column privilege.

This way, even if the admin account is compromised, it's limited to read only on selected tables.

Answer 2

This sounds like a very strange setup and I would say it's pretty insecure. Typically if you want to save SQL queries to be executed later, you would create a stored procedure which you could then call. Storing the sql query in a database table seems like a bad idea from a performance, best practices and security perspective.

It's such a strange setup that you would not be able to apply the accepted methods for preventing SQL Injection attacks and because of this I'm almost certain that there would end up being a vulnerability. Without seeing exactly how you implement this, it's impossible to say what that vulnerability would be.

To answer your questions:

1. Without seeing exactly how you implement this it's impossible to say for sure, but if you are asking if there is a "possibility" - yes, absolutely
2. I would look at OWASP's guide on whitelisting input if you insist on moving forward this this.

Answer 3

Any time you store fully written out queries in a database column and then allow an 'admin' access to those queries, there is a possibility that a bad actor will be able to access them and change them.

As a best practice, you should not be storing plain text queries in the database. Maybe you should encrypt the string when you store it, or, if MySQL supports it, turn on table or column level encryption in the database it self.

Answer 4

As others have already pointed out, this might not be the best setup. But if you do this, I have two recommendations:

• Principle of least privilege: Make sure that the database user that executes the queries only has privelige to do the bare minimum it needs. Should it only be SELECT queries? Well, only allow them. Does it only need to read from three tables? Then only give access to exactly those three tables. This answer makes a good point about using views to only give access to parts of tables.

• Filtering/validating SQL queries: You mention using things like blacklisting certain words for security. While blacklisting is the wrong way to go here, you could use validation but only as an addition - not a replacement - to the above point. The way I would go about it is to do something like this:

  if(sql.trim().substr(0, 7) !== "SELECT ") { echo "Houston, we got a problem!" }
  

  Note that this is only effective if you use a function that only accepts single statements (i.e. no multiple statements separated by ;).

Finally, if these queries are used for something else than just display the data you are in deep trouble. If they actually control the logic of the application, e.g. "list users with edit priveligies" or "list articles that can be deleted", there is no end to the problems you could have if an admin account is breached.