Quote a SQL Value in Rails

If you saw my last post about Geocoding, you'll notice that the value passed to the geocode sql function is an address. To properly pass that value, we need to make sure that we quote it for SQL land.

❌ Bad

ActiveRecord::Base.connection.execute(<<~SQL)
  select
    rating
  from geocode('#{address}', 1)
SQL

Passing a mundane address like 100 O'Connel Ave will cause the above to throw an error in the database

But if we use the quote function from ActiveRecord, we properly quote our strings for SQL:

✅ Better

quoted_address = ActiveRecord::Base.connection.quote(address)

ActiveRecord::Base.connection.execute(<<~SQL)
  select
    rating
  from geocode(#{quoted_address}, 1)
SQL

Doing this ensures that we mitigate SQL Injection attacks and we properly account for things like single quotes in our values.

https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Quoting.html#method-i-quote