Ecto where like

Questions / Help
,
1

Hello.
I’m still learning elixir, phoenix and ecto and stumbled upon a routine task that I can’t seem to figure out how to handle.
That is complex where cases and mysql like operator. Basically what I need is simple search for example for query like this:
select id, name from posts where deleted_at is null and (name like '%foo%' or body like '%foo%')

What I have tried so far is something like this, but it gives me error that function fragment is not defined:
params["search"] is defined above this code
searchQuery = from p in Post, # where: (like(p.name, ^fragment("'%?%'", params["search"])) or like(s.body, ^fragment("'%?%'", params["search"]))) and is_nil(p.deleted_at)

Any help would be greatly appreciated,
Thanks

2 Likes
2

Hi,
My I ask why do you want to use fragment here?
Would not work something like this?

like_term = "%#{params["search"]}%"
searchQuery = from p in Post,
# where: (like(p.name, like_term) or like(s.body, like_term)) and
is_nil(p.deleted_at)
3 Likes
3

@silviurosu sure - if I understand correctly, the fragment will escape the string… Am I wrong? Will doing it like you suggest escape the input? Since the params[“search”] comes from directly form user input, I have to escape it first.
Does ecto automatically escapes all inputs? I come from PHP so I’m used to escape everything before running the queries to get rid of SQL injections. Most ORMs do it, but when you start writing raw queries, it needs to be escaped…

1 Like
4

You need to escape it against LIKE injection, but SQL injection is impossible if you use the ecto query syntax - ecto uses parametrized queries exclusively, so whatever is injected into the query using ^ is never part of the query itself (but a parameter), so there’s no need for escaping.

7 Likes