Phusion white papers Phusion overview

Rails SQL injection vulnerability: hold your horses, here are the facts

By Hongli Lai on January 3rd, 2013

Update: see also the article Securing the Rails session secret.

Update 2: a statement from Michael Koziarski of the Rails security team regarding the severity of this bug has been added. He urges people to upgrade immediately. Please scroll to the “Conclusion” section for details.

Update 3: new advisories (CVE-2013-0155 and CVE-2013-0156) have been published. These vulnerabilities are unrelated to the one reported in this blog post, but are extremely critical. Upgrade immediately.

Yesterday a Ruby on Rails SQL injection vulnerability was announced which affects all versions. This immediately received widespread attention on Hacker News. Unfortunately the announcement doesn’t clearly explain how the vulnerability exactly works, which caused a lot of confusion and unnecessary panic, especially among people who are less familiar with Ruby or Rails.

Here are the facts, along with a clear explanation for non-Rails people.

Summary: what is this vulnerability?

  • The bug allows SQL injection through dynamic finder methods (e.g. find_by_foo(params[:foo])). I will explain dynamic finders in a bit.
  • The bug affects all Ruby on Rails versions.
  • A known exploitable scenario is when all of the following applies:
    1. You’re using Authlogic (a third party but popular authentication library).
    2. You must know the session secret token.

    There are other exploitable scenarios, but it really depends on what your app is doing. Since it is impossible to prove that something isn’t insecure, you should take the vulnerability seriously and upgrade anyway even if you think you aren’t affected.

What is this vulnerability NOT?

For those who know Rails:

  • The bug does not affect normal finder methods (e.g. find(params[:id])).
  • The bug is not exploitable through request parameters.
  • The bug is not in Authlogic. It’s in Rails. It just so happens that Authlogic triggers it.
  • Devise (another third-party authentication library) does not trigger the bug.
  • Point 6, as described at Rails Security Digest. ‘params’ Case, is a totally different and unrelated issue. The issue described there is quite severe and deserves serious attention, so please keep your eye open on any new advisories.

For those who do not know Rails:

  • It does not mean all unupgraded Rails apps are suddenly widely vulnerable.
  • It does not mean Rails doesn’t escape SQL inputs.
  • It does not mean Rails doesn’t provide parameterized SQL APIs.
  • It does not mean Rails encourages code that are inherently prone to SQL injection. The code should be safe but due to a subtlety was not. This has been fixed.

The main exploitable scenario

Rails provides finder methods for all ActiveRecord (database) models. For example, to lookup a user using a primary key that was provided through the “id” request parameter, one would usually write:

User.find(params[:id])

Rails also provides so-called “dynamic finder methods”. It generates a “find_by_*” method for all database columns in model. If your “users” table have the “id”, “name” and “phone” columns, then it will generate methods so you can write things like this:

User.find_by_id(params[:id])
User.find_by_name(params[:name])
User.find_by_phone(params[:name])

The vulnerability is in these dynamic finder methods, not in the normal and often-used find method.

ActiveRecord protects you against SQL injection by escaping input for you. For example the following works as expected, with no vulnerability:

User.find_by_name("kotori'; DROP TABLE USERS; --")
# => SELECT * FROM users WHERE name = 'kotori\'; DROP TABLE USERS; --' LIMIT 1

But ActiveRecord also defines ways for the programmer to inject SQL fragments into the query so that the programmer can customize the query when necessary. The injection interfaces are documented and the programmer is not supposed to pass user input to those interfaces. Normally, the strings passed to the injection interfaces are constant strings that never change. One of those injection interfaces is the options parameter (normally second parameter) for the “find_by_*” methods:

# Fetches a user record by name, but only fetch the 'id' and 'name' fields.
User.find_by_name("kotori", :select => "id, name")
# => SELECT id, name FROM users WHERE name = 'kotori' LIMIT 1

# You can inject arbitrary SQL if you wish:
User.find_by_name("kotori", :select => "id, name FROM users; DROP TABLE users; --")
# => SELECT id, name FROM users; DROP TABLE users; -- FROM users WHERE name = 'kotori' LIMIT 1

The vulnerability lies in the fact that “find_by_*” also accepted calls in which only the options parameter is given. In that case, it thinks that the value parameter is nil.

User.find_by_name(:select => "1; DROP TABLE users; --")
# => SELECT 1; DROP TABLE users; -- FROM users WHERE name IS NULL LIMIT 1;

