How to use Postgres sql like operator

Phoenix Forum Questions / Help
, ,
1

I have created a function which will show the list of products or a single product with specific name lets say ‘Widget 1’ but how can I show the list of all products that starts with a letter ‘W’. I want to use like operator for the sql query as shown below:

select * from products where name like ‘%Widget 1%’;

but when I tried the like in my script it is showing error as shown below:

Ecto.Query.CompileError

# p.name(like("%Widget 1%", order_by: [desc: p.id()], select: struct(p, [:id, :name, :quantity, :inserted_at, :updated_at]))) is not a valid query expression. If you want to invoke p.name/1 in a query, make sure that the module p is required and that name/1 is a macro

The working code is as shown below of my product module

def list_products do
    query = from p in MyStore.Products.Product,
                 where: p.name == "Widget 1",
                 order_by: [
                   desc: p.id
                 ],
                 select: struct(
                   p,
                   [:id, :name, :quantity, :inserted_at, :updated_at]
                 )
    products = MyStore.Repo.all(query)
end
2

Oh its my mistake using like incorrect way I got the solution the like should be used as shown below:

def list_products do
    query = from p in MyStore.Products.Product,
                 where: like(p.name,"%Widget 1%"),
                 order_by: [
                   desc: p.id
                 ],
                 select: struct(
                   p,
                   [:id, :name, :quantity, :inserted_at, :updated_at]
                 )
    products = MyStore.Repo.all(query)
end
2 Likes
3

Use ilike instead of like, it is more secure

4

Wait what? Since when? Isn’t the only difference that ILIKE is case insensitive where LIKE is case sensitive? Weren’t they both pretty poor on security in general?

5

I found a blog post once but this is what i found now hoep that it helps https://books.google.ro/books?id=jfKoCwAAQBAJ&pg=PA234&lpg=PA234&dq=sql+ilike+better+security+operator&source=bl&ots=n1L6jig6RQ&sig=ACfU3U0gIOtIOnhIgCCDokQftPQ3sqmQaA&hl=ro&sa=X&ved=2ahUKEwjvseDa7IPnAhUNHMAKHbq-AC4Q6AEwA3oECAkQAQ#v=onepage&q=sql%20ilike%20better%20security%20operator&f=false

6

Hmm I looked over that section and surrounding but I’m missing where it says that ILIKE is more secure than LIKE?

1 Like
7

I am sure that read something in blog and it had also some examples but can’t find it. So I will go with the assumption that I misunderstood that part or that the author of that blog was wrong.

1 Like