also referencing https://bitbucket.org/zzzeek/sqlalchemy/issue/3034/use-my-own-bindparam-for-querylimit
On Mar 21, 2014, at 9:39 AM, Ryan Kelly <[email protected]> wrote: > I see your compatibility concerns. > > Let me see if .offset() without .limit() does the right thing. > > -Ryan Kelly > > On Thu, Mar 20, 2014 at 10:39 PM, Michael Bayer > <[email protected]> wrote: >> the int() catch here on limit/offset is something we added due to user >> report which raised the issue that it's a security hazard, back when we used >> to render the given value directly in the SQL without using a bound >> parameter. We fixed both that it allowed non-int values as well as that it >> didn't use a bound parameter; technically only one or the other is needed at >> most. But it quickly got picked up by about a hundred "security advisory" >> bots blasting for months about it as a SQLAlchemy security advisory, so I >> haven't gone near liberalizing it. At this point the "int" check could >> probably switch on either int or a SQL expression (just not straight text). >> Looking at the way limit_clause() is written we'd have to change how >> _limit/_offset are interpreted in order to allow other expressions in there. >> There may be existing recipes that expect _limit/_offset to be plain >> integers though. >> >> but without even getting into changing anything, looking at the source for >> the postgresql compiler it would appear ALL is already emitted if OFFSET is >> present and LIMIT is not: >> >> from sqlalchemy.sql import select >> from sqlalchemy.dialects import postgresql >> print select(['foo']).offset(5).compile(dialect=postgresql.dialect()) >> >> output: >> >> SELECT foo >> LIMIT ALL OFFSET %(param_1)s >> >> Seems like it's been this way for years, it already emits ALL. So there's >> no issue? >> >> >> >> >> On Mar 20, 2014, at 5:49 PM, Ryan Kelly <[email protected]> wrote: >> >>> Redshift needs LIMIT ALL to avoid attempting an optimization which >>> causes it to crash. >>> >>> Note that OFFSET 0 is the same as omitting OFFSET, but it acts as an >>> optimization fence. This, I suppose, is a similar thing. >>> >>> -Ryan Kelly >>> >>> On Thu, Mar 20, 2014 at 5:38 PM, Michael Bayer <[email protected]> >>> wrote: >>>> >>>> LIMIT NULL and LIMIT ALL per the PG docs at >>>> http://www.postgresql.org/docs/9.0/static/queries-limit.html are the same >>>> as omitting the number. These would appear to be syntactical helpers >>>> that you wouldn't really need when working with a select() construct >>>> (unless you're trying to get at some PG optimizer quirk). >>>> >>>> so why exactly do you need to emit these otherwise unnecessary keywords ? >>>> >>>> >>>> >>>> On Mar 20, 2014, at 5:29 PM, Ryan Kelly <[email protected]> wrote: >>>> >>>>> Hi: >>>>> >>>>> It seems that the argument to query.limit must be a number. However, >>>>> NULL (which I imagine could be passed by the null() construct or as a >>>>> string) and ALL (which, I suppose could be text("ALL") or >>>>> literal("ALL") or just the string "ALL") are perfectly acceptable >>>>> values on PostgreSQL. >>>>> >>>>> Is there some way to convince SQLAlchemy to render these values? >>>>> >>>>> -Ryan Kelly >>>>> >>>>> -- >>>>> 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 http://groups.google.com/group/sqlalchemy. >>>>> For more options, visit https://groups.google.com/d/optout. >>>> >>>> -- >>>> 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 http://groups.google.com/group/sqlalchemy. >>>> For more options, visit https://groups.google.com/d/optout. >>> >>> -- >>> 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 http://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. >> >> -- >> 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 http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > > -- > 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 http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
