On Thu, Mar 7, 2019 at 4:47 PM Walt <waltas...@gmail.com> 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.
