oss-security - Re: Unsafe Query Risk in Active Record

Follow @Openwall on Twitter for new release announcements and other news

[<prev] [next>] [<thread-prev] [day] [month] [year] [list]

Message-Id: <c8ff1395-4f21-453f-8c3d-fa9f4c8555b0@googlegroups.com>
Date: Tue, 13 May 2014 01:53:53 -0700 (PDT)
From: Jordi Massaguer <jordimassaguerpla@...il.com>
To: ruby-security-ann@...glegroups.com
Cc: rubyonrails-security@...glegroups.com, oss-security@...ts.openwall.com, 
	oss-security@...ts.openwall.com
Subject: Re: Unsafe Query Risk in Active Record

Hi,

I wrote a simple script that checks that you don't have column names that 
matches its table name.

See:

https://gist.github.com/jordimassaguerpla/afc020bbb3f015dccaff

However, I don't understand how to check for the join aliases which 
conflict with column names. Can you give me a hint? Can this be detected by 
parsing the db/schema.rb as in the previous script?

thanks

jordi massaguer pla



On Tuesday, May 6, 2014 6:20:29 PM UTC+2, Rafael Mendonça França wrote:
>
> This advisory concerns a security risk in all supported versions of Active Record.  There is no patch to apply for this issue.
>
> Due to the query API that Active Record supports, there is a risk of unsafe query generation in two scenarios.  Databases with a table that contains a column with the same name as the table and queries with join aliases which conflict with column names could be vulnerable to an attack where the attacker can perform certain manipulations to the SQL queries generated by Rails.
>
> Determining Vulnerability
> -------------------------
>
> A vulnerable application will either contain columns named identically to their table, or have column names which conflict with join aliases.
>
> For example, if you had a model called SecurityToken, which contained an attribute called `security_tokens` then the following code could be manipulated to return additional records:
>
>   SecurityToken.find_by_security_tokens(params[:security_tokens])
>
> Due to the typical approach of pluralizing for tables and using singular names for columns, this kind of conflict is very unlikely.
>
> The second scenario would be join aliases which conflict with a column name already in the query.  For instance suppose `users` has a denormalized column `contacts` which indicates how many times a user has been contacted, and a Contact record for each.  The following query could be manipulated to return additional records:
>
>   User.joins(:contacts).where(contacts: params[:contacts])
>
> Workarounds
> -----------
>
> The simple work around is to call to_s on the value from params:
>
>   SecurityToken.find_by_security_tokens(params[:security_tokens].to_s)
>
>   User.joins(:contacts).where(contacts: params[:contacts].to_s)
>   
> Alternatively, rename any columns which collide with table names or likely join aliases.
>
> Future Fixes
> ------------
>
> Unfortunately it is not possible to implement a reliable fix for this risk without breaking applications which rely on related functionality to build their queries.  Future releases of Rails will be able to address this, however that functionality will need to be built in the open and have a long beta period to flush out unanticipated edge cases.
>
> Credits 
> ------- 
>
> Thanks to Joe van Dyk and to Fraser Newton of Clio for reporting the issue to us.
>
>
Content of type "text/html" skipped

Please check out the Open Source Software Security Wiki, which is counterpart to this mailing list.

Confused about mailing lists and their use? Read about mailing lists on Wikipedia and check out these guidelines on proper formatting of your messages.