Squeel audit components

thingy.rb

class Thingy < ActiveRecord::Base
  attr_accessible :name, :inty

  scope :name_eql_sqli,    lambda { |param| where("name = #{param}") }
  scope :name_eql_no_sqli, lambda { |param| where("name = ?", param) }
  scope :name_eql_squeeli, lambda { |param| where{name == param}     }

  scope :inty_eql_squeeli, lambda { |param| where{inty == param} }

  scope :name_like_sqli,    lambda { |param| where("name LIKE '%#{param}%'")    }
  scope :name_like_no_sqli, lambda { |param| where("name LIKE ?", "%#{param}%") }
  scope :name_like_squeeli, lambda { |param| where{name =~ param}               }

  def self.arel_name_like(match)
    thingies = Thingy.arel_table
    Thingy.where(thingies[:name].matches("%#{match}%"))
  end
end

thingy_postgresql_spec.rb

require 'spec_helper'

describe Thingy do
  before :all do
    ActiveRecord::Base.establish_connection(ActiveRecord::Base.configurations['test-postgresql'])
    Thingy.create(name: "first")
    Thingy.create(name: "second")
  end

  after :all do
    Thingy.delete_all
  end

  context "scopes" do
    describe "name equality scopes" do
      # scope :name_eql_sqli,    lambda { |param| where("name = #{param}") }
      # scope :name_eql_no_sqli, lambda { |param| where("name = ?", param) }
      # scope :name_eql_squeeli, lambda { |param| where{name == param}     }

      let(:query_string) { "'' OR '1'='1'" }

      it { Thingy.all.count.should == 2 }
      it { Thingy.name_eql_sqli(query_string).count.should eql 2 }
      it { Thingy.name_eql_no_sqli(query_string).count.should eql 0 }

      describe "generic SQLi" do
        it do
          scope = Thingy.name_eql_sqli("'first' OR \"name\"<>'')--'")
          puts scope.to_sql
          scope.length.should eql 2
        end

        it "will pass UNIONS to get all objects" do
          query_string = "'') UNION (SELECT \"thingies\".* FROM \"thingies\" WHERE ('1'='1')"
          scope = Thingy.name_eql_sqli(query_string)
          puts scope.to_sql
          scope.length.should eql 2
        end
      end

      describe "SQueeLi" do
        it do
          query_string = %q(\\' OR "name"<>'--)
          scope = Thingy.name_eql_squeeli(query_string)
          puts scope.to_sql
          scope.length.should eql 2
        end

        it "will pass UNIONS to get all objects" do
          query_string = %q/\\' UNION (SELECT "thingies".* FROM "thingies" WHERE "name"<>')--/
          scope =  Thingy.name_eql_squeeli(query_string)
          puts scope.to_sql
          scope.length.should eql 2
        end
      end
    end

    describe "multiple queries" do
      it "should not execute multiple queries" do 
        query_string = "'first'); SELECT * FROM \"thingies\" WHERE ('1'='1'"
        scope = Thingy.name_eql_sqli(query_string)
        puts scope.to_sql
        scope.length.should eql 2
        Thingy.all.length.should eql 2
      end
    end

    describe "name like scopes" do
      # scope :name_like_sqli,    lambda { |param| where("name LIKE '%#{param}%'") }
      # scope :name_like_no_sqli, lambda { |param| where("name LIKE '%?%'", param) }
      # scope :name_like_squeeli, lambda { |param| where{name =~ param}            }

      it { Thingy.all.count.should == 2 }
      it { Thingy.name_like_sqli("foo%' OR '1'='1' OR name LIKE '%").count.should == 2}

      it do
        scope = Thingy.name_like_no_sqli("foo%' OR '1'='1' OR name LIKE '%")
        puts scope.to_sql
        scope.length.should == 0
      end

      it do
        scope = Thingy.name_like_no_sqli("%\\' OR \"thingies\".\"name\" <> '--")
        puts scope.to_sql
        scope.length.should == 0
      end

      it do
        scope = Thingy.name_like_squeeli("%\\' OR \"thingies\".\"name\" <> '--")
        puts scope.to_sql
        scope.length.should == 2
      end

      it do
        scope = Thingy.name_like_no_sqli("%%")
        puts scope.to_sql
        scope.length.should == 0
      end

      it do
        thingies = Thingy.arel_name_like("%%")
        puts thingies.to_sql
        thingies.length.should == 0
      end

      it { Thingy.name_like_squeeli("%%").length.should == 2 }
    end
  end
end