Last month, I was introduced to an issue that a MySQL Database Administrator cannot control the SQL mode that an application runs under. Any user can set the SQL mode for the connection’s session, invalidating the global SQL mode that might exist in the configuration file. In his post, Chris Travers points out the problem (part in brackets added for context):

If you have two or ten applications reading and writing the same tables, then each one of them can decide what sort of data assumptions the server should use [by setting the SQL Mode] when validating the data prior to storing it.

To illustrate this with an example, one MySQL SQL mode is ALLOW_INVALID_DATES, which will only validate month and day so that the month is between 1 and 12, and the day is between 1 and 31 on DATE and DATETIME data types. So, if SQL mode is set to ALLOW_INVALID_DATES, ’2012-09-31′ is a valid date and MySQL won’t complain.

By allowing ‘ALLOW_INVALID_DATES’, we allow the storage of invalid dates, violating the integrity of our data! As a database administrator, I want to prevent this. So I set the server in strict mode (any sql mode that includes ‘STRICT_ALL_TABLES’ or ‘STRICT_TRANS_TABLES’, according to the MySQL documentation) using the /etc/my.cnf ‘sql_mode’ option and restart the machine. Great, now no one can insert invalid dates, right? Wrong. Any application can get around this restriction by changing their session sql_mode.

Fortunately, there is a tool for a database administrator to control the SQL Mode: Stored Procedures. Upon careful reading of the documentation, you come to this blurb:

MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked.

Here is a quick script to test this out:

-- Create the procedure in strict mode
SET @@session.sql_mode='STRICT_ALL_TABLES'; 
DELIMITER $$
DROP PROCEDURE IF EXISTS `cast_date` $$
CREATE PROCEDURE `cast_date` ()
BEGIN
    SELECT CAST('2012-09-31' AS DATE);
END $$
DELIMITER ;
CALL cast_date();

-- Attempt to allow invalid dates
SET @@session.sql_mode='ALLOW_INVALID_DATES';
CALL cast_date();
SHOW WARNINGS;

An application attempting to override the session’s sql_mode would expect this to succeed. Instead we get this:

mysql> CALL cast_date();
+----------------------------+
| CAST('2012-09-31' AS DATE) |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2012-09-31' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

Fantastic, I have just enforced the integrity of my date columns, assuming I also limit all access to modify the tables only through stored procedures. There is a catch though. Parameters of stored procedures utilize the session’s SQL mode value when invoked. The above stored procedure is pretty useless, as I would want to pass the date into the procedure:

SET @@session.sql_mode='STRICT_ALL_TABLES';
DELIMITER $$
DROP PROCEDURE IF EXISTS `cast_date` $$
CREATE PROCEDURE `cast_date` ( IN in_date DATE)
BEGIN
    SELECT in_date; -- NOT CASTING
END $$
DELIMITER ;

-- Still in strict mode, we get an error
CALL cast_date('2012-09-31');

-- ERROR 1292 (22007): Incorrect date value: '2012-09-31' for column 'in_date' at row 1

-- Allowing invalid dates, this works
SET @@session.sql_mode='ALLOW_INVALID_DATES';
CALL cast_date('2012-09-31');
-- +------------+
-- | in_date    |
-- +------------+
-- | 2012-09-31 |
-- +------------+
-- 1 row in set (0.00 sec)
-- Query OK, 0 rows affected (0.00 sec)

To overcome this, we have to be vigilant inside the stored procedures to enforce our types:

SET @@session.sql_mode='STRICT_ALL_TABLES';
DELIMITER $$
DROP PROCEDURE IF EXISTS `cast_date` $$
CREATE PROCEDURE `cast_date` ( IN in_date DATE)
BEGIN
    SET @my_date := CAST(in_date AS DATE);
    SELECT @my_date; 
END $$
DELIMITER ;

-- Still in strict mode, we get an error
CALL cast_date('2012-09-31');

-- Allowing invalid dates, we get null and a warning
SET @@session.sql_mode='ALLOW_INVALID_DATES';
CALL cast_date('2012-09-31');

In summary, while MySQL natively allows each connection to change the SQL Mode it runs in, stored procedures offer a method to prevent this. An argument might be made against limiting access to your tables only through stored procedures, but that is a topic for a different day. How far are you willing to go to protect the integrity of your data?

Be Sociable, Share!

•