On 07/27/2016 10:11 PM, Andrew M wrote:
FYI, there seems to be a limitation of the implementation of params for
textual SQL - params appears to silently insert single quotes,
preventing the use of params to describe column names in the SQL query.
that's SQL . Bound parameters are not Python string interpolations.
They are specifically for values and do not act as substitutions for
identifiers. If you want just plain string interpolations, use
string.format().
The workaround is to use a conventional string with Python .format
(which in my mind begs the question as to why SQLAlchemy needs its own
method handling params ... ?).
Bound parameters are an essential part of SQL. They allow a SQL
statement to be defined semantically without any data inside of them.
Some databases use these semantic structures to produce query plans, or
represent them as prepared statements, both of which can be reused for
any number of parameter sets. Beyond that, bound parameters provide a
layer of data marshaling from the database Python driver (DBAPI) to the
native protocol used by the backend (note that bound parameters are
typically typed). Most famously, bound parameters provide the only way
to guarantee against SQL injection attacks by creating a full barrier
between the intent of the statement (e.g. its structure) and data values
provided from the outside.
links:
https://en.wikipedia.org/wiki/Prepared_statement - note that bound
parameters are used in non-prepared statements as well, often the DBAPI
drivers will in fact do the string escaping themselves when traditional
server side binding is not available
https://www.python.org/dev/peps/pep-0249/#id15 - DBAPI execute(), see
paramstyle
http://use-the-index-luke.com/sql/where-clause/bind-parameters
To explain, I was trying to use a parameter for the column name as well
as the value:
session.query(Table).filter(:column_name =
:value).params(column_name='foo', value = 'bar')
And when this didn't work, I tried putting :value in single quotes:
session.query(Table).filter(:column_name =
':value').params(column_name='foo', value = 'bar')
This threw an error which revealed that params was inserting single
quotes (and preventing the use of params to describe column names, as
column names can't be encapsulated in SQL):
LINE 1: SELECT * FROM Table WHERE 'foo' = ''bar'' ...
My workaround is to use from_statement with Python format:'
session.query(Table).from_statement("SELECT * FROM Table WHERE
{column_name} = '{value}'".format(column_name='foo', value='bar'))
Regards,
Andrew
p.s. Am loving SQLAlchemy, thank you.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
