

SQL Injection Vulnerability in Rails - speleding
https://groups.google.com/group/rubyonrails-security/browse_thread/thread/7546a238e1962f59

======
julian37
Makes me consider to start always casting URL query parameters as a defensive
measure, regardless of this specific vulnerability being patched.

~~~
michiel3
This is probably a best practice you could learn yourself to implement every
time you know for sure you don't want a GET/POST variable to be a hash/array.
This can also prevent other application errors when someone passes a
hash/array which is used somewhere in the code where it is expected to be a
string or something.

~~~
mseebach
I'm not terribly familiar with the Rails feature in question, but it seems to
me that GET/POST params should never be interpreted automatically. Parsing a
param into any other type than a string should be explicit.

~~~
tptacek
You're (respectfully) not terribly familiar with Rails, then, because the
interpretation of foo[bar]=xxx as { :foo => { :bar => 'xxx' } } is one of the
core patterns in the framework. Code all across the platform depends on that
behavior.

~~~
sim0n
This is the same with PHP. Be aware anyone using something like MongoDB, if
you don't sanitize/cast your inputs, your app could be vulnerable.

e.g. if you have the code:

    
    
      $collection->findOne( array( 'username' => $_POST['username'], 'password' => $_POST['password'] ) );
    

someone could POST something like username[$ne]='?'&password[$ne]='?' and
login.

------
klodolph
How do I read this? I just get a "sign in" wall... like all other "Google
Groups" posts.

~~~
Daniel_Newby
IIRC Google gets confused if you were once logged in. Either log in or delete
your Google cookies.

------
fendale
Sigh - why oh why doesn't Rails use bind variables? Just about every DBMS I
have used supports them (Sqlite, Mysql, Oracle, Sybase I know for sure) and if
you correctly bind the variables into the query, fears about SQL Injection
pretty much disappear.

Then there is performance - in Oracle bind variables are essential for
scalability. I read somewhere that Mysql performs better without bind
variables.

For most databases by caching prepared statement handles and binding /
executing that handle many times, it will perform better than handling many
one off queries. I have benchmarked a 20% improvement in throughput on Oracle
by making just that change (which is a one liner in the JDBC driver).

~~~
Morg
Well maybe you should check that again.

Your approach sounds like you should have stored procs instead. Using prepared
statements or variable binding to fight SQL injections is not the best idea,
although its widespread.

In most cases where you want a prepared statement, you'd be better off using a
stored proc, as you'll skip the expensive optimization every single time.

MySQL is not even a real RDBMS (no ACID, no triggers, fail APIs, etc.), anyone
using it should switch to PostgreSQL yesterday unless their data really
doesn't matter.

SQL injections are 100% avoided by user input control in the application, and
the simplest way is to escape all escape characters, that may require reading
a bit of doc but w/e.

~~~
fendale
> Using prepared statements or variable binding to fight SQL injections is not
> the best idea

What is the best idea then? If you bind variables to SQL statements, you are
SQL injection safe 100% of the time. There is no crafty input sequence that
can fool anything.

> In most cases where you want a prepared statement, you'd be better off using
> a stored proc, as you'll skip the expensive optimization every single time.

I am only qualified to speak about Oracle which is a DB I know extremely well.
A query is a query, whether it comes from Java, Perl, Ruby or inside of a
stored proc. If you prepare a statement once, and then cache that handle and
execute it many times, you optimize the query one time. Also in Oracle, if you
prepare-bind-execute one time only, the next time you do the same sequence of
steps you Oracle doesn't have to optimize the query again - it can spot it is
the same as a previous query and short circuit the process.

Mysql I _think_ doesn't cache SQL statements for later reuse like Oracle,
which is why binding isn't as important for _performance_ (in Oracle, not bind
queries is a pretty good way to bring the database to its knees) - but its
still essential for security.

> SQL injections are 100% avoided by user input control in the application,
> and the simplest way is to escape all escape characters

What this bug has just proven, is that this escaping is not all that easy -
crafty attackers can come up will all sorts of strings that seem to work
around the escaping time and time again.

------
sce
This only applies to ActiveRecord, I wonder if e.g. DataMapper is vulnerable
in a similar way.

~~~
snyff
So far, I don't think DataMapper is vulnerable. The mapping is not done in the
same way (datamapper use hard coded properties).

As an example, the following

    
    
      User.all(':email '=> {':id' => '1"'})
    

will throw

    
    
      ArgumentError: condition ":email " does not map to a property or relationship in User
      because of the space before any SQL request is performed.
    

and

    
    
      User.all(':email '=> {':id' => '1"'})
    

will create the following query:

    
    
       Query	SELECT `id`, `email`, `password` FROM `users` WHERE `email` IN ('[":id", "1\""]') ORDER BY `id`
       120602  8:41:03	    3 Quit

------
sams99
prepared statements often eliminate this vector ... the thing is, mysql2 the
most popular db access gem for rails does not even implement prepared
statements, and AR does not have a mechanism to send these to a db.

In an ideal world the app passes the db gem 'select * from posts where Id =
?', [1] and then the gem takes care of preparing and executing. In all the
real world web apps I have seen the number of "distinct" SQL statements they
need to run are quite low (in the 100s) so preparing is a no brainer.

I do not see this at all as a Ruby problem it has nothing to do with dynamic
typing, I see it as a problem approach to db access. I believe Sequel (another
less popular db gem for Ruby) is not even theoretically prone to these kind of
issues.

