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)
Why does the first not work? It works in Pgadmin. I enabled autocommit with Flask-Sqlalchemy.
I am doing a workshop on SQL injection, so please dont rewrite the solution!
Advertisement
Answer
The way SQLAlchemy’s autocommit works is that it inspects the issued statements, trying to detect whether or not data is modified:
…, 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=Trueexecution 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
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.
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.