The newest release 1.0.11 seems has problem #162

Closed
libin0120 opened this Issue Sep 5, 2012 · 16 comments

2 participants

@libin0120

It will make active_record generated SQL syntax missing quotes. For instance:

A revised example error message are like:


A ActiveRecord::StatementInvalid occurred in sessions#create:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@mydomain.com LIMIT 1' at line 1: SELECT users.* FROM users WHERE users.removed = 0 AND users.email = myname@mydomain.com LIMIT 1

activerecord (3.1.8) lib/active_record/connection_adapters/mysql2_adapter.rb:701:in `query'

you will see there is no quote around email address.

The app is using latest devise for authentication.

@ernie
ActiveRecord Hackery member

Did 1.0.9 work properly for you?

@ernie
ActiveRecord Hackery member

@libin0120 I tried to duplicate this using ActiveRecord 3.1.8 and was unsuccessful. Things I tried:

Person.where(:id => 1, :name => 'Ernie Miller').to_sql
=> "SELECT \"people\".* FROM \"people\"  WHERE ((\"people\".\"id\" = 1 AND \"people\".\"name\" = 'Ernie Miller'))" 

class Person
  scope :id_is_1, where(:id => 1)
end

Person.id_is_1.where(:name => 'Ernie Miller').to_sql
=> "SELECT \"people\".* FROM \"people\"  WHERE \"people\".\"id\" = 1 AND \"people\".\"name\" = 'Ernie Miller'" 

Can you provide:

  1. A failing spec, or
  2. A small sample application that exhibits the problem?
@libin0120
@ernie
ActiveRecord Hackery member

OK. If that's the case. Try this:

  1. Open up your copy of the Squeel gem, and edit the file lib/squeel/visitors/visitor.rb (you can find this file's location via bundle show squeel
  2. Remove line 171: alias :visit_String :visit_passthrough
  3. Save the file, and reload your application.

Does the problem go away?

@libin0120
@ernie
ActiveRecord Hackery member

Do you encounter this issue outside of the query that Devise issues to authenticate? I'm trying to narrow down how this is happening. A simple, self-contained query that always fails is important for me to more quickly track down the issue.

@ernie
ActiveRecord Hackery member

Also, what version of ARel are you using?

@libin0120
@libin0120
@ernie
ActiveRecord Hackery member
@ernie
ActiveRecord Hackery member

OK, I've set up a bare bones Rails app to test with.

Gemfile:

source 'http://rubygems.org'

gem 'rails', '3.1.8'
gem 'squeel'
gem 'devise'

gem 'mysql2'

group :assets do
  gem 'sass-rails',   '~> 3.1.5'
  gem 'coffee-rails', '~> 3.1.1'
  gem 'uglifier', '>= 1.0.3'
end

gem 'jquery-rails'

group :test do
  gem 'turn', '~> 0.8.3', :require => false
end

Log Output:

Started POST "/users/sign_in" for 127.0.0.1 at 2012-09-05 18:00:49 -0400
  Processing by Devise::SessionsController#create as HTML
  Parameters: {"utf8"=>"✓", "authenticity_token"=>"0KDTvpMCbK5cGWHswVgc49ElvQ3c1wtczp5w+TkOvig=", "user"=>{"email"=>"ernie@erniemiller.org", "password"=>"[FILTERED]", "remember_me"=>"0"}, "commit"=>"Sign in"}
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`email` = 'ernie@erniemiller.org' LIMIT 1

It's pretty clear to me that your issue is a conflict with either another gem, or something you're doing in your scopes. If you can provide any more details about what plugin or default_scope you're using for your user.removed condition, that'll help.

@libin0120
@ernie
ActiveRecord Hackery member

Could you send me your project's Gemfile? Might be something there that will give me a clue.

@ernie
ActiveRecord Hackery member

Tried with a default scope to try and reproduce as well, with no luck.

If you can send along your Gemfile and any default scope you're setting on your user model, I'll see what I can do, but otherwise I'm thinking this isn't a Squeel issue. Please do keep me updated on what you find, though!

@libin0120

Update:

After change back to 1.0.11, same issue happens when testing from Rails console:

irb(main):007:0> User.find_by_email('abc@abc.com')
User Load (36.5ms) SELECT users.* FROM users WHERE users.removed = 0 AND users.email = abc@abc.com LIMIT 1
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@abc.com LIMIT 1' at line 1: SELECT users.* FROM users WHERE users.removed = 0 AND users.email = abc@abc.com LIMIT 1
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/connection_adapters/mysql2_adapter.rb:687:in query'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/connection_adapters/mysql2_adapter.rb:687:in
exec_query'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:244:in log'
from /usr/lib/ruby/gems/1.8/gems/activesupport-3.1.3/lib/active_support/notifications/instrumenter.rb:21:in
instrument'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:239:in log'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/connection_adapters/mysql2_adapter.rb:685:in
exec_query'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/connection_adapters/mysql2_adapter.rb:679:in select'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in
select_all'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in select_all'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/base.rb:470:in
find_by_sql'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/relation.rb:111:in to_a'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/relation.rb:129:in
to_a'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/relation/finder_methods.rb:376:in find_first'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/relation/finder_methods.rb:122:in
first'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/relation/finder_methods.rb:263:in send'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/relation/finder_methods.rb:263:in
find_by_attributes'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/base.rb:1063:in send'
from /usr/lib/ruby/gems/1.8/gems/activerecord-3.1.3/lib/active_record/base.rb:1063:in
method_missing'

from (irb):7irb(main):008:0>

Here is the GemFile

Using rake (0.9.2.2)
Using multi_json (1.3.6)
Using activesupport (3.1.3)
Using builder (3.0.0)
Using i18n (0.6.1)
Using activemodel (3.1.3)
Using erubis (2.7.0)
Using rack (1.3.6)
Using rack-cache (1.2)
Using rack-mount (0.8.3)
Using rack-test (0.6.1)
Using hike (1.2.1)
Using tilt (1.3.3)
Using sprockets (2.0.4)
Using actionpack (3.1.3)
Using mime-types (1.18)
Using polyglot (0.3.3)
Using treetop (1.4.10)
Using mail (2.3.3)
Using actionmailer (3.1.3)
Using active_utils (1.0.3)
Using braintree (2.13.4)
Using money (3.7.1)
Using activemerchant (1.20.3)
Using arel (2.2.3)
Using tzinfo (0.3.33)
Using activerecord (3.1.3)
Using activerecord-import (0.2.9)
Using activeresource (3.1.3)
Using acts_as_tree (0.1.1)
Using american_date (1.0.0)
Using xml-simple (1.1.1)
Using aws-s3 (0.6.2)
Using multi_xml (0.4.1)
Using httparty (0.8.1)
Using json (1.7.3)
Using nokogiri (1.5.2)
Using uuidtools (2.1.2)
Using aws-sdk (1.3.9)
Using bartt-ssl_requirement (1.4.1)
Using bcrypt-ruby (3.0.1)
Using bitmask_attributes (0.3.0)
Using blankslate (2.1.2.4)
Using bootstrap-sass (2.0.4.0)
Using browser (0.1.4)
Using bundler (1.0.0)
Using cancan (1.6.7)
Using rack-ssl (1.3.2)
Using rdoc (3.12)
Using thor (0.14.6)
Using railties (3.1.3)
Using chosen-rails (0.9.8.1)
Using chronic (0.6.7)
Using cocaine (0.2.1)
Using cocoon (1.0.22)
Using coffee-script-source (1.2.0)
Using execjs (1.3.0)
Using coffee-script (2.2.0)
Using coffee-rails (3.1.1)
Using country_select (1.0.0)
Using curb (0.8.1)
Using default_value_for (1.0.7)
Using orm_adapter (0.0.7)
Using warden (1.1.1)
Using devise (2.1.0)
Using dynamic_form (1.1.4)
Using exception_notification (2.5.2)
Using loofah (1.2.0)
Using sax-machine (0.1.0)
Using feedzirra (0.0.24)
Using sass (3.1.15)
Using sass-rails (3.1.5)
Using font-awesome-sass-rails (2.0.0.0)
Using geocoder (1.1.1)
Using geonames (0.2.2)
Using has_scope (0.5.1)
Using responders (0.6.5)
Using inherited_resources (1.3.0)
Using jbuilder (0.4.3)
Using jquery-rails (1.0.19)
Using jquery-ui-rails (1.0.0)
Using libv8 (3.3.10.4)
Using meta-tags (1.2.6)
Using mysql2 (0.3.11)
Using paperclip (2.5.2)
Using pdfkit (0.5.2)
Using polyamorous (0.5.0)
Using rack-rewrite (1.2.1)
Using rails (3.1.3)
Using ransack (0.6.0)
Using show_for (0.2.5)
Using simple_form (2.0.2)
Using squeel (1.0.11)
Using tabs_on_rails (2.1.1)
Using therubyracer (0.10.2)
Using typus (3.1.10)
Using uglifier (1.2.3)
Using validates_url_format_of (0.3.0)
Using whenever (0.7.2)
Using will_paginate (3.0.3)
Using ya2yaml (0.30)

@libin0120

Sorry, think this is the Gemfile you are looking for:

# ---- Source ----
source 'http://rubygems.org'

# ---- Rails ----
gem 'rails' 

# --- Libraries ---
gem 'mysql2'

gem 'inherited_resources', '~> 1.3.0'
gem 'has_scope'
gem 'cancan' # , :git => 'git://github.com/Sija/cancan.git', :branch => 'squeel'

gem 'devise'
gem 'paperclip', '2.5.2'
# gem 'geokit', '1.5'
# gem 'geokit-rails3'
gem 'geocoder', '1.1.1'   

# payment
gem 'activemerchant'

# CMS
gem 'typus'

gem 'will_paginate'
gem 'ransack'
#gem 'meta_search'

gem 'squeel',  '1.0.9' 

# Views
gem 'dynamic_form'
gem 'simple_form' #, :git => 'git://github.com/plataformatec/simple_form.git'
gem 'show_for'
gem "tabs_on_rails"
gem "cocoon"  # For embeded form
gem 'meta-tags', :require => 'meta_tags' # SEO
gem 'country_select'
gem 'jbuilder'
gem 'chosen-rails'

# Model
gem "default_value_for"
gem "validates_url_format_of"
gem 'american_date'
gem 'bitmask_attributes'

# Support
gem 'ya2yaml'
gem 'pdfkit'
gem "activerecord-import", '>= 0.2.0'
gem 'exception_notification', :require => 'exception_notifier' # For exception email notification
gem 'geonames'
gem 'acts_as_tree', '0.1.1'
gem 'aws-s3' 
gem 'aws-sdk', '~> 1.3.4'
gem "browser"

# will replace it after Rails 3.1
gem 'bartt-ssl_requirement', :require => 'ssl_requirement' # For SSL support
gem 'whenever', :require => false

# Tools
gem 'httparty'
group :production do
  gem 'therubyracer', :platforms => :ruby
end

# Rack
gem 'rack-rewrite'

gem 'feedzirra'
gem 'curb'
gem 'nokogiri'

# --- API ---


# --- Rails 3.1 & Assets ---
# Gems used only for assets and not required
# in production environments by default.
group :assets do
  gem 'sass-rails', " ~> 3.1.0"
  gem 'coffee-rails', " ~> 3.1.0"
  gem 'uglifier'
  gem 'bootstrap-sass', '~> 2.0.4.0'
  gem 'jquery-ui-rails'
  gem 'font-awesome-sass-rails'
end

gem 'jquery-rails'
@ernie ernie added a commit that closed this issue Oct 7, 2012
@ernie ernie Remove a passthrough for String.
Should be unnecessary to remove this, but this fixes #162 and I honestly
don't remember why I added the passthrough to begin with. ARel handles
quoting/escaping on String so passing it through from Squeel shouldn't
have an effect. Since no specs fail when I remove the line, I'm gonna
give myself a good slap on the wrist for writing code that isn't
justified by specs, remove the line, and close #162 in good conscience.
bd8d604
@ernie ernie closed this in bd8d604 Oct 7, 2012
@ernie ernie added a commit that referenced this issue Oct 7, 2012
@ernie ernie Remove a passthrough for String.
Should be unnecessary to remove this, but this fixes #162 and I honestly
don't remember why I added the passthrough to begin with. ARel handles
quoting/escaping on String so passing it through from Squeel shouldn't
have an effect. Since no specs fail when I remove the line, I'm gonna
give myself a good slap on the wrist for writing code that isn't
justified by specs, remove the line, and close #162 in good conscience.
a518a99
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment