Ok, makes sense. Thanks for the help. On Saturday, April 6, 2019 at 7:04:42 PM UTC-7, Mike Bayer wrote: > > On Sat, Apr 6, 2019 at 8:43 PM Zac Goldstein <gol...@gmail.com > <javascript:>> wrote: > > > > >they are severe. > > I thought this would likely be the case. Exposure to DoS attacks is > interesting and something I hadn't thought of, so I'm glad you brought that > up. > > > > I do have a parser already set up that I could use for this, but I was > hoping for something more foolproof. The other thought I had was to use a > plpgsql function to build the DDL with an > > EXECUTE format(...) USING > > statement. Obviously this is more work, but it could be worth it. I know > we're getting away from sqla here, but do you know if it would be secure? > According to this post > https://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html > > `EXECUTE USING` is "100% safe" from injection, but it's not clear to me > that that's actually true. > > im sure it's safe from injection if the user input you are putting > into it is only sent to the parameters themselves, but since you're > looking to give users access to the expression, none of that really > matters much. > > > > > > > > > > On Saturday, April 6, 2019 at 5:00:14 PM UTC-7, Mike Bayer wrote: > >> > >> On Sat, Apr 6, 2019 at 6:56 PM Zac Goldstein <gol...@gmail.com> 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 sqlal...@googlegroups.com. > >> > To post to this group, send email to sqlal...@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 sqlal...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > 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.
