Rails 4 Like Query - Activerecord Adds Quotes

Rails 4 LIKE query - ActiveRecord adds quotes

Your placeholder is replaced by a string and you're not handling it right.

Replace

"name LIKE '%?%' OR postal_code LIKE '%?%'", search, search

with

"name LIKE ? OR postal_code LIKE ?", "%#{search}%", "%#{search}%"

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)}%")

Rails ActiveRecord: How to use bind variables with double quotes on jsonb

You can't put parameter placeholders inside quoted strings.

The fact that Rails allows you to do that and substitutes a single-quoted string inside the single-quoted string indicates that Rails has failed (as usual) to understand rules of SQL.

But you can put a parameter placeholder in an expression, with other strings. I am not a regular PostgreSQL user, but I assume you can concatenate strings together to form a complete JSON literal:

Blog.where("upload_data @> '[ { \"name\": \"' || ? || '\"}]'", name)

You might find it makes your code more clear if you parameterize the whole JSON value. Use %Q() to avoid needing to backslash the literal double-quotes.

Blog.where("upload_data @> ?", %Q([ { "name": "#{name}" } ]))

Or to make sure to generate valid JSON, I'd put the expression in Ruby syntax, and then convert to JSON:

Blog.where("upload_data @> ?", JSON.generate( [{name: name}] ))

Escaping Quotes in Rails Query

You don't need to interpolate to create the arguments for where, just bind it/them:

Merchant.where("lower(name) = ?", mname)

Active Record will take care of that and your query will most likely look like this:

SELECT "merchants".* FROM "merchants" WHERE (lower(name) = 'brandy''s boy')

Escaping is doing in the internals of the framework, from the docs:

If an array is passed, then the first element of the array is treated
as a template, and the remaining elements are inserted into the
template to generate the condition. Active Record takes care of
building the query to avoid injection attacks, and will convert from
the ruby type to the database type where needed. Elements are inserted
into the string in the order in which they appear.

User.where(["name = ? and email = ?", "Joe", "joe@example.com"])
# SELECT * FROM users WHERE name = 'Joe' AND email = 'joe@example.com';

Doing:

Merchant.where(["lower(name) = ?", mname])

Is pretty much the same as doing:

Merchant.where("lower(name) = ?", mname)

So your query is handled as stated above.

Search using like query in Ruby on Rails

It's often true that a bad name indicates wrong thinking. I believe your name Search for the model is in this category. It should probably be called Tutorial, no? Search is something you do to a model, not the model itself.

If this guesswork is correct and the model is now called Tutorial and it has a field called name that is a string, then your model will be

class Tutorial < ActiveRecord::Base

  def self.search(pattern)
    if pattern.blank?  # blank? covers both nil and empty string
      all
    else
      where('name LIKE ?', "%#{pattern}%")
    end
  end

end

This makes the model "smart" on how to search through tutorial names: Tutorial.search('foo') will now return all tutorial records that have foo in their names.

So we can create a controller that uses this new functionality:

class SearchController < ApplicationController 

  def show
    @tutorials = Tutorial.search(params[:q])

    respond_to do |format|
      format.html # show.html.erb
      format.json { render json: @tutorial }
    end
  end
end

The corresponding view must display the tutorials. Yours doesn't. The simplest way to do this is write a partial that renders exactly one tutorial. Say it's called _tutorial.html.erb.

Then in the view for Search, you need to add

<%= render :partial => @tutorials %>

to actually display the search results.

Addition

I'll build a little example.

# Make a new rails app called learning_system
rails new learning_system             

# Make a new scaffold for a Tutorial model.
rails g scaffold Tutorial name:string description:text 

# Now edit app/models/tutorial.rb to add the def above.

# Build tables for the model.
rake db:migrate

rails s # start the web server

# Now hit http://0.0.0.0:3000/tutorials with a browser to create some records.

<cntrl-C> to kill the web server

mkdir app/views/shared
gedit app/views/shared/_search_box.html.erb
# Edit this file to contain just the <%= form_tag you have above.

# Now add a header at the top of any view you like, e.g. 
# at the top of app/views/tutorials/index.html.erb as below
# (or you could use the layout to put it on all pages):

<h1>Listing tutorials</h1>
<%= render :partial => 'shared/search_box' %>

# Make a controller and view template for searches
rails g controller search show  

# Edit config/routes.rb to the route you want:  get "search" => 'search#show'

# Verify routes:

rake routes
       search GET    /search/:id(.:format)         search#show
    tutorials GET    /tutorials(.:format)          tutorials#index
              POST   /tutorials(.:format)          tutorials#create
 new_tutorial GET    /tutorials/new(.:format)      tutorials#new
edit_tutorial GET    /tutorials/:id/edit(.:format) tutorials#edit
     tutorial GET    /tutorials/:id(.:format)      tutorials#show
              PUT    /tutorials/:id(.:format)      tutorials#update
              DELETE /tutorials/:id(.:format)      tutorials#destroy

# Edit app/controllers/search_controller.rb as above.

# Create app/views/tutorial/_tutorial.html.erb with following content:
<tr>
<td><%= tutorial.name %></td>
<td><%= tutorial.description %></td>
</tr>

# Edit app/views/search/show.html.erb to have following content:
<h1>Show Search Results</h1>
<table>
<%= render :partial => @tutorials %>
</table>

Now try a little test. Fill in a search criterion and press the Search button.

How do I properly escape double quotes in a PostgreSQL like query?

The problem is that there is not a unique way to express that data in YAML:

hide_on_map:
  - no
  - yes

"hide_on_map": [false, true]

are both valid YAML representations of your data.

I fear you cannot avoid using some native type, or at least a "compacted" JSON text (which would contain literally '"hide_on_map":[false,true]'.

Rails ActiveRecord includes where with like

Just use string as argument for where

scope :filter, -> (query) { joins(:city).where('cities.name LIKE ?', "%#{ query }%") }

ActiveRecord query only if attribute is inferior than another

You can just have a string of what would be your query inside the where, i usually use this approach because it makes me feel like im writing plain SQL.

This should work for you:

@meals = Meal.where("week_day = 1 
                 AND vacation_mode = false
                 AND today_orders < max_daily_orders")

---

Related Topics