Rails Postgres find attribute by substring (multiple words)

Question

I'm trying to figure out how to use the LIKE statement to achieve the following in Postgres:

Data: Test Project

The following search queries should return Test Project: test est est project pro

Currently my method in the my projects.rb model looks like this:

self.where('name LIKE ?', "%#{search}%")

(I'll be adding downcase of course) It currently fetches any variation of the first word of the :name attribute value but no the second, so searching pro would return nothing.

projects.rb model

  def self.search(search)
    if search
      scope :find_name, lambda { |search| where("name ILIKE :search", search: "%#{search.downcase}%") }
    else
      self.where(nil)
    end
  end


projects_controller.rb

def index                                
  @projects = Project.search(params[:search]
end  

index.html.erb
<p id="notice"><%= notice %></p>
<div class="projects-index">
  <div class="container-body text-center">
    <h1>Projects</h1>
    <%= form_tag projects_path, method: :get, id: "projects_search" do %>
      <p>
        <%= text_field_tag :search %>
      </p>
    <% end %>
  </div>
  <!-- CARDS -->
  <div class="row cards-section">
    <div id="projects">
      <%= render partial: 'projects' %>
    </div>
    <div class="col-3">
      <%= link_to new_project_path, class: "new-project text-center" do %>
        <div class="align vertical">
          <div class="align-flex center">
            <%= image_tag "add_project.png" %>
          </div>
          <p>New Project</p>
        </div>
      <% end %>
    </div>
  </div>
</div>

_projects.html.erb partial

<% @projects.each do |project| %>
  <div class="col-3">
    <div class="existing-card">
      <div class="top">
        <div class="ui icon left pointing dropdown button">
          <i class="ellipsis vertical icon"></i>
          <div class="menu">
            <div class="item">
              <%= link_to 'Edit', edit_project_path(project), class: "link" %>
            </div>
            <div class="item">
              <%= link_to 'Share', new_user_invitation_path, class: "link" %>
            </div>
            <div class="item">
              <%= link_to 'Delete', project_path(project), method: :delete, data: { confirm: 'Are you sure you want to delete this project?' }, class: "link" %>
            </div>
          </div>
        </div>
      </div>
      <div class="card-container align-flex center">
        <p><%= project.name %></p>
      </div>
    </div>
  </div>
<% end %>

According to guides.rubyonrails.org/active_record_querying.html this is an unsafe approach that can lead to SQL injection. @Sachin_R approach seems safer. — Stephane Paquet
– Stephane Paquet, Commented Aug 24, 2017 at 6:15
Thanks for bringing that up, which part exactly makes it vulnerable to SQL injections? (So I can avoid them in the future) — Dotol
– Dotol, Commented Aug 24, 2017 at 6:21
Putting the variable directly into the conditions string will pass the variable to the database as-is. This means that it will be an unescaped variable directly from a user who may have malicious intent. If you do this, you put your entire database at risk because once a user finds out they can exploit your database they can do just about anything to it. Never ever put your arguments directly inside the conditions string — Stephane Paquet
– Stephane Paquet, Commented Aug 24, 2017 at 6:41
For more details you can also read guides.rubyonrails.org/security.html#sql-injection — Stephane Paquet
– Stephane Paquet, Commented Aug 24, 2017 at 6:43

widjajayd · Accepted Answer · 2017-08-24 07:03:10Z

2

with your query you just need to change LIKE to ILIKE

self.where('name ILIKE ?', "%#{search.downcase}%")

for more save I would like to suggest using scope below

scope :find_name, lambda { |search| where("name ILIKE :search", search: "%#{search.downcase}%") }

edited for your case

do not use scope inside method, for more about scope please see link and for your case you can change your index as follow

def index                                
  @projects = Project.find_name(params[:search]
end

and change this

  def self.search(search)
    if search
      scope :find_name, lambda { |search| where("name ILIKE :search", search: "%#{search.downcase}%") }
    else
      self.where(nil)
    end
  end

to this

scope :find_name, lambda { |search| where("name ILIKE :search", search: "%#{search.downcase}%") }

edited Aug 24, 2017 at 7:03

answered Aug 24, 2017 at 6:10

widjajayd

6,2735 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Dotol Over a year ago

Thank you, that worked beautifully, just to make sure now that @Stephane_Paquet brought it up, is this snippet safe from SQL injections?

Dotol Over a year ago

Just noticed that threw an error:

ActionView::Template::Error (undefined method 'each' for :find_name:Symbol): web_1          |     1: <% @projects.each do |project| %>

widjajayd Over a year ago

can you upload your controller and view, I'll try to help

widjajayd Over a year ago

Edited my answer, inside your project.rb please put your scope out from method see my edited answer (bottom section)

widjajayd Over a year ago

Let us continue this discussion in chat.

|

Sachin R · Accepted Answer · 2017-08-24 06:31:53Z

1

try

self.where('name ILIKE %?', "%#{search}%")

edited Aug 24, 2017 at 6:31

answered Aug 24, 2017 at 6:06

Sachin R

11.9k11 gold badges37 silver badges42 bronze badges

2 Comments

Stephane Paquet Over a year ago

ILIKE will make the search case insensitive.

Dotol Over a year ago

I tried this approach and I got this error:

ActionView::Template::Error (PG::SyntaxError: ERROR:  syntax error at or near "%" web_1          | LINE 1: SELECT "projects".* FROM "projects" WHERE (name ILIKE %''%) web_1          |                                                               ^ web_1          | : SELECT "projects".* FROM "projects" WHERE (name ILIKE %''%)):

Collectives™ on Stack Overflow

Rails Postgres find attribute by substring (multiple words)

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related