Is it possible to modify database using SQL injection with Active Record

Question

Using Rails 4.0 I have a controller action which uses request parameters in an active record query:

@clients = Client.where(params[:where]).order(params[:order]).limit(params[:limit])

Would it be possible for a user, using sql injection, to modify the database, or obtain information from the database that is not contained in the clients table?

If so an example would be greatly appreciated, as well as a simple method to prevent the possibility (I understand that placeholders or hash parameters would remove the vulnerablity but I don't see how to create the same functionality using them)

Thanks in advance.

Edit

I understand that these methods have vulnerabilities. My question concerns the extent of the vulnerablity. I was not able to find the answer to my specific question in the rails guides.

Answer 1

The where method assumes that its argument is a SQL fragment if it's a string.

What happens next probably depends on the database used, for example on mysql Active Record turns off the ability for a query to contain multiple statements, so it's not just a case of injecting a where clause with a ';'.

Current versions of rails use prepared statements on postgres, which also forbids the injection of multiple statements.

In general relying on this seems extremely risky and may vary on other databases. Even if data modification / theft is not possible, denial of service would be trivial.

It would also be possible to steal information of the form "is there a user with email address xyz" by using a subquery that uses EXISTS. With a little time an patience you could also extract values, for example if I know a user's id and want to extract their email address I could run subselects of the form

SELECT * from users where id = 123 and email_address like 'A%'

If I get results then I know the email address starts with A, and I can move onto the next letter. If not then I check whether the email address starts with B and so on. Once I've got the first character, then queries of the form

SELECT * from users where id = 123 and email_address like 'FA%'
SELECT * from users where id = 123 and email_address like 'FB%'
SELECT * from users where id = 123 and email_address like 'FC%'

allow me to extract the second character of the email address. With a few thousand queries you could get their whole email address.

Another way to do this would be to use the sleep function on mysql - it would allow the extraction of variables one byte at a time based on how long the page took to load