[DB-SIG] In praise of pyformat

[Carl Karsten]

Mike Meyer wrote:
> On Wed, 15 Aug 2007 12:35:55 -0500 Carl Karsten <carl at personnelware.com> wrote:
> 
>> Mike Meyer wrote:
>>> On Wed, 15 Aug 2007 09:44:56 -0400 Art Protin <aprotin at research.att.com> wrote:
>>>>     Carsten Haese wrote:
>>>>> On Tue, 2007-08-14 at 10:18 -0400, Mike Meyer wrote:
>>>>>>> How often does an identifier come from an untrusted source?
>>>>>> Um, how about in every web-based app that has a real search facility?
>>>>>> One that lets the user specify which column(s) they want to check, or
>>>>>> that can search multiple tables?
>>>>> Even if you take an identifier directly from an untrusted source, nobody
>>>>> is forcing you to stick it into a query unchecked.
>>>> The better question is why is anybody letting him.
>>>> It is the worst form of programming to use unchecked data.
>>>> So is he arguing that he needs tools to check & validate the values before
>>>> using them as table or column names?
>>> Not quite. I'm asking for a tool that will safely insert identifiers
>>> from an untrusted source into a query, much the same way that
>>> parameter binding lets me insert values from an untrusted source.
>>>
>> I would like to point out a big difference between the two: parameters are a 
>> feature of the db engine's API that has to be dealt with in the python dbapi 
>> module in order to be used.  validating identifier names does not require 
>> anything in dbapi.   This distinction may be a reason against adding additional 
>> functionality into dbapi.
> 
> Major nit: I didn't say "validate identifier names", I said "safely
> insert identifiers". To me (and my opinion is the one that matters,
> 'cause it's my statement :-) there's a big difference. You can "safely
> insert identifiers" by creating a delimited identifier that can be
> mapped back to the given string, no matter what's in it. There's no
> implication that the identifier is legal for the database at hand,
> just that it'll be parsed as an identifier by the underlying database
> (though modules should be free to do more).  "Validate identifier
> names", on the other hand, implies that you'll tell me whether or not
> the identifier is a legal identifier in context - that it doesn't
> validate any constraints the underlying SQL engine has, and refers to
> an entity that actually exists, etc. If that's not what you meant, I'm
> sorry.
> 
> This matches my understanding - and experience - with parameter
> binding. If I pass in a parameter that's nominally of the correct type
> but violates a database constraint (either formally, or on the type of
> the column), I don't expect dbapi to "fix" it, I expect an exception
> representing an error from the underlying SQL engine.
> 
> And yes, dbapi has to have parameter binding to access that facility
> in some databases. But it doesn't for others - because they don't have
> parameter binding. But the dbapi spec requires it anyway, and for good
> reason. I believe those same reasons apply in this case.
> 

Even with your correction, it is still something that is not a feature of any db 
engine's API that has to be dealt with in the python dbapi module in order to be 
used.   and I don't see why it matters that some db's dont support parameters.

btw - what db's dont support parameters?

Carl K