title: Using substring in Rails query the safe way
body: <p>I'm currently writing a query to look in a table some grouped results in order to build an alphabetical pagination index.</p>
  
  <p>The query I have looks something like the following (based on this <a href="https://stackoverflow.com/questions/666525/group-by-first-character">answer</a>):</p>
  
  <pre><code>criteria = "substr(%s, 1, 1)" % field_name
  Posts.select(criteria).group(criteria).order(criteria).count(:id)
  </code></pre>
  
  <p>Now I would like to improve this query in order to avoid any SQL injection but I couldn't really find a way to sanitise it.</p>
  
  <p>As far as I see there's no way to use LIKE for this particular query - or at least I haven't found it - and use a <code>where</code> clause.</p>
  
  <p>Now the example is using a <code>Posts</code> entity, but in production it will be used with many kind of objects and I's like to have a generic system to specify a field name dynamically.</p>
  
  <p>The only idea I had was to get the table column name and validate the field against that list before proceeding, although this may be overkilling in a production scenario.</p>
  
  <p>The environment I have is Ruby 1.9.3, Rails 3.2.17, and a MySQL db. </p>
  
  <p><strong>Update 1</strong></p>
  
  <p>I'll define better the context: in the query above I'm using <code>Post</code> but what I really have it's an ActiveRecord query where I'm appending the <code>select</code> and <code>group</code> criteria.</p>
  
  <p>Something like:</p>
  
  <pre><code>query = Post.where(:condition =&gt; something)
  # .. do more stuff here
  
  criteria = "substr(%s, 1, 1)" % field_name
  query.select(criteria).group(criteria).order(criteria).count(:id)
  </code></pre>