通过SQLAlchemy使用Postgresql执行多个语句不会持久保存更改 [英] Executing multiple statements with Postgresql via SQLAlchemy does not persist changes

问题描述

这不起作用–更新无效:

This does not work – the update has no effect:

command = "select content from blog where slug = 'meow'; update account_balance set balance=200 where id=1; select 1 from blog;"
content = db.engine.scalar(command)

切换语句将执行更新并成功选择:

Switching the statements performs the update and select successfully:

command = "update account_balance set balance=200 where id=1; select content from blog where slug = 'meow';"
content = db.engine.scalar(command)

为什么第一个不起作用?它可以在Pgadmin中工作.我用Flask-Sqlalchemy启用了自动提交.

Why does the first not work? It works in Pgadmin. I enabled autocommit with Flask-Sqlalchemy.

我正在举办有关SQL注入的研讨会，所以请不要重写解决方案！

I am doing a workshop on SQL injection, so please dont rewrite the solution!

推荐答案

SQLAlchemy自动提交的工作方式是检查发出的语句，尝试

The way SQLAlchemy's autocommit works is that it inspects the issued statements, trying to detect whether or not data is modified:

...，SQLAlchemy实现了自己的自动提交"功能，该功能在所有后端完全一致地工作.这是通过检测代表数据更改操作的语句(即INSERT，UPDATE，DELETE)以及数据定义语言(DDL)语句(如CREATE TABLE，ALTER TABLE)，然后在没有事务进行时自动发出COMMIT来实现的.该检测基于语句上是否存在autocommit=True执行选项.如果该语句是纯文本语句且未设置标志，则使用正则表达式来检测INSERT，UPDATE，DELETE以及用于特定后端的各种其他命令.

..., SQLAlchemy implements its own "autocommit" feature which works completely consistently across all backends. This is achieved by detecting statements which represent data-changing operations, i.e. INSERT, UPDATE, DELETE, as well as data definition language (DDL) statements such as CREATE TABLE, ALTER TABLE, and then issuing a COMMIT automatically if no transaction is in progress. The detection is based on the presence of the autocommit=True execution option on the statement. If the statement is a text-only statement and the flag is not set, a regular expression is used to detect INSERT, UPDATE, DELETE, as well as a variety of other commands for a particular backend

因为不支持多个结果集在SQLAlchemy级别，在第一个示例中，检测只是省略了发出COMMIT，因为 first 语句是SELECT，而在第二个示例中，它是UPDATE.没有尝试从多个语句中检测到数据修改语句.

Since multiple result sets are not supported at SQLAlchemy level, in your first example the detection simply omits issuing a COMMIT because the first statement is a SELECT, where as in your second example it is an UPDATE. No attempt to detect data modifying statements from multiple statements takes place.

如果您查看 ，您会看到它执行了 regex与 AUTOCOMMIT_REGEXP匹配 .换句话说，它仅在文本开头匹配.

If you look at PGExecutionContext.should_autocommit_text(), you'll see that it does a regex match against AUTOCOMMIT_REGEXP. In other words it matches only at the beginning of the text.

这篇关于通过SQLAlchemy使用Postgresql执行多个语句不会持久保存更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！