Same query, 3 databases, 3 different results

The SQL standard leaves a lot of room for different implementations. This is a little demonstration of one of such differences.

SQLite  3.7.4

sqlite> create table t1 (id serial, t time);
sqlite> insert into t1(t) values ('00:05:10');
sqlite> select t,t*1.5 from t1;
00:05:10|0.0

MySQL 5.6.4-m5
mysql> create table t1 (id serial, t time);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(t) values ('00:05:10');
Query OK, 1 row affected (0.00 sec)

mysql> select t,t*1.5 from t1;
+----------+-------+
| t        | t*1.5 |
+----------+-------+
| 00:05:10 |   765 |
+----------+-------+
1 row in set (0.00 sec)

PostgreSQL 9.0.3
test=# create table t1 (id serial, t time);
NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
CREATE TABLE
test=# insert into t1(t) values ('00:05:10');
INSERT 0 1
test=# select t,t*1.5 from t1;
    t     | ?column?
----------+----------
 00:05:10 | 00:07:45
(1 row)

I think that the behaviour from PostgreSQL is the correct one. MySQL will just remove the :'s to get the string 000510 and then multiplies that value. The behaviour from SQLite is even more strange.

Of course for MySQL you could use the time_to_sec and sec_to_time functions.

mysql> select t,sec_to_time(time_to_sec(t)*1.5) from t1;
+----------+-------------------------------+
| t        | sec_to_time(time_to_sec(t)*2) |
+----------+-------------------------------+
| 00:05:10 | 00:07:45                      |
+----------+-------------------------------+
1 row in set (0.00 sec)

But those functions are not available on SQLite.

sqlite> select t,sec_to_time(time_to_sec(t)*1.5) from t1;
Error: no such function: sec_to_time

How not to grant permissions

I went to EuroBSDcon in Maarssen, the Netherlands. It was a great conference and I might write another blog about it.

I the conference bag there was a copy of the dutch Linux Magazine. The magazine is very nice and covers a broad range of topics.

One article about LogicalDOC caught my attention. The LogicalDOC software uses a MySQL database. and the magazine listed some SQL code about how to create the database user:
create user logicaldoc;
set password FOR logicaldoc@´%´=
PASSWORD('wachtwoord´);
grant all privileges on logical-
doc.*
to logicaldoc@´%´ identified by 
'wachtwoord´;
create database logicaldoc;
flush privileges;

These statements won't work as some of the quotes are wrong. But let's ignore that.


After executing the first line the grant is like this:

GRANT USAGE ON *.* TO 'logicaldoc'@'%'

So the user logicaldoc is allowed to connect from ANY host WITHOUT password. And yes that does work.

After the second statement the grant looks like this:

GRANT USAGE ON *.* TO 'logicaldoc'@'%' IDENTIFIED BY PASSWORD '*0CD1AE57344BB752E3A08B733916948E5A4BF96C'

Now the user is protected by a password, that's mutch better!

After the third statement the grants looks like this:

GRANT USAGE ON *.* TO 'logicaldoc'@'%' IDENTIFIED BY PASSWORD '*0CD1AE57344BB752E3A08B733916948E5A4BF96C'

GRANT ALL PRIVILEGES ON `logicaldoc`.* TO 'logicaldoc'@'%'

Any user who managed to get a connection after the first statement will still be connected and will now be granted ALL PRIVILEGES including SUPER.
Update: This will not include the SUPER privilege (Thanks to Shlomi Noach for noticing this.)

And of course the flush privileges statement was not necessary because they didn't directly modify the mysql.* tables.

It should have been like this:
CREATE USER 'logicaldoc'@'localhost' IDENTIFIED BY 'password';
CREATE DATABASE logicaldoc;
GRANT SELECT, UPDATE, INSERT,… ON `logicaldoc`.* TO 'logicaldoc'@'localhost'; 

This will continue to work if the NO_AUTO_CREATE_USER sql_mode is set.
It assumes that the database connection will use a UNIX socket.

Granting specific rights instead of a simple GRANT ALL is better for security and allows mutch more functionality to work like the read_only setting for slaves, reserved connections for emergencies and the init_connect setting.

The statements seem to have been copy-pasted from the LogicalDOC online documentation. There they also suggest to supply the root password on the commandline with -ppassword instead of using -p and let MySQL prompt for the password.

This doesn't mean that LogicalDOC is insecure. It only means that the person writing the documentation probably didn't fully understand MySQL Permissions.

The OurSQL podcast episode 59 has many more information about MySQL and Security.