Ruby on Rails SQL Injection

I thought that SQL injection was "mostly" a thing of the past with newer frameworks such as Rails and Django. I mean, short of concatenating your query string together, it is much harder to set yourself up for failure.

sentiental• 14 years ago

Unfortunately it's not hard to manipulate this one. AR generates a couple queries when you do a single invocation - the first is a meta data query to get info about the table you're talking to. The second (or third, etc) are the actual hard queries.

It's the first query, the metadata one, that applies the passed arguments in a raw form directly to the query. The exploit takes place inside of a 'show' operation. It's totally unprotected and lets you run pretty much any select you're interested in.

It also goes beyond prepared statements - the metadata query in question is totally separate from the one specified by any parameterized query you'd pass using something like ('id = ?', params[:id]). So essentially, as a developer, you can't do anything better. This is kind of on the framework side alone :/

lansing• 14 years ago

Take a look at the test they added in the patch for an example of how the exploit would go down.

kmf• 14 years ago

Looks like two tests (test_where_error_with_hash and test_where_with_table_name).

You can see those tests here:

http://seclists.org/oss-sec/2012/q2/att-504/3-2-sql-injectio...

(this is for 3.2, you can change the patch name for 3.1, etc.)

zhoutong• 14 years ago

Based on this report, this is my rough understanding of this vulnerability:

When you pass nested query parameters such as "id[a]=b&id[c]=d", Rails will parse it as parameters :id => {'a' => 'b', 'c' => 'd'}

If you use where(:id => params[:id]) it will become where(:id => {'a' => 'b', 'c' => 'd'})

And when Rails convert that to SQL statement, something can be exploited (though it's not mentioned in the report).

benmmurphy3• 14 years ago

as far as i can see it is a little harder to exploit because you can't access the results of the sql injection. but you can change how long it takes for the query to execute based on the value in a column so you can extract arbitrary values from the database using this technique.

c3• 14 years ago

It's only a security problem if you're using the Model#where form. If you're doing Model#all or #each or whatever, you're fine.

omgsean• 14 years ago

Are you sure? That's what I thought at first, but was the .where form even available in versions before 3.0?

mhartl2• 14 years ago

Thanks for the heads-up. I've updated the Rails Tutorial book accordingly.

graeme• 14 years ago

For those of us following the tutorial, is the gemfile the only thing that changed?:

    gem 'rails', '3.2.6'

I also had to run "bundle update railties" to fix a couple of dependences.

mhartl2• 14 years ago

Yes, the relevant change is in the Gemfile for each example project. Running

  $ bundle update rails
  $ bundle install

should bring you up to speed.

BadassFractal• 14 years ago

Wasn't 3.2.4 specifically released a few weeks ago to address that very same SQL injection vulnerability? Is this a regression?

dutchbrit• 14 years ago

I was about to say, I swear I read an exact same vulnerability a few days/weeks ago, also in regards to the where statement.

sanxiyn2• 14 years ago

This one is a variant of that attack.

Toshio• 14 years ago

I've looked at this and a few other ActiveRecord vulnerabilities that look arcane at first sight, but the simplest solution that covers all of them is to first check whether params[:something] is nil and refuse to do any DB stuff if it is, and second to explicitly convert all of your params[:something] input to the types of data you were expecting, using to_i, to_s or to_f. Additionally, if using where clauses, strip the input of all apostrophes or double quotes. I don't think there would be anything left to exploit if everybody were to adopt this semi-paranoid approach to protecting your code from bad input.

telent• 14 years ago

> strip the input of all apostrophes or double quotes.

This approach will be a hard sell for Father O'Reilly and Dwight David "Ike" Eisenhower

sirfried• 14 years ago

how lame, do AR devs need someone to remind them about bind variables/params ?

Robin_Message• 14 years ago

(rant) Since Rails devs don't appear to believe in databases (and to be fair, if I was targeting MySQL version 3 I'm not sure I would believe in them much either), they don't bother using database features for binding. Instead having their own half baked (well, it 90% baked now, but it still has the occasional squishy bit) binding system.

Same for validations, foreign keys, primary keys, indexes, enumerations, etc. (/rant)

benmmurphy3• 14 years ago

the places where these sql injections were happening wouldn't be prevented from traditional sql bindings which are applied to parts of the where clause or set values. in JDBC land i don't think you can do "SHOW TABLES FROM ? WHERE ...."

amalag• 14 years ago

Binding variables is prepared statements? AR does support that, how does that affect this vulnerability?

benmmurphy3• 14 years ago

I have an article on these issues but mostly focusing on the ones fixed in 3.2.4/3.2.5. Article is a bit weird because it was written before 3.2.4 and I thought some of the issues were user problems rather than framework problems.

http://benmmurphy.github.com/blog/2012/05/15/abusing-dynamic...

scosman• 14 years ago

second in a month? I stopped using rails in favour of sintra+activerecord. Active record might need to go next :(

amurmann• 14 years ago

So you think that if you write everything from scratch it won't have any flaws? Of course it will have flaws, you just won't know about it.

calinet6• 14 years ago

To be fair, this attack is more based on form parsing and constructing SQL statements from fields, not necessarily ActiveRecord itself.

thibaut_barrere• 14 years ago

I'm using both Sinatra and Rails, but I would not advise someone to switch to Sinatra because Rails has more "fixed vulnerabilities".

Around me there are many times more Rails apps than Sinatra apps. I do believe that the increased exposure of Rails makes it more likely to detect vulnerabilities over there.

danso• 14 years ago

I saw this post last night and assumed it was the same vulnerability from a week ago.

Thanks to everyone who upvoted and made the comment that it requires a whole new patch/upgrade, I would've missed it otherwise.

This is probably a case in which editorializing the submission title ("This is brand new from last week") would've been a good service

oops• 14 years ago

FYI you can get Rails security alerts directly to your inbox via http://groups.google.com/group/rubyonrails-security

Ruby on Rails SQL Injection

Related Stories