Friday, August 17, 2007

Oooh really bad bug in 4.1.23, 4.1.24b INNODB only

Bug: 30485


[miguel@skybr 4.1]$ bin/mysql -uroot db77
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.24-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `GiftCodes` (
-> `code` varchar(32) collate utf8_bin NOT NULL default '',
-> `used_by_id` bigint(10) unsigned NOT NULL default '0',
-> PRIMARY KEY (`code`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> insert into GiftCodes values ('foo',7);
Query OK, 1 row affected (0.00 sec)

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo | 7 |
+------+------------+
1 row in set (0.00 sec)

mysql> update GiftCodes set used_by_id='1' where code='foo';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo | 7 |
+------+------------+
1 row in set (0.00 sec)

14 comments:

burtonator said...

Well THAT's bad!

Raj said...

used_by_id is a bigint. So you're telling me that you expect mysql to do a string-to-number conversion for you? forget it. This is not behaviour you should count on, no matter what the db, or that it works for some particular engine.

Further, MySQL is being nice and telling you it updated 0 rows, so getting the original data back is not a surprise.

Your statement should read:

update GiftCodes set used_by_id=1 where code='foo';

buggy sql will result in buggy results. or, in other words, garbage in garbage out.

--Raj.

Dathan said...

Raj has no idea what he is talking about

Anonymous said...

For Dathan:
Do you?

Dathan said...

Yup, I do. To stop sql injection all rvalues should be escaped. The drawback is a type conversion internally in mysql which is not that bad at all.

Anonymous said...

For Dathan:
I thought the best way to avoid sql injection attack is to use prepared statements and positional/bind parameters, NOT using the escaping parameters process.
As it turns out, MySQL used to have (I don't know if it still has) problems with real_escape_chars() function.

On another note, Raj is right. You cannot do that kind of update providing invalid data type for the filtering/set clauses. And counting on someone implementing some kind of auto converting process, it's plain dum. You wanna select integers, use "...used_by_id=1"; wanna select character data types, use "...used_by_id='1'". That's why quoting was implemented in a RDBMS, to differentiate between numeric data types and character data types. Cheers dude!

Dathan said...

I don't know if I agree with this, it's been an established feature for quite some time.

Raj doesn't believe that it works or should work

"So you're telling me that you expect mysql to do a string-to-number conversion for you? forget it" -- Yes I do and it does :)

He's wrong.

Escaping this way is safe and reliable.

Using prepared statements is not an option for many folks since in many cases there is no performance gain from setting up a prepared statement executed from a webpage.


Anyway this has nothing to do with the bug listed above. It's an update bug due to a UTF-8 padding issue caused by a blind commit which was rolled back in 5.0

Anonymous said...

Ok, I think the whole conversion is on the wrong track. The point is I don't trust MySQL to do right data type conversions, especially from char to int, without getting weird results. In this case, MySQL acted half way correctly, notifying the user that no data was changed, like it was supposed to, given the data type conversion thing, BUT it didn't throw an error like any serious database would do.

Using this conversion, coupled with the escaping process you're promoting, is a time bomb.

"Using prepared statements is not an option for many folks since in many cases there is no performance gain from setting up a prepared statement executed"
Well, overlooking the possibility that you could use prepared statements in your application before writing a single line of code, that's the real problem.
In my mind set, I'd chase first reliability and THEN performance, especially if I'd have to tackle a more serious application.

Dathan said...

Yea, I guess that would be a good method to do in retrospect. Don't you find the whole notion of using prepared statements so cumbersome to debug when trying to find out why a query is taking so long? (I guess this is a mute point when mysql releases the query log toggle).

Anonymous said...

Probably yeah, it is cumbersome but no more cumbersome than grepping through the whole darn code file to look for the statement where is it generated.
In this case, the best thing you could do is to attach a SQL comment that will inform you about the path/filename/line number where that prepared statement is called. In this way whenever you encounter performance problems, you'll see the statement + the SQL comment either in mytop either in the slow log/general log. Definitely it will help you.

Dathan said...

Yup, I do that already. Many of my talks show this as a great tool for graphing load based on query.

Without rvalues tracking down ranges that span many rows is a bit difficult.

I.E. this statement is taking X seconds, but why?

Anonymous said...

By the way, I forgot to mention that one of my preferred mysql versions are Oracle 9i/10g and PostgreSQL 8.2.xx onwards.

sexy said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情趣,情趣,情趣,情趣,按摩棒,跳蛋,充氣娃娃,情境坊歡愉用品,情趣用品,情人節禮物,情惑用品性易購

免費A片,AV女優,美女視訊,情色交友,免費AV,色情網站,辣妹視訊,美女交友,色情影片,成人影片,成人網站,A片,H漫,18成人,成人圖片,成人漫畫,情色網,日本A片,免費A片下載,性愛

A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,av dvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX

情趣用品,A片,免費A片,AV女優,美女視訊,情色交友,色情網站,免費AV,辣妹視訊,美女交友,色情影片,成人網站,H漫,18成人,成人圖片,成人漫畫,成人影片,情色網


情趣用品,A片,免費A片,日本A片,A片下載,線上A片,成人電影,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,微風成人區,成人文章,成人影城,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,臺灣情色網,色情,情色電影,色情遊戲,嘟嘟情人色網,麗的色遊戲,情色論壇,色情網站,一葉情貼圖片區,做愛,性愛,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,美女交友,做愛影片

av,情趣用品,a片,成人電影,微風成人,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,愛情公寓,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,aio,av女優,AV,免費A片,日本a片,美女視訊,辣妹視訊,聊天室,美女交友,成人光碟

情趣用品.A片,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,色情遊戲,色情網站,聊天室,ut聊天室,豆豆聊天室,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,免費A片,日本a片,a片下載,線上a片,av女優,av,成人電影,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室

sex said...

徵信社
情趣用品
情趣用品
情趣用品
情趣
情趣


SM
充氣娃娃


SM
性感睡衣


免費視訊聊天室
aio交友愛情館
愛情公寓
情色貼圖
情色文學
情色小說
情色電影
情色論壇
成人論壇
辣妹視訊
視訊聊天室
情色視訊
免費視訊

免費視訊聊天
視訊交友網
視訊聊天室
視訊美女
視訊交友
ut聊天室
聊天室
豆豆聊天室
尋夢園聊天室
聊天室尋夢園
080聊天室
080苗栗人聊天室
女同志聊天室

上班族聊天室
小高聊天室






免費視訊聊天
免費視訊聊天室
免費視訊
ut聊天室
聊天室
豆豆聊天室 聊天室尋夢園
影音視訊聊天室


色情遊戲
寄情築園小遊戲
情人視訊網
辣妹視訊
情色交友

成人論壇
情色論壇
愛情公寓
情色
色情聊天室
色情小說
做愛
做愛影片
性愛


一葉情貼圖片區
情趣用品


辣妹視訊
美女視訊
視訊交友網
視訊聊天室
視訊交友
視訊美女