[IMP] core: add SQL wrapper #134677
[IMP] core: add SQL wrapper #134677
Conversation
|
|
odoo/tools/sql.py
Outdated
| def __init__(self, string: str = "", *args): | ||
| if isinstance(string, SQL): | ||
| string, args = string.__string, string.__args | ||
| string % tuple("x" for arg in args) |
There was a problem hiding this comment.
Seems a shame not to allow post-hoc formatting so we can e.g. create a query, then format arguments in it in a loop. This way the query can be a constant or easier to parameterize.
Here an additional layer has to be added, and I'm not convinced the "intrinsic" formatting is really worth it: in _() it's used because that allows handling of (and falling back in) the case where a translator has broken the source string and added/removed placeholders.
Also makes it harder to support keyword parameterization, despite how useful that is (although keyword parameterization does raise the question of mixing when combining queries, which is a concern with printf-style formatting).
There was a problem hiding this comment.
I introduced this enforcement because I was almost unable to debug a SQL object with a missing parameter. The issue is that you generally have a composition of SQL objects, finding which one misses its parameter and where it has been created is really non-trivial.
For this problem, I suggest to add a parameter placeholder, i.e., an object that contains a value which may be updated post-hoc, and implements the SQL adaptation protocol. So you would put that as a parameter anywhere in your SQL objects, and it would simply end up in the sql.params list. If you set its internal value before executing that SQL object, that value will be adapted by psycopg2.
| @@ -23,23 +23,108 @@ | |||
| 'SET DEFAULT': 'd', | |||
| } | |||
|
|
|||
|
|
|||
| class SQL: | |||
There was a problem hiding this comment.
Might be worth having this in a more prominent location e.g. odoo.sql? And possibly re-export the more useful of SQL utilities from there?
There was a problem hiding this comment.
Do you mean the class SQL, the module sql, or only a subset of the module sql ?
There was a problem hiding this comment.
Mostly the SQL class. The existing content of tools.sql is a lot more specific (mostly to setup / migration), but if we consider that SQL will get used in every non-static query it would make sense to make it prominent and easily accessible, rather than tuck it into a grab bag of more minor tooling.
508acab
to
7fa16a2
Compare
af0de97
to
9fed713
Compare
|
@moylop260 take a look |
2ea8c7b
to
25f3eb6
Compare
We introduce a new class of objects to wrap SQL code together with its
parameters. It is designed to be easily composable and to discourage
SQL injections. Its API is similar to the methods of module 'logging':
the code is a format string, and the positional parameters are meant to
be merged into it using the string formatting operator.
# default and increment are parameters of the SQL code in first argument
term = SQL("COALESCE(value, %s) + %s", default, increment)
# term can safely be injected into another SQL, besides regular parameters
query = SQL("SELECT %s FROM mytable WHERE id = %s", term, id_)
The SQL wrapper can return the final SQL code string as query.code, and
the corresponding parameters as query.params (list). The cursor method
execute() can now take an SQL object, and execute it just like
cr.execute(query.code, query.params)
It is quite easy to make SQL objects safe against SQL injections: if the
code is a string literal, then the SQL object is guaranteed safe,
provided the SQL objects within its parameters are themselves safe.
Part-of: #134677
…er_items_query() closes #134677 Related: odoo/enterprise#47759 Signed-off-by: Raphael Collet <rco@odoo.com>
This completes odoo/odoo#134677.
This completes odoo/odoo#134677.
This completes odoo/odoo#134677. closes #6674 Signed-off-by: Raphael Collet <rco@odoo.com>
This completes odoo/odoo#134677. X-original-commit: 2662fa5
This completes odoo/odoo#134677. closes #6724 X-original-commit: 2662fa5 Signed-off-by: Raphael Collet <rco@odoo.com>
We introduce a new class of objects to wrap SQL code together with its parameters. It is designed to be easily composable and to discourage SQL injections. Its API is similar to the methods of module
logging: the code is a format string, and the positional parameters are meant to be merged into it using the string formatting operator.The SQL wrapper can return the final SQL code string as
query.code, and the corresponding parameters asquery.params(list). The cursor methodexecute()can now take anSQLobject, and execute it just likeIt is quite easy to make SQL objects safe against SQL injections: if the code is a string literal, then the SQL object is guaranteed safe, provided the SQL objects within its parameters are themselves safe.