Rob Biedenharn
2006-Jun-30 15:32 UTC
[Rails] find_by_sql not quoting properly (in acts_as_taggable plugin)
I have run into a very strange problem discovered through the use of
the acts_as_taggable plugin, but related to quoting/sanitizing the
interpolated list in a find_by_sql. Apologies for the length, but I
wanted to be complete. ;-)
The method from acts_as_taggable.rb is:
def find_tagged_with(list)
find_by_sql(["SELECT #{table_name}.* FROM #{table_name},
tags, taggings " +
"WHERE #{table_name}.#{primary_key} =
taggings.taggable_id " +
"AND taggings.taggable_type = ''#
{acts_as_taggable_options[:taggable_type]}'' " +
"AND taggings.tag_id = tags.id AND tags.name
IN (?)", list]
)
end
Originally, this built a string directly to pass to find_by_sql, but
I changed it thusly:
trunk/vendor/plugins/acts_as_taggable/lib/acts_as_taggable.rb
r13 r127
25 25 module SingletonMethods
26 26 def find_tagged_with(list)
27 find_by_sql(
28 "SELECT #{table_name}.* FROM #{table_name}, tags,
taggings " +
29 "WHERE #{table_name}.#{primary_key} =
taggings.taggable_id " +
30 "AND taggings.taggable_type = ''#
{acts_as_taggable_options[:taggable_type]}'' " +
31 "AND taggings.tag_id = tags.id AND tags.name IN (#
{list.collect { |name| "''#{name}''" }.join(",
")})"
27 find_by_sql(["SELECT #{table_name}.* FROM #
{table_name}, tags, taggings " +
28 "WHERE #{table_name}.#{primary_key} =
taggings.taggable_id " +
29 "AND taggings.taggable_type = ''#
{acts_as_taggable_options[:taggable_type]}'' " +
30 "AND taggings.tag_id = tags.id AND
tags.name IN (?)", list]
32 31 )
33 32 end
As you can see, if list.any? {|t| t.include?("''")} the
resulting
string could have a bad set of single quotes.
And, of course, on my development machine it fixed the problem
wonderfully:
$ uname -mpsrv ; mysql --version ; rails --version
Darwin 8.7.1 Darwin Kernel Version 8.7.1: Wed Jun 7 16:19:56 PDT
2006; root:xnu-792.9.72.obj~2/RELEASE_I386 i386 i386
mysql Ver 14.7 Distrib 4.1.18, for apple-darwin8.3.1 (i686) using
readline 4.3
Rails 1.1.2
And rails is frozen:
rab:trunk $ svn info vendor/rails/
Path: vendor/rails
URL: http://dev.rubyonrails.org/svn/rails/tags/rel_1-1-2
Repository Root: http://dev.rubyonrails.org/svn/rails
Repository UUID: 5ecf4fe2-1ee6-0310-87b1-e25e094e27de
Revision: 4515
Node Kind: directory
Schedule: normal
Last Changed Author: david
Last Changed Rev: 4205
Last Changed Date: 2006-04-09 18:15:39 -0400 (Sun, 09 Apr 2006)
Properties Last Updated: 2006-06-29 21:21:10 -0400 (Thu, 29 Jun 2006)
Now we move to production and expect the same behavior -- after all,
the platforms are similar, right?
$ uname -mpsrv ; mysql --version ; rails --version
FreeBSD 5.4-STABLE FreeBSD 5.4-STABLE #2: Tue Sep 6 04:42:26 GMT
2005 root@jervis.textdrive.com:/usr/obj/usr/src/sys/TEXTDRIVEN
i386 i386
mysql Ver 14.7 Distrib 4.1.18, for portbld-freebsd5.4 (i386) using 4.3
Rails 1.1.2
However, these are the snippets from the development.log on my Mac
and the production.log on TextDrive:
Processing TagController#index (for 127.0.0.1 at 2006-06-30 10:46:46)
[GET]
Session ID: 63f13e4e972204fd2e0021949cb25901
Parameters: {"action"=>"index",
"id"=>"don''t",
"controller"=>"tag"}
Tag Columns (0.000270) SHOW FIELDS FROM tags
Tag Load (0.000681) SELECT * FROM tags WHERE (tags.`name` = ''don
\''t'' ) LIMIT 1
Post Load (0.011631) SELECT posts.* FROM posts, tags, taggings
WHERE posts.id = taggings.taggable_id AND taggings.taggable_type
= ''Post'' AND taggings.tag_id = tags.id AND tags.name IN
(''don\''t'')
Post Columns (0.000269) SHOW FIELDS FROM posts
Rendering within layouts/application
Rendering tag/index
Note the "IN (''don\''t'')" in the SQL, but in
production.log:
Processing TagController#index (for xxx.xxx.xxx.xxx at 2006-06-30
14:54:57) [GET]
Session ID: d07b7fa700bb02bdffb15639505200b6
Parameters: {"action"=>"index",
"id"=>"don''t",
"controller"=>"tag"}
ActiveRecord::StatementInvalid (Mysql::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 ''t'')''
at line 1:
SELECT posts.* FROM posts, tags, taggings WHERE posts.id = taggings
.taggable_id AND taggings.taggable_type = ''Post'' AND
taggings.tag_id
= tags.id AND tags.name IN (''don''t'')):
/vendor/rails/activerecord/lib/active_record/connection_adapters/
abstract_adapter.rb:120:in `log''
/vendor/rails/activerecord/lib/active_record/connection_adapters/
mysql_adapter.rb:185:in `execute''
/vendor/rails/activerecord/lib/active_record/connection_adapters/
mysql_adapter.rb:337:in `select''
/vendor/rails/activerecord/lib/active_record/connection_adapters/
mysql_adapter.rb:176:in `select_all''
/vendor/rails/activerecord/lib/active_record/base.rb:390:in
`find_by_sql''
/vendor/plugins/acts_as_taggable/lib/acts_as_taggable.rb:31:in
`find_tagged_with''
/app/controllers/tag_controller.rb:15:in `index''
/vendor/rails/actionpack/lib/action_controller/base.rb:910:in
`perform_action_without_filters''
/vendor/rails/actionpack/lib/action_controller/filters.rb:368:in
`perform_action_without_benchmark''
/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:
69:in `perform_action_without_rescue''
/usr/local/lib/ruby/1.8/benchmark.rb:293:in `measure''
/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:
69:in `perform_action_without_rescue''
/vendor/rails/actionpack/lib/action_controller/rescue.rb:82:in
`perform_action''
/vendor/rails/actionpack/lib/action_controller/base.rb:381:in
`process_without_filters''
/vendor/rails/actionpack/lib/action_controller/filters.rb:377:in
`process_without_session_management_support''
/vendor/rails/actionpack/lib/action_controller/
session_management.rb:117:in `process''
/vendor/rails/railties/lib/dispatcher.rb:38:in `dispatch''
/vendor/rails/railties/lib/fcgi_handler.rb:150:in
`process_request''
/vendor/rails/railties/lib/fcgi_handler.rb:54:in `process!''
/usr/local/lib/ruby/gems/1.8/gems/fcgi-0.8.6.1/./fcgi.rb:600:in
`each_cgi''
/usr/local/lib/ruby/gems/1.8/gems/fcgi-0.8.6.1/./fcgi.rb:597:in
`each_cgi''
/vendor/rails/railties/lib/fcgi_handler.rb:53:in `process!''
/vendor/rails/railties/lib/fcgi_handler.rb:23:in `process!''
/users/home/xxxxx-xxxxx/xxxxx/xxxxx.xxxxx/web/public/
dispatch.fcgi:24
exception occured: Mysql::Error: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server versio
n for the right syntax to use near ''t'')'' at line 1:
SELECT posts.*
FROM posts, tags, taggings WHERE posts.id = taggings.taggable_id
AND taggings.taggable_type = ''Post'' AND taggings.tag_id =
tags.id AND
tags.name IN (''don''t'')
Rendering status500layoutfalsefile/users/home/xxxxx-xxxxx/xxxxx/
xxxxx.xxxxx/web/public/../config/../public/500.html within layouts/
application
Rendering /users/home/xxxxx-xxxxx/xxxxx/xxxxx.xxxxx/web/public/../
config/../public/500.html (500)
BAM! See the "IN (''don''t'')", I know
that''s bad, but all the code is
the *same* so why isn''t it being quoted properly??
I''ve trolled down into active_record/base.rb and the call tree: (dive
in yourself if you prefer...)
find_by_sql(sql)
sanitize_sql(sql)
statement, *values = sql
replace_bind_variables(statement, values)
bound = values.dup
statement.gsub(''?'') {
quote_bound_value(bound.shift) }
if (value.respond_to?
(:map) && !value.is_a?(String))
value.map { |v|
connection.quote(v) }.join('','')
else
connection.quote(value)
end
looks like the tag or tags in list will be quoted whether
"don''t" or
["don''t"] (with the same result, too).
Does this make sense to anyone? If it worked the same on both
systems, I''d think that there was an extra interpolation of the sql
that turns "\''" into "''" (thus undoing the
sanitize_sql and quote work).
-Rob
Rob Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com
+1 513-295-4739
Reasonably Related Threads
Wisdom of the Ancients
