Re: [sqlalchemy] Security Implications of Directly Executing Queries Compiled to Strings

[Mike Bayer]

On Fri, Mar 8, 2019 at 6:09 PM Walter Askew  wrote:
>
>
>
> On Mar 8, 2019, at 11:57 AM, Mike Bayer  wrote:
>
> this use is insecure and is not supported.  SQLAlchemy's bound
> parameter rendering is only intended for debugging and for special DDL
> scenarios and should not be used for ordinary SQL statements passed to
> a database.
>
>
> Would you mind expanding upon why this use is insecure? What sort of problems 
> would you expect to encounter?
> Any suggestions for how you’d put together a secure version of this use case?

Datatypes like Integer() when rendering the literal value pass the
incoming input directly to str() which goes straight to the database.
There is no step to test that the value is actually a number,
things like that.   I'd like the literal_binds flag to remain
available as it is extremely useful and necessary for some use cases,
but I don't want it turned into a CVE which would imply a great deal
of effort require to lock it down, when the DBAPIs already perform
this function when used correctly.   So it is important that I remain
absolutely clear that the literal_binds flag was not intended to be
used this way.

If this were my project, I'd not struggle with the HTTP interface at
all, I'd get a dump of the entire database and copy it into an actual
database backend and have my users query from that.  However, failing
that, I would ensure strong type coercion using a form validation
library (things like, if a field is expected to be an integer, it
makes sure the value is in fact an integer) and I'd probably disallow
quote characters inside of strings.   Id ensure that the SQL database
account does not allow any write operations of any kind from this
interface. I'd probably create my own mini-query language on top
that translates into SQL so that there is no direct link between
untrusted users typing commands and what gets sent to any kind of
backend.Basically this is not something I'd be comfortable doing
casually.



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

Re: [sqlalchemy] contains_eager option on polymorphic joined query

[Mike Bayer]

On Fri, Mar 8, 2019 at 3:56 PM  wrote:
>
> Hi everyone!
>
> I am trying to use the contains_eager option on a simple polymorphic query 
> that looks like:
>
> class Superclass():
> common_relationship = relationship('Common', ...)
> discriminator_field = Column(String...)
> __mapper_args__ = {'polymorphic_identity': 'superclass', 
> 'polymorphic_on': discriminator_field}
>
> class Subclass(Superclass):
> fields...
>
> poly = with_polymorphic(Superclass, '*')
> db.session.query(poly).options(contains_eager(poly.common_relationship)).join(poly.common_relationship).filter(Common.id
>  == 1).all()
>
> This code throws an error when I execute the query:
>
> sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) subquery uses 
> ungrouped column "common.id" from outer query

this example is very vague and does not indicate what you are doing in
full.  Below is your example turned into a working mapping including
your exact query using Postgresql, works fine, and notably does not
generate a subquery which would only occur if there was more going on
above. Please provide a complete MCVE, most likely by modifying the
fully runnable code below, thanks!

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import contains_eager
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm import with_polymorphic

Base = declarative_base()


class Superclass(Base):
__tablename__ = "s1"

id = Column(Integer, primary_key=True)
common_id = Column(ForeignKey("c.id"))

common_relationship = relationship("Common")
discriminator_field = Column(String)
__mapper_args__ = {
"polymorphic_identity": "superclass",
"polymorphic_on": discriminator_field,
}


class Subclass(Superclass):
__tablename__ = "s2"
id = Column(ForeignKey("s1.id"), primary_key=True)
__mapper_args__ = {"polymorphic_identity": "subclass"}


class Common(Base):
__tablename__ = "c"
id = Column(Integer, primary_key=True)


e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)
s.add(Subclass(common_relationship=Common()))
s.commit()

poly = with_polymorphic(Superclass, "*")
s.query(poly).options(contains_eager(poly.common_relationship)).join(
poly.common_relationship
).filter(Common.id == 1).all()

query at the end:

2019-03-08 23:50:11,847 INFO sqlalchemy.engine.base.Engine SELECT c.id
AS c_id, s1.id AS s1_id, s1.common_id AS s1_common_id,
s1.discriminator_field AS s1_discriminator_field, s2.id AS s2_id
FROM s1 LEFT OUTER JOIN s2 ON s1.id = s2.id JOIN c ON c.id = s1.common_id
WHERE c.id = %(id_1)s
2019-03-08 23:50:11,847 INFO sqlalchemy.engine.base.Engine {'id_1': 1}




>
>
> Removing the contains_eager option and replacing it with a lazy or join 
> option does solve the problem, but introduces overhead to my SQL query. What 
> I am trying to achieve here is for the query to not perform two joins (once 
> for the join to filter on, and once for the join to populate the 
> poly.common_relationship field). Am I approaching this entirely wrong here?
>
> Thanks in advance!
>
> Jay
>
> This e-mail is private and confidential and is for the addressee only. If 
> misdirected, please notify us by telephone, confirming that it has been 
> deleted from your system and any hard copies destroyed. You are strictly 
> prohibited from using, printing, distributing or disseminating it or any 
> information contained in it save to the intended recipient.
>
> --
> 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 

Re: [sqlalchemy] Security Implications of Directly Executing Queries Compiled to Strings

[Walter Askew]

