SQL Injection Vulnerability in Rails
groups.google.com
groups.google.com
e.g. if you have the code:
$collection->findOne( array( 'username' => $_POST['username'], 'password' => $_POST['password'] ) );
params[:id] could be a string, integer, hash, or array when accessed in the controller actions.
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.
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).
>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.
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, 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.
Alternately, suppose one would like to use SQLite with a competent ORM--what's the harm in that?
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.
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 QuitIn 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.
since it is already public i'm thinking of adding my own write up
Anyway, the gist is: if you have an unpatched rails server stop reading this, you need to upgrade RIGHT AWAY.
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.
Disclosers gonna disclose. Know what I'm saying?
The URI here was just for people to test if they were vulnerable or not.
Scroll down to test_where_error.
Well, at least you're funny.
As a general rule of thumb, unless you mean to say that Hindley-Milner causes genocide, say "sticklers", or "evangelists", or "fanbois" even.
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
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.
But really, security without reading sources is blind more or less calculated risk, not security.
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?
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.