Notice about seconds overroll - S7K bug

[Tonu Samuel <tonu () please do not remove this spam ee>]

I would like to make your attention on bug which was introduced tonight
and can affect some people who are using (var)char field to store
timestamp data.

It is not worst security bug. It affects only people who already had bug
in their code. Just now this bug become visible/exploitable.

This is not MySQL bug. This is how people use their database. Also
similar situation can be found in other software. I would like to inform
people in public list as maybe some people have to search similar
problems.

The problem:
Computers store time and date usually as integer value representing
amount of seconds from 1 January 1970. Tonight it overrolled from
999999999 to 1000000000.

Possible bug and exploit relies on fact that some people have used
character type of field to store this seconds information (we have
already such case)

example:

mysql> create table session (expire varchar(100) not null);
Query OK, 0 rows affected (0.31 sec)
 
mysql> insert into session values (999999997), (999999998), (999999999),
(1000000000), (1000000001);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql>
mysql> select * from session;
+------------+
| expire     |
+------------+
| 999999997  |
| 999999998  |
| 999999999  |
| 1000000000 |
| 1000000001 |
+------------+
5 rows in set (0.00 sec)
 
mysql>


Let's assume that this table contains values we use somewhere to
authenticate users. After user logs in, we write down session expiry
time and later we check it like this:

mysql> select count(*) from session where expire  >= '1000032535';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
 
mysql>

WOW, what happened? Shouldn't be 100003253 bigger than any value in
table? It worked yesterday!

In MySQL we suggested people to use quotation marks around integer
values. This can avoid many web-based attacks targeted to modify SQL
commands (more information on
http://www.mysql.com/doc/G/e/General_security.html). This is the reason
why people put quotation marks around integer expressions and this is
correct. Also automatic type casting will fix the source problem is
column data is integer or some time/date vale. But when both column is
character type and expression, they get compared as strings. And as we
know, strings get sorted in order:

1,11,2,22

but integers:

1.2.11.22

So, this is why 100003253 < 1000000000

It is possible that some web applicatons have endless expiry times now
and not only in MySQL contexts. 

-- 
For technical support contracts, goto https://order.mysql.com/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Tonu Samuel <tonu () mysql com>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Security Administrator
/_/  /_/\_, /___/\___\_\___/   Hong Kong, China
       <___/   www.mysql.com