a proper way to escape %% when building LIKE queries in Rails 3 / ActiveRecord
If I understand correctly, you're worried about "%" appearing inside some_url
and rightly so; you should also be worried about embedded underscores ("_") too, they're the LIKE version of "." in a regex. I don't think there is any Rails-specific way of doing this so you're left with gsub
:
.where('url like ?', some_url.gsub('%', '\\\\\%').gsub('_', '\\\\\_') + '%')
There's no need for string interpolation here either. You need to double the backslashes to escape their meaning from the database's string parser so that the LIKE parser will see simple "\%" and know to ignore the escaped percent sign.
You should check your logs to make sure the two backslashes get through. I'm getting confusing results from checking things in irb
, using five (!) gets the right output but I don't see the sense in it; if anyone does see the sense in five of them, an explanatory comment would be appreciated.
UPDATE: Jason King has kindly offered a simplification for the nightmare of escaped escape characters. This lets you specify a temporary escape character so you can do things like this:
.where("url LIKE ? ESCAPE '!'", some_url.gsub(/[!%_]/) { |x| '!' + x })
I've also switched to the block form of gsub
to make it a bit less nasty.
This is standard SQL92 syntax, so will work in any DB that supports that, including PostgreSQL, MySQL and SQLite.
Embedding one language inside another is always a bit of a nightmarish kludge and there's not that much you can do about it. There will always be ugly little bits that you just have to grin and bear.
Safe ActiveRecord like query
To ensure that your query string gets properly sanitized, use the array or the hash query syntax to describe your conditions:
Foo.where("bar LIKE ?", "%#{query}%")
or:
Foo.where("bar LIKE :query", query: "%#{query}%")
If it is possible that the query
might include the %
character and you do not want to allow it (this depends on your usecase) then you need to sanitize query
with sanitize_sql_like
first:
Foo.where("bar LIKE ?", "%#{sanitize_sql_like(query)}%")
Foo.where("bar LIKE :query", query: "%#{sanitize_sql_like(query)}%")
How do I do a LIKE % query in ActiveRecord?
like_keyword = "%#{keyword}%"
MyModel.where("description LIKE ?", like_keyword)
Rails Query with ILIKE
Since this is a lot similar to a slug system, you should just add a new field and call it whatever you find suitable, just don't forget to add an index
so you don't waste time searching in strings.
Also you could add a before_create
or before_save
callback to auto create it when you save the object, in the format you are planning to search for.
Escape parameters in a query object, using ActiveRecord?
The best way is probably to create a prepared statement using the raw Postgres driver. Unfortunately, ActiveRecord does not expose a way do do this generically. They may add it soon now that mysql2 supports prepared statements. But in the meantime, here's how to do it with the raw PG
driver in rails.
http://deveiate.org/code/pg/PG/Connection.html#method-i-prepare
conn = ActiveRecord::Base.connection.raw_connection
conn.prepare('my_query', 'SELECT foo FROM bar WHERE baz=$1 OR baz=$2')
result = conn.exec_prepared('my_query', ['param1', 'param2'])
Note the use of $
as the symbol to indicate a positional parameter. The numbers correspond to the parameter's position in the array you pass to exec_prepared
.
Case-insensitive search in Rails model
You'll probably have to be more verbose here
name = "Blue Jeans"
model = Product.where('lower(name) = ?', name.downcase).first
model ||= Product.create(:name => name)
Rails: How to find_by a field containing a certain string
I think something like this should work:
Topic.where("name like ?", "%apple%")
To accomodate for your edit:
Topic.where("name like ?", "%#{@search}%")
Basic string interpolation, you're using the value of @search
inside the string %%
, so you @search = "apple"
then you end up with %apple%
Related Topics
Oracle - Why Does the Leading Zero of a Number Disappear When Converting It To_Char
Sql: Combine Select Count(*) from Multiple Tables
Regular Expressions Inside SQL Server
How to Select Columns from a Table Which Have Non Null Values
Update with Case and in - Oracle
Upgrading a Varchar Column to Enum Type in Postgresql
Can There Be Constraints with the Same Name in a Db
SQL Server 2005: Insert Multiple Rows with Single Query
Dealing with System.Dbnull in Powershell
Is There a Simple Way to Query the Children of a Node
How to Count Number of Occurrences for All Different Values in Database Column
Similarity Function in Postgres with Pg_Trgm
How to Convert Date to a Format 'Mm/Dd/Yyyy'
Behaviour of Not Like with Null Values