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..

bor1s · Answer 1 · 2011-08-22T10:01:47.453

14

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.

edited Aug 22 '11 at 10:01

answered Aug 22 '11 at 09:50

bor1s

4,081
18
25

Result: `protected method 'sanitize_sql_array' called for ActiveRecord::Base:Class` – nothing-special-here Aug 22 '11 at 09:54
..or you can use this: `ActiveRecord::Base.send(:sanitize_sql_array, "your string")` – bor1s Aug 22 '11 at 09:57
2

The last one works... but it still executes the 'evil' sql code. ;) – nothing-special-here Aug 22 '11 at 10:05
so, if you want to just update records - use update_all like DanneManne said ;) (http://apidock.com/rails/ActiveRecord/Base/update_all/class) – bor1s Aug 22 '11 at 10:09

score 10 · Answer 2 · answered Aug 22 '11 at 10:05

10

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)

answered Aug 22 '11 at 10:05

Michael Koper

9,586
7
45
59

2

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) – Chad M Dec 23 '15 at 21:49
[I found this relevant regarding an alternative to monkey patching](https://stackoverflow.com/a/2329394/673826) – mlt Sep 25 '17 at 19:14
Thanks for going the extra and giving example on how to use it. – Adrian Carr Feb 13 '18 at 23:09
Also note, that it won't work with arrays. It flattens them out :( – mlt Jun 08 '18 at 19:37

score 8 · Answer 3 · answered Oct 20 '11 at 19:05

8

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.

answered Oct 20 '11 at 19:05

Jay Levitt

1,680
1
19
28

Thanks, this worked perfectly. Just adding that this goes in the initializer. – KPheasey Sep 27 '13 at 15:14

score 7 · Answer 4 · edited Jul 01 '19 at 18:32

7

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

edited Jul 01 '19 at 18:32

Zac

2,201
24
48

answered Oct 19 '16 at 02:50

c g

389
1
4
11

3

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? – MattD Mar 07 '19 at 20:03
Agree with your point @MattD. Going forward I'll follow as you suggested. – c g May 05 '19 at 07:10

score 1 · Answer 5 · edited Mar 16 '15 at 20:31

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".

score 0 · Answer 6 · answered Dec 02 '20 at 03:47

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).

Rails, how to sanitize SQL in find_by_sql

6 Answers6

Linked

Related