#117 ✓hold
Allan Cochrane

Illegal MySQL in #to_sql and #to_hash

Reported by Allan Cochrane | September 25th, 2007 @ 10:15 AM

in script/console I run

>> SimulationColumn.select{'name' == "comfy"}.to_sql

=> "SELECT * FROM simcols WHERE 'name' = 'comfy'"

(This is a legacy database, the tables don't follow RoR conventions!)

Copy/pasting that string into MySQL:

mysql> SELECT * FROM simcols WHERE 'name' = 'comfy';

Empty set (0.02 sec)

mysql> explain SELECT * FROM simcols WHERE 'name' = 'comfy';


| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |


| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |


1 row in set (0.00 sec)

Shouldn't #to_sql return:

SELECT * FROM simcols WHERE `name` = 'comfy';

or avoid quoting the column name?

Comments and changes to this ticket

  • Chris Wanstrath

    Chris Wanstrath September 28th, 2007 @ 12:34 PM

    • State changed from “new” to “open”

    Are you using ActiveRecord? What DB -- SQLite?

    Ambition should try to use your DB's quoting via the following code:

        def sanitize(value)
          if value.is_a? Array
            return value.map { |v| sanitize(v) }.join(', ') 
          case value 
          when true,  'true'                                                        
          when false, 'false'                                                       
          when Regexp
            if active_connection?                                                   
  • Allan Cochrane

    Allan Cochrane September 28th, 2007 @ 03:27 PM

    DB is MySQL.

    To illustrate:

    I'm expecting the result of

    FormalResult.find(:all, :conditions => {'module_name' =>"comfy"})

    to be the same as:

    FormalResult.find(:all, :conditions => {'module_name' =>"comfy"})

    But the SQL emitted looks significantly different:

    SELECT * FROM DM19_FORMAL WHERE (DM19_FORMAL.`module_name`='comfy')


    SELECT * FROM DM19_FORMAL WHERE ('module_name' = 'comfy')

    Raw ActiveRecord#find gets the quoting right, with backticks.

    Given your definition of #sanitize above I tried:

    >> ActiveRecord::Base.connection.quote(:symbol)

    => "'--- :symbol\\n'"

    >> ActiveRecord::Base.connection.quote('string')

    => "'string'"

    So it may be that the fact that am fiddling with the database connection (due to my legacy database arrangement) is causing a problem.

    I'm not sure where to look next.

    Presumably quoting strings works OK in a 'standard' database arrangement?

    I'd revert to using symbols to identify column names but I have some columns that look like name1.name2 and they need to be quoted.

    As an aside, in the sanitize method you show, I see there's a when clause for a Regexp. I'm not familiar with the code but when would you see a Regexp on the left hand side of an expression that would need to be quoted?

  • Chris Wanstrath

    Chris Wanstrath October 9th, 2007 @ 02:14 PM

    Can you post some of the ActiveRecord code you had to write in order to get your legacy DB working? set_table_name and all that? Ideally I'd like to add it to our test suite -- no reason Ambition shouldn't work for you

    As for the Regexp, sanitize (I believe) is used on the right and left side of the WHERE condition. So if you're using MySQL, you want to turn /chris/i into 'chris'

  • Allan Cochrane

    Allan Cochrane October 9th, 2007 @ 04:00 PM

    On reflection, I'm not sure it's got much to do with changing table names, because that part seems to be correct:

    From script/console:

    >> Project.table_name

    => "ProjectsDB.projects"

    This just shows that I have an AR model defined:

    class Project < ActiveRecord::Base
      set_table_name "ProjectsDB.projects"

    Some experiments -

    >> Project.select{|z| z.column == 'value'}.to_sql

    => "SELECT * FROM ProjectsDB.projects WHERE ProjectsDB.projects.`column` = 'value'"

    The above shows that the table name is correctly used in the SQL.

    >> Project.select{:column == 'value'}.to_sql

    => "SELECT * FROM ProjectsDB.projects WHERE column = 'value'"

    The above shows the table name is still used, but not in the WHERE clause.

    >> Project.select{'column' == 'value'}.to_sql

    => "SELECT * FROM ProjectsDB.projects WHERE 'column' = 'value'"

    This shows that when I use a string to identify the column name it is interpreted as a string constant and not as a column name. My expectation was that there would be a 'column'.to_sym call (or maybe a :column.to_s call) somewhere along the line and identifying a column by string or symbol would be identical.


    As an aside, the syntax I'd really like to use is:

    Project.select{ associatedmodel.column == 'value' }

    Where the model looks like:

    class Project < ActiveRecord::Base
      has_many :associatedmodel

    Now I know that the select{} above doesn't work, so the syntax:

    Project.select{ 'associatedmodel.column' == 'value' }

    is a reasonable alternative.

    I would like to offer the syntax to users in a DSL. I feel that writing an expression like:

    select{ 'associatedmodel.column' == 'value' }

    is more natural to my users than asking them to write:

    select{|z| z.associatedmodel.column == 'value' }

  • Chris Wanstrath

    Chris Wanstrath November 13th, 2007 @ 02:14 PM

    • State changed from “open” to “hold”

    We're rolling a new gem soon, which may fix the problem... stay tuned...

  • Allan Cochrane

    Allan Cochrane March 7th, 2008 @ 03:26 AM

    I think the issue may have been solved:

    >> Project.select{'column' == 'value'}.to_sql
    => "SELECT * FROM ProjectsDB.projects WHERE column = 'value'"

    which shows that the column name, being passed in as a string, is correctly inserted into the SQL query.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Shared Ticket Bins

People watching this ticket