Posted over 8 years ago. Visible to the public.

Properly sanitizing column names for MySQL

There are times when you need to send SQL to the database, like this:

Copy
def self.some_count(field) field = connection.quote_column_name(field) scoped(:select => "COUNT(DISTINCT #{field}) AS count") end

Although the given variable is sanitized here, the MySQLAdapter's (and probably other adapters as well) method for this is insufficient as it only wraps backticks around it, not helping against injection:

Copy
Klass.some_count("id`); DELETE FROM users; -- ") # Will result in this SQL which is valid but definitely undesirable: SELECT COUNT(DISTINCT `id`); DELETE FROM users; -- `) AS count;

If you are doing something like the above, the correct way is to first remove all backticks from the given string. Afterwards the unsafe code is wrapped in backticks. The resulting SQL query will look like this:

Copy
SELECT COUNT(DISTINCT `id); DELETE FROM users; -- `) AS count; MySQL will complain about a missing column '`id); DELETE FROM users; -- `' which is safe.

Put the attached fix for the MySQL adapter into config/initializers/.

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Avatar
Thomas Eisenbarth
Last edit:
about 4 years ago
by Martin Straub
Attachments:
mysql_adapter.rb
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Thomas Eisenbarth to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more