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
Add a comment
|
6 Answers
You can do:
Dude.connection.quote("O'Malley")
result: "'O''Malley'"
NOTE: Once upon a time you could do
Dude.sanitize("O'Malley")
but that's been deprecated, and the current method
Dude.sanitize_sql("O'Malley")
does not escape apostrophes.
3 Comments
mltsy
In case anybody else is wondering, yes, they are exactly the same: sanitize calls connection.quote
Jasper van den Berg
The
sanitize function has been removed in favor of sanitize_sql in rails 5.1.
Jay Dorsey
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)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)
2 Comments
Harish Shetty
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.
Jared Beck
Four years later,
sanitize_sql_array and its cousins are still not part of the public API. Is there a convenient public equivalent?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.
1 Comment
Duke
With the Mysql2 gem:
Mysql2::Client.escape("O'Malley") # => "O\\'Malley"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'"
2 Comments
tvdeyen
This will not prevent you from SQL injection.
Nathan
@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.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]
2 Comments
Andy Lindeman
I can dig it up, but really: why are you inserting outside ActiveRelation/ActiveModel?
Jason Swett
I'm doing an
INSERT IGNORE and I'm updating multiple records in one statement for performance.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".
