RAW SQL working on Postgres but not in SQLAlchemy

[Scheck David]

Hi,

I've a simple query as this : 

    def count_references(self, uri):

        sql = 'SELECT count(*) FROM (SELECT image.id, json_array_elements(image.uri_reference)::text as uri_ref FROM image) ss WHERE ss.uri_ref = \'\"{0}\"\''.format(uri)

        result = self.session.execute(text(sql))

I tested It on pgadmin and all works very good. and SQLAlchemy is throwing an error as : 

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "image" does not exist

LINE 1: ...ements(image.uri_reference)::text as uri_ref FROM image) ss ...

[Mike Bayer]

this would suggest your Engine is not accessing the same database, or
does not have the same permissions, as that of your PG admin session.

also I would strongly advise against directly substituting variables
into literals in SQL strings using format() as this is the source of
SQL injection attacks. Please use a bound parameter, e.g.:

execute(text("select * from table where foo = :bar").params(bar='some bar'))

> --
> 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.

[Piyush Nalawade]

Hi Mike, 

In the above example does the text and params help to protect from SQL injection attacks? 

[Piyush Nalawade]

Hi Mike, 

And also to avoid using format, right ? 

Thanks and Regards,

Piyush Nalawade

[Jonathan Vanasco]

On Tuesday, March 26, 2019 at 1:46:37 PM UTC-4, Piyush Nalawade wrote:

Hi Mike, 

In the above example does the text and params help to protect from SQL injection attacks?  

yes.

see https://docs.sqlalchemy.org/en/latest/core/sqlelement.html?#sqlalchemy.sql.expression.text on how the raw text is handled

in terms of params,  https://docs.sqlalchemy.org/en/latest/core/sqlelement.html?#sqlalchemy.sql.expression.ClauseElement.params

passing the values in via `params` invokes the database support for bind parameters, which is what protects you from sql injection in values passed in.

[Piyush Nalawade]

Big thanks. Learned something new. 

--

[Scheck David]

Thanks for the answer Mike, I'll use your advice. It was indeed a problem of wrong database config. Thanks for the answer.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/pDuIdeSzR8Q/unsubscribe.
To unsubscribe from this group and all its topics, 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.

--

With kindest regards,

David SCHECK

PRESIDENT/DEVELOPER

Phone: +32 4 87 86 70 12
Visit our website ! https://www.sphax.org