> On Mar 8, 2019, at 11:57 AM, Mike Bayer  wrote:
> 
> this use is insecure and is not supported.  SQLAlchemy's bound
> parameter rendering is only intended for debugging and for special DDL
> scenarios and should not be used for ordinary SQL statements passed to
> a database.

Would you mind expanding upon why this use is insecure? What sort of problems 
would you expect to encounter?
Any suggestions for how you’d put together a secure version of this use case?

-- 
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] Making Python3 list from set returned by SQL query

[Rich Shepard]

Two classes in the model have columns with values constrained to specific
text strings; one table has two such columns, another table has one.

Because new strings might be added to the lists of allowed values for that
column each has the acceptable values as rows in a table rather than in a
column constraint.

SQL queries processed by SA to select rows in each table return sets. How do
I transpose each set to a python list can will be used by tkinter to display
them in a ttk.Combobox?

Because these lists need to be present before the classes for the associated
tables are processed how should the queries and lists be represented in the
view module? In a separate class?

If the answers are in the SA docs please point me to them.

And, if my questions are not sufficiently clear let me know and I'll try to
be more explicit.

TIA,

Rich

--
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] contains_eager option on polymorphic joined query

[jay . chia]

Hi everyone!

I am trying to use the contains_eager option on a simple polymorphic query 
that looks like:

class Superclass():
common_relationship = relationship('Common', ...)
discriminator_field = Column(String...)
__mapper_args__ = {'polymorphic_identity': 'superclass', 
'polymorphic_on': discriminator_field}

class Subclass(Superclass):
fields...

poly = with_polymorphic(Superclass, '*')
db.session.query(poly).options(contains_eager(poly.common_relationship)).
join(poly.common_relationship).filter(Common.id == 1).all()

This code throws an error when I execute the query:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) subquery uses 
ungrouped column "common.id" from outer query

Removing the contains_eager option and replacing it with a lazy or join 
option does solve the problem, but introduces overhead to my SQL query. 
What I am trying to achieve here is for the query to not perform two joins 
(once for the join to filter on, and once for the join to populate the 
poly.common_relationship field). Am I approaching this entirely wrong here?

Thanks in advance!

Jay

-- 
This e-mail is private and confidential and is for the addressee only. If 
misdirected, please notify us by telephone, confirming that it has been 
deleted from your system and any hard copies destroyed. You are strictly 
prohibited from using, printing, distributing or disseminating it or any 
information contained in it save to the intended recipient.

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

Re: [sqlalchemy] delete orphan unfavorite operations

[Mike Bayer]

On Fri, Mar 8, 2019 at 3:05 PM Conferency  wrote:
>
> Thanks.
>
> I didn't use association proxy.
>
> > "because you can't add Tool to Person.tools". This is real code and I can 
> > directly append the tool instance.

that will cause this error:

sqlalchemy.orm.exc.FlushError: Attempting to flush an item of type
 as a member of collection
"User.user_keywords". Expected an object of type  or a polymorphic subclass of this type.


Feel free to send a complete working example along to show what you
are doing in full.



