binding parameters in quotes

[mdob]

Hi, 

Is it correct behavior that parameter placeholders in quotes e.g. SELECT * FROM Artist WHERE Name LIKE "%:pattern%" are recognized as valid parameters? 

from sqlalchemy.sql import text
from sqlalchemy.dialects import sqlite
from sqlalchemy import create_engine
engine = create_engine('sqlite:////home/mike/Chinook.sqlite', echo=True)


s = 'SELECT * FROM Artist WHERE Name LIKE "%:pattern%"'
q = text(s)

c = q.compile()
print c.params

{'pattern': None}

If parameter is provided

engine.execute(q, {'pattern': 'foo'})

engine echo:

2019-03-20 12:44:14,668 INFO sqlalchemy.engine.base.Engine SELECT * FROM Artist WHERE Name LIKE '%?' 
2019-03-20 12:44:14,669 INFO sqlalchemy.engine.base.Engine ('foo',)

and error is raised

ProgrammingError: (sqlite3.ProgrammingError) Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied. [SQL: u'SELECT * FROM Artist WHERE Name LIKE "%?%"'] [parameters: ('foo',)] (Background on this error at: http://sqlalche.me/e/f405)

When executed without parameters
engine.execute(q, {'pattern': 'foo'})

different error is raised

StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'pattern' [SQL: u'SELECT * FROM Artist WHERE Name LIKE "%?%"'] (Background on this error at: http://sqlalche.me/e/cd3x)

It feels to me like finding parameters in https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/compiler.py#L895 should exclude placeholders inside quotes.

Michal

[Mike Bayer]

This is not how bound parameters work in relational databases. Bound
parameters are not Python substitution strings, they are more often
than not processed by the server, or at least have to behave as though
they were. This means you cannot assume a parameter is substituted
inside of a value as you are doing here. The correct syntax for what
you are trying to do is:

s = 'SELECT * FROM Artist WHERE Name LIKE '%' || :pattern || '%'

that is, the concatenation you are doing is explicit in SQL.


>
> Michal
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Victor Olex]

Thanks Mike, though the question is valid - why does regex in SQLAlchemy allow for discovering parameter token inside quotes? Have you seen a legitimate case for that?

[Mike Bayer]

On Thu, Mar 21, 2019 at 3:33 PM Victor Olex
<victo...@vtenterprise.com> wrote:
>
> Thanks Mike, though the question is valid - why does regex in SQLAlchemy allow for discovering parameter token inside quotes? Have you seen a legitimate case for that?

the regex in SQLAlchemy is not a SQL parser, it's just putting bound
parameters where the user typed them. It does not detect invalid
SQL, that's the database's job.

[Victor Olex]

It will however produce parameter for certain string literals in a valid query. It may be a minute detail, but since you have that regex for detecting parameters it might well avoid that (unless there is a case).

>>> from sqlalchemy.sql import text
>>> q = text("SELECT * from T WHERE C1 = :param1 and C2 = 'Group :A'")
>>> print(q.compile())
SELECT * from T WHERE C1 = :param1 and C2 = 'Group :A'
>>> print(q.compile().params)
{'param1': None, 'A': None}

[Mike Bayer]

On Fri, Mar 22, 2019 at 1:59 PM Victor Olex
<victo...@vtenterprise.com> wrote:
>
> It will however produce parameter for certain string literals in a valid query. It may be a minute detail, but since you have that regex for detecting parameters it might well avoid that (unless there is a case).
>
> >>> from sqlalchemy.sql import text
> >>> q = text("SELECT * from T WHERE C1 = :param1 and C2 = 'Group :A'")
> >>> print(q.compile())
> SELECT * from T WHERE C1 = :param1 and C2 = 'Group :A'
> >>> print(q.compile().params)
> {'param1': None, 'A': None}

Hi Victor -

yes, colons may appear in string literals, and on the PostgreSQL
backend they are also part of PG's shorthand CAST operator. so there
is a need to be able to render a colon character in text(). This is
achieved by escaping the colon with a backslash, as documented at
https://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=text#sqlalchemy.sql.expression.text
which illustrates a colon inside of a literal string. Hope this
helps.