SQL Injection Vulnerability in Rails(groups.google.com)
groups.google.com
SQL Injection Vulnerability in Rails
https://groups.google.com/group/rubyonrails-security/browse_thread/thread/7546a238e1962f59
11 comments
How do I read this? I just get a "sign in" wall... like all other "Google Groups" posts.
IIRC Google gets confused if you were once logged in. Either log in or delete your Google cookies.
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.
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.
[deleted]
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.
> 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.
As a general rule of thumb, unless you mean to say that Hindley-Milner causes genocide, say "sticklers", or "evangelists", or "fanbois" even.
[deleted]
I liked the part where you insult and slander those of us that appreciate typing and also call out (just one of many) reasons why can be a "good thing".
>few
Well, at least you're funny.
Well, at least you're funny.
Anyone using Nginx naxsi module to protect against this?
https://code.google.com/p/naxsi/
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.
http://blog.pentesterlab.com/2012/06/cve-2012-2661-exploitat...
since it is already public i'm thinking of adding my own write up
since it is already public i'm thinking of adding my own write up
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.
Anyway, the gist is: if you have an unpatched rails server stop reading this, you need to upgrade RIGHT AWAY.
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.
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.
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.
Exactly. OSX had very bad security before the first big news on virus's, and it won't have good security before another thousand big news on virus's, trojans and backdoors.
The full disclosure debate is almost as old as the Internet itself. There's a case to be made on either side.
Disclosers gonna disclose. Know what I'm saying?
Disclosers gonna disclose. Know what I'm saying?
To be honest, I think there is a lot more work to do (at least 2 more hours just to dump the database version) even for the author. I don't think any script kiddie will be able to exploit it based on the information provided. Or a least until someone put together a SQLmap tutorial for it.
The URI here was just for people to test if they were vulnerable or not.
The URI here was just for people to test if they were vulnerable or not.
How'd you find this? It's a good one.
Seems they would be close enough that they didn't want to share it. Trying to avoid too many script kiddie attacks on unpatched rails servers.
https://rubyonrails-security.googlegroups.com/attach/fc2da6c...
Scroll down to test_where_error.
Scroll down to test_where_error.
Good to know. Thanks for sharing.
Makes me consider to start always casting URL query parameters as a defensive measure, regardless of this specific vulnerability being patched.
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.
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.
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.
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:
e.g. if you have the code:
$collection->findOne( array( 'username' => $_POST['username'], 'password' => $_POST['password'] ) );
I get it now. I though there was interpretation of the right hand side of the =.
It's not just query parameters. Could be post params as well.
A better solution would be to have a way to indicate "this parameter needs to be this type".
params[:id] could be a string, integer, hash, or array when accessed in the controller actions.
params[:id] could be a string, integer, hash, or array when accessed in the controller actions.
> A better solution would be to have a way to indicate "this parameter needs to be this type".
If only there were some kind of concept of typing, perhaps enforced by the language and statically applied, which would eliminate this class of error entirely...boy, that would be helpful in larger systems.
If only there were some kind of concept of typing, perhaps enforced by the language and statically applied, which would eliminate this class of error entirely...boy, that would be helpful in larger systems.
For those who don't know, these are called prepared statements. Instead of mashing strings together, you define variables in your sql string, and associate a value and type with each variable. Of course this doesn't stop bugs in the implementation, as in this case.
I believe the parent was mocking Ruby for its dynamic typing, rather than what you're discussing.
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?
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
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
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.
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.
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.
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.
But really, security without reading sources is blind more or less calculated risk, not security.
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?
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?
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.
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.
This only applies to ActiveRecord, I wonder if e.g. DataMapper is vulnerable in a similar way.
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
As an example, the following
User.all(':email '=> {':id' => '1"'})
ArgumentError: condition ":email " does not map to a property or relationship in User
because of the space before any SQL request is performed.
User.all(':email '=> {':id' => '1"'})
Query SELECT `id`, `email`, `password` FROM `users` WHERE `email` IN ('[":id", "1\""]') ORDER BY `id`
120602 8:41:03 3 QuitSigh - 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).
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).
This has nothing to do with bind variables. It was not a bug in how variables were bound/escaped. The bug was that Rails treated a variable (which sometimes is a user input) as a column name in certain cases.
I admit I had not read the actual bug report before I commented, but I just did and it reads:
>Impacted code directly passes request params to the `where` method of an ActiveRecord class like this: Post.where(:id => params[:id]).all >An attacker can make a request that causes `params[:id]` to return a specially crafted hash that will cause the WHERE clause of the SQL statement to query an arbitrary table with some value.
For me that is a classic SQL injection attack that can be avoided 100% of the time if you use bind variables for all user input into an SQL query.
Rails doesn't use bind variables - it uses something that looks similar, but it is actually rails code that escapes and concatenates the user input into the query string before executing it. This bug was in that escaping code. However, if Rails correctly bound the inputs to all queries (which is pretty easy to do) then this escaping code could be removed totally and this problem would never have appeared.
>Impacted code directly passes request params to the `where` method of an ActiveRecord class like this: Post.where(:id => params[:id]).all >An attacker can make a request that causes `params[:id]` to return a specially crafted hash that will cause the WHERE clause of the SQL statement to query an arbitrary table with some value.
For me that is a classic SQL injection attack that can be avoided 100% of the time if you use bind variables for all user input into an SQL query.
Rails doesn't use bind variables - it uses something that looks similar, but it is actually rails code that escapes and concatenates the user input into the query string before executing it. This bug was in that escaping code. However, if Rails correctly bound the inputs to all queries (which is pretty easy to do) then this escaping code could be removed totally and this problem would never have appeared.
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.
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.
> 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.
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.
"Your approach sounds like you should have stored procs instead."
Not necessarily. As one moves towards stored procs, it becomes more common to place business logic in said stored procs. This tends to go against MVC and also limits some scaling options (as your business logic then is executing in your DB). They also often will tie you to a singular DB...making moving DBs more painful.
"Using prepared statements or variable binding to fight SQL injections is not the best idea, although its widespread." I read it not as _the_ best idea, but as yet another layer to potentially catch something. Belt _and_ suspenders if you will.
Don't get me wrong. I'm not saying prepared statements are the greatest thing since sliced bread; however, in oracle or postgresql backed apps, they're a best practice to investigate.
"Using prepared statements or variable binding to fight SQL injections is not the best idea, although its widespread." I read it not as _the_ best idea, but as yet another layer to potentially catch something. Belt _and_ suspenders if you will.
Don't get me wrong. I'm not saying prepared statements are the greatest thing since sliced bread; however, in oracle or postgresql backed apps, they're a best practice to investigate.
[deleted](1)
I am weary of reading that every RDBMS does an expensive query-plan calculation on every non-sproc DML... maybe you know of a few that behave this way, but there are plenty that don't.
Alternately, suppose one would like to use SQLite with a competent ORM--what's the harm in that?
Alternately, suppose one would like to use SQLite with a competent ORM--what's the harm in that?