Change to all-parametrised queries

[grigi]

Is your feature request related to a problem? Please describe.
Two actually:

1. Security as in SQL excaping issues. Until we are 100% parametrised, we have limited defence against SQL injection attacks.
2. We can't handle non-text representative fields for updates or filtering until we change over to parametrised, as those fields don't parse well as text.

Describe the solution you'd like
Update PyPika to allow parametrised queries.

Describe alternatives you've considered
There isn't really. Build our own is too much work for negative gain.

Additional context
We can't guarantee that parameters will be presented in the SQL query in the order we specify, so I feel the simplest solution might be to send the parameters as per usual, but then have a to_parametrised_query() method that returns (str, query parameter objects) so we can use parametrised queries, and PyPika is then in charge of managing order.

Then we need to update our code to use the parameters.

Done:

• Add parameter support to PyPika
• Use parameters for Inserts
• Use parameters for Deletes
• Use parameters for Updates
• Add BinaryField
• Have an escaping strategy for filters using LIKE
• Use parameters for Queryset Updates
• Use parameters for Queryset Filters
• Use parameters for Related matching

[grigi]

Started this at kayak/pypika#201

[arlyon]

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

[grigi]

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.

[grigi]

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.

[grigi]

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.