Using E'' escapes for Postgres

[O B]

Hello! Does SQLAlchemy use E'' escapes for PostgreSQL? Postgres recommends using E'' since version 8.1 (released in 2005) and requires it since… not sure, I think since 9.0 or 9.1.

I.e. when I want to insert 'xy\\nzy' instead of

INSERT INTO test (somestring) VALUES ('xy\\\\nzy');

SA should execute

INSERT INTO test (somestring) VALUES (E'xy\\nzy');

Currently I use SQLAlchemy 0.7.3 (yes, I know it's old, I'm ready to upgrade) and Postgres issues warnings like this: "WARNING:  nonstandard use of escape in a string literal."

[mike bayer]

On 11/16/2016 09:33 AM, O B wrote:
> Hello! Does SQLAlchemy use E'' escapes for PostgreSQL? Postgres
> recommends

> <https://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-CONSTANTS,>

> using E'' since version 8.1 (released in 2005) and requires it since…
> not sure, I think since 9.0 or 9.1.|

SQLAlchemy sends bound parameters to the database driver, which is
usually psycopg2. You'd have to look at the driver to see what its
behavior is (though I'm pretty sure psycopg2 does the "E" thing).

> |
> I.e. when I want to insert |'xy\\nzy'|instead of
> |

> INSERT INTO test (somestring)VALUES ('xy\\\\nzy');

> |
>
> SA should execute
> |||
> |
> INSERT INTO test (somestring)VALUES (E'xy\\nzy');

When you send a SQL string that includes literal values directly in the
statement without use of bound parameters, that's the string that goes
in. SQLAlchemy doesn't modify literal SQL strings in any way before
passing to the database driver.

It is generally considered to be a poor security practice to embed
literal values in SQL statements without using bound parameters.

> |
> ||
> ||Currently I use SQLAlchemy 0.7.3 (yes, I know it's old, I'm ready to
> upgrade)||| and Postgres issues warnings like this: "WARNING:
> nonstandard use of escape in a string literal."
>

> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[O B]

Hi! Thanks! I see E'' escaping in the psycopg2 sources. May be I have too old psycopg2.

среда, 16 ноября 2016 г., 18:10:19 UTC+3 пользователь Mike Bayer написал: