search

psycopg / psycopg2

PostgreSQL database adapter for the Python programming language
https://www.psycopg.org/
Other
3.31k stars 501 forks source link

psycopg2.extras._split_sql cannot split SQL with % in a column name #824

Closed ecederstrand closed 5 years ago

ecederstrand commented 5 years ago

This came up trying to call execute_values() with a column name that has special characters:

>>> from psycopg2.extras import execute_values
>>> execute_values(None, b'INSERT INTO foo.bar ("col_with_%") VALUES %s', [[1], [2], [3]])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3.5/site-packages/psycopg2/extras.py", line 1268, in execute_values
    pre, post = _split_sql(sql)
  File "/usr/lib/python3.5/site-packages/psycopg2/extras.py", line 1305, in _split_sql
    % token[1:].decode('ascii', 'replace'))
ValueError: unsupported format character: '"'

Possibly _split_sql should do the tokenizing from the back instead?

dvarrazzo commented 5 years ago

It would fail in different scenarios. The right thing to do is to %% the %.

This makes me thing that if any the sql.Identifier should do the same. However that would be a different bug. This is not a bug in psycopg but in your query: you have to fix it. You would have the same problem with a normal cursor.execute().