Re: Another MySQL Problem

[MRAB]

Victor Subervi wrote:
> Hi;
>
> mysql> truncate tem126072414516;
> Query OK, 0 rows affected (0.00 sec)
>
> Then I run a script:
>
> if whatDo == 'insert':
> try:
> sql = 'insert into %s (ProdID, Quantity) values ("%s", "%s");' %
> (tmpTable, prodid, quantity)
> print sql
> cursor.execute(sql)
>
> that runs this (printed out) sql insert statement:
>
> insert into tem126072832767 (ProdID, Quantity) values ("2", "2");
>
> But then this...
>
> mysql> select * from tem12607282453 t join products p on t.ID=p.ID;
> Empty set (0.00 sec)
>
> mysql> insert into tem126072829782 (ProdID, Quantity) values ("2", "2");
> ERROR 1062 (23000): Duplicate entry '2' for key 2
>
> I have to manually truncate the table to manually insert it, and then it
> works. But programmatically, it doesn't insert...but it apparently
> inserts something, or it wouldn't throw the duplicate entry error. Where
> is the problem?
>
1. The table names look different.

2. Did you commit the changes?

[John Nagle]

MRAB wrote:
> Victor Subervi wrote:
>> Hi;
>>
>> mysql> truncate tem126072414516;
>> Query OK, 0 rows affected (0.00 sec)
>>
>> Then I run a script:
>>
>> if whatDo == 'insert':
>> try:
>> sql = 'insert into %s (ProdID, Quantity) values ("%s", "%s");' %
>> (tmpTable, prodid, quantity)
>> print sql
>> cursor.execute(sql)

Don't put values into an SQL statement using the "%" operator. It doesn't
do SQL escapes and allows SQL injection attacks.

Try something more like this (assuming that tmpTable does NOT come
from external input, which would be very risky).

cursor = db.cursor() ## create cursor
sql = 'insert into ' + tmpTable + ' (ProdID, Quantity) values (%s,%s);'
values = (prodid, quantity) ## values to insert
print sql
cursor.execute(sql, values) ## let SQL do the substitution
db.commit() ## commit transaction

> 1. The table names look different.
> 2. Did you commit the changes?

That, too.

John Nagle