Bug #17998 | prevent users from setting ALLOW_INVALID_DATES in their session | ||
---|---|---|---|
Submitted: | 6 Mar 2006 22:21 | Modified: | 5 Oct 2008 10:29 |
Reporter: | Lukas Smith | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S4 (Feature request) |
Version: | all | OS: | Any (irrelevant) |
Assigned to: | CPU Architecture: | Any |
[6 Mar 2006 22:21]
Lukas Smith
[6 Mar 2006 22:32]
Arjen Lentz
I think this feature request makes sense. Possible ways to implement this: - A flag to tell the server to maintain AT LEAST the global SQL_MODE setting. (i.e. a user cannot set anything less than that within their connection) - An option like minimum_sql_mode to do the same, but allow the global setting to differ (the global setting should be subject to this minimum also, though)
[8 Mar 2006 19:20]
MySQL Verification Team
Thank you for the bug report. mysql> insert into tb5 (nasc) values ("2006-14-01"); ERROR 1292 (22007): Incorrect date value: '2006-14-01' for column 'nasc' at row 1 mysql> set sql_mode="ALLOW_INVALID_DATES"; Query OK, 0 rows affected (0.03 sec) mysql> insert into tb5 (nasc) values ("2006-14-01"); Query OK, 1 row affected, 1 warning (0.00 sec)
[11 Sep 2007 14:32]
Roland Bouman
Hi all, what about making it privilege to set the sql_mode, or maybe even privilege the individual sql_mode settings?
[17 Sep 2007 7:31]
Lukas Smith
I think this would solve the problem. However as more and more privileges are added the more people will miss role's in order to better group and manage privileges.
[4 Oct 2008 19:05]
Konstantin Osipov
There is an alternative solution: store SQL_MODE of session with the table when it was created, and activate it for inserts into the table. This alternate solution suggests a workaround: since we do the exact this thing with stored procedures, only allow inserts into the table via a stored procedure. Example: mysql> set sql_mode=default; Query OK, 0 rows affected (0.00 sec) mysql> drop table if exists t1; Query OK, 0 rows affected (0.00 sec) mysql> drop procedure if exists p1; Query OK, 0 rows affected (0.00 sec) mysql> create table t1 (a int unique auto_increment, d date); Query OK, 0 rows affected (0.00 sec) mysql> set sql_mode='strict_all_tables'; Query OK, 0 rows affected (0.00 sec) -- sic: d_in is varchar, otherwise the truncation would happen at assignment of d_in and will be guarded, again, by session sql_mode, not procedure sql_mode mysql> create procedure p1 (d_in varchar(255)) insert into t1 (d) values (d_in); Query OK, 0 rows affected (0.00 sec) mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> call p1('2006-14-01'); ERROR 1292 (22007): Incorrect date value: '2006-14-01' for column 'd' at row 1 mysql> select * from t1; Empty set (0.00 sec) mysql> insert into t1 (d) values ('2006-14-01'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 'd' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t1; +---+------------+ | a | d | +---+------------+ | 1 | 0000-00-00 | +---+------------+ 1 row in set (0.00 sec)
[4 Oct 2008 19:09]
Konstantin Osipov
Another simple way to fix this: when ALLOW_INVALID_DATES bit is being set, check that the global value has this bit. If it doesn't refuse to change the session setting.
[5 Oct 2008 10:20]
Arjen Lentz
Konstantin, please refer to my original comment which described a simple, more generic, and effective approach. Session mode should not be allowed to be anything less than what's set globally. Realistically it's not just about bad dates, it's about other nasties.
[5 Oct 2008 10:29]
Lukas Smith
Well that might be overly harsh. Especially once SQL_MODE's are enabled by default. Then again, if I disallow double quotes for quoting, do I want developers to be able to enable them? After all they would end up in logs etc. Then again we have no way to enable SQL_MODE's on a per database level. So this means that admins that do want do leverage SQL_MODE's will have to provide a separate server instance for legacy applications. So maybe we should divide SQL_MODE's two catagories: Those that change the allowed SQL syntax and those that determine storage. The later type is a much bigger problem for a DBA, than the first. We might be more harsh on the later type for this reason.
[5 Oct 2008 10:37]
Konstantin Osipov
I personally think the whole sql_mode approach is just broken. The problem is that it contains options that are too fundamental to server operation -- and normally should be the same across the entire site, otherwise interoperability issues will occur inevitably. At the same time it also contains some very basic settings, which make sense to be changed at the runtime. For the first group, I believe there should be no possibility to change the value dynamically. The second group should not be part of sql_mode.