12

I am trying to search in a database to see if a string matches part of another string in the database. I can get it to match if the two are exact using ilike, but when I am searching for just part of the string it does not catch data that contains it. Here is what my code looks like for the query:

    servicesstate = Repo.all(from p in Callme.Service, where: ilike(p.locations, ^zip.state))

It will match when the values are exact ("South Carolina", "South Carolina"), but I want it to match when it is something like ("Located in South Carolina", "South Carolina")

Thanks

Afshin Moazami
  • 2,092
  • 5
  • 33
  • 55
user2480169
  • 151
  • 1
  • 5

1 Answers1

21

You can use the % syntax for LIKE/ILIKE:

servicesstate = Repo.all(from p in Callme.Service, where: ilike(p.locations, ^"%#{zip.state}%"))

Note that this will not work correctly if zip.state contains a %. If it can contain %, you'll have to use Ecto.Query.API.fragment/1 with a query like this.

Community
  • 1
  • 1
Dogbert
  • 212,659
  • 41
  • 396
  • 397
  • 3
    The best idea to express apprecation is to vote up and also check this answer as a solution of your problem. – PatNowak Jul 21 '16 at 19:42
  • 1
    Does `ilike(p.locations, ^"%#{user_input}%")` allow SQL injection? – Nathan Long Dec 07 '16 at 15:53
  • 2
    @NathanLong nope. Just answered your latest question related to this moments ago :) – Dogbert Dec 07 '16 at 15:55
  • @Dogbert and for the benefit of those here, what was your answer? – cayblood Apr 14 '17 at 16:04
  • 4
    Never mind, here is @Dogbert's answer: http://stackoverflow.com/a/41021806/281199 – cayblood Apr 14 '17 at 16:07
  • @cayblood thanks for the link! The short answer for future readers is, no, it doesn't allow SQL injection. – Dogbert Apr 14 '17 at 17:00
  • 1
    Note that, although injection of SQL *statements* is impossible, LIKE injections are still possible (as documented in Ecto: https://hexdocs.pm/ecto/Ecto.Query.API.html#like/2) that can significantly slow down queries. This requires some sort of user input sanitising. – Daniel Mar 02 '19 at 21:20