Rails Ruby on Rails → 3.x

Comments and changes to this ticket

• You flagged this item as spam.
  

  Pete Yandell June 22nd, 2009 @ 03:36 AM

  I'm seeing a similar error. I'm trying to come up with a minimal test to reproduce.

• You flagged this item as spam.
  

  Pete Yandell June 22nd, 2009 @ 04:03 AM

  A minimal test is attached, based on a production app in which I'm seeing this issue.

  The guts of it is this:

  # This succeeds.
  publisher.subscribers.tagged_with("a").all(:include => :tags)
  
  # This generates invalid SQL.
  publisher.subscribers.tagged_with("a.").all(:include => :tags)
  

  The tagged_with named scope looks like this:

  lambda {|tag| { :joins => :tags, :conditions => ["tags.name = ?", tag ] } }
  

  So, if you include a dot anywhere in the tag you're looking for, ActiveRecord starts spitting out bad SQL!

  The error is:

  SQLite3::SQLException: ambiguous column name: tags.id: SELECT "subscribers"."id" AS t0_r0, "subscribers"."publisher_id" AS t0_r1, "tags"."id" AS t1_r0, "tags"."name" AS t1_r1 FROM "subscribers"  LEFT OUTER JOIN "taggings" ON ("subscribers"."id" = "taggings"."subscriber_id")  LEFT OUTER JOIN "tags" ON ("tags"."id" = "taggings"."tag_id")   INNER JOIN "taggings" ON ("subscribers"."id" = "taggings"."subscriber_id")  INNER JOIN "tags" ON ("tags"."id" = "taggings"."tag_id")  WHERE (((tags.name = 'a.') AND ("subscribers".publisher_id = 1)) AND ("subscribers".publisher_id = 1))  (ActiveRecord::StatementInvalid)
  

  Same problem occurs on both MySQL and SQLite.

  If I just do Subscriber.tagged_with (i.e. leave out the parent relationship), the problem doesn't occur.

  • test.rb 1.1 KB

• You flagged this item as spam.
  

  Marcelo Serpa April 29th, 2010 @ 07:54 PM

  I have a similar problem, but what triggered it was an actual search string that included a dot.

  The problem lies on lib/active_record/associations.rb#tables_in_string, the following regexp:

  /([.a-zA-Z_]+).?./

  Just searches for anything that has a pattenr like string.string and returns the part before the dot. So, even if you have a search string of "%search." it will think the string "search" is actually a table name and try to join it.

  The solution to my specific problem would be to make the regexp more specific and to search ignore this pattern when preceded by % and followed by %, but since Ruby 1.8.7 doesn't support regexp lookahead or lookbehind, not sure what to do without impacting the API too much.

  I'll try to come up with something and get a patch up.

  Marcelo.

  • screen-shot-2010-04-29-at-14746-pm.png 326 KB

• You flagged this item as spam.
  

  Marcelo Serpa April 29th, 2010 @ 07:57 PM

  Another solution, at the application level, is to just sanitize dots from the string. In my case this would work, but not sure it would work for you guys. I think that we are not supposed to include dots in strings we provide to find's parameters. I can live with that, for now.

• 

  Jeremy Kemper May 4th, 2010 @ 06:48 PM

  • Milestone changed from “2.x” to “3.x”

  [bulk edit]

• 

  Rohit Arondekar October 9th, 2010 @ 04:07 AM

  • Importance changed from “” to “”

  Any updates here?

• You flagged this item as spam.
  

  David Bittencourt November 25th, 2010 @ 10:24 PM

  This is still an issue in 3.0.3, causing things like:

  Alpha.where(:name => 'foobar').includes(:bravo)
  

  To generate normal queries like this:

  SELECT "alphas".* FROM "alphas" WHERE ("alphas"."name" = 'foobar')
  SELECT "bravos".* FROM "bravos" WHERE ("bravos"."id" IN (...))
  

  While something like:

  Alpha.where(:name => 'foo.bar').includes(:bravo)
  

  To generate this join query:

  SELECT "alphas"."id" AS t0_r0, "alphas"."name" AS t0_r1, "alphas"."created_at" AS t0_r2, 
  "alphas"."updated_at" AS t0_r3, "alphas"."bravo_id" AS t0_r4, "bravos"."id" AS t1_r0, 
  "bravos"."some" AS t1_r1, "bravos"."other" AS t1_r2, "bravos"."created_at" AS t1_r3, 
  "bravos"."updated_at" AS t1_r4 FROM "alphas" LEFT OUTER JOIN "bravos" ON 
  "bravos"."id" = "alphas"."bravo_id" WHERE ("alphas"."name" = 'foo.bar')
  

  When you have more complex queries it can go bad very easily (performance-wise and exceptions) just because the user included a dot in their query.

  An example of a crashing issue is if the Bravo model is in a separate database. You can handle it fine in the first case as it does separate queries, but the second one will raise an SQL exception because the table "bravos" doesn't exist in Alpha model's database. Very bad that the user can break your application by just adding a dot in a string.

  Here are other open tickets about this issue:

  https://rails.lighthouseapp.com/projects/8994/tickets/3446
  https://rails.lighthouseapp.com/projects/8994/tickets/5200

  Sorry, but I don't know enough about Rails internals to propose a patch, just wanted to give an update as I found this issue today. I had to rewrite my code to not use includes in some queries to avoid this issue.

• 

  Rohit Arondekar November 28th, 2010 @ 12:23 AM

  • Assigned user set to “Neeraj Singh”
• 

  rails March 1st, 2011 @ 12:00 AM

  • Tag changed from “:include, :joins, activecord, named_scope” to “activecord, include, joins, named_scope”
  • State changed from “new” to “open”

  This issue has been automatically marked as stale because it has not been commented on for at least three months.

  The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

  Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

• 

  rails March 1st, 2011 @ 12:00 AM

  • State changed from “open” to “stale”