I have a search field that includes the following code:
wildcard_search = “%#{search_term}%”
list |> where([b], ilike(field(b, :manufacturer_id), ^wildcard_search))
It works well, but I noticed that a user could enter % in the search box and return all the items in the database. I was wondering, what is the best way to prevent this? Should I just create a function to remove % and other wildcards?
I think you need to step back and figure out what limits you need to place on a wildcard search. For instance, is it an issue if a user can extract all items in your database with a relatively small set of queries (a-z, 0-9)? If so, then you also need to disallow 1-character queries. But is that an acceptable constraint, etc…
I’m not sure how easy it is with ecto to use the ESCAPE keyword, but doing Regex.replace(~r/([%_]/, input, ~S"[\1]") should work pretty well as a first level of sanitising.
But please do not forget to filter the empty string as input.
Careful! That’s not enough! ilike/like is powerful enough to bring down a database with certain bad data! ilike/like should never ever be used for user searches!
@tonarie
If you are using PostgreSQL then setup a proper search, a ts_* set of searches if you are searching for text. You will get better results, it will be faster (especially if you setup a ts* index too!), and it’s safe!
Abide by this rule though: Never allow untrusted content into ilike/like
If you want something even more powerful than full text search then PostgreSQL has even more options too!
EDIT: If you really want a simple field search though then don’t use ilike/like, instead use the string comparison operators based on whatever specific criteria you want, like contains might be enough.