Skip to content

text() colon-escaping behaviour #3644

New issue
New issue
Closed
Closed
text() colon-escaping behaviour#3644
Labels
bugSomething isn't workinglow prioritysql
Milestone
 
1.0.xx
@sqlalchemy-bot

Description

@sqlalchemy-bot
sqlalchemy-bot
opened on Feb 3, 2016
Collaborator

Migrated issue, originally created by Sebastian Bank (@xflr6)

Using the postgres double colon shortcut for CAST(expression AS type), e.g.:

import sqlalchemy as sa
engine = sa.create_engine('postgresql://postgres@/')
engine.execute('SELECT * FROM pg_attribute WHERE attrelid = %(tab)s::regclass', tab='pg_class')

Trying this with text, I think the docs would suggest using \:s:

engine.execute(sa.text('SELECT * FROM pg_attribute WHERE attrelid = :tab\:\:regclass'), tab='pg_class')

But this raises ProgrammingError: (psycopg2.ProgrammingError) (rendered as SELECT * FROM pg_attribute WHERE attrelid = %(tab)s\::regclass).

As expected, this also raises:

engine.execute(sa.text('SELECT * FROM pg_attribute WHERE attrelid = :tab::regclass'), tab='pg_class')

(rendered as SELECT * FROM pg_attribute WHERE attrelid = :tab::regclass)

This finally works (extra space):

engine.execute(sa.text('SELECT * FROM pg_attribute WHERE attrelid = :tab ::regclass'), tab='pg_class')

But is this the intended way?

Activity

sqlalchemy-bot

sqlalchemy-bot commented on Feb 3, 2016

@sqlalchemy-bot
sqlalchemy-bot
on Feb 3, 2016
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

it's a bug. here's a patch:

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index dbaa23a..cc9a49a 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -53,7 +53,7 @@ LEGAL_CHARACTERS = re.compile(r'^[A-Z0-9_$]+$', re.I)
 ILLEGAL_INITIAL_CHARACTERS = set([str(x) for x in range(0, 10)]).union(['$'])
 
 BIND_PARAMS = re.compile(r'(?<![:\w\$\x5c]):([\w\$]+)(?![:\w\$])', re.UNICODE)
-BIND_PARAMS_ESC = re.compile(r'\x5c(:[\w\$]+)(?![:\w\$])', re.UNICODE)
+BIND_PARAMS_ESC = re.compile(r'\x5c(:[\w\$]*)(?![:\w\$])', re.UNICODE)
 
 BIND_TEMPLATES = {
     'pyformat': "%%(%(name)s)s",
sqlalchemy-bot

sqlalchemy-bot commented on Feb 3, 2016

@sqlalchemy-bot
sqlalchemy-bot
on Feb 3, 2016
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • set milestone to "1.0.xx"
sqlalchemy-bot

sqlalchemy-bot commented on Feb 9, 2016

@sqlalchemy-bot
sqlalchemy-bot
on Feb 9, 2016
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

  • Fixed bug in :func:.expression.text construct where a double-colon
    expression would not escape properly, e.g. some\:\:expr, as is most
    commonly required when rendering Postgresql-style CAST expressions.
    fixes text() colon-escaping behaviour #3644

→ 29dcaa2

sqlalchemy-bot

sqlalchemy-bot commented on Feb 9, 2016

@sqlalchemy-bot
sqlalchemy-bot
on Feb 9, 2016
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

  • Fixed bug in :func:.expression.text construct where a double-colon
    expression would not escape properly, e.g. some\:\:expr, as is most
    commonly required when rendering Postgresql-style CAST expressions.
    fixes text() colon-escaping behaviour #3644

(cherry picked from commit 29dcaa2)

→ 498b072

sqlalchemy-bot

sqlalchemy-bot commented on Feb 9, 2016

@sqlalchemy-bot
sqlalchemy-bot
on Feb 9, 2016
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • changed status to closed
sqlalchemy-bot

sqlalchemy-bot commented on Feb 9, 2016

@sqlalchemy-bot
sqlalchemy-bot
on Feb 9, 2016
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

wow, what the crap, the compiler.py isn't in the commit

sqlalchemy-bot

sqlalchemy-bot commented on Feb 9, 2016

@sqlalchemy-bot
sqlalchemy-bot
on Feb 9, 2016
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • changed status to reopened
sqlalchemy-bot

sqlalchemy-bot commented on Feb 9, 2016

@sqlalchemy-bot
sqlalchemy-bot
on Feb 9, 2016
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

OK, it got inadvertently committed in c1316a2 for master, is not in 1.0

sqlalchemy-bot

sqlalchemy-bot commented on Feb 9, 2016

@sqlalchemy-bot
sqlalchemy-bot
on Feb 9, 2016
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

→ 9a156c5

sqlalchemy-bot

sqlalchemy-bot commented on Feb 9, 2016

@sqlalchemy-bot
sqlalchemy-bot
on Feb 9, 2016
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • changed status to closed
sqlalchemy-bot
closed this as completedon Feb 9, 2016
sqlalchemy-bot
added
sql
bugSomething isn't working
low priority
on Nov 27, 2018
sqlalchemy-bot
added this to the 1.0.xx milestone on Nov 27, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workinglow prioritysql

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @sqlalchemy-bot

        Issue actions
          text() colon-escaping behaviour · Issue #3644 · sqlalchemy/sqlalchemy