Not many people ever use the second parameter, but code of the following form is quite common:

User.find_by_name(params[:name])

params[:name] is normally a string. Can an attacker somehow ensure that params[:name] is an options hash? Yes. Rails converts request parameters of a certain form into hashes. Suppose you call the controller method like this:

/example-url?name[select]=whatever&name[limit]=23

params[:name] is now a hash: { "select" => "whatever", "limit" => 23 }

However, this is not exploitable. Ruby has two datatypes, strings and symbols. Symbols are kind of like string constants. You’ve seen them before in this article: :select is a symbol. The vulnerability can only be triggered when the keys are symbols, but the Rails-generated request parameter hashes all have string keys thanks to the way HashWithIndifferentAccess works.

An attacker can only exploit this if the application somehow passes an arbitrary hash to “find_by_*”, yet with symbol keys. We now bring in the second part of the puzzle: Authlogic. This exploit is described here and works as follows.

Authlogic accepts authentication credentials through multiple ways: cookies, Rails session data, HTTP basic authentication, etc. All user accounts have a so-called persistence token, and the user must provide this persistence token through one of the authentication methods in order to authenticate himself. Authlogic looks up the user associated with the persistence token using roughly the following call:

User.find_by_persistence_token(the_token)

Can an attacker ensure that the_token is an options hash? Yes, but only through the Rails session data authentication method. In all the other methods, the_token is always a string.

The Rails session mechanism allows storing arbitrary Ruby objects, including hashes with symbol keys. Rails provides a variety of session stores, the default being the cookie store which stores session data in a cookie on the client. The cookie data is not encrypted, but is signed with an HMAC to prevent tampering. The cookie store is fast, does not require any server-side maintenance, and is only meant for session data that do not contain sensitive information such as credit card numbers. Apps that store sensitive information in the session should use the database session store instead. Nevertheless, it turned out that 95% of all Rails apps only ever store the user authentication credentials in the session, so the cookie store was made the default.

So to inject arbitrary SQL, you need to tamper with the cookie, which requires the HMAC key. The HMAC key is the so-called session secret. As the name implies, it is supposed to be secret. Rails generates a random 512-bit secret upon project creation. This is why most Rails apps that are running Authlogic are not exploitable: the attacker does not know the secret. Open source Rails apps however can form a problem. Many of them come with a default session secret, but the user never customizes them, so all those instances end up using the same HMAC key, making them very easily exploitable. Of course, in this case the operator have to worry about more than just SQL injection. If the HMAC key is known then anybody can send fake credentials to the app.

Other exploitable scenarios

Your code is vulnerable if you call Foo.find_by_whatever(bar), where bar can be an arbitrary user-specified hash with symbol keys. HashWithIndifferentAccess stores keys as strings, not symbols, so that does not trigger the vulnerability.

Mitigation

There are several ways to mitigate this.

  1. Upgrade to the latest Rails version. This solves everything, you don’t need to do anything else. “find_by_*” has been patched so that the first parameter may not be an options hash.
  2. Ensure that you only pass strings or integers to “find_by_*”, e.g. find_by_name(params[:name].to_s). This requires changing all code, including third party code. I do not recommend this as you’ll likely overlook things. If you upgrade Rails, you don’t need to do this.
  3. Keep your session secret secret! If you write open source Rails apps, make sure the user generates a different session secret upon installation. Don’t let them use the default one.

Demo app

We’ve put together a demo app which shows that the bug is not exploitable through request parameters. Setup this app, run it, and try to attack it as follows:

curl 'http://127.0.0.1:3000/?id\[limit\]=1'

You will see that this attack does not succeed in injecting SQL.

Conclusion

So here you have it. Some folks on Hacker News asked “how can this giant bug be overlooked”? As you can see, it is not a “giant bug”, it is much more subtle than that and requires a specific combination of code and circumstances to work. Most apps are not vulnerable.

Update: Michael Koziarski of the Rails security team said the following:

“When we told people they should upgrade immediately we meant it. It *is* exploitable under some circumstances, so people should be upgrading immediately to avoid the risk.”

