Rails, how to sanitize SQL in find_by_sql

Question

Is there a way to sanitize sql in rails method find_by_sql?

I've tried this solution: Ruby on Rails: How to sanitize a string for SQL when not using find?

But it fails at

Model.execute_sql("Update users set active = 0 where id = 2")

It throws an error, but sql code is executed and the user with ID 2 now has a disabled account.

Simple find_by_sql also does not work:

Model.find_by_sql("UPDATE user set active = 0 where id = 1")
# => code executed, user with id 1 have now ban

Edit:

Well my client requested to make that function (select by sql) in admin panel to make some complex query(joins, special conditions etc). So I really want to find_by_sql that.

Second Edit:

I want to achieve that 'evil' SQL code won't be executed.

In admin panel you can type query -> Update users set admin = true where id = 232 and I want to block any UPDATE / DROP / ALTER SQL command. Just want to know, that here you can ONLY execute SELECT.

After some attempts I conclude sanitize_sql_array unfortunatelly don't do that.

Is there a way to do that in Rails??

Sorry for the confusion..

Barbital

Answer 1

Try this:

connect = ActiveRecord::Base.connection();
connect.execute(ActiveRecord::Base.send(:sanitize_sql_array, "your string"))

You can save it in variable and use for your purposes.

Strobe

Result: protected method 'sanitize_sql_array' called for ActiveRecord::Base:Class – Barbital

..or you can use this: ActiveRecord::Base.send(:sanitize_sql_array, "your string") – Strobe

The last one works... but it still executes the 'evil' sql code. ;) – Barbital

so, if you want to just update records - use update_all like DanneManne said ;) (apidock.com/rails/ActiveRecord/Base/update_all/class) – Strobe

Answer 2

I made a little snippet for this that you can put in initializers.

class ActiveRecord::Base  
  def self.escape_sql(array)
    self.send(:sanitize_sql_array, array)
  end
end

Right now you can escape your query with this:

query = User.escape_sql(["Update users set active = ? where id = ?", true, params[:id]])

And you can call the query any way you like:

users = User.find_by_sql(query)

Orlina

This'll work, but just a heads up, you won't need the "send". Calling a protected method is allowed by the receiver when the caller is the same class. That is, you'll only need the method definition to be: "def self.escape_sql(obj); sanitize_sql_array obj; end;" (semicolons used since SO comments down allow return lines) – Maritsa

I found this relevant regarding an alternative to monkey patching – Nickienicklaus

Thanks for going the extra and giving example on how to use it. – Immix

Also note, that it won't work with arrays. It flattens them out :( – Nickienicklaus

Answer 3

Slightly more general-purpose:

class ActiveRecord::Base  
  def self.escape_sql(clause, *rest)
    self.send(:sanitize_sql_array, rest.empty? ? clause : ([clause] + rest))
  end
end

This one lets you call it just like you'd type in a where clause, without extra brackets, and using either array-style ? or hash-style interpolations.

Cajun

Thanks, this worked perfectly. Just adding that this goes in the initializer. – Driving

Answer 4

User.find_by_sql(["SELECT * FROM users WHERE (name = ?)", params])

Source: http://blog.endpoint.com/2012/10/dont-sleep-on-rails-3-sql-injection.html

Iamb

It's generally best to include an explanation of the code you provided rather than simply linking to a blog where it's explained. What happens if the owner of that blog updates the post to no longer include the code you provided, or deletes the post, or deletes the blog? – Lucho

Agree with your point @MattD. Going forward I'll follow as you suggested. – Iamb

Answer 5

Though this example is for INSERT query, one can use similar approach for UPDATE queries. Raw SQL bulk insert:

users_places = []
users_values = []
timestamp = Time.now.strftime('%Y-%m-%d %H:%M:%S')
params[:users].each do |user|
    users_places << "(?,?,?,?)" # Append to array
    users_values << user[:name] << user[:punch_line] << timestamp << timestamp
end

bulk_insert_users_sql_arr = ["INSERT INTO users (name, punch_line, created_at, updated_at) VALUES #{users_places.join(", ")}"] + users_values
begin
    sql = ActiveRecord::Base.send(:sanitize_sql_array, bulk_insert_users_sql_arr)
    ActiveRecord::Base.connection.execute(sql)
rescue
    "something went wrong with the bulk insert sql query"
end

Here is the reference to sanitize_sql_array method in ActiveRecord::Base, it generates the proper query string by escaping the single quotes in the strings. For example the punch_line "Don't let them get you down" will become "Don\'t let them get you down".

Craftwork

Answer 6

I prefer to do it with key parameters. In your case it may looks like this:

  Model.find_by_sql(["UPDATE user set active = :active where id = :id", active: 0, id: 1])

Pay attention, that you pass ONLY ONE parameter to :find_by_sql method - its an array, which contains two elements: string query and hash with params (since its our favourite Ruby, you can omit the curly brackets).

Forefinger