@tomoharu33 (中垣智晴)posted at 2020-12-28

Railsで検索機能を実装したい

Q&A

Closed

Rails SQLite3 cloud9

解決したいこと

Rails初学者で、こちらの記事を参考にしながら、検索機能を作成しています。
https://qiita.com/fukmaru/items/c22ba2700e9ad368770c

記事との環境の違いは以下です。

記事：PostgreSQL 10.4
自分：sqlite3 3.7.17 (cloud9で開発)

Gemfileなどは変更しており、検索機能以前の実装については問題なく動いています。

発生している問題・エラー

検索実行時に、以下のエラーが出てしまいます。

SQLite3::SQLException: near "~": syntax error: SELECT "contents".* FROM "contents" WHERE "contents"."is_published" = ? AND (`body` ~~* any(array['%hoge%']))

該当するソースコード

class PagesController < ApplicationController
  def home
    @contents = Content.where(is_published: true)

    search_params = params.dig("s")
    if search_params
      search_params_arr = search_params.split
      @contents = @contents.where("body ~~* any(array[#{search_params_arr.map{ |i| %Q('%#{i}%') }.join(',')}])") if search_params_arr.present?
    end
    @search_str = search_params

  end
end

自分で試したこと

@contents.where以下の( )内を "id = 1" に変更すると動いた。@contents.where以下の( )内が怪しいと推測。
body をバッククォートで囲ってみた（`body`）が、解決せず。
サンプルコードとの環境の違いが原因かと思い、postgresqlとsqliteの構文の違いなどを調べるが、わからず。

よろしくお願いします。

1Answer

@uasi posted at 2020-12-28

~~* は PostgreSQL 独自の演算子です。 SQLite 3 は対応していません。同じ意味の LIKE 演算子が使えます。

元のコードでは (`body` ~~* any(array['%hoge%', '%fuga%'])) のように any() と組み合わせて OR 検索していますが、 SQLite 3 で同等なクエリは以下のようになります。

`body` LIKE '%hoge%' OR `body` LIKE '%fuga%'

さらに、パラメータに含まれるいくつかの文字を適切にエスケープする必要もあります。元のコードはエスケープ処理をしていないために SQL インジェクション攻撃に対して脆弱です。

コードを以下のように書き換えればおそらく同等の検索機能が実現できると思います。

if search_params
  search_params_arr = search_params.split
  if search_params_arr.present?
    escaped_search_params = search_params_arr.map do |param|
      # パラメータに含まれるいくつかの文字をエスケープする。
      # シングルクオートで囲むので、パラメータ内のシングルクオートを二重にしてエスケープ。
      # また LIKE のパターンで特別な意味を持つ % と _ に @ を前置してエスケープ。（@ 自身も）
      param.gsub(?', "''").gsub(/[@%_]/, '@\0')
    end
    search_condition = escaped_search_params.map do |param|
      "`body` LIKE '%#{param}%' ESCAPE '@'"
    end.join(' OR ')
    @contents = @contents.where(search_condition)
  end
end

参考ページ

SQLite が理解する SQL の文法 https://sqlite.org/lang.html
PostgreSQL のパターンマッチング https://www.postgresql.org/docs/13/functions-matching.html

2Like

Comments

@tomoharu33
Questioner
いただいた通りに実装したら、想定している挙動になりました！
大変ありがとうございます！！
@tomoharu33
Questioner
参考ページもありがとうございます、熟読します。

Are you sure you want to delete the question?