From: John Machin on
On Jan 7, 1:38 pm, Steve Holden <st...(a)holdenweb.com> wrote:
> John Machin wrote:
>
> [...]> I note that in the code shown there are examples of building an SQL
> > query where the table name is concocted at runtime via the %
> > operator ... key phrases: "bad database design" (one table per
> > store!), "SQL injection attack"
>
> I'm not trying to defend the code overall, but most databases won't let
> you parameterize the table or column names, just the data values.

That's correct, and that's presumably why the OP is constructing whole
SQL statements on the fly e.g.

cursor.execute('select max(ID) from %sCustomerData;' % store)

What is the reason for "but" in "but most databases won't ..."? What
are you rebutting?

Let me try again: One table per store is bad design. The
implementation of that bad design may use:

cursor.execute('select max(ID) from %sCustomerData;' % store)
or (if available)
cursor.execute('select max(ID) from ?CustomerData;', (store, ))
but the implementation means is irrelevant.
From: Steve Holden on
John Machin wrote:
> On Jan 7, 1:38 pm, Steve Holden <st...(a)holdenweb.com> wrote:
>> John Machin wrote:
>>
>> [...]> I note that in the code shown there are examples of building an SQL
>>> query where the table name is concocted at runtime via the %
>>> operator ... key phrases: "bad database design" (one table per
>>> store!), "SQL injection attack"
>> I'm not trying to defend the code overall, but most databases won't let
>> you parameterize the table or column names, just the data values.
>
> That's correct, and that's presumably why the OP is constructing whole
> SQL statements on the fly e.g.
>
> cursor.execute('select max(ID) from %sCustomerData;' % store)
>
Well yes, but that is just a symptom of the real disease, which is that
he has very little idea what he is doing.

> What is the reason for "but" in "but most databases won't ..."? What
> are you rebutting?
>
I was simply pointing out that the OP had chosen the only available way
of generating variable table names. The bad database design has been
discussed,if not ad infinitum then certainly ad nauseam. All advice has
been ignored.

> Let me try again: One table per store is bad design. The
> implementation of that bad design may use:
>
> cursor.execute('select max(ID) from %sCustomerData;' % store)
> or (if available)
> cursor.execute('select max(ID) from ?CustomerData;', (store, ))
> but the implementation means is irrelevant.

[Do you know any database on which the latter technique will work? I
realise I said "most", but I suspect I should have said "all" - at least
I can't think of a counterexample now I have put myself on the spot].

I pointed this out to the OP some time ago. It won't make any
difference. In the particular code you mention the tables didn't seem to
be constructed from user input, thereby removing much of the danger of
SQL injection exploits, but I could be wrong - reading it made me feel
squeamish so I didn't analyze it thoroughly.

The whole thing is a hodge-podge of Python and HTML produced by an
individual who appears to feel it isn't necessary to understand either
HTTP or HTML in order to produce dynamic web sites, whose grasp of
Python itself is slight and whose response to constructive criticism is
to defer acting on it until it is no longer going to be helpful.

regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
PyCon is coming! Atlanta, Feb 2010 http://us.pycon.org/
Holden Web LLC http://www.holdenweb.com/
UPCOMING EVENTS: http://holdenweb.eventbrite.com/