1

I was reading up on SQL injection attacks from the Rails Guides and was wondering if the below line could be susceptible to a SQL Injection attack:

accounts.where("lower(name) LIKE '%#{params[:query].downcase}%'")

If I read and understand the guide correctly, could a user do something like

 `'\''; DROP TABLE users;`

Would that drop my table?

------EDIT------

Same for these examples:

Account.where(id: account_id).first
current_user.actions.where(id: params[:clear_action_reminder]).first
edits.where.not(account_id: non_human_ids)
Rating.where(project_id: @project.id, account_id: current_user.id).first_or_initialize

These are of course application specific queries but because there is no ? in the query could these all potentially be disastrous?

1

It's probably open to a generic SQL injection, according to this discussion in the pgsql-general mailing-list:

http://www.postgresql.org/message-id/20140626102204.36ced4dd@red.yakaraplc.local

The poster looked into ActiveRecord internals and reports that it uses libpq's PQsendQuery, which does accept several queries separated by semi-colons. This is from 2014, so presumably current.

So, assuming that your params[:query].downcase has not already gone through a layer of quoting, a DROP TABLE could be injected into it.

Even if ActiveRecord used PQexecParams or a similar call that doesn't accept semi-colon seperated queries, there would still be the possibility of injecting a function call, as in ...LIKE '%whatever' AND do_something_bad(...) AND 'whatever'='%'. This possibility is opened by lack of proper literal strings quoting, or lack of handling the parameters as recommended by the API.

And even when using prepared statements or injection with proper escaping, LIKE with user-supplied contents require an additional special quoting anyway, since it has its own syntax with % and _ being wildcards and its own escape mechanism with \ configurable with the ESCAPE clause. Otherwise the users can't search a literal % or _

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.