Because that is exactly what it should do since you are passing it in as a query argument via ^extra_criteria
.
Remember that SQL queries are cached, anything passed to a query argument, as with the normal SQL interfaces, ‘is’ an entire argument, it is not SQL and not inferred as SQL. Allowing something like price > 200000
as raw SQL is just begging for someone to accidentally mis-type something and suddenly wipe your whole database, that is why every SQL interface (not just with Ecto, I mean base native interfaces) highly encourage using prepared statements with arguments, not just for an efficiency boost but also for safety.
Thus you need to encode your request some other way, like say if you want to encode a comparison on a field then encode it as a tuple like {:>, :price, 200000}
, then you can generate your query like:
query = Enum.reduce(all_filters, query, fn
{:>, field, value} -> where(query, [q], field(q, ^field) > value)
{:==, field, value} -> where(query, [q], field(q, ^field) == value)
{:<, field, value} -> where(query, [q], field(q, ^field) < value)
{:!=, field, value} -> where(query, [q], field(q, ^field) != value)
end)
...etc...
And of course you can make something more specialized for your input data. For example here is a copy/paste from one of my big generators:
{:active_around, %NaiveDateTime{} = dt}, squery ->
where(
squery,
[course, section, dept],
section.ssbsect_ptrm_start_date <= ^dt and section.ssbsect_ptrm_end_date >= ^dt
)
{:registered, true}, squery ->
where(
squery,
[course, section, dept, student_course],
student_course.sfrstcr_rsts_code in ["RA", "RE", "RW"]
)
{:withdrawn, true}, squery ->
where(
squery,
[course, section, dept, student_course],
student_course.sfrstcr_rsts_code == "WD"
)
{:department, dept_code}, squery when is_binary(dept_code) ->
where(squery, [course, section, dept], course.scbcrse_dept_code == ^dept_code)
{:department, dept_code}, squery when is_list(dept_code) ->
where(squery, [course, section, dept], course.scbcrse_dept_code in ^dept_code)
{:subject, subject_code}, squery when is_binary(subject_code) ->
where(squery, [course, section, dept], section.ssbsect_subj_code == ^subject_code)
{:subject, subject_code}, squery when is_list(subject_code) ->
where(squery, [course, section, dept], section.ssbsect_subj_code in ^subject_code)
{:course, course_number}, squery when is_binary(course_number) ->
where(squery, [course, section, dept], section.ssbsect_crse_numb == ^course_number)
{:course, course_number}, squery when is_list(course_number) ->
where(squery, [course, section, dept], section.ssbsect_crse_numb in ^course_number)
And I have a whole ton more than that in that one function (it’s a huge general lookup function). There is no way I would ever allow a user-defined filter to be injected into SQL. Even assuming no malice, it would be simple to accidentally craft something that could grab data they shouldn’t get, or could wipe the database, etc…
Remember the Golden Rule of front-end programming: Don’t Trust User Input.
^.^