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:
- The test app
- The test cases
Bruce Burdick contributed the idea of inferring the Schema :field
s by introspecting the underlying ActiveRecord::Base subclass.
I’d love to hear your feedback on this module.