On Sat, Apr 6, 2019 at 6:56 PM Zac Goldstein <[email protected]> wrote:
>
> Hello,
>
> I'd like to create tables based off user input, but I'm uncertain about the
> security implications.
they are severe. DDL is modification to the database schema
structure and requires a lot of privileges too, depending on database
backend it can also easily lead to DOS types of attacks as CREATE
TABLE can be an expensive operation and additionally it likely has
problems if someone tries to create a table with 10000 columns, for
example. While I've never favored this approach, it's not unheard
of, I believe at least for some period of time Reddit was creating
tables for subreddits, or something like that. But the link between
user input and CREATE TABLE would have lots of indirection.
> Most of the raw text data consists of column and table names, which will be
> restricted and validated to snake_case with no special characters. I assume
> I'm okay there, but confirmation would be nice.
I'd limit the length of column names and the number of columns as
well, I'd also limit how many tables one user can build and how
quickly.
> I would also like to include the ability for users to specify expressions for
> constraints, which is obviously more concerning.
I'd likely use a parser for their constraint text and then re-generate
it out again. Every character they're putting in should be known.
This gets particularly challenging with literal values; again I'd be
looking at length, quoting, things like that. Keep in mind numeric
values aren't quoted, but don't trust that a number is actually a
number without coercing it to a float/int, stuff like that.
SQLAlchemy also does not offer this kind of thing. *dont* use
literal_binds alone for this, it doesn't do things like check that
numbers are actually numbers, it just calls str() etc. Nothing
untrustred should be passed to SQLAlchemy when doing DDL everything
needs to be sanitized first.
>
> As an example, which I'm not even sure would be correct injection, but should
> give you an idea:
> CheckConstraint("id = 0)); DROP TABLE table_meta; COMMIT; --",
> name="ck_injectin_test")
yes it's hugely dangerous.
>
>
> So in this case the entire expression string in the first argument would be
> raw user input. I tried running `create` with the above just to see what
> would happen and psycopg2 raised an exception saying there was a syntax
> error, which seems consistent with their injection examples but it could just
> be that I don't know what I'm doing. So long story short, is there a safe
> way of doing this?
nothing out of the box, you'd need to implement it and then worry,
unfortunately.
>
> Thanks
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
