UserQuery: Ruby Package Simplifies General Searching in Rails

Leave a reply

See: https://github.com/kstephens/userquery

The Ruby package userquery
allows users to do general queries on SQL database table columns using a simple query language. The package parses tokens from the user’s query and generates SQL WHERE clauses immune to SQL injection attacks.

For example, if a user wants to search for all entries records on a DATETIME field named date, the user can enter: “11/1/2006“ into a text field associated with searching on the date column.

UserQuery will intuitively convert this query into an SQL WHERE clause fragment:

(
 (entries.date >= '2006-11-01 00:00:00') 
AND 
 (entries.date <  '2006-11-02 00:00:00')
)

The user query syntax includes “NOT“, “OR“, “AND“ operators, grouping with parentheses, well as relational operators like “LESS THAN 5“ or “>= $500“. Keyword searching, like “foo AND NOT ‘bar baz’“ using SQL LIKE operators is configurable.

To use UserQuery:

  • a Schema specifies how the query parser will interpret the user’s query for each column,
  • a Parameters object binds the query values to the schema and provides a Rails-compatible domain object,
  • the query strings are parsed and the SQL WHERE clause is generated.
require 'user_query'

# This specifies the query schema.
s = UserQuery::Schema.
      new(:table => 'entries',
          :field => [  # col,   type                        
                     [ :id,     :number ],
                     [ :date,   :datetime ],
                     [ :memo,   :string ],
                     [ :amount, :money ]
                    ]
          )

# This represents the user's query input.
user_input = { :date => '11/1/2006' }
p = UserQuery::Parameters.new(user_input)

# Query is parsed and the SQL WHERE clause in generated.
puts s.sql(user_input, p)

The :money column type above refers to the Currency::Money class.

The UserQuery Parser object uses a recursive-descent parser which recognizes tokens in the user query string based on the column’s type. It generates an abstract parse tree which is then converted to SQL using the column bindings from the schema object and the data value token constants found by the parser by a Generator object. Syntax errors in the user’s input are returned in the Parameters object.

A Parameters object can be used in a Rails controller to collect query input from text fields in a form template:

require 'user_query'

class EntriesController < ActionController::Base
  model :entry

  def index
    list
    render :action => 'list'
  end

  def list
    @query = UserQuery::Parameters.new(params[:query] ||= { })

    q_sql = UserQuery::Schema.
      new(:table => Entry,
          :field => [
                     # Override ActiveRecord::Base introspection:
                     [ :amount, :money ] 
                    ]
          ).sql(@query)

    @entries_pages, @entries = 
     paginate :entries,
              :class_name => 'Entry',
              :per_page => 20,
              :conditions => [ @q_sql ? @q_sql.gsub(/%/, '%%') : '1' ],
              :order => 'id'

  end

  # Other methods ...
end

When an ActiveRecord::Base subclass is used as the Schema’s :table option, the Schema attempts to infer query fields by using the cls.columns() method. Column type mappings can be overriden.

The corresponding views/entries/list.rhtml view:

<%= start_form_tag :action => 'list' %>

<%= error_messages_for 'query' %>

<table class="entries_list">
  <tr>
    <td align="center"><%= text_field 'query', 'id', :size => 4 %></td>
    <td align="center"><%= text_field 'query', 'name', :size => 10 %></td>
    <td align="center"><%= text_field 'query', 'date', :size => 10 %></td>
    <td align="center"><%= text_field 'query', 'memo', :size => 20 %></td>
    <td align="center"><%= text_field 'query', 'amount', :size => 10 %></td>
    <td align="center"><%= submit_tag 'Search' %></td>
  </tr>
  <tr>
    <th>ID</th>
    <th>Name</th>
    <th>Date</th>
    <th>Memo</th>
    <th>Amount</th>
  </tr>
<% for entry in @entries %>
  <tr>
    <td align="right"><%= entry.id %></td>
    <td><%=h entry.name %></td>
    <td><%=h entry.date.strftime("%Y/%m/%d") %></td>
    <td><%=h entry.memo %></td>
    <td align="right"><%=h entry.amount.format %></td>
  </tr>
<% end %>

</table>

<%= end_form_tag %>

A Parameters object interfaces to Rails as an ActiveRecord::Base object to simplify collecting input from view template forms and reporting errors back to the user. However, UserQuery can be used without Rails.

The first file release is pending, so please visit the svn repo at: 

git clone https://github.com/kstephens/userquery.git

For more examples, see:

Bruce Burdick contributed the idea of inferring the Schema :fields by introspecting the underlying ActiveRecord::Base subclass.

I’d love to hear your feedback on this module.