Incorrect PostgreSQL escaping for schema name exclusion in query
amillet opened this issue · 1 comments
Hello,
I've encountered an issue related to PostgreSQL escaping within a query that is intended to exclude certain schema names (for example pgmain), specifically those starting with pg_. The query in question does not correctly escape the underscore character. Here's the problematic query:
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT LIKE 'pg\_%'
AND schema_name != 'information_schema'
According to the PostgreSQL documentation (https://www.postgresql.org/docs/current/ddl-schemas.html), schema names beginning with pg_ are reserved for system. The documentation implies that to correctly escape the underscore in SQL queries, a double backslash (\\
) should be used instead of a single backslash (\
).
Therefore, the corrected version of the query should be:
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT LIKE 'pg\\_%'
AND schema_name != 'information_schema'
Do you think we can modify the impacted code? I believe there are several queries involved.
Yes. Please send a PR, with tests demonstrating the bug.