Skip to content

Commit b5987b0

Browse files
committed
Treat # as the XOR operator it is, not as a comment marker
select 17 # 5 returned 17 instead of 20, and said nothing about it. sqlparse follows MySQL and reads # as the start of a comment, so sqlparse.format(strip_comments=True) dropped the rest of the line before the statement was sent. Reported upstream as dbcli#1646; the sqlparse side is andialbrecht/sqlparse#539. Worth saying plainly: we introduced this. Our dbcli#1559 changed that call from strip_comments=False to True so that rstrip(";") would work with a comment after the semicolon. It fixed that and broke this. The intent of dbcli#1559 only needs the TRAILING comments gone, so strip_trailing_comments() now does exactly that, with PostgreSQL's rules rather than sqlparse's: -- to end of line, /* */ which nest, and no comment markers honoured inside string literals, quoted identifiers or dollar-quoted bodies. A comment in the middle of a statement is kept, as the server would see it. # is just an operator. Both call sites move over: the one before execution in pgexecute, and _is_complete() in pgbuffer, where "select 17 # 5;" would otherwise never look finished. 15 tests.
1 parent f0e5a6b commit b5987b0

6 files changed

Lines changed: 183 additions & 5 deletions

File tree

‎changelog.rst‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,19 @@
11
Upcoming
22
========
33

4+
Bug fixes:
5+
----------
6+
7+
* Treat ``#`` as the bitwise XOR operator it is in PostgreSQL, not as a comment
8+
marker. ``select 17 # 5`` returned 17 instead of 20, silently, because
9+
``sqlparse.format(strip_comments=True)`` follows MySQL and drops everything
10+
after a ``#``. Trailing comments are now removed with PostgreSQL's own rules
11+
(``--`` to end of line, nestable ``/* */``, and markers inside string
12+
literals, quoted identifiers or dollar-quoted bodies left alone), so a
13+
comment after the final semicolon still goes and the rest of the statement
14+
reaches the server as written. Reported upstream as dbcli/pgcli#1646; the
15+
underlying sqlparse limitation is andialbrecht/sqlparse#539.
16+
417
Features:
518
---------
619

‎pgcli/packages/parseutils/__init__.py‎

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -138,3 +138,62 @@ def strip_markdown(text):
138138
return text, "" # already SQL: the common case, untouched
139139
return "\n".join(lines[index:]), "%d line(s) of text before the statement" % index
140140
return text, "" # nothing that looks like SQL: let the server complain
141+
142+
143+
def strip_trailing_comments(sql):
144+
"""Return ``sql`` without the comments that trail its last statement.
145+
146+
``sqlparse.format(strip_comments=True)`` cannot be used for this. sqlparse
147+
follows MySQL and treats ``#`` as a comment marker, but in PostgreSQL it is
148+
the bitwise XOR operator, so it eats the rest of the line: ``select 17 # 5``
149+
becomes ``select 17`` and quietly returns 17 instead of 20 (dbcli/pgcli
150+
issue #1646, andialbrecht/sqlparse issue #539).
151+
152+
This scans the text with PostgreSQL's own rules instead: ``--`` to end of
153+
line and ``/* */`` (which nest) are comments, string literals, quoted
154+
identifiers and dollar-quoted bodies are not, and ``#`` is an operator like
155+
any other. Only trailing comments are dropped, so a comment in the middle
156+
of a statement is kept as the server would see it.
157+
"""
158+
last = 0 # one past the last character that is part of the statement
159+
i, n = 0, len(sql)
160+
while i < n:
161+
ch = sql[i]
162+
if ch in " \t\r\n":
163+
i += 1
164+
elif sql.startswith("--", i):
165+
end = sql.find("\n", i)
166+
i = n if end == -1 else end + 1
167+
elif sql.startswith("/*", i):
168+
depth, i = 1, i + 2
169+
while i < n and depth:
170+
if sql.startswith("/*", i):
171+
depth, i = depth + 1, i + 2
172+
elif sql.startswith("*/", i):
173+
depth, i = depth - 1, i + 2
174+
else:
175+
i += 1
176+
elif ch in "'\"":
177+
i += 1
178+
while i < n:
179+
if sql[i] == ch:
180+
if i + 1 < n and sql[i + 1] == ch: # '' or "" escapes itself
181+
i += 2
182+
continue
183+
i += 1
184+
break
185+
i += 1
186+
last = i
187+
elif ch == "$":
188+
match = re.match(r"\$[\w]*\$", sql[i:])
189+
if match:
190+
tag = match.group(0)
191+
end = sql.find(tag, i + len(tag))
192+
i = n if end == -1 else end + len(tag)
193+
else:
194+
i += 1
195+
last = i
196+
else:
197+
i += 1
198+
last = i
199+
return sql[:last]

