How to Sanitize Raw SQL in Rails 4

How to sanitize raw SQL in a Ruby script

I don't know Sequel, but did you try standard insert method?

connection = Sequel.connect('...')
table_name = connection.from(:table_name)
# OR
# table_name = DB.from(:table_name)
# table_name = DB[:table_name]
table_name.insert(csv_row.to_h)

It's more reliable I believe, because you avoid difference between COLUMN_NAMES and record_values.

Avoid sql injection with connection.execute

You can use the methods in ActiveRecord::Sanitization::ClassMethods.

You do have to be slightly careful as they are protected and therefore only readily available for ActiveRecord::Base subclasses.

Within a model class you could do something like:

class MyModel < ActiveRecord::Base

  def bespoke_query(params)
    query = sanitize_sql(['select * from somewhere where a = ?', params[:search]])
    connection.execute(query)
  end

end

You can send the method to try it out on the console too:

> MyModel.send(:sanitize_sql, ["Evening Officer ?", "'Dibble'"])
=> "Evening Officer '\\'Dibble\\''"

Rails ActiveRecord sanitize_sql replaces ? in string

In the end I read through the ActiveRecord source and couldn't identify a way to handle this situation without a lot of code changes. There doesn't appear to be a way to escape the ? characters.

To resolve it for this one query I ended up using the SQL chr() function to generate a character that would pass the santization step untouched:

select 'http://www.google.com' || chr(63) || 'q=' || res from some_table where a = ?;

ASCII character 63 is ?.

Although not a perfect solution, I could at least get this one SQL query into the system without having to make massive code changes.

Ruby on Rails: How to sanitize a string for SQL when not using find?

Add this method to your model:

class Media < ActiveRecord::Base
  def self.execute_sql(*sql_array)     
    connection.execute(send(:sanitize_sql_array, sql_array))
  end
end

Now you can execute any SQL such as:

Media.execute_sql('UPDATE medias SET vectors = ? WHERE id = 1', '::')

Reference

1) sanitize_sql_array

---

Related Topics