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