37

I know about prepared statements, but if I'm using raw SQL, does ActiveRecord have a way to manually escape values?

Something like this would be nice:

self.escape("O'Malley") # O\'Malley
Jason Swett
  • 43,526
  • 67
  • 220
  • 351

6 Answers6

65

You can do:

Dude.sanitize("O'Malley")

or

Dude.connection.quote("O'Malley")

both with the same result: => "'O''Malley'"

konus
  • 1,612
  • 17
  • 24
  • 2
    In case anybody else is wondering, yes, they are exactly the same: [sanitize calls connection.quote](http://apidock.com/rails/ActiveRecord/Base/sanitize/class) – mltsy Nov 09 '16 at 20:11
  • 4
    The `sanitize` function has been removed in favor of `sanitize_sql` in rails 5.1. – Jasper van den Berg Jun 07 '19 at 09:58
  • 2
    As of, or maybe in, Rails 5.2.4.1 (at least) these do not appear to give the same result, at least with Postgres as a backend. `Model.connection.quote` works as expected (wraps in single quotes, double-single quotes any single quotes). `sanitize_sql` does nothing in the example shown (it might sanitize other things, but it doesn't escape single quotes) – Jay Dorsey Feb 19 '20 at 06:35
45

A quick dive into the ActiveRecord source reveals its method "sanitize_sql_array" for sanitizing the [string, bind_variable[, bind_variable]] type of sql statement

You could call it directly:

sql = ActiveRecord::Base.send(:sanitize_sql_array, ["insert into foo (bar, baz) values (?, ?), (?, ?)", 'a', 'b', 'c', 'd'])
res = ActiveRecord::Base.connection.execute(sql)
jemminger
  • 5,133
  • 4
  • 26
  • 47
  • 2
    Jason: This is a better solution as it is DB independent. If the app is deployed on Heroku, currently accepted solution(@quest) will not work. – Harish Shetty Feb 17 '11 at 03:55
  • 11
    Four years later, `sanitize_sql_array` and its cousins are still not part of the public API. Is there a convenient public equivalent? – Jared Beck Feb 05 '15 at 00:13
26

You can easily use the mysql2 gem to do this:

irb(main):002:0> require 'rubygems'
=> true
irb(main):003:0> require 'mysql2'
=> true
irb(main):004:0> Mysql2::Client.escape("O'Malley") # => "O\\'Malley"
=> "O\\'Malley"

Or if using the earlier mysql (not mysql2) gem:

irb(main):002:0> require 'rubygems'
=> true
irb(main):003:0> require 'mysql'
=> true
irb(main):004:0> Mysql.escape_string("O'Malley")
=> "O\\'Malley"

This will allow you to escape anything you want then insert to the db. You can also do this on most models in your rails application using the sanitize method. For instance say you have a model called Person. You could do.

Person.sanitize("O'Malley")

That should do the trick.

mahemoff
  • 44,526
  • 36
  • 160
  • 222
quest
  • 776
  • 5
  • 14
9

If you don't want the extra single quotes wrapping your string that occur when you use the solution posted by @konus, you can do this:

Dude.connection.quote_string("O'Malley")

This returns "O\'Malley" instead of "'O\'Malley'"

Nathan
  • 7,816
  • 8
  • 33
  • 44
  • 3
    This will not prevent you from SQL injection. – tvdeyen Nov 01 '12 at 17:01
  • 4
    @tvdeyen: Would you be willing to illustrate in what manner the above would be vulnerable, especially in comparison to the accepted answer? Ultimately, `quote_string` is going to be dependent on the particular ActiveRecord adapter being used. For the mysql2 adapter and the abstract mysql adapter, `quote` calls `quote_string` for strings values and wraps the result in quotes. Are you thinking some kind of multibyte injection or something else? Thank you. – Nathan Nov 09 '12 at 04:16
5

Even with Model.find_by_sql you can still use the form where question marks stand in as escaped values.

Simply pass an array where the first element is the query and succeeding elements are the values to be substituted in.

Example from the Rails API documentation:

Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]
Andy Lindeman
  • 12,087
  • 4
  • 35
  • 36
2

In case somebody is looking for a more concrete example of @jemminger's solution, here it is for bulk insert:

users_places = []
users_values = []
timestamp = Time.now.strftime('%Y-%m-%d %H:%M:%S')
params[:users].each do |user|
    users_places "(?,?,?,?)"
    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".

Zeeshan
  • 3,462
  • 2
  • 25
  • 28