>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. 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 > <javascript:>> 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 <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.