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.