Is This Open To SQL Injection?

[Victor Subervi]

On Thu, Jul 8, 2010 at 10:45 AM, Stephen Hansen <me+list/python at ixokai.io>wrote:

> On 7/8/10 6:20 AM, Victor Subervi wrote:
> > However, I now have another error. Here is my current command:
> >
> >     cursor.execute("insert into personalDataKeys (Store, User,
> > useFirstName, useLastName, usePhone, useCell, useFax, useAddress,
> > useShippingAddress, useDOB, useEmail, usePW) values (%s, %s, %s, %s, %s,
> > %s, %s, %s, %s, %s, %s, %s)", ([store, user] + col_vals))
>
> Quick point: why the parens around [store, user] + col_vars? They're
> redundant.
>
> >
> > I get this error from MySQL which I am having a hard time understanding:
> >
> > LATEST FOREIGN KEY ERROR
> > ------------------------
> > 100708  6:15:01 Transaction:
> > TRANSACTION 0 9382, ACTIVE 0 sec, process no 5326, OS thread id
> > 1169992000 inserting, thread declared inside InnoDB 500
> > mysql tables in use 1, locked 1
> > 3 lock struct(s), heap size 368, undo log entries 1
> > MySQL thread id 1502, query id 23700 localhost beno update
> > insert into personalDataKeys (Store, User, useFirstName, useLastName,
> > usePhone, useCell, useFax, useAddress, useShippingAddress, useDOB,
> > useEmail, usePW) values ('specialty', 'patients', 1, 1, 1, 1, 1, 1, 0,
> > 1, 1, 1)
> > Foreign key constraint fails for table `test/personalDataKeys`:
> > ,
> >   CONSTRAINT `personalDataKeys_ibfk_1` FOREIGN KEY (`Store`) REFERENCES
> > `products` (`Store`)
>
> A foreign key is a constraint, a restriction, which says that rows in
> TableA ("personalDataKeys") depend on certain *matching* rows to already
> exist and always be valid in TableB ("products"); the exact match is a
> column they have in common ("Store").
>
> The purpose of foreign keys is to keep data consistent. Here, it appears
> as if you have established a key such that the 'store' column in your
> personalDataKeys table must point to a certain row in the products table
> which has a 'store' column of the exact same value.
>
> This error message is indicating that when you do this INSERT, there is
> no corresponding row in the products table.
>

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.
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? After rethinking this, it occurred to me that I probably made a
mistake in copying my create table command from personalData to
personalDataKeys, both of which had the foreign key of Store referenced to
table products. 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:

mysql> select * from personalData;
Empty set (0.00 sec)

Here's the deal:

mysql> describe personalData;
+-------------------+------------------+------+-----+------------+----------------+
| Field             | Type             | Null | Key | Default    |
Extra          |
+-------------------+------------------+------+-----+------------+----------------+
| ID                | int(10) unsigned | NO   | PRI | NULL       |
auto_increment |
| Store             | varchar(40)      | NO   | MUL | NULL
|                |
| User              | varchar(50)      | NO   | MUL | NULL
|                |
| FirstName         | varchar(100)     | NO   |     | NULL
|                |
| LastName          | varchar(100)     | NO   |     | NULL
|                |
| Phone             | varchar(13)      | YES  |     | NULL
|                |
| Cell              | varchar(13)      | YES  |     | NULL
|                |
| Fax               | varchar(13)      | YES  |     | NULL
|                |
| AddressID         | int(11)          | NO   | MUL | NULL
|                |
| ShippingAddressID | int(11)          | NO   | MUL | NULL
|                |
| DOB               | date             | YES  |     | 2000-01-01
|                |
| Email             | varchar(100)     | NO   |     | NULL
|                |
| PW                | varchar(12)      | NO   |     | NULL
|                |
+-------------------+------------------+------+-----+------------+----------------+
13 rows in set (0.00 sec)

mysql> describe personalDataKeys;
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| Store              | varchar(40) | NO   | MUL | NULL    |       |
| User               | varchar(50) | NO   | MUL | NULL    |       |
| useFirstName       | tinyint(1)  | NO   |     | NULL    |       |
| useLastName        | tinyint(1)  | NO   |     | NULL    |       |
| usePhone           | tinyint(1)  | NO   |     | NULL    |       |
| useCell            | tinyint(1)  | NO   |     | NULL    |       |
| useFax             | tinyint(1)  | NO   |     | NULL    |       |
| useAddress         | tinyint(1)  | NO   |     | NULL    |       |
| useShippingAddress | tinyint(1)  | NO   |     | NULL    |       |
| useDOB             | tinyint(1)  | NO   |     | NULL    |       |
| useEmail           | tinyint(1)  | NO   |     | NULL    |       |
| usePW              | tinyint(1)  | NO   |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

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. All of this, however, obviously happens before any data is
actually entered into either personalData or products. It now seems to me
that I have mistakenly put the constraint on the wrong table; that it should
be on personalData and not personalDataKeys, but before I do that I would
like confirmation that this looks correct.
TIA,
beno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100708/86bf7398/attachment-0001.html>