Read more

Properly sanitizing column names for MySQL

Thomas Eisenbarth
February 01, 2011Software engineer at makandra GmbH

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

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

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show archive.org snapshot

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:

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:

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/.

Posted by Thomas Eisenbarth to makandra dev (2011-02-01 12:39)