Is This Open To SQL Injection?

[Stephen Hansen]

On 7/8/10 9:03 AM, Victor Subervi wrote:
> mysql> describe products Store;
> +-------+-------------+------+-----+---------+-------+
> | Field | Type        | Null | Key | Default | Extra |
> +-------+-------------+------+-----+---------+-------+
> | Store | varchar(40) | NO   | MUL | NULL    |       |
> +-------+-------------+------+-----+---------+-------+
> 1 row in set (0.00 sec)
> 
> mysql> describe personalDataKeys Store;
> +-------+-------------+------+-----+---------+-------+
> | Field | Type        | Null | Key | Default | Extra |
> +-------+-------------+------+-----+---------+-------+
> | Store | varchar(40) | NO   | MUL | NULL    |       |
> +-------+-------------+------+-----+---------+-------+
> 1 row in set (0.00 sec)
> 
> They both use innodb. They're both indexed. I was thinking after getting
> your email that maybe I'd set the varchars to different lengths, but no.

A foreign key isn't about the schema, per se; its not about the
varchar's being different lengths (as they discard trailing padding)--
its about *data*. True, if you had varchar(20) and varchar(40), then if
any string longer then 20 wouldn't ever pass -- but that's really
secondary. (That's not saying a database may refuse to accept a FK if
data types are mismatched)

If "personalDataKeys" has a foreign key connecting it to "products",
then you can't add something to personalDataKeys with store =
"specialty" unless something already exists in "products" with store =
"speciality";

> However...
> 
> mysql> select * from products;
> Empty set (0.00 sec)
> 
> Is it that I can't insert into personalDataKeys until I've first done so
> in products? 

Yes, that's precisely what foreign keys do.

> That wasn't necessary, since personalDataKeys only
> needs to be associated with personalData, so I dropped and recreated the
> table, updating personalDataKeys foreign key to reference personalData;
> however, once again:

Are you certain this is what you want? It sounds like you may be using
foreign keys without fully understanding what they are.

Think of them like a big arrow.

If you define a foreign key in personalDataKeys, referencing
personalData, you should picture a large arrow pointing from
personalDataKeys to personalData.

It's pointing because the "constraint" created by the foreign key means,
"Every record in this table, personalDataKeys, has a column which *must*
exist in its referenced table, personalData, before that record is
allowed to be added."

A foreign key isn't just a description of a relationship: its a strict
rule, declaring that a certain field in one table *actually* refers
directly to a *specific* row in *another* table: therefore, this field
can't be allowed to be any value which doesn't exist already in that
other table.

A "primary key" lets you uniquely identify a certain row in one table. A
"foreign key" lets you identify a certain row in *another table*, that
this table ultimately depends on.

> In personalDataKeys I store which fields will be required for a given
> store as it relates to personal data. For example, if there is a
> pharmacy with users 'doctors' and 'patients', certain fields in
> personalData will be required for one but not the other, and this needs
> to be inserted into personalDataKeys. 

My concern here is that you're making *columns* which are
"store-dependent", such as sometimes in one store, personalData will
have a column/field named "foo", but another store it won't use "foo"
but instead use "bar", depending on how the store itself is configured.

I'd refer you back to my previous email which described two schemes to
record "store-dependant" data: one using a separate table for each store
type, another using a generic key/value table. Having one big table with
a big mix of columns that various store configurations pick and choose
seems like a very inflexible design.

Additionally (if you do keep this design), these two tables you
described seem to make the columns that are used tied to *users*, not
*stores*. The key for personalDataKeys is (Store, User): but isn't it
the case that for a certain kind of store (i.e., a pharmacy), /all/
users in that store will have the same fields in personalData be
relevant? So shouldn't "personalDataKeys" really be "storeDataKeys"?
I.e., a configuration of the store itself of what data keys it considers
relevant.

> All of this, however, obviously
> happens before any data is actually entered into either personalData or
> products. 

Yeah, the constraints and such happen before data is entered. But once
they are created, you have to actual enter data in the correct order.
The constraints enforce consistency so programmer-error can't introduce
data into the tables which is out of whack with the data layout.

-- 

   Stephen Hansen
   ... Also: Ixokai
   ... Mail: me+list/python (AT) ixokai (DOT) io
   ... Blog: http://meh.ixokai.io/

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 487 bytes
Desc: OpenPGP digital signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20100708/41d1459e/attachment.sig>