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<http://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 <waltas...@gmail.com<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<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. -- 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.
