Can someone enlighten me on how easy this SQL injection vulnerability is to exploit?
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.
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 :/
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.
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.
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.
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.
(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)
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 ...."
