johnpmitsch

def self.find_by_version(key, operator, value)
      major, minor = value.to_s.split('.')
      if minor.nil? or /[<>]/ =~ operator
        query = where("major #{operator} ?", major)
      else
        query = where("major #{operator} ? and minor #{operator} ?", major, minor)
      end
      _, conditions = query.to_sql.split("WHERE")
      { :conditions => conditions }
    end

johnpmitsch

2015-05-29 09:28:13 [I] Completed 500 Internal Server Error in 17.2ms (Views: 0.3ms | ActiveRecord: 1.6ms)
2015-05-29 09:28:13 [D] With body: {"displayMessage":"PG::SyntaxError: ERROR:  syntax error at or near \"'4'\"\nLINE 1: ...ent_views\".\"id\" IN (1, 2, 4, 3)) AND (( (major IN '4' and mi...\n                                                             ^\n: SELECT COUNT(*) FROM \"katello_content_view_versions\"  WHERE \"katello_content_view_versions\".\"id\" IN (SELECT \"katello_content_view_versions\".\"id\" FROM \"katello_content_view_versions\" INNER JOIN \"katello_content_views\" ON \"katello_content_views\".\"id\" = \"katello_content_view_versions\".\"content_view_id\" WHERE \"katello_content_views\".\"id\" IN (1, 2, 4, 3)) AND (( (major IN '4' and minor IN '0')))","errors":["PG::SyntaxError: ERROR:  syntax error at or near \"'4'\"\nLINE 1: ...ent_views\".\"id\" IN (1, 2, 4, 3)) AND (( (major IN '4' and mi...\n                                                             ^\n: SELECT COUNT(*) FROM \"ka

johnpmitsch

2015-05-29 09:28:13 [I] Completed 500 Internal Server Error in 17.2ms (Views: 0.3ms | ActiveRecord: 1.6ms)
2015-05-29 09:28:13 [D] With body: {"displayMessage":"PG::SyntaxError: ERROR:  syntax error at or near \"'4'\"\nLINE 1: ...ent_views\".\"id\" IN (1, 2, 4, 3)) AND (( (major IN '4' and mi...\n                                                             ^\n: SELECT COUNT(*) FROM \"katello_content_view_versions\"  WHERE \"katello_content_view_versions\".\"id\" IN (SELECT \"katello_content_view_versions\".\"id\" FROM \"katello_content_view_versions\" INNER JOIN \"katello_content_views\" ON \"katello_content_views\".\"id\" = \"katello_content_view_versions\".\"content_view_id\" WHERE \"katello_content_views\".\"id\" IN (1, 2, 4, 3)) AND (( (major IN '4' and minor IN '0')))","errors":["PG::SyntaxError: ERROR:  syntax error at or near \"'4'\"\nLINE 1: ...ent_views\".\"id\" IN (1, 2, 4, 3)) AND (( (major IN '4' and mi...\n                                                             ^\n: SELECT COUNT(*) FROM \"ka

johnpmitsch

# takes something like key="version" operator="<=" value="1.3" 

def self.find_by_version(key, operator, value)
      major, minor = value.split('.')
      major ||= 0
      if /[<>]/ =~ operator
        minor ||= 0
        query = where("major #{operator} :major OR (major = :major AND minor #{operator} :minor)", :major => major, :minor => minor)
      else
        if minor.nil?

johnpmitsch

def find_by_version(_key, operator, value)
  major, minor = value.split('.')
  case
  when value.to_f < 1
    query = {}
  when /[<>]/ =~ operator
    minor ||= 0
    query = "major #{operator} :major OR (major = :major AND minor #{operator} :minor)"
  when minor.nil?
    query = "major #{operator} (?)"

johnpmitsch

This query works,

Model.where("major #{operator} :major OR (major = :major AND minor #{operator} :minor)", :major => major, :minor => minor)

but I am worried about sql injection passing an escaped string

so I tried this

`Model.where("major :operator :major OR (major = :major AND minor :operator :minor)", :major => major, :minor => minor, :operator => :operator)` 

johnpmitsch

    def self.find_by_version(_key, operator, value)
      if ['>', '<', '=', '<=', '>=', '^', 'IN', 'NOT IN'].include?(operator) && value.to_f >= 1
        major, minor = value.split(".")
        case
        when /[<>]/ =~ operator
          minor ||= 0
          query = where("major #{operator} :major OR (major = :major AND minor #{operator} :minor)", :major => major, :minor => minor)
        when minor.nil?
          query = where("major #{operator} (:major)", :major => major)
        else

johnpmitsch

: SELECT COUNT(*) FROM "katello_content_view_versions"  WHERE "katello_content_view_versions"."id" IN (SELECT DISTINCT "katello_content_view_versions"."id" FROM "katello_content_view_versions" INNER JOIN "katello_content_views" ON "katello_content_views"."id" = "katello_content_view_versions"."content_view_id" WHERE "katello_content_views"."id" IN (30237652, 405955044, 282203774, 909058347, 419082953, 762379426, 1058174566, 566851579, 64601712) AND "katello_content_view_versions"."content_view_id" = 419082953 ORDER BY major DESC)
2015-06-03 16:34:21 [E] ActiveRecord::StatementInvalid: PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ..._versions"."content_view_id" = 419082953 ORDER BY major DESC...

johnpmitsch

def index
      includes = [:content_view, :environments, :composite_content_views, :history => :task]
      sort = "#{ContentViewVersion.table_name}.major desc, #{ContentViewVersion.table_name}.minor desc"
      respond(:collection => scoped_search(index_relation.uniq, sort, '', :includes => includes))
    end

    def index_relation
      version_number = params.permit(:version)[:version]
      versions = ContentViewVersion.readable
      versions = versions.where(:content_view_id => @view.id) if @view

johnpmitsch

[jomitsch@john katello-deploy{master}]$ vagrant up centos7-devel
Bringing machine 'centos7-devel' up with 'libvirt' provider...
==> centos7-devel: Creating image (snapshot of base box volume).
==> centos7-devel: Creating domain with the following settings...
==> centos7-devel:  -- Name:              katello-deploy_centos7-devel
==> centos7-devel:  -- Domain type:       kvm
==> centos7-devel:  -- Cpus:              2
==> centos7-devel:  -- Memory:            3560M
==> centos7-devel:  -- Base box:          centos7_1
==> centos7-devel:  -- Storage pool:      default