Is string.sanitize the best way to protect from sql injection.
Do we need to install the Sanitize gem for it or is there a better way?
value = "O'Brian"
value.sanitize =>"O\'Brian"
or
value.escape =>"O\'Brian"
It s probably included by default in Rails 5 , but what about using sinatra.
To protect against SQL injections, you should us prepared statements. About all high-level database adapters offer capabilities to use and properly escape variables. In ActiveRecord, this looks like this:
value = "O'Brian"
Person.where(name: value).to_sql
# => "SELECT `people`.* FROM `people` WHERE `people`.`name` = 'O\\'Brian'"
Other database adapters like Sequel or DataMapper have similar capabilities.
When using a plain database adapter like pg or mysql2, you can use plain prepared statements on the database level.
With mysql2, this can look like this:
value = "O'Brian"
statement = @client.prepare("SELECT * FROM people WHERE name = ?")
result = statement.execute(value)
Alternatively, all adapters offer database-specific string escape methods. But you should generally stick to prepared statements as they are safer to use when you just don't attempt to reason about escaping but delegate all of this to a library which does this consistently.
As a final note about the sanitize method and the sanitize gem, they are not intended for escaping SQL fragments and won't save you from SQL injections when used that way. The sanitize gem is used to ensure that HTML code only contains safe whitelisted tags and attributes. It has nothing to do with escaping SQL and will result in vulnerable code if used that way!