From a775cb190312edba8ef3feb6345ac446d7e8f113 Mon Sep 17 00:00:00 2001 From: David Heinemeier Hansson Date: Tue, 7 Dec 2004 21:14:20 +0000 Subject: Added the option for sanitizing find_by_sql and the offset parts in regular finds [Sam Stephenson] git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@75 5ecf4fe2-1ee6-0310-87b1-e25e094e27de --- activerecord/CHANGELOG | 5 +++++ activerecord/lib/active_record/base.rb | 13 ++++++++---- activerecord/test/finder_test.rb | 37 +++++++++++++++++++++++++++++++++- 3 files changed, 50 insertions(+), 5 deletions(-) diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG index eb8a906338..f4119046d5 100644 --- a/activerecord/CHANGELOG +++ b/activerecord/CHANGELOG @@ -1,5 +1,10 @@ *CVS* +* Added the option for sanitizing find_by_sql and the offset parts in regular finds [Sam Stephenson]. Examples: + + Project.find_all ["category = ?", category_name], "created ASC", ["? OFFSET ?", 15, 20] + Post.find_by_sql ["SELECT * FROM posts WHERE author = ? AND created > ?", author_id, start_date] + * Fixed value quoting in all generated SQL statements, so that integers are not surrounded in quotes and that all sanitation are happening through the database's own quoting routine. This should hopefully make it lots easier for new adapters that doesn't accept '1' for integer columns. diff --git a/activerecord/lib/active_record/base.rb b/activerecord/lib/active_record/base.rb index f52a1524d2..b876f83abb 100755 --- a/activerecord/lib/active_record/base.rb +++ b/activerecord/lib/active_record/base.rb @@ -274,21 +274,25 @@ module ActiveRecord #:nodoc: # Returns an array of all the objects that could be instantiated from the associated # table in the database. The +conditions+ can be used to narrow the selection of objects (WHERE-part), # such as by "color = 'red'", and arrangement of the selection can be done through +orderings+ (ORDER BY-part), - # such as by "last_name, first_name DESC". A maximum of returned objects can be specified in +limit+. Example: + # such as by "last_name, first_name DESC". A maximum of returned objects and their offset can be specified in + # +limit+ (LIMIT...OFFSET-part). Examples: # Project.find_all "category = 'accounts'", "last_accessed DESC", 15 + # Project.find_all ["category = ?", category_name], "created ASC", ["? OFFSET ?", 15, 20] def find_all(conditions = nil, orderings = nil, limit = nil, joins = nil) sql = "SELECT * FROM #{table_name} " sql << "#{joins} " if joins add_conditions!(sql, conditions) sql << "ORDER BY #{orderings} " unless orderings.nil? - sql << "LIMIT #{limit} " unless limit.nil? + sql << "LIMIT #{sanitize_conditions(limit)} " unless limit.nil? find_by_sql(sql) end - # Works like find_all, but requires a complete SQL string. Example: + # Works like find_all, but requires a complete SQL string. Examples: # Post.find_by_sql "SELECT p.*, c.author FROM posts p, comments c WHERE p.id = c.post_id" + # Post.find_by_sql ["SELECT * FROM posts WHERE author = ? AND created > ?", author_id, start_date] def find_by_sql(sql) + sql = sanitize_conditions(sql) connection.select_all(sql, "#{name} Load").inject([]) { |objects, record| objects << instantiate(record) } end @@ -360,6 +364,7 @@ module ActiveRecord #:nodoc: # Returns the result of an SQL statement that should only include a COUNT(*) in the SELECT part. # Product.count "SELECT COUNT(*) FROM sales s, customers c WHERE s.customer_id = c.id" def count_by_sql(sql) + sql = sanitize_conditions(sql) count = connection.select_one(sql, "#{name} Count").values.first return count ? count.to_i : 0 end @@ -1073,4 +1078,4 @@ module ActiveRecord #:nodoc: string[0..3] == "--- " end end -end \ No newline at end of file +end diff --git a/activerecord/test/finder_test.rb b/activerecord/test/finder_test.rb index cc240c8acc..721ad76d56 100755 --- a/activerecord/test/finder_test.rb +++ b/activerecord/test/finder_test.rb @@ -1,11 +1,13 @@ require 'abstract_unit' require 'fixtures/company' require 'fixtures/topic' +require 'fixtures/entrant' class FinderTest < Test::Unit::TestCase def setup @company_fixtures = create_fixtures("companies") @topic_fixtures = create_fixtures("topics") + @entrant_fixtures = create_fixtures("entrants") end def test_find @@ -23,6 +25,20 @@ class FinderTest < Test::Unit::TestCase } end + def test_find_all_with_limit + entrants = Entrant.find_all nil, "id ASC", 2 + + assert_equal(2, entrants.size) + assert_equal(@entrant_fixtures["first"]["name"], entrants.first.name) + end + + def test_find_all_with_prepared_limit_and_offset + entrants = Entrant.find_all nil, "id ASC", ["? OFFSET ?", 2, 1] + + assert_equal(2, entrants.size) + assert_equal(@entrant_fixtures["second"]["name"], entrants.first.name) + end + def test_find_with_entire_select_statement topics = Topic.find_by_sql "SELECT * FROM topics WHERE author_name = 'Mary'" @@ -30,6 +46,13 @@ class FinderTest < Test::Unit::TestCase assert_equal(@topic_fixtures["second"]["title"], topics.first.title) end + def test_find_with_prepared_select_statement + topics = Topic.find_by_sql ["SELECT * FROM topics WHERE author_name = ?", "Mary"] + + assert_equal(1, topics.size) + assert_equal(@topic_fixtures["second"]["title"], topics.first.title) + end + def test_find_first first = Topic.find_first "title = 'The First Topic'" assert_equal(@topic_fixtures["first"]["title"], first.title) @@ -71,4 +94,16 @@ class FinderTest < Test::Unit::TestCase assert_not_equal "'something ' 1=1'", ActiveRecord::Base.sanitize("something ' 1=1") assert_equal "'something; select table'", ActiveRecord::Base.sanitize("something; select table") end -end \ No newline at end of file + + def test_count + assert_equal(0, Entrant.count("id > 3")) + assert_equal(1, Entrant.count(["id > ?", 2])) + assert_equal(2, Entrant.count(["id > ?", 1])) + end + + def test_count_by_sql + assert_equal(0, Entrant.count_by_sql("SELECT COUNT(*) FROM entrants WHERE id > 3")) + assert_equal(1, Entrant.count_by_sql(["SELECT COUNT(*) FROM entrants WHERE id > ?", 2])) + assert_equal(2, Entrant.count_by_sql(["SELECT COUNT(*) FROM entrants WHERE id > ?", 1])) + end +end -- cgit v1.2.3