What would the potential abuses be for an HTTP API that accepted raw SQL?

Question

I was wondering what some of the potential abuses could be for an HTTP API that accepted SQL queries and output results sets. The fact that this is the canonical definition of SQL injection is not lost on me :)

In my case, I have a specific PHP/MySQL application in mind which is Magento - an eCommerce platform.

Here are a few security measures I've considered:

• Non-standard URL route - to prevent mass scanning, each installation would have it's own URL route (this is similar to how Magento's admin backend is handled)
• A decent length API key
• HTTPS-only access
• Read-only access via a read-only MySQL user.
• Read-only access via SQL parsing. It would require a little less configuration if read-only access could be implemented this way. I was considering simply parsing the queries for "INSERT ", "UPDATE ", "DROP ", etc.
• Blacklisted tables and fields. For the most part, configuration data is stored in a table called core_config_data, so that could be blacklisted. Also things like credit card tokens can be stored in a specific field of a specific table. In very poor implementations, credit cards can be stored in the clear as well, which would need to be blacklisted of course.
• White list table and field access - Perhaps only allowing access to whitelisted tables and fields would be a better approach than a blacklist.
• There are a lot of community extensions that could potentially store sensitive information as well. It would be hard to generically block them by default, so I think a whitelist might be the only option to solve for this.
• Some mechanism for a maximum number / frequency of retries
• Other standard things that are used to secure SSH connections for example, such as an IP whitelist, public key auth, VPN, etc.

Assuming that these measures were in place, would this be as secure or more secure than, let's say, SSH access locked down by an IP whitelist.

UPDATE: Few more specifics of the application, to follow up on some of the questions in the answers.

• The thing that I'm looking to build is a Magento module that will get deployed to the store and accept SQL queries to return back data to my SaaS app. I own both the module and the SaaS app.
• The application in question would be a SaaS app that tapped into Magento to pull data in. So it's not a situation where only trusted employees have access.
• The ideal minimal level of security (from an amount-of-configuration perspective) would be to not use a read-only user.
• Let's assume that the false positives that would be blocked are not an issue (Sorry, "Bobby Insert Tables").

UPDATE 2: Just wanted to flesh out a little bit more the reason why I chose to frame this question in terms of comparing it to an SSH connection with an IP address whitelist.

An SSH connection for all intents and purposes is already functionally the same as a raw SQL API (plus a whole lot more) in the sense that you can SSH in and connect to MySQl and then do whatever. So my thinking is that if a SQL API is as secure or more secure than an SSH connection, it may present an acceptable level of risk.

Also, I know this type of question can be overly subjective, so I wanted to frame it in a way that could have a relatively objective answer, which is why I wanted to compare it to a specific SSH scenario.

Answer 1

The potential for abuse obviously depends on the application. Does this only face trusted employees on an intranet/VPN with privileged access? Would these employees otherwise have read-only access to the DB, because there's a multitude of diverse queries they'll need to run on the data? In that case, I can imagine allowing arbitrary raw SQL queries to be generated by the trusted user.

Or is this application out there facing any random attacker who may see your online store and is trying to do havoc? In that case -- I strongly recommend you do not do this. Take the time and go through the workflows and create the allowed parameterized queries that have permission to be run -- the user of the HTTP API only has permission to alter the parameters to these queries.

Parsing the SQL command for bad terms like UPDATE/DROP is a very bad idea. People can get really clever bypassing these preventions (e.g., you bypass filters that remove <script> tags by using <scr<script>ipt> which then becomes <script>). Similarly there may be valid reasons for some field to have the value "Update" in it. There's possibilities of encoding issues/unicode normalization issues to bypass your filter. E.g., an attacker sends DRO%C0%50 in the URL encoded string which gets past the filter that flags "DROP", but then when the bytes DRO\xc0P gets sent to the database its processed identical to being DROP allowing an attacker to drop the database. (For more see this trick used in directory traversal attacks).

Having a read-only account is a better solution, but again you may not want an attacker to be able to access some private fields (e.g., CC #s, hashes of passwords, etc.). Furthermore, it isn't hard to just create malicious SQL commands that are very CPU intensive and can be used to do a denial of service attacks on your database.

Letting an untrusted end-user write code (and an SQL command is code) is always an anti-pattern and should be avoided. Yes, this will require more work initially coming up with the allowed parameterized queries, but its well worth it.

---

EDIT:

If your queries that are generated by your application are always fully trusted this is a different issue. That is your application is generating pairs of parameterized queries like "SELECT name FROM users WHERE id = ?" sent to the database along with an array of untrusted user input -- ["1"] or in case of a failed SQL injection attack ["1 OR 1 = 1; DROP TABLE user; --"]). Or potentially more problematically, but possibly safe you use some existing well known SQL injection sanitizing functions to sanitize your input to send something. I personally wouldn't risk the second route, but a mature sanitizing functions may be able to do this.

Then all you need to do is use authenticated encryption (e.g., the messages are encrypted and MAC'd) between your application and the HTTPS API. An IP address white list would be one reasonable way to accomplish this (in addition to say sending a password) all over TLS (and verifying identity of the other server hosting the API to prevent Man in the Middle attacks).

Answer 2

Most databases (and this includes MySQL) are designed to support this mode of operation securely. This used to be widely used with "fat client" applications that access the database directly. The idea is that you give the relevant users the database permissions they need, and the database controls access. Some databases (notably Oracle) support extremely granular permissions, down to row and column level control, as well as "virtual private databases".

These days, fat clients tend to access a web service rather that a database directly, but a similar model is used for analytical staff who need to run custom queries, but only have limited permissions on the database.

Unfortunately, the security of this approach has been largely discredited. Most databases have had numerous vulnerabilities where an low-privileged database user can escalate their privileges. Oracle in particular has had dozens of such vulnerabilities. In contrast, most databases have had few vulnerabilities that allow a remote unauthenticated user to take control of the database.

Some security companies (including my employer) offer tools and services to improve database security. However, the architectural trend is to make databases more private and only accessible to application servers, which reduces the importance of this kind of security.

If you are really keen to do what you propose, the security of your database becomes much more important. Your suggestion to parse and filter SQL queries has some merit, although beware that SQL is extremely complex to parse. There are appliances (e.g. Oracle database firewall) that do this, although they're not really aimed at your scenario.

Perhaps you need some generic query API, but not necessarily SQL? In that case you could consider something like the Java Persistence API. This can map to SQL on the server, but because it is simpler than SQL, there is less to go wrong.

Answer 3

To add to what others said, you don't need database table write access to install backdoor. Though it was using SQL Server rather than MySQL, someone once showed me a script that allowed to create new users on the server through SQLI (by shelling out to system i believe, don't remember).

If you do need flexible data access, I would look into some OData wrapper that protects database by not running arbitrary SQL, but flexible enough to support varying queries.

I did see applications however that sent out SQL in their HTML (I believe facebook), but they sent it signed so they wouldn't accept the sql back if it didn't come from them. Meaning no-one could change it without breaking the signature, upon which they'd reject request.