On Thu, Mar 21, 2019 at 3:33 PM Victor Olex
<victor.o...@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.
>
> On Wednesday, March 20, 2019 at 9:58:58 AM UTC-4, Mike Bayer wrote:
>>
>> On Wed, Mar 20, 2019 at 7:59 AM mdob <mike.do...@gmail.com> wrote:
>> >
>> > 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.
>>
>> 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.
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