References

  • http://www.akitaonrails.com/ AkitaOnRails

    Enlightening as usual. Very clear explanation, I was myself panicked to upgrade my apps by the way the security fix was released (https://groups.google.com/forum/?fromgroups=#!topic/rubyonrails-security/DCNTNp_qjFM) which at first glance felt like all dynamic finders had a hole. Good to know it’s a very tiny hole in a very specific situation. Explanations like this should be more clear on the security fix release itself. Kudos for you guys again!

  • Flip Sasser

    Nice writeup! But doesn’t HashWithIndifferentAccess behave as though symbol keys are set even when they’re not (and vice versa)? I thought that was the point if HWIA but maybe I’m off!

  • Flip Sasser

    Sorry, point *of* HWIA. Lazy typing over here.

  • http://twitter.com/emil_soman Emil Soman

    Very useful ! I was just wondering about the specific scenarios where this could be a vulnerability . The patch should link to this page .

  • Fritz

    Good write up, thanks for,making it clear where the real threats from this vulnerability.

  • http://www.phusion.nl/ Hongli Lai

    `find_by_*` eventually triggers code that checks whether only valid options are given. This code utilizes assert_valid_keys() which explicitly checks for key symbols. See apply_finder_options() in active_record/relation/spawn_methods.rb.

  • Jonas

    Are you not above such PR moves as this? This is a quite serious security problem, it’s not something to overlook. While obviously obscure, and not many applications are vulnerable, that is no consolation for those applications which are.

    Obscure bugs are no less serious that very common, for the affected software. Saying otherwise is like saying Rails do not need to concern itself with security compared to, say, the relative popularity of PHP.

    The fact that this bug is a design bug — every part of Rails and in this case Authlogic works as intended, but with unforeseen consequences — is particularly frightening. There is simply too much magic in here, when this kind of cross interaction bugs are not even not by the original implementors.

  • hoooopo

    curl ‘http://127.0.0.1:3000/posts?id[limit]=1′ for demo?

  • John Bitme

    Yes, when I think of obvious PR moves, I definitely think of people providing full technical details as well as a list of exploitable and non-exploitable scenarios.

    Please.

  • http://twitter.com/workmad3 David Workman

    Personally, I think that the fact that exploitation of this bug requires the application to have leaked the key that sessions are signed with makes it less severe. If that key has been leaked (through misconfiguration or a security breach in the environment) then the application is vulnerable in myriad ways in most apps, including an attacker being able to switch users on a whim (with the usual case of rails apps that only store the user id in the session).

    As you say, it is still a problem, but this article never claimed it wasn’t (and the fact that the exploitation has now been fixed also says the exploit was taken seriously). The article even says to upgrade just in case the exploit is wider than their analysis indicates. Security is being taken seriously, the article is just providing the facts and details of the exploit to counter the FUD coming from incomplete information and knee-jerk reactions.

  • Dude

    Ridiculous. Get your head out of your ass, Jonas.

  • http://www.phusion.nl/ Hongli Lai

    The other commenters have already said everything that needs to be said. But this part is not true:

    The fact that this bug is a design bug — every part of Rails and in this case Authlogic works as intended, but with unforeseen consequences — is particularly frightening.

    Rails *wasn’t* working as intended. That’s why it has been fixed. The first parameter should never have been recognized as an options hash in the first place.

  • dennis

    Thanks for the post! Clears up a lot of questions!

  • http://www.facebook.com/people/Dan-Kaminsky/515164691 Dan Kaminsky

    Thanks for the detailed data. It would seem that you’ve identified a core Rails bug, in that session secrets are generated at project creation rather than during some sort of “installation phase”. Exactly how much damage can be done with this? You write:

    “Of course, in this case the operator have to worry about more than just SQL injection. If the HMAC key is known then anybody can send fake credentials to the app.”

    Are, in fact, most Rails-based open source projects running with remote auth bypass?

  • http://www.phusion.nl/ Hongli Lai

    I don’t see that as a bug. It could be a problem but it depends on the use case.

    There are actually not that many open source Rails apps out there. Most people use Rails to write proprietary web apps so Rails optimizes for that use case. In proprietary web apps it is convenient to store the session secret in version control because then the setup phase (both in production and development) becomes less complicated.

    Only when the code is open source does this become a problem.

  • http://twitter.com/ericmonti Eric Monti

    There is a ‘rake secret’ task available in every generated rails project which generates a new random secret for you, however it does not perform the configuration in place.

    > rake secret # Generate a cryptographically secure secret key (this is typically used to generate a secret for cookie sessions).

    I agree it would be better to have something like this included directly in rails: https://github.com/digineo/secret_token_replacer

    I thought I remembered there being just such a task built in somewhere, but I guess I was wrong.

    Even so, if you are developing an open-source rails application and distributing it, you should probably be on the hook to provide this critical configuration step (by including just such a module and using it in your installation procedure, for example). I think it’s a stretch to call this a core Rails bug.

  • prtflio

    very well written. thank you

  • http://twitter.com/ericmonti Eric Monti

    Thanks for digging into this and clarifying the issue. I myself panicked a bit after playing around with find_by_* in IRB instead of with real requests. The detail about symbol keys versus strings is pretty crucial.

  • http://twitter.com/timonvonk Timon Vonk

    <3 for the detailed explanation and the heads up!

  • http://www.facebook.com/people/Dan-Kaminsky/515164691 Dan Kaminsky

    Secure by default implies you’re safe in normal configurations. This is a normal configuration and it failed.

  • Jonas

    What you are dealing with is a company in damage control-mode, not a serious discussion of security practices.

    The fact that the article above is overly long and only concerns itself with cases when the bug is *not* exploitable should be enough. Seriously. That’s not what security issues should be about. As a developer, (hopefully) the intended audience of this article, you should be concerned about *when* your application is vulnerable.

    It should be all about the short paragraph “Other exploitable scenarios”. Then a thorough technical walkthrough in how these scenarios come about. I will easily bet you a case of beer that other code than the authentication library will trip this bug, considering circumstances.

  • Jonas

    The whole point of this PR stunt is to make you believe a leaked key is necessary to exploit the bug. You believe this only because you did not read the article close enough. Read it again! Read the code!

    While all the facts are true in this article, it is presented in a dishonest way which make reactions like yours perfectly natural (and in fact, intended). I would expect more from a technical community.

    My bet from the above comment stands. Any takers?

  • dude

    If they are, I’m sure it is the programmer’s fault completely.

    You know, just like the bug that someone finally had to 0wn github with to prove it was a problem.

    You can always k-line the guy, that’ll show’em!

  • Can Acar

    This is not only a problem for open source projects. The opensource repository just provides easy/public way to access the key file. There are other ways to “exploit” a key shared unintentionally across different installations of a rails application. First of all, a developer that sells a closed source application to multiple sites would know the key. Admins of all the sites that run that particular application would know the “default” key and would be able to attack other sites (competitors perhaps?) that are using the same application. If I have an account in one site, I may be able to “reuse” my cookies on another site even without knowing the key at all. I am sure there may be many other ways to break the trust an application puts into the “cookie” or any other client provided data because the HMAC validation passes.

    The fact that there is no easy way to renew the key builtin to the rails is a big oversight. Even then, it is still not “secure-by-default” as it requires the developer to call these methods at the right time. One solution is to tie the keys to something that is unique across installations, possibly the domain name, and generate a new one automatically if it changes.

  • http://twitter.com/fernandezpablo Pablo Fernandez

    What if I have:

    User.find_by_foo(:select => params[:fields])

    That’s a problem right?

  • http://twitter.com/dandemeyere Dan DeMeyere

    When you say ‘Upgrade to the latest Rails version’, do you mean the latest stable 3.2.X branch (i.e. 3.2.10) or are you talking about the Rails 4.0 edge branch?

  • Toons

    Thank you for taking the time to write such a clear explaination

  • Mike

    very nice

  • http://www.phusion.nl/ Hongli Lai

    We didn’t write Rails, we only use it.

  • http://www.phusion.nl/ Hongli Lai

    The whole point of this PR stunt is to make you believe a leaked key is necessary to exploit the bug.

    The whole point of the article is to explain the nature and the severity of the vulnerability so that developers can do proper risk assessment. It seems you would only be contend with an article that says “Rails is 100% broken and insecure and will everyone should abandon it immediately”.

  • http://www.phusion.nl/ Hongli Lai

    Yes, very big SQL injection right there. Don’t do that, or sanitize your input.

  • http://www.facebook.com/profile.php?id=14301110 Shaun Russell

    friendly_id (used for creating slugs) allows you to use .find instead of .find_by_slug — I was worried that this may be exploitable, but it doesnt appear to be so.

  • C. C. Duplá

    No, the last patch level (3rd number in the rails version). For example, if you are using rails 3.1.8, upgrade to 3.1.9. usually patches do not make API changes, so there are less chances that you would need to rewrite parts of your rails app.

    Still, after the upgrade run your tests before deploying to production…

  • Alric

    Methinks posting anonymously == trolling, in this case.

  • http://twitter.com/homakov Egor Homakov
  • http://twitter.com/homakov Egor Homakov

    yeah

  • Jack

    When I read this post http://homakov.blogspot.de/2013/01/rails-security-digest-eli5.html mostly Point 6 and this is true, then this vulnerability is more serious as you’r post implies.

  • Steve Jorgensen

    This points out another thing that has always made me uncomfortable in Rails, which is the extent to which Rails behavior depends upon data that it receives at runtime. In this case, there is a specific structure of data that should be expected from a particular HTML form, but ActionPack happily builds an arbitrary data structure based on whatever data it receives in the POST. Similarly, the attributes and attribute types of an ActiveRecord model are determined by inspecting the table structure at run-time, which assumes that there is a way to to inspect that properly. This can be broken in may cases by views, and there are cases where it would be nice for the model to treat the attribute as a different type than what would normally be inferred.

    Until today, I hadn’t even thought about the potential security implications of having arbitrary nested array/hash structures defined by POST data that is specified by the client. It’s likely that many applications have weaknesses in that regard besides this the ones related to this Rails bug.

  • http://www.phusion.nl/ Hongli Lai

    Point 6 is a totally different and unrelated issue. But point 6 *is* serious yes.

  • http://www.phusion.nl/ Hongli Lai

    Point 6 in the linked article is a different and unrelated issue.

  • http://www.phusion.nl/ Hongli Lai

    Agreed. When I started with Rails I’ve always wondered whether Foo.find(params[:id]) couldn’t cause trouble if params[:id] is a hash. I found out that ActiveRecord just threw an exception and was convinced that “surely the Rails guys must have thought about this scenario”. But now it has shown to cause subtle problems in other places. Perhaps it’s time to evaluate an alternative that requires explicit casting of data structures.

  • http://www.phusion.nl/ Hongli Lai

    After giving your comment some thoughts, I believe you have a point. I’ve written the article “Securing the Rails session secret”: http://blog.phusion.nl/2013/01/04/securing-the-rails-session-secret/

  • http://twitter.com/homakov Egor Homakov

    that’s right. The case from the subject is nothing in fact, and there is very low probability to find same in real app. But Point 6 is just worse vuln with params

  • Jonas

    Thank you for making that clear.

    I still believe the article could have been a lot clearer on that point. It is a very long article that lists various ways in which Rails is not vulnerable. That is not a very tactful way to treat a vulnerability disclosure.

    If you do not believe me, I think the fact that actual developers in this very thread have been misled to believe that a key is required to exploit this bug should be proof enough!

    I’m trying to make a contructive point here, about how to handle security disclosures in a responsible way, but silly accusations like I would like to “see Rails 100% broken” (which would in fact be very detrimental to my income) or is “trolling” does not help.

    Again, this article is more heavy on the PR rather than disclosure. That is a problem. The fact that actual developers are misled is a problem. Being angry with me when I correctly point this out is a problem.

  • http://www.phusion.nl/ Hongli Lai

    Fair enough. You may want to know that by now, the article has been updated with a statement that urges people to upgrade.

  • Jonas

    I don’t think I can put it more clear:

    No facts are wrong in this article. But it is very heavy on ways Rails is not exploitable, and very sparse on ways that it is.

    Developers are misled by this. How is this not a problem?

    I am not interested in pointless flamewars and will not answer your accusations about my hidden agenda or how I think you wrote Rails(!). And while I would also be happy to register an account if that is what it takes to get civil replies, I somehow doubt that would help.

  • http://www.phusion.nl/ Hongli Lai

    Then we have an agreement, I’m also not interested in pointless flamewars. I apologize if I came over as not being civil. As I said before, I welcome constructive criticism and definitely welcome discussion, as you can see at http://blog.phusion.nl/2013/01/04/securing-the-rails-session-secret/. You may be happy to know that the article has been updated with a statement from a Rails security member who urges people to upgrade.

  • http://twitter.com/amolpujari Amol Pujari

    great post, I always use `find_by_id` as it does not raise any exception if object not found which `find` does

  • please

    fix your god damn thing.

  • Bubba

    A good compromise might be a message warning at the end of rails generate saying what one should do next.

  • http://www.examplesofsql.com/ Serhii Burkovskyi

    nice information