At Highgroove, we love working with the community, whether it’s hosting hack nights, organizing ATLRUG, or contributing to open-source software. One of our key contributions involves auditing those ruby gems upon which we most rely, or the ones that present the most perceived risk. Ernie Miller’s Squeel definitely piqued our security interest. Read on to hear about the results of our audit and how we worked with Mr. Miller to validate our results.

The Problem

Squeel is one of the “scope helpers” listed in our ever-evolving security audit process. It provides a powerful DSL that makes building Rails scopes more “ruby-ish,” rather than being strings of SQL that get parsed and used. It also exposes a huge amount of functionality without requiring developers to get into raw SQL. That said, anytime a gem exposes any SQL functionality, it should give developers pause, particularly if that SQL may happen to contain user input.

SQL Injection (SQLi) is scary because of the level of exposure resulting from leaked information; there are a number of widely publicized compromises of large sites due to its exploitation. Moreover, it is notorious for being difficult to quash in field of web security. Sanitizing user input in such a way that it can be used directly in an SQL query is incredibly difficult, and really shouldn’t be done. There are best practices to ameliorate this problem, such as using prepared statements. Rails also helps by providing powerful query tools that make writing SQL a rare necessity. But when you’re using libraries that compose and execute SQL for you, how can you ensure that those libraries are following best practices?

Resolving the Issue

To audit Squeel, we built a very simple Rails 3.2 application with a model that was intentionally built to be vulnerable to classic SQLi in a scope, for example scope :name_eql_sqli, lambda { |param| where("name = #{param}") }.

Immediately following the vulnerable scope, we would include a version that is mitigated in the standard Rails best-practices fashion, for example scope :name_eql_no_sqli, lambda { |param| where("name = ?", param) }.

Finally, we would add a Squeel-provided scope we hoped to exploit, always ambitiously called _squeeli, for example: scope :name_eql_squeeli, lambda { |param| where{name == param} }. In hindsight, perhaps the latter should have been called _squeeli? or _maybe_squeeli.

SQLi is very much a database-specific practice; for many vulnerabilities, an attack that exploits one site won’t work on the exact same site using a different back-end database. As such, we focused the audit on Postgres because it is used by so many sites and hosting platforms. Being that we love Test-Driven Design so much, the natural route was to use RSpec in the hopes that a tight testing loop would allow rapid discovery of any vulnerabilities. You can find that test suite here.

After many iterations of trying to find a vulnerability, the audit seemed to be a success–with the minor setback that it wasn’t working. We used Postgres’ excellent logging functionality via postgres --log_statement=all, and it seemed that Postgres was reporting that a bad query was hitting the database. After copy/pasting that query into the console, we received the (bad) results that we expected. That Postgres was apparently getting what we wanted is great, but back in the Rails app ActiveRecord was not returning the results we had hoped for. After much toil in an effort to get the desired results, we ultimately decided to report to the author and get his take on the problem.

It’s no secret that we love Github because it makes software development a true joy. Only in this process did I find a small shortcoming in using it. When Highgroove find a bug in software, we open an issue or even a pull request with a patch (though many software maintainers prefer that the issue be opened first, and that a pull request be made only after they’ve approved the fix or change).

But what do you do when the issue may have security ramifications not just for you, but for all users of that library? Posting a vague “your software has a security vulnerability” issue seems next to useless, and posting a full proof-of-concept exploit to the issue exposes all users of the software to compromise. Some variety of “responsible disclosure”/private flag for issues would have been awesome, but since we didn’t have one, I had to try something else.

Reaching out

I decided to reach out to Mr. Miller directly. Other Highgroovers have chatted with him on IRC, and he is available on his Twitter, Google+, LinkedIn and Github accounts, in addition to his personal website. Mr. Miller was extremely receptive to hearing about what we had found, and he examined it carefully. In the end, it appeared that the Postgres logs were giving us a false sense of accomplishment by indicating that we had found a vulnerability, but the query actually was escaped and sanitized correctly before getting there.

Mr. Miller’s openness to the possibility of an issue spoke to his pride as a software craftsman. That quality alone gives us additional confidence in using a gem which, if written without care, could cause immense problems for us and our customers. Now we can use the gem confidently, and we’re freed to focus on our own process while using it.

How do you reach out to software developers with sensitive questions? Do you have a protocol in place for responsible disclosure in your security process?

Image credit: francescomucio