tlocke
commented
5 years ago Hi @rspeer, in a case like this where PostgreSQL encounters an error within a transaction you probably need to end the transaction with a rollback. Then you can carry on using the connection.
If user-supplied data contains codepoint 0 (the character represented in Python as
\x00), then when this data is passed to as a parameter to.execute()-- in a properly parameterized query -- the query will return this error:('ERROR', 'ERROR', '22021', 'invalid byte sequence for encoding "UTF8": 0x00', 'wchar.c', '2017', 'report_invalid_encoding')Future calls to
.execute()on the same connection end up being in the same transaction, at which point every call will return:('ERROR', 'ERROR', '25P02', 'current transaction is aborted, commands ignored until end of transaction block', 'postgres.c', '1568', 'exec_bind_message')until something destroys the connection, such as ending the process.
As far as I can tell, pg8000 doesn't give me a way to explicitly manage transactions, such as a context manager. (If there is such a thing, please point me to its documentation.) I was surprised that a connection could become unusable in this way due to an ostensibly read-only SELECT query, and I don't see any guidance for what I should be doing instead.
The user-supplied data in my case was
/r/Synonym\x00'||SLeeP(3)&&'1. (Yes, this indicates that someone is trying to devise an arbitrary code execution attack against my application in particular.)