ColumnSifter

ColumnSifter provides SQL-like column filtering for admin screens: it turns a WHERE-like expression string into a SQL-injection-safe ActiveRecord where condition.

Admin screen filtering articles with a SQL-like query

User input is run through a custom lexer → parser → compiler, and the condition is assembled so that:

  • values → passed via bind placeholders (?)
  • column names → only canonical names matched against a whitelist (searchable_columns) are emitted
  • operators → fixed tokens only

so no raw input string leaks into the SQL.

Installation

# Gemfile
gem 'column_sifter'

Usage

Subclass ColumnSifter::Base and declare the columns you allow filtering on with searchable_columns. The target model is resolved from the class name with the Sifter suffix stripped (e.g. ArticleSifter → Article).

# app/column_sifters/article_sifter.rb
class ArticleSifter < ColumnSifter::Base
  searchable_columns(:id, :author_id, :status, :published_at)
end

If the model can't be resolved from the class name (different name, namespaced, etc.), declare it explicitly with model:

class ArticleSifter < ColumnSifter::Base
  model BlogPost
  searchable_columns(:id, :author_id, :status, :published_at)
end

Instantiate the sifter, pass the expression to sift!, and use the result to build the query:

class ArticlesController < ApplicationController
  def index
    @sifter = ArticleSifter.new
    @sifter.sift!(params[:sift_query])

    @articles =
      if @sifter.error.blank?
        Article.where(@sifter.sql_template, *@sifter.binds)
      else
        Article.none
      end
  end
end

sift! is a bang method that mutates the instance, storing the result internally. You can then read:

  • sql_template … the SQL fragment with placeholders
  • binds … the array of values for the placeholders
  • input … the given input string
  • error … the parse error message (nil on success)

A search form for the expression:

<%= form_with url: articles_path, method: :get do %>
  <%= text_field_tag :sift_query, @sifter&.input,
                     placeholder: "status = 1 AND author_id IN (1, 2, 3)" %>
  <% if @sifter&.error %>
    <div><%= @sifter.error %></div>
  <% end %>
  <small>searchable columns: <%= ArticleSifter.searchable_columns.join(', ') %></small>
  <%= submit_tag 'Search' %>
<% end %>

Supported syntax

  • comparison operators: = != <> > >= < <=
  • IN (...)
  • IS NULL / IS NOT NULL
  • combinators: AND OR NOT and parentheses ()

License

MIT