>
> class Association(db.Model):
> ...
> id = db.Column(Integer, primary_key=True, index=True)
> tool_id = db.Column(Integer, ForeignKey('tools.id'))
> user_id = db.Column(Integer, ForeignKey(users.id'))
> user = db.relationship('User', back_populates='tool')
> tool = db.relationship('Tool', back_populates='user')
>
>
>
> On Friday, March 8, 2019 at 8:34:53 AM UTC-5, Mike Bayer wrote:
>>
>> On Thu, Mar 7, 2019 at 4:46 PM Conferency  wrote:
>> >
>> > Hi, I have 3 classes: two have many to many relationship between them, one 
>> > is association class.
>> >
>> > class Person:
>> > ...
>> > tools = relationship('Association', back_populates='user', lazy=True, 
>> > cascade='all, delete-orphan')
>> >
>> > class Tool:
>> > ...
>> >users = relationship('Association', back_populates='tool', lazy=True, 
>> > cascade='all, delete-orphan')
>> >
>> >
>> > One person object has 3 tools [, , ]. 
>> > When I update the tool of the person I did
>> >
>> > person.tools = []
>> > for tool_id in [2, 4, 5]:
>> > tool = Tool.query.get(tool_id)
>> > if tool:
>> > person.tools.append(tool)
>> >
>> >
>> > This operation fails, since when I set tools to an empty list, tool 1,2,3 
>> > are deleted, so only tool 4,5 are added into the list. If I remove the 
>> > delete-orphan in the cascade, I will have some redundant data in the 
>> > database. I can remove the unwanted tools from person.tools first instead 
>> > of setting it to empty list. I'd like to know if there is a simple way to 
>> > achieve same result. Thank you.
>>
>> that's not the real code because you can't add Tool to Person.tools,
>> it refers to an "Association" object which is not pictured.  Are you
>> using the association proxy?
>>
>> If I take the example from the docs at
>> https://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects,
>> I can run the same code you have and at the end the state is correct.
>>  It does delete the association objects that are removed, but it then
>> recreates them on the append pass.  If you want it to maintain the
>> association objects that are already there, you need to manipulate the
>> association objects directly.   Try out the script below.
>>
>> from sqlalchemy import Column, Integer, String, ForeignKey
>> from sqlalchemy.orm import relationship, backref
>>
>> from sqlalchemy.ext.associationproxy import association_proxy
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy import create_engine
>> from sqlalchemy.orm import Session
>>
>> Base = declarative_base()
>>
>>
>> class User(Base):
>> __tablename__ = "user"
>> id = Column(Integer, primary_key=True)
>> name = Column(String(64))
>>
>> # association proxy of "user_keywords" collection
>> # to "keyword" attribute
>> keywords = association_proxy("user_keywords", "keyword")
>>
>> def __init__(self, name):
>> self.name = name
>>
>>
>> class UserKeyword(Base):
>> __tablename__ = "user_keyword"
>> user_id = Column(Integer, ForeignKey("user.id"), primary_key=True)
>> keyword_id = Column(Integer, ForeignKey("keyword.id"), primary_key=True)
>> special_key = Column(String(50))
>>
>> # bidirectional attribute/collection of "user"/"user_keywords"
>> user = relationship(
>> User, backref=backref("user_keywords", cascade="all, delete-orphan")
>> )
>>
>> # reference to the "Keyword" object
>> keyword = relationship("Keyword")
>>
>> def __init__(self, keyword=None, user=None, special_key=None):
>> self.user = user
>> self.keyword = keyword
>> self.special_key = special_key
>>
>>
>> class Keyword(Base):
>> __tablename__ = "keyword"
>> id = Column(Integer, primary_key=True)
>> keyword = Column("keyword", String(64))
>>
>> def __init__(self, keyword):
>> self.keyword = keyword
>>
>> def __repr__(self):
>> return "Keyword(%s)" % repr(self.keyword)
>>
>>
>> e = create_engine("sqlite://", echo=True)
>> Base.metadata.create_all(e)
>>
>> s = Session(e)
>>
>> u1 = User("foo")
>> k1, k2, k3, k4, k5 = (
>> Keyword("k1"),
>> Keyword("k2"),
>> Keyword("k3"),
>> Keyword("k4"),
>> Keyword("k5"),
>> )
>>
>> u1.keywords = [k1, k2, k3]
>> s.add_all([u1, k1, k2, k3, k4, k5])
>> s.commit()
>>
>>
>> u1.keywords = []
>> for kname in ["k2", "k4", "k5"]:
>> k = 

Re: [sqlalchemy] delete orphan unfavorite operations

[Conferency]

Thanks.

I didn't use association proxy.

> "because you can't add Tool to Person.tools". This is real code and I can 
directly append the tool instance.

class Association(db.Model):
...
id = db.Column(Integer, primary_key=True, index=True)
tool_id = db.Column(Integer, ForeignKey('tools.id'))
user_id = db.Column(Integer, ForeignKey(users.id'))
user = db.relationship('User', back_populates='tool')
tool = db.relationship('Tool', back_populates='user')



On Friday, March 8, 2019 at 8:34:53 AM UTC-5, Mike Bayer wrote:
>
> On Thu, Mar 7, 2019 at 4:46 PM Conferency > 
> wrote: 
> > 
> > Hi, I have 3 classes: two have many to many relationship between them, 
> one is association class. 
> > 
> > class Person: 
> > ... 
> > tools = relationship('Association', back_populates='user', 
> lazy=True, cascade='all, delete-orphan') 
> > 
> > class Tool: 
> > ... 
> >users = relationship('Association', back_populates='tool', lazy=True, 
> cascade='all, delete-orphan') 
> > 
> > 
> > One person object has 3 tools [, , ]. 
> When I update the tool of the person I did 
> > 
> > person.tools = [] 
> > for tool_id in [2, 4, 5]: 
> > tool = Tool.query.get(tool_id) 
> > if tool: 
> > person.tools.append(tool) 
> > 
> > 
> > This operation fails, since when I set tools to an empty list, tool 
> 1,2,3 are deleted, so only tool 4,5 are added into the list. If I remove 
> the delete-orphan in the cascade, I will have some redundant data in the 
> database. I can remove the unwanted tools from person.tools first instead 
> of setting it to empty list. I'd like to know if there is a simple way to 
> achieve same result. Thank you. 
>
> that's not the real code because you can't add Tool to Person.tools, 
> it refers to an "Association" object which is not pictured.  Are you 
> using the association proxy? 
>
> If I take the example from the docs at 
>
> https://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects,
>  
>
> I can run the same code you have and at the end the state is correct. 
>  It does delete the association objects that are removed, but it then 
> recreates them on the append pass.  If you want it to maintain the 
> association objects that are already there, you need to manipulate the 
> association objects directly.   Try out the script below. 
>
> from sqlalchemy import Column, Integer, String, ForeignKey 
> from sqlalchemy.orm import relationship, backref 
>
> from sqlalchemy.ext.associationproxy import association_proxy 
> from sqlalchemy.ext.declarative import declarative_base 
> from sqlalchemy import create_engine 
> from sqlalchemy.orm import Session 
>
> Base = declarative_base() 
>
>
> class User(Base): 
> __tablename__ = "user" 
> id = Column(Integer, primary_key=True) 
> name = Column(String(64)) 
>
> # association proxy of "user_keywords" collection 
> # to "keyword" attribute 
> keywords = association_proxy("user_keywords", "keyword") 
>
> def __init__(self, name): 
> self.name = name 
>
>
> class UserKeyword(Base): 
> __tablename__ = "user_keyword" 
> user_id = Column(Integer, ForeignKey("user.id"), primary_key=True) 
> keyword_id = Column(Integer, ForeignKey("keyword.id"), 
> primary_key=True) 
> special_key = Column(String(50)) 
>
> # bidirectional attribute/collection of "user"/"user_keywords" 
> user = relationship( 
> User, backref=backref("user_keywords", cascade="all, 
> delete-orphan") 
> ) 
>
> # reference to the "Keyword" object 
> keyword = relationship("Keyword") 
>
> def __init__(self, keyword=None, user=None, special_key=None): 
> self.user = user 
> self.keyword = keyword 
> self.special_key = special_key 
>
>
> class Keyword(Base): 
> __tablename__ = "keyword" 
> id = Column(Integer, primary_key=True) 
> keyword = Column("keyword", String(64)) 
>
> def __init__(self, keyword): 
> self.keyword = keyword 
>
> def __repr__(self): 
> return "Keyword(%s)" % repr(self.keyword) 
>
>
> e = create_engine("sqlite://", echo=True) 
> Base.metadata.create_all(e) 
>
> s = Session(e) 
>
> u1 = User("foo") 
> k1, k2, k3, k4, k5 = ( 
> Keyword("k1"), 
> Keyword("k2"), 
> Keyword("k3"), 
> Keyword("k4"), 
> Keyword("k5"), 
> ) 
>
> u1.keywords = [k1, k2, k3] 
> s.add_all([u1, k1, k2, k3, k4, k5]) 
> s.commit() 
>
>
> u1.keywords = [] 
> for kname in ["k2", "k4", "k5"]: 
> k = s.query(Keyword).filter_by(keyword=kname).one() 
> u1.keywords.append(k) 
>
> s.commit() 
>
> print(u1.keywords) 
>
>
> output at the end: 
>
> [Keyword('k2'), Keyword('k4'), Keyword('k5')] 
>
>
>
>
> > 
> > -- 
> > 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 

Re: [sqlalchemy] Security Implications of Directly Executing Queries Compiled to Strings

[Mike Bayer]

On Fri, Mar 8, 2019 at 2:00 PM Walt  wrote:
>
>
>
> On Friday, March 8, 2019 at 12:19:54 PM UTC-6, Mike Bayer wrote:
>>
>>
>> I've re-read your original request.   If I am reading correctly, *you*
>> are generating these values and passing them to an HTTP web service of
>> some kind.  In this scenario, there is no untrusted input, so there is
>> no security concern. If OTOH you are receiving values *from* an
>> HTTP web service that is open to untrusted users, then you need to be
>> concerned about security.
>
>
> Maybe the HTTP stuff is obscuring my question. Essentially what I'm asking is 
> the difference between
>
> query = sqlalchemy.select()
> conn.execute(query)
> vs.
> conn.execute(query.compile(compile_kwargs={"literal_binds": True, 
> dialect=dialect_postgresql.dialect()}).string)
>
> particularly in contexts where the query contains literals provided by 
> untrusted users.

this use is insecure and is not supported.  SQLAlchemy's bound
parameter rendering is only intended for debugging and for special DDL
scenarios and should not be used for ordinary SQL statements passed to
a database.


>
> I'm in the situation where I have to query a database by sending SQL strings 
> through an HTTP service rather than by connecting to the database directly, 
> and I'm generating SQL strings in contexts where I have to consider untrusted 
> user input.

you should sanitize all user input, both untrusted and trusted (since
browsers cannot be trusted),  before passing for literal rendering.

>
>
>> It's not about the "type" but the "context".  The library is large and 
>> supports many dialects and dbapi drivers.  Over time, that has meant many 
>> things have had to support 'textual arguments' and supplying literal SQL to 
>> certain commands.  (see 
>> https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#orm-tutorial-literal-sql
>>  )
>
>
> Thanks - this is helpful!
>
> --
> 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.

Re: [sqlalchemy] Re: Security Implications of Directly Executing Queries Compiled to Strings

['Van Klaveren, Brian N.' via sqlalchemy]

As has been said, if you are generating the SQL, you will be fine so long as 
you use parameters and no blind string interpolation.

This isn't really any different that any other API in that regard - obviously 
you don't want to allow a non-substituted first name field of the form `'; DROP 
TABLE USERS;`

If you are worried about security in terms of accepting SQL (which sounds like 
it's a different concern than yours), you will really want a full-fledged 
parser.

In astronomy, we have a long history of actually letting users execute SQL 
directly via a REST API (there's even a specification called the "Table Access 
Protocol"). There's some SQL parsers out there in Python, but not much that's 
easily reusable or full features. I had started porting the Presto parser to 
Python (github.com/slaclab/lacquer)  but I 
ended up with something kind of buggy (someday I'll try to get back to it... 
Antlr4 has good Python support now) and we had switched that service to Java 
because we needed better threading and I switched to directly using the Presto 
SQL parser. You could easily write a query validator, for example as a CLI or 
as a REST API, to verify there's only one statement with the presto framework 
if you want good SQL support and to verify the statements and operations are 
okay. This is probably out of scope for your needs, but it may be something to 
bring up to your team if you are worried about security more generally for this 
service.

Brian


On Mar 8, 2019, at 9:56 AM, Walt 
mailto:waltas...@gmail.com>> wrote:



On Friday, March 8, 2019 at 11:32:01 AM UTC-6, Jonathan Vanasco wrote:
Do you control the HTTP API or is this someone else's system?

It's someone else's. I'm living in a world where folks have generated their SQL 
with regular old string processing & interpolation in Python because there's no 
database connection to work with. I'm hoping to replace this with sqlalchemy + 
.compile()-ing.

Does the API just execute the raw sql it is provided, and you're looking to 
generate that?  What you want to do is pipe a SqlAlchemy query into a function 
that can compile it into the right statement for your database.  Below is an 
example of PostgreSQL that worked on SqlAlchemy 1 (probably 1.3. too but I 
haven't tested):

Yep, just executes it raw. I've got the basic examples working, I just wanted 
to understand the limitations of using SQLAlchemy vs. DBAPI literal binding, 
particularly any security implications, before I start advocating for more 
people in my organization to start using this workflow.

The security concerns you brought up deal with how/what SqlAlchemy treats as 
trusted user input or not.  Most functions in SqlAlchemy will escape the values 
by default, very few will not and are documented with a dragon in the database. 
 If you are using values for those items in Sql you need to filter them 
yourself.

Thanks, so the limitations the documentation is bringing up are more that 
SQLAlchemy doesn't know how to bind the same variety of types as does the DBAPI?

For 99.9% of use cases though, you can just compile your sql to the database's 
dialect and just send it without worry.

Hooray!

Thank you for your response!

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

Re: [sqlalchemy] Security Implications of Directly Executing Queries Compiled to Strings

[Mark Steward]

My understanding is that this is untrusted data, providing a frontend to a
DB, where the "connection" is actually an HTTP API.

I'd be super cautious here, as there are loads of historic encoding issues
with each database backend. Don't forget newlines and null bytes. If you
can, whitelist a subset of characters that are definitely safe, limit
length, validate numbers are in the form you expect. Don't lean on sqla's
escaping because it's not intended for that purpose.

You also need to consider HTTP injection. If it's in a URL, you need to
escape slashes, question marks, etc.



Mark

On Fri, 8 Mar 2019, 18:19 Mike Bayer,  wrote:

> On Fri, Mar 8, 2019 at 10:31 AM Walter Askew  wrote:
> >
> >
> >
> > On Mar 8, 2019, at 5:25 AM, Mike Bayer  wrote:
> >
> >
> > SQL injection has to do with strings that are sent to the database
> > engine.   from what you said above, it seems like you are generating
> > strings just to display them on a webpage?
> >
> >
> > No, I’m not displaying SQL strings on a webpage. I’m asking about the
> safety of directly executing SQL strings returned by .compile() calls, in
> particular when they contain user-provided values.
>
> I've re-read your original request.   If I am reading correctly, *you*
> are generating these values and passing them to an HTTP web service of
> some kind.  In this scenario, there is no untrusted input, so there is
> no security concern. If OTOH you are receiving values *from* an
> HTTP web service that is open to untrusted users, then you need to be
> concerned about security.
>
>
> >
> > that would be the use case
> > for literal_binds.you would not pass these strings to a database
> > engine unless you sanitized all input from the user.
> >
> >
> > I’m asking for more details about the limitations SQLAlchemy has on
> sanitizing user input when compiling strings with literal_binds. The docs
> say things like:
> >
> >  SQLAlchemy has limited ability to do this stringification in certain
> circumstances such as that of emitting DDL. In order to access this
> functionality one can use the literal_binds flag, passed to compile_kwargs:
> >
> > ...
> >
> > the above approach has the caveats that it is only supported for basic
> types, such as ints and strings, and furthermore if a bindparam() witho
> pre-set value is used directly, it won’t be able to stringify that either.
> >
> >
> > and I’m wondering how limited this ability is, what the implications of
> using SQLAlchemy to bind literals rather than going through DBAPI like
> normal are, and if this is an issue if you only try to bind 'basic types,
> such as ints and strings.’
> >
> > I tried testing out a few simple SQL injection examples and saw them get
> quoted out by SQLAlchemy’s literal binding correctly, and I’m wondering how
> safe it is to rely on SQLAlchemy’s literal binding given those warnings in
> the documentation.
> >
> > --
> > 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.
>

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

Re: [sqlalchemy] Security Implications of Directly Executing Queries Compiled to Strings

[Walt]

On Friday, March 8, 2019 at 12:19:54 PM UTC-6, Mike Bayer wrote:
>
>
> I've re-read your original request.   If I am reading correctly, *you* 
> are generating these values and passing them to an HTTP web service of 
> some kind.  In this scenario, there is no untrusted input, so there is 
> no security concern. If OTOH you are receiving values *from* an 
> HTTP web service that is open to untrusted users, then you need to be 
> concerned about security. 
>

Maybe the HTTP stuff is obscuring my question. Essentially what I'm asking 
is the difference between

query = sqlalchemy.select()
conn.execute(query)
vs.
conn.execute(query.compile(compile_kwargs={"literal_binds": True, 
dialect=dialect_postgresql.dialect()}).string)

particularly in contexts where the query contains literals provided by 
untrusted users.

I'm in the situation where I have to query a database by sending SQL 
strings through an HTTP service rather than by connecting to the database 
directly, and I'm generating SQL strings in contexts where I have to 
consider untrusted user input.


It's not about the "type" but the "context".  The library is large and 
> supports many dialects and dbapi drivers.  Over time, that has meant many 
> things have had to support 'textual arguments' and supplying literal SQL to 
> certain commands.  (see 
> https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#orm-tutorial-literal-sql
>  
> 
>  ) 


Thanks - this is helpful! 

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

Re: [sqlalchemy] Security Implications of Directly Executing Queries Compiled to Strings

[Mike Bayer]

On Fri, Mar 8, 2019 at 10:31 AM Walter Askew  wrote:
>
>
>
> On Mar 8, 2019, at 5:25 AM, Mike Bayer  wrote:
>
>
> SQL injection has to do with strings that are sent to the database
> engine.   from what you said above, it seems like you are generating
> strings just to display them on a webpage?
>
>
> No, I’m not displaying SQL strings on a webpage. I’m asking about the safety 
> of directly executing SQL strings returned by .compile() calls, in particular 
> when they contain user-provided values.

I've re-read your original request.   If I am reading correctly, *you*
are generating these values and passing them to an HTTP web service of
some kind.  In this scenario, there is no untrusted input, so there is
no security concern. If OTOH you are receiving values *from* an
HTTP web service that is open to untrusted users, then you need to be
concerned about security.


>
> that would be the use case
> for literal_binds.you would not pass these strings to a database
> engine unless you sanitized all input from the user.
>
>
> I’m asking for more details about the limitations SQLAlchemy has on 
> sanitizing user input when compiling strings with literal_binds. The docs say 
> things like:
>
>  SQLAlchemy has limited ability to do this stringification in certain 
> circumstances such as that of emitting DDL. In order to access this 
> functionality one can use the literal_binds flag, passed to compile_kwargs:
>
> ...
>
> the above approach has the caveats that it is only supported for basic types, 
> such as ints and strings, and furthermore if a bindparam() witho pre-set 
> value is used directly, it won’t be able to stringify that either.
>
>
> and I’m wondering how limited this ability is, what the implications of using 
> SQLAlchemy to bind literals rather than going through DBAPI like normal are, 
> and if this is an issue if you only try to bind 'basic types, such as ints 
> and strings.’
>
> I tried testing out a few simple SQL injection examples and saw them get 
> quoted out by SQLAlchemy’s literal binding correctly, and I’m wondering how 
> safe it is to rely on SQLAlchemy’s literal binding given those warnings in 
> the documentation.
>
> --
> 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.

[sqlalchemy] Re: Security Implications of Directly Executing Queries Compiled to Strings

[Jonathan Vanasco]

On Friday, March 8, 2019 at 12:56:26 PM UTC-5, Walt wrote:
>
> Thanks, so the limitations the documentation is bringing up are more that 
> SQLAlchemy doesn't know how to bind the same variety of types as does the 
> DBAPI?
>

Hopefully I will get this write so Mike won't have to correct me...

It's not about the "type" but the "context".  The library is large and 
supports many dialects and dbapi drivers.  Over time, that has meant many 
things have had to support 'textual arguments' and supplying literal SQL to 
certain commands.  (see 
https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#orm-tutorial-literal-sql
 
) 

I think there are some odd issues with binding different types, but they're 
largely for the database specific columns/types and few people will 
encounter them.

For example, in the bugreport i mentioned above... some people discovered 
one could submit unsafe input to a group_by or order_by clause.  That 
action is an anti-pattern, as it the functions are designed and documented 
for safe developer input, but some person on the internet generated a CVE 
report... so it became a vulnerability that had to be addressed.

In your example above though, ensuring you have an INT will catch an error 
before it hits the database. Otherwise, if you submit text, SqlAlchemy will 
properly escape it , submit it, and you will get a database error that 
needs to be parsed to figure things out.  (if sqlalchemy were talking to 
the database directly, it would wrap the error into a standardized 
sqlalchemy error that handles the various dialect-specific errors for that 
type, but you'd still need to parse it to figure out what happened on the 
db layer)

-- 
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] Re: Security Implications of Directly Executing Queries Compiled to Strings

[Walt]

On Friday, March 8, 2019 at 11:32:01 AM UTC-6, Jonathan Vanasco wrote:
>
> Do you control the HTTP API or is this someone else's system?
>

It's someone else's. I'm living in a world where folks have generated their 
SQL with regular old string processing & interpolation in Python because 
there's no database connection to work with. I'm hoping to replace this 
with sqlalchemy + .compile()-ing.
 

> Does the API just execute the raw sql it is provided, and you're looking 
> to generate that?  What you want to do is pipe a SqlAlchemy query into a 
> function that can compile it into the right statement for your database.  
> Below is an example of PostgreSQL that worked on SqlAlchemy 1 (probably 
> 1.3. too but I haven't tested):
>

Yep, just executes it raw. I've got the basic examples working, I just 
wanted to understand the limitations of using SQLAlchemy vs. DBAPI literal 
binding, particularly any security implications, before I start advocating 
for more people in my organization to start using this workflow.
 

> The security concerns you brought up deal with how/what SqlAlchemy treats 
> as trusted user input or not.  Most functions in SqlAlchemy will escape the 
> values by default, very few will not and are documented with a dragon in 
> the database.  If you are using values for those items in Sql you need to 
> filter them yourself.  
>

Thanks, so the limitations the documentation is bringing up are more that 
SQLAlchemy doesn't know how to bind the same variety of types as does the 
DBAPI?
 

> For 99.9% of use cases though, you can just compile your sql to the 
> database's dialect and just send it without worry.
>

Hooray!

Thank you for your response!

-- 
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] Re: Security Implications of Directly Executing Queries Compiled to Strings

[Jonathan Vanasco]

forgot to add:

in your example: NEVER TRUST USER INPUT.  you'll get a much better error 
and can fix problems when you don't trust them and try to sanitize stuff

- (x > user_provided_value)
+ (x > int(user_provided_value))  # or float or whatever

The stuff 'compile' generates won't necessarily work on your database 
though; you need to pass in a dialect.  (hence the function that just 
compiles it with a set predetermined options)

-- 
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] Re: Security Implications of Directly Executing Queries Compiled to Strings

[Jonathan Vanasco]

Do you control the HTTP API or is this someone else's system?

Does the API just execute the raw sql it is provided, and you're looking to 
generate that?  What you want to do is pipe a SqlAlchemy query into a 
function that can compile it into the right statement for your database.  
Below is an example of PostgreSQL that worked on SqlAlchemy 1 (probably 
1.3. too but I haven't tested):

The security concerns you brought up deal with how/what SqlAlchemy treats 
as trusted user input or not.  Most functions in SqlAlchemy will escape the 
values by default, very few will not and are documented with a dragon in 
the database.  If you are using values for those items in Sql you need to 
filter them yourself.  

Until recently, group_by and order_by were "vulnerable" to the anti-pattern 
of submitting raw untrusted user input to them.  see 
https://github.com/sqlalchemy/sqlalchemy/issues/4481 

For 99.9% of use cases though, you can just compile your sql to the 
database's dialect and just send it without worry.

-



# pypi
import sqlparse
from sqlalchemy.dialects import postgresql as dialect_postgresql


# 
==


def print_query(q):
"""
prints a sqlalchemy query
"""
print("-" * 30)
if hasattr(q, 'statement'):
print("[q.statement.compile %s]" % type(q))
statement = 
str(q.statement.compile(dialect=dialect_postgresql.dialect(), 
compile_kwargs={"literal_binds": True}))
elif hasattr(q, 'compile'):
print("[q.compile %s]" % type(q))
statement = str(q.compile(dialect=dialect_postgresql.dialect(), 
compile_kwargs={"literal_binds": True}))
else:
print("[q %s]" % type(q))
statement = str(q)
print(sqlparse.format(statement, reindent=True, 
keyword_case='upper'))
print("-" * 30)

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

Re: [sqlalchemy] Security Implications of Directly Executing Queries Compiled to Strings

[Walter Askew]

> On Mar 8, 2019, at 5:25 AM, Mike Bayer  wrote:
>> 
> SQL injection has to do with strings that are sent to the database
> engine.   from what you said above, it seems like you are generating
> strings just to display them on a webpage?  

No, I’m not displaying SQL strings on a webpage. I’m asking about the safety of 
directly executing SQL strings returned by .compile() calls, in particular when 
they contain user-provided values.

> that would be the use case
> for literal_binds.you would not pass these strings to a database
> engine unless you sanitized all input from the user.

I’m asking for more details about the limitations SQLAlchemy has on sanitizing 
user input when compiling strings with literal_binds. The docs say things like:

>  SQLAlchemy has limited ability to do this stringification in certain 
> circumstances such as that of emitting DDL. In order to access this 
> functionality one can use the literal_binds flag, passed to compile_kwargs:
> ...

> the above approach has the caveats that it is only supported for basic types, 
> such as ints and strings, and furthermore if a bindparam() witho pre-set 
> value is used directly, it won’t be able to stringify that either.

and I’m wondering how limited this ability is, what the implications of using 
SQLAlchemy to bind literals rather than going through DBAPI like normal are, 
and if this is an issue if you only try to bind 'basic types, such as ints and 
strings.’

I tried testing out a few simple SQL injection examples and saw them get quoted 
out by SQLAlchemy’s literal binding correctly, and I’m wondering how safe it is 
to rely on SQLAlchemy’s literal binding given those warnings in the 
documentation.

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

Re: [sqlalchemy] delete orphan unfavorite operations

[Mike Bayer]

On Thu, Mar 7, 2019 at 4:46 PM Conferency  wrote:
>
> Hi, I have 3 classes: two have many to many relationship between them, one is 
> association class.
>
> class Person:
> ...
> tools = relationship('Association', back_populates='user', lazy=True, 
> cascade='all, delete-orphan')
>
> class Tool:
> ...
>users = relationship('Association', back_populates='tool', lazy=True, 
> cascade='all, delete-orphan')
>
>
> One person object has 3 tools [, , ]. When I 
> update the tool of the person I did
>
> person.tools = []
> for tool_id in [2, 4, 5]:
> tool = Tool.query.get(tool_id)
> if tool:
> person.tools.append(tool)
>
>
> This operation fails, since when I set tools to an empty list, tool 1,2,3 are 
> deleted, so only tool 4,5 are added into the list. If I remove the 
> delete-orphan in the cascade, I will have some redundant data in the 
> database. I can remove the unwanted tools from person.tools first instead of 
> setting it to empty list. I'd like to know if there is a simple way to 
> achieve same result. Thank you.

that's not the real code because you can't add Tool to Person.tools,
it refers to an "Association" object which is not pictured.  Are you
using the association proxy?

If I take the example from the docs at
https://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects,
I can run the same code you have and at the end the state is correct.
 It does delete the association objects that are removed, but it then
recreates them on the append pass.  If you want it to maintain the
association objects that are already there, you need to manipulate the
association objects directly.   Try out the script below.

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref

from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

Base = declarative_base()


class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = Column(String(64))

# association proxy of "user_keywords" collection
# to "keyword" attribute
keywords = association_proxy("user_keywords", "keyword")

def __init__(self, name):
self.name = name


class UserKeyword(Base):
__tablename__ = "user_keyword"
user_id = Column(Integer, ForeignKey("user.id"), primary_key=True)
keyword_id = Column(Integer, ForeignKey("keyword.id"), primary_key=True)
special_key = Column(String(50))

# bidirectional attribute/collection of "user"/"user_keywords"
user = relationship(
User, backref=backref("user_keywords", cascade="all, delete-orphan")
)

# reference to the "Keyword" object
keyword = relationship("Keyword")

def __init__(self, keyword=None, user=None, special_key=None):
self.user = user
self.keyword = keyword
self.special_key = special_key


class Keyword(Base):
__tablename__ = "keyword"
id = Column(Integer, primary_key=True)
keyword = Column("keyword", String(64))

def __init__(self, keyword):
self.keyword = keyword

def __repr__(self):
return "Keyword(%s)" % repr(self.keyword)


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

u1 = User("foo")
k1, k2, k3, k4, k5 = (
Keyword("k1"),
Keyword("k2"),
Keyword("k3"),
Keyword("k4"),
Keyword("k5"),
)

u1.keywords = [k1, k2, k3]
s.add_all([u1, k1, k2, k3, k4, k5])
s.commit()


u1.keywords = []
for kname in ["k2", "k4", "k5"]:
k = s.query(Keyword).filter_by(keyword=kname).one()
u1.keywords.append(k)

s.commit()

print(u1.keywords)


output at the end:

[Keyword('k2'), Keyword('k4'), Keyword('k5')]




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

Re: [sqlalchemy] Security Implications of Directly Executing Queries Compiled to Strings

[Mike Bayer]

On Thu, Mar 7, 2019 at 4:47 PM Walt  wrote:
>
> For some odd reasons, I'm in a situation where I don't have direct access to 
> a database, but I do have an HTTP API fronting the database which I can 
> submit SQL strings to and get results back from. I'd like to use SQLAlchemy 
> to generate the query strings I send to the HTTP API, but I'm wondering about 
> the security implications of doing so, particularly in the face of 
> user-provided values and protecting from SQL injection attacks.
>
> I'd like to be able to do something like
>
> send_to_http_sql_api(
> sqlalchemy.select([cols]).where(x > 
> user_provided_value).compile(compile_kwargs={"literal_binds": True}).string
> )
>
> that is, directly executing SQL strings produced by .compile() calls
>
> The docs say:
>
> https://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html
>
>> SQLAlchemy normally does not stringify bound parameters, as this is handled 
>> appropriately by the Python DBAPI, not to mention bypassing bound parameters 
>> is probably the most widely exploited security hole in modern web 
>> applications. SQLAlchemy has limited ability to do this stringification in 
>> certain circumstances such as that of emitting DDL. In order to access this 
>> functionality one can use the literal_binds flag, passed to compile_kwargs:
>> ...
>> the above approach has the caveats that it is only supported for basic 
>> types, such as ints and strings, and furthermore if a bindparam() witho 
>> pre-set value is used directly, it won’t be able to stringify that either.
>
>
> What are the more specific security concerns I should have in trying to 
> directly execute strings compiled by SQLAlchemy itself? Is this okay if 
> sticking to "basic types, such as ints and strings" or Is this just a bad 
> idea? Is there a way I can use the DBAPI or other tools to more safely 
> generate SQL strings that can be securely executed directly, even though I 
> don't have a direct database connection?

SQL injection has to do with strings that are sent to the database
engine.   from what you said above, it seems like you are generating
strings just to display them on a webpage?  that would be the use case
for literal_binds.you would not pass these strings to a database
engine unless you sanitized all input from the user.

There are, as it turns out, a whole host of security issues when you
take untrusted web input from users and then display it on web pages!
 e.g. scripting attacks.   so you need to worry about that as well.
A google search for "sanitize web input" comes up with a lot of great
tutorials on this subject.

some canonical starting points:

https://en.wikipedia.org/wiki/Cross-site_scripting
https://en.wikipedia.org/wiki/SQL_injection


I've been involved in the web industry for 25 years and these days, I
basically never want to put an input box on a web page myself, the
implications of doing so are large since you have to know all the
latest vulnerabilities which are evolving constantly and I don't have
the time/interest in doing that anymore.I always use some
well-maintained product if I have to do so and even then I run the
whole thing as locked down as I can possibly make it.



>
> Thanks for your help -- would love to find a way to work with this API that 
> doesn't have me generating every bit of the SQL strings myself!
>
> --
> 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.