‎pgcli/pgbuffer.py‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
import logging
22

3-
import sqlparse
3+
from .packages.parseutils import strip_trailing_comments
44
from prompt_toolkit.enums import DEFAULT_BUFFER
55
from prompt_toolkit.filters import Condition
66
from prompt_toolkit.application import get_app
@@ -15,7 +15,10 @@ def _is_complete(sql):
1515
# CREATE FUNCTION command.
1616
# Strip trailing comments so that "SELECT 1; -- note" is recognized as
1717
# complete (the semicolon is not at the end when a comment follows).
18-
stripped = sqlparse.format(sql, strip_comments=True).strip()
18+
# Not sqlparse's strip_comments: it reads "#" as a comment marker, which in
19+
# PostgreSQL is the bitwise XOR operator (issue #1646), so "select 17 # 5;"
20+
# would never look complete.
21+
stripped = strip_trailing_comments(sql).strip()
1922
return stripped.endswith(";") and not is_open_quote(sql)
2023

2124

‎pgcli/pgexecute.py‎

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,8 @@
1010
from psycopg.conninfo import make_conninfo
1111
import sqlparse
1212

13+
from .packages.parseutils import strip_trailing_comments
14+
1315
sqlparse.engine.grouping.MAX_GROUPING_DEPTH = None
1416
sqlparse.engine.grouping.MAX_GROUPING_TOKENS = None
1517

@@ -403,10 +405,12 @@ def run(
403405
# run each sql query
404406
for sql in sqlarr:
405407
# Remove spaces, eol and semi-colons.
406-
# Strip comments first so rstrip(";") works when there are
407-
# trailing comments after the semicolon, e.g.:
408+
# Strip trailing comments first so rstrip(";") works when there are
409+
# comments after the semicolon, e.g.:
408410
# vacuum freeze verbose t; -- 82% towards emergency
409-
sql = sqlparse.format(sql, strip_comments=True).strip().rstrip(";")
411+
# Not sqlparse's strip_comments: it reads "#" as a comment marker,
412+
# which in PostgreSQL is the bitwise XOR operator (issue #1646).
413+
sql = strip_trailing_comments(sql).strip().rstrip(";")
410414
sql = sql.strip()
411415
if not sql:
412416
continue
Lines changed: 84 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,84 @@
1+
"""Dropping the comments that trail a statement.
2+
3+
sqlparse cannot do this for PostgreSQL: it follows MySQL and reads ``#`` as a
4+
comment marker, while in PostgreSQL ``#`` is the bitwise XOR operator. That is
5+
dbcli/pgcli issue #1646 and andialbrecht/sqlparse issue #539: `select 17 # 5`
6+
silently returns 17 instead of 20.
7+
"""
8+
9+
from pgcli.packages.parseutils import strip_trailing_comments
10+
11+
12+
def test_hash_is_an_operator_not_a_comment():
13+
"""The reported bug: everything after # was dropped."""
14+
assert strip_trailing_comments("select 17 # 5") == "select 17 # 5"
15+
assert strip_trailing_comments("select 17 # 5;") == "select 17 # 5;"
16+
17+
18+
def test_hash_keeps_its_own_trailing_comment_separate():
19+
assert strip_trailing_comments("select 17 # 5; -- note") == "select 17 # 5;"
20+
21+
22+
def test_trailing_line_comment_is_dropped():
23+
"""What #1559 fixed: rstrip(';') needs the comment gone first."""
24+
assert strip_trailing_comments("vacuum freeze verbose t; -- 82% towards emergency") == "vacuum freeze verbose t;"
25+
26+
27+
def test_trailing_block_comment_is_dropped():
28+
assert strip_trailing_comments("select 1; /* done */") == "select 1;"
29+
30+
31+
def test_nested_block_comments():
32+
"""PostgreSQL block comments nest, unlike C."""
33+
assert strip_trailing_comments("select 1; /* a /* b */ c */") == "select 1;"
34+
35+
36+
def test_several_trailing_comments():
37+
assert strip_trailing_comments("select 1;\n-- one\n/* two */\n-- three") == "select 1;"
38+
39+
40+
def test_comments_in_the_middle_are_kept():
41+
"""Only trailing comments go: the server should see the rest as written."""
42+
for sql in ("select 1, /* keep */ 2;", "select 1\n-- keep\n, 2;"):
43+
assert strip_trailing_comments(sql) == sql
44+
45+
46+
def test_comment_markers_inside_strings_are_text():
47+
for sql in ("select '-- not a comment';", "select 'a /* b */ c';", "select 'a # b';"):
48+
assert strip_trailing_comments(sql) == sql
49+
50+
51+
def test_doubled_quotes_inside_strings():
52+
sql = "select 'it''s -- fine';"
53+
assert strip_trailing_comments(sql) == sql
54+
55+
56+
def test_quoted_identifiers():
57+
sql = 'select 1 as "-- odd name";'
58+
assert strip_trailing_comments(sql) == sql
59+
60+
61+
def test_dollar_quoted_body_is_left_alone():
62+
"""A function body holds its own comments and must survive intact."""
63+
sql = "do $$ begin -- inside\n perform 1; end $$;"
64+
assert strip_trailing_comments(sql) == sql
65+
tagged = "create function f() returns int as $body$ select 1; -- inner\n$body$ language sql;"
66+
assert strip_trailing_comments(tagged) == tagged
67+
68+
69+
def test_only_a_comment_leaves_nothing():
70+
assert strip_trailing_comments("-- just a note") == ""
71+
assert strip_trailing_comments("/* just a note */") == ""
72+
73+
74+
def test_empty_and_whitespace():
75+
assert strip_trailing_comments("") == ""
76+
assert strip_trailing_comments(" \n ") == ""
77+
78+
79+
def test_unterminated_block_comment():
80+
assert strip_trailing_comments("select 1; /* never closed") == "select 1;"
81+
82+
83+
def test_unterminated_string_does_not_hang():
84+
assert strip_trailing_comments("select 'never closed") == "select 'never closed"

‎tests/test_trailing_comments.py‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,3 +45,18 @@ def test_multiple_semicolons_with_comment(self):
4545

4646
def test_comment_with_special_chars(self):
4747
assert _is_complete("VACUUM ANALYZE; -- 81.0% towards emergency, 971 MB") is True
48+
49+
50+
class TestIsCompleteWithTheXorOperator:
51+
"""``#`` is bitwise XOR in PostgreSQL. While trailing comments were being
52+
stripped with sqlparse, which reads ``#`` as MySQL does, a statement using
53+
it never looked complete (dbcli/pgcli#1646)."""
54+
55+
def test_xor_statement_is_complete(self):
56+
assert _is_complete("select 17 # 5;") is True
57+
58+
def test_xor_statement_with_a_trailing_comment(self):
59+
assert _is_complete("select 17 # 5; -- note") is True
60+
61+
def test_xor_without_a_semicolon_is_not_complete(self):
62+
assert _is_complete("select 17 # 5") is False

0 commit comments

Comments
 (0)