Change to all-parametrised queries about tortoise-orm HOT 5 OPEN

Started this at kayak/pypika#201

from tortoise-orm.

Just ran into a problem with the GIS work that this should solve.

Unless I am mistaken this will have the side-effect of allowing us to use pypika function objects in queries as well since pypika should properly handle converting them to raw sql. Practically everything in GIS is done via SQL functions and currently if you pass them in to the sql driver values list instead of having pypika "render" it properly it will wrap it in quotes / escape it.

Although re-running a query with a mixture of functions and raw sql parameters may cause problems because of the INSERT_CACHE on tortoise's end

from tortoise-orm.

Hmm, I think this will help for any non-text object. Pypika has a Function class in utils.py that you could subclass for SQL functions?
I think as long as you pass a value parameter for inserts only, I think it would work. But yes, for that to work PyPika and Tortoise would need to update.

I also think #72 and 'capabilities' would probably be needed to make the GIS implementation not feel hacky.

from tortoise-orm.

PyPika 0.22 is released with Parameter() syntax. we should update to use it where we can.
I had a look at filtering/updating, and it seems to require quite the refactor to do it.

from tortoise-orm.

v0.16.6 fixed some SQL injection issues for MySQL.
We should focus on parametrizing Queryset Updates as they are at least contained in the UpdateQuery.

from tortoise-orm.