On Sat, Apr 6, 2019 at 8:43 PM Zac Goldstein <[email protected]> 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 <[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. -- 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.
