I want to fetch records that have some string field start with a given prefix and end on any one character. Basically:
Model.where('field LIKE ?', "#{prefix}_").count
The problem is that the prefix itself might contain special characters (like % or _).
Is there a way to escape the prefix, but not the trailing _ without rolling my own sanitizer with a bunch of #gsubs?
There is no better solution than replacing all _ with \_ and all % with \% to escape their special meaning.
Model.where("field LIKE ?||'_'", escapeDataFunction("#{prefix}")).count
The idea is to escape what needs to be escaped and hard code the other part in the "where" condition. Also note that when using substitution variables (? or :1), then the data need not be escaped at all in general, but "like" expressions are an exception, and in that case, you should escape the special characters with meaning in the like operator.