Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

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.

share|improve this question
1  
I've been noodling on an equivalent problem - pentesting an app that permits SQL queries via form submission. All the SQL injection tools seem focused on getting injection, what do you do when you've already got it?!? –  gowenfawr May 15 at 19:40
    
+1 I've found the same thing from a brief search as well. Related question which I don't think is a duplicate, but is more specific to the question of preventing write access in mysql specifically: dba.stackexchange.com/questions/65212/… –  kalenjordan May 15 at 19:41
2  
A potential abuse would be a fairly simple SQL query that does a horrendous cartesian product and kills your db engine that has to consume large amounts of memory and/or disk space to execute it. –  ypercube May 15 at 21:01
    
Perhaps this goes beyond the scope of the question, but can you elaborate on the need to allow freehand SQL? It seems like the trouble to implement all these bullets (and likely more) is greater than to write a service layer to protect SQL and avoid direct queries. I think the question as posed is an XY Problem and it would help identify a better answer if we understand the underlying problem to be solved. –  LB2 May 15 at 22:09
    
@ypercube Thanks! I'm sorry for kind of mangling the nature of this question - starting it out by asking what potential abuses could be made and ending up by asking whether or not it's less secure than SSH with an IP whitelist. Any comment on the latter? –  kalenjordan May 15 at 23:22

3 Answers 3

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).

share|improve this answer
    
Thanks Jim Bob. Very helpful. The special characters is a great example - thanks for that. Might that be avoided by essentially doing, e.g., preg_replace('^[A-Z]', '')? Or is it a lot more complicated than that? Did you think the way I framed the question was at all useful? i.e. is this less secure than SSH / IP whitelist? –  kalenjordan May 15 at 20:57
    
What do you mean SSH / IP white list? Can you ensure that your SaaS application (sitting on a few known IPs) needing to use the API only generates trusted SQL queries? Then restricting to whitelisted IPs (and forcing HTTPS or an alternative form of authenticated encryption - with authentication) seems fine -- granted you have to ensure that attackers can't get your application to create bad queries somehow. The regex you suggested should work assuming its done before filtering and other characters are never needed in the SQL - but I'd still recommend parameterized queries. –  dr jimbob May 15 at 21:54
1  
@kalenjordan, The regex ^[A-Z] matches the first character of the input if it is in the range A-Z (and your preg_replace call would then delete it). Presumably for the itent you're talking about, you'd want something to the effect of [^a-zA-Z0-9\s] (which would match any character at any point in the input which is not alphanumeric or whitespace). –  Brian S May 15 at 21:57
    
@drjimbob thanks. I added an update to explain the SSH / IP whitelist deal a little better. Yes I can trust my SaaS app to not generate malicious SQL queries. For the sake of clarity, do you think it would make sense to update your answer to a Yes and then to include your recommendations? I also need to update my question to be a tiny bit more clear as well I think. –  kalenjordan May 15 at 23:32
    
@BrianS right sorry I was just giving a quick example regex. Would need backticks and a few other things as well. But is it safe to say there would be no other way for SQL to be abused if we went that route? –  kalenjordan May 15 at 23:33

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.

share|improve this answer
    
+1 Thanks @paj28. That context is very helpful. I'll drill into some of these details further. –  kalenjordan May 15 at 20:34

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.

share|improve this answer
    
+1 Thanks @LB2 - very helpful. I had considered using some kind of wrapper but the main reason I was leaning away from that was to reduce complexity in the server-side application. I will check out OData wrapper - perhaps it will be a good fit! –  kalenjordan May 15 at 20:36
    
just to confirm - from the overall perspective, would you say Yes or No that this would be less secure than SSH / IP whitelist? Sorry if that question misses the point, I'm in the process of trying to determine whether it's even worth spending a lot of time pursuing this solution and drilling into all the potential options out there. –  kalenjordan May 15 at 20:37
    
@kalenjordan My background is in webapp security, not so much in network layer. SSH/IP seems to be only applicable if your users are trusted (ignoring for the moment benefits of layered security). Per your edit in question, they're not. As such that means your database is under threat and needs to be protected from untrusted users. So based on that, I'd say SSH/IP is not sufficient, and freehand SQL queries are still very much a threat. Virtually all other bullets have been discredited in other posts and are easily circumventable. The API key assumes trusted user, which again is not the case. –  LB2 May 15 at 22:06
    
I'm sorry I'm doing such a horrible job of explaining this question. So when you say are users trusted - yes, the "user" of the API endpoint would be my own SaaS app that I own. The URL to this endpoint wouldn't even be publicly visible. I added another bullet point to UPDATE #1 to clarify. –  kalenjordan May 15 at 23:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.