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

Reply via email to