------
danso
Is there an example of what this special hash looks like? E resulting hash
that is, not necessarily the attack that leads to its construction.

~~~
benmmurphy
[http://blog.pentesterlab.com/2012/06/cve-2012-2661-exploitat...](http://blog.pentesterlab.com/2012/06/cve-2012-2661-exploitation-
write-up.html)

since it is already public i'm thinking of adding my own write up

~~~
speleding
I don't believe in security through obscurity but I really think the author of
that blog post is not doing the world a favor by explaining how to use this
bug with URI encoded copy/paste examples for every script kiddie to use, less
than 24 hours after it hit.

Anyway, the gist is: if you have an unpatched rails server stop reading this,
you need to upgrade RIGHT AWAY.

~~~
daeken
Anything that makes people more aware of security issues, what's involved in
exploiting vulnerabilities, and how the other side things about these things,
is a _very_ good thing.

The vast majority of programmers don't know a thing about security. Anything
that can be done to improve that, even in the slightest, is a great thing for
this world.

~~~
speleding
Like I said, I'm not in favor of keeping it a secret, and I am all for making
people aware, but "making people aware" does not have to be giving copy/paste
examples to script kiddies of how to do the exploit within 24 hours of it
being reported.

------
rabidsnail
I'm surprised more type system nazis haven't come out of the woodwork. This is
one of the few serious production bugs that actually would have been caught by
static typing.

~~~
lsb
> type system nazis

As a general rule of thumb, unless you mean to say that Hindley-Milner causes
genocide, say "sticklers", or "evangelists", or "fanbois" even.

------
LogicX
Anyone using Nginx naxsi module to protect against this?
<https://code.google.com/p/naxsi/>

------
purephase
Good to know. Thanks for sharing.

------
sparknlaunch
In laymen terms what does this mean? I understood SQL injections are one of
the most common forms of attack, therefore why has this happened on such a
popular framework? Is it user error or rails error?

~~~
danso
Appears to be Rails error. ActiveRecord provides a convenient method for
accessing the database layer. Part of the convenience is that thhe method
handles the sanitizing part...something which, when people have to write out
themselves, tend to slip up on a rare (but too often) occasion.

The bug reportedly occurs by sending this method a specifically crafted input,
which bypasses the sanitization that prevents injection.

So devs who have placed their trust in the ActiveRecord method now have
compromised systems

~~~
Morg
As usual, when you trust a piece of code without reading the source, the fail
will be strong.

This is like java's GUID that are random but not unique, etc. you can't guess
it from the function name or description, you need to know the internal
process to know how it's going to explode and when.

~~~
Cloven
95% of even good developers wouldn't be able to tell when a sql sanitization
function is poorly coded or has a hidden gotcha. Having the source is not
nearly as important as trusting the upstream to be smart and to promptly
resolve security issues when discovered.

~~~
Morg
I trust noone. except maybe the pgsql guys. However, imho on the topic of SQL
injection, either escaping the escape characters is enough or you should
change DBMSs / APIs right away.

But really, security without reading sources is blind more or less calculated
risk, not security.

~~~
PetrolMan
That's a really strange way of looking at things, in my opinion. There are
things in life that you just have to trust implicitly. I'm not saying someone
else's code falls in that category but just because it is open source and you
can supposedly discover any caveats or security risks on your own does not
make that task truly reasonable. I'm not in a position where I can read
through all of the source code for MySQL, Apache, Passengers, Rails, Ruby, etc
in order to make sure that someone hasn't made a mistake. To be honest, I'm
not sure that I would recognize an error like this by just reading the code.

What do you do with proprietary/closed source software? What do you do with
hardware that is just as capable of poorly implementing security? What about
poor decisions that really only become apparent after a security hole is
discovered?

~~~
Morg
First, I don't believe I need real security, that protects me from most of the
worries you cited.

I know it's not safe, and I don't care.

It's like mail or gmail or anything, I know someone has access to my data, and
I don't care because it's unavoidable/ not an issue.

You have to trust, but actively try to prove wrong, that weeds out most of the
crappy software, like MySQL, MSSQL (lolwut 32 trigger chain?lets cut it here
silently) or others.

You have to base your decision on stuff that really works rather than the
latest fad, so fck ruby and all that crap, write in C, that's safe, proof is
even the chinese and the military have their OS written in C.

Proprietary/closed source, you remain paranoid, test it yourself for what you
can think, never think it cannot be the cause.

Hardware you cannot trust, have to learn where the limitations are, remain
paranoid as well, question the status quo (is ECC really doing its job or am I
just trusting my enterprise data to magic).

Poor decisions that you realize later ? everyone makes mistakes, who cares ?

IMO the main thing is, don't trust anyone to do it right, especially in IT,
sometimes you come to trust a specific group, like linux kernel or pgsql
because they're proven right time and again - and imo you have to leave it
there, I don't want to write an OS at the moment.

Most poor security decisions are related to trivial things like: -using
windows -not updating your OS / kernel / tart -using _testing_ tech, like the
latest release of ruby, node.js, mongolianDB, etc. -not researching tech
before using it (i.e. google mysql ACID, you'll read a few of my posts from
when I was pissed off to discover it was in fact just a toy db with half-
implemented features) -not actively trying to hack/destroy your own creation
-not spending a few K on a honeypot session -not actually knowing anything
about hacking -not reading about standard hacking tactics, like SQLi for nubs,
XSS, MitM HTTPS, tomato launchers and many more

etc. I'm no security pro and I wouldn't pretend being one before winning
several honeypots.

