Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31123

Stored programs does not validate SQLs when using certain SQL modes

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4(EOL), 10.5, 10.6, 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
    • 10.5, 10.6, 10.11
    • Stored routines, Triggers
    • None

    Description

      sql_mode=ALLOW_INVALID_DATES does not validate when inserting date through trigger/procedure

      10.4.29-opt>SET sql_mode = '';
      Query OK, 0 rows affected (0.000 sec)
       
      10.4.29-opt>CREATE TABLE t1 (a INT);
      Query OK, 0 rows affected (0.011 sec)
       
      10.4.29-opt>CREATE TABLE t2 (a datetime);
      Query OK, 0 rows affected (0.014 sec)
       
      10.4.29-opt>CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES('2023-02-31');
      Query OK, 0 rows affected (0.006 sec)
       
      10.4.29-opt>INSERT INTO t1 (a) VALUES (1);
      Query OK, 1 row affected (0.003 sec)
       
      10.4.29-opt>SELECT * FROM t2;
      +---------------------+
      | a                   |
      +---------------------+
      | 0000-00-00 00:00:00 |
      +---------------------+
      1 row in set (0.000 sec)
       
      10.4.29-opt>SET sql_mode = 'ALLOW_INVALID_DATES';
      Query OK, 0 rows affected (0.000 sec)
       
      10.4.29-opt>INSERT INTO t1 (a) VALUES (2);
      Query OK, 1 row affected (0.003 sec)
       
      10.4.29-opt>SELECT * FROM t2;
      +---------------------+
      | a                   |
      +---------------------+
      | 0000-00-00 00:00:00 |
      | 0000-00-00 00:00:00 |
      +---------------------+
      2 rows in set (0.005 sec)
       
      10.4.29-opt>
      

      Expected result

      10.11.3-opt>select * from t2;
      +---------------------+
      | a                   |
      +---------------------+
      | 0000-00-00 00:00:00 |
      | 2023-02-31 00:00:00 |
      +---------------------+
      2 rows in set (0.000 sec)
       
      10.11.3-opt>
      

      test case for sp

      SET sql_mode = '';
      DROP TABLE t1;
      CREATE TABLE t1(a datetime);
      delimiter //
      CREATE PROCEDURE p1()
      BEGIN
        INSERT INTO t1 VALUES ('2023-02-31');
      END //
      delimiter ;
      call p1();
      SELECT * FROM t1;
      SET sql_mode = 'ALLOW_INVALID_DATES';
      call p1();
      SELECT * FROM t1;
      

      Attachments

        Issue Links

          Activity

            built-in function is not validating when using sql_mode=IGNORE_SPACE

            Actual result

            11.0.2-opt>delimiter //
            11.0.2-opt>CREATE PROCEDURE p1()
                -> BEGIN
                ->   CREATE TABLE count (i INT);
                -> END //
            Query OK, 0 rows affected (0.007 sec)
             
            11.0.2-opt>delimiter ;
            11.0.2-opt>SET sql_mode = 'IGNORE_SPACE';
            Query OK, 0 rows affected (0.000 sec)
             
            11.0.2-opt>call p1();
            Query OK, 0 rows affected (0.013 sec)
             
            11.0.2-opt>
            11.0.2-opt>drop table count;
            Query OK, 0 rows affected (0.013 sec)
            

            Expected result

            11.0.2-opt>CREATE TABLE count (i INT);
            ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'count (i INT)' at line 1
            11.0.2-opt>
            

            ramesh Ramesh Sivaraman added a comment - built-in function is not validating when using sql_mode=IGNORE_SPACE Actual result 11.0.2-opt>delimiter // 11.0.2-opt> CREATE PROCEDURE p1() -> BEGIN -> CREATE TABLE count (i INT ); -> END // Query OK, 0 rows affected (0.007 sec)   11.0.2-opt>delimiter ; 11.0.2-opt> SET sql_mode = 'IGNORE_SPACE' ; Query OK, 0 rows affected (0.000 sec)   11.0.2-opt>call p1(); Query OK, 0 rows affected (0.013 sec)   11.0.2-opt> 11.0.2-opt> drop table count ; Query OK, 0 rows affected (0.013 sec) Expected result 11.0.2-opt> CREATE TABLE count (i INT ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'count (i INT)' at line 1 11.0.2-opt>

            Some more test cases
            sql_mode=EMPTY_STRING_IS_NULL

            SET sql_mode=DEFAULT;
            delimiter //
            CREATE OR REPLACE PROCEDURE p1()
            BEGIN
               SELECT '' AS empty;
            END //
            delimiter ;
            SET sql_mode = 'EMPTY_STRING_IS_NULL';
            call p1();
            

            Actual result

            11.0.2-opt>call p1();
            +-------+
            | empty |
            +-------+
            |       |
            +-------+
            1 row in set (0.000 sec)
             
            Query OK, 0 rows affected (0.000 sec)
             
            11.0.2-opt>
            

            Expected result

            11.0.2-opt>SELECT '' AS empty;
            +-------+
            | empty |
            +-------+
            |  NULL |
            +-------+
            1 row in set (0.000 sec)
             
            11.0.2-opt>
            

            sql_mode=HIGH_NOT_PRECEDENCE

            SET sql_mode=DEFAULT;
            delimiter //
            CREATE OR REPLACE PROCEDURE p1()
            BEGIN
               SELECT NOT 1 BETWEEN -5 AND 5;
            END //
            delimiter ;
            SET sql_mode = 'HIGH_NOT_PRECEDENCE';
            call p1();
            

            Actual result

            11.0.2-opt>call p1();
            +------------------------+
            | NOT 1 BETWEEN -5 AND 5 |
            +------------------------+
            |                      0 |
            +------------------------+
            1 row in set (0.000 sec)
             
            Query OK, 0 rows affected (0.000 sec)
            11.0.2-opt>
            

            Expected result

            11.0.2-opt>SELECT NOT 1 BETWEEN -5 AND 5;
            +------------------------+
            | NOT 1 BETWEEN -5 AND 5 |
            +------------------------+
            |                      1 |
            +------------------------+
            1 row in set (0.000 sec)
             
            11.0.2-opt>
            

            sql_mode=NO_AUTO_VALUE_ON_ZERO

            SET sql_mode=DEFAULT;
            delimiter //
            CREATE OR REPLACE PROCEDURE p1()
            BEGIN
              CREATE OR REPLACE TABLE t1 (i INT AUTO_INCREMENT, KEY(i));
              INSERT INTO t1 VALUES(0);
            END //
            delimiter ;
            SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
            call p1();
            SELECT * FROM t1;
            

            Actual result

            11.0.2-opt>call p1();
            Query OK, 1 row affected (0.016 sec)
             
            11.0.2-opt>SELECT * FROM t1;
            +---+
            | i |
            +---+
            | 1 |
            +---+
            1 row in set (0.000 sec)
             
            11.0.2-opt>
            

            Expected result

            11.0.2-opt>truncate t1;
            Query OK, 0 rows affected (0.028 sec)
             
            11.0.2-opt>INSERT INTO t1 VALUES(0);
            Query OK, 1 row affected (0.002 sec)
             
            11.0.2-opt>SELECT * FROM t1;
            +---+
            | i |
            +---+
            | 0 |
            +---+
            1 row in set (0.000 sec)
             
            11.0.2-opt>
            

            sql_mode=NO_BACKSLASH_ESCAPES

            SET sql_mode=DEFAULT;
            delimiter //
            CREATE OR REPLACEv PROCEDURE p1()
            BEGIN
              CREATE OR REPLACE TABLE t1 (a VARCHAR(20));
              INSERT INTO t1 VALUES ("\\");
            END //
            delimiter ;
            SET sql_mode = 'NO_BACKSLASH_ESCAPES';
            call p1();
            SELECT * FROM t1;
            

            Actual result

            11.0.2-opt>call p1();
            Query OK, 1 row affected (0.011 sec)
             
            11.0.2-opt>SELECT * FROM t1;
            +------+
            | a    |
            +------+
            | \    |
            +------+
            1 row in set (0.000 sec)
             
            11.0.2-opt>
            

            Expected result

            11.0.2-opt>truncate t1;
            Query OK, 0 rows affected (0.006 sec)
             
            11.0.2-opt>INSERT INTO t1 VALUES ("\\");
            Query OK, 1 row affected (0.001 sec)
             
            11.0.2-opt>SELECT * FROM t1;
            +------+
            | a    |
            +------+
            | \\   |
            +------+
            1 row in set (0.000 sec)
             
            11.0.2-opt>
            

            sql_mode='NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'

            SET sql_mode=DEFAULT;
            CREATE OR REPLACE TABLE t1 (i INT  NOT NULL AUTO_INCREMENT PRIMARY KEY,name varchar(2), key(name) comment 'string') ;
            delimiter //
            CREATE OR REPLACE PROCEDURE p1()
            BEGIN
              SHOW CREATE TABLE t1;
            END //
            delimiter ;
            SET sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
            call p1();
            

            Actual result

            11.0.2-opt>call p1();
            +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table                                                                                                                                                                                                              |
            +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | t1    | CREATE TABLE `t1` (
              `i` int(11) NOT NULL AUTO_INCREMENT,
              `name` varchar(2) DEFAULT NULL,
              PRIMARY KEY (`i`),
              KEY `name` (`name`) COMMENT 'string'
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
            +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            1 row in set (0.001 sec)
             
            Query OK, 0 rows affected (0.001 sec)
             
            11.0.2-opt>
            

            Expected result

            11.0.2-opt>SHOW CREATE TABLE t1\G
            *************************** 1. row ***************************
                   Table: t1
            Create Table: CREATE TABLE `t1` (
              `i` int(11) NOT NULL,
              `name` varchar(2) DEFAULT NULL,
              PRIMARY KEY (`i`),
              KEY `name` (`name`)
            )
            1 row in set (0.000 sec)
             
            11.0.2-opt>
            

            ramesh Ramesh Sivaraman added a comment - Some more test cases sql_mode=EMPTY_STRING_IS_NULL SET sql_mode=DEFAULT; delimiter // CREATE OR REPLACE PROCEDURE p1() BEGIN SELECT '' AS empty; END // delimiter ; SET sql_mode = 'EMPTY_STRING_IS_NULL'; call p1(); Actual result 11.0.2-opt>call p1(); +-------+ | empty | +-------+ | | +-------+ 1 row in set (0.000 sec)   Query OK, 0 rows affected (0.000 sec)   11.0.2-opt> Expected result 11.0.2-opt>SELECT '' AS empty; +-------+ | empty | +-------+ | NULL | +-------+ 1 row in set (0.000 sec)   11.0.2-opt> sql_mode=HIGH_NOT_PRECEDENCE SET sql_mode=DEFAULT; delimiter // CREATE OR REPLACE PROCEDURE p1() BEGIN SELECT NOT 1 BETWEEN -5 AND 5; END // delimiter ; SET sql_mode = 'HIGH_NOT_PRECEDENCE'; call p1(); Actual result 11.0.2-opt>call p1(); +------------------------+ | NOT 1 BETWEEN -5 AND 5 | +------------------------+ | 0 | +------------------------+ 1 row in set (0.000 sec)   Query OK, 0 rows affected (0.000 sec) 11.0.2-opt> Expected result 11.0.2-opt>SELECT NOT 1 BETWEEN -5 AND 5; +------------------------+ | NOT 1 BETWEEN -5 AND 5 | +------------------------+ | 1 | +------------------------+ 1 row in set (0.000 sec)   11.0.2-opt> sql_mode=NO_AUTO_VALUE_ON_ZERO SET sql_mode=DEFAULT; delimiter // CREATE OR REPLACE PROCEDURE p1() BEGIN CREATE OR REPLACE TABLE t1 (i INT AUTO_INCREMENT, KEY(i)); INSERT INTO t1 VALUES(0); END // delimiter ; SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; call p1(); SELECT * FROM t1; Actual result 11.0.2-opt>call p1(); Query OK, 1 row affected (0.016 sec)   11.0.2-opt>SELECT * FROM t1; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.000 sec)   11.0.2-opt> Expected result 11.0.2-opt>truncate t1; Query OK, 0 rows affected (0.028 sec)   11.0.2-opt>INSERT INTO t1 VALUES(0); Query OK, 1 row affected (0.002 sec)   11.0.2-opt>SELECT * FROM t1; +---+ | i | +---+ | 0 | +---+ 1 row in set (0.000 sec)   11.0.2-opt> sql_mode=NO_BACKSLASH_ESCAPES SET sql_mode=DEFAULT; delimiter // CREATE OR REPLACEv PROCEDURE p1() BEGIN CREATE OR REPLACE TABLE t1 (a VARCHAR(20)); INSERT INTO t1 VALUES ("\\"); END // delimiter ; SET sql_mode = 'NO_BACKSLASH_ESCAPES'; call p1(); SELECT * FROM t1; Actual result 11.0.2-opt>call p1(); Query OK, 1 row affected (0.011 sec)   11.0.2-opt>SELECT * FROM t1; +------+ | a | +------+ | \ | +------+ 1 row in set (0.000 sec)   11.0.2-opt> Expected result 11.0.2-opt>truncate t1; Query OK, 0 rows affected (0.006 sec)   11.0.2-opt>INSERT INTO t1 VALUES ("\\"); Query OK, 1 row affected (0.001 sec)   11.0.2-opt>SELECT * FROM t1; +------+ | a | +------+ | \\ | +------+ 1 row in set (0.000 sec)   11.0.2-opt> sql_mode='NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS' SET sql_mode=DEFAULT; CREATE OR REPLACE TABLE t1 (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name varchar(2), key(name) comment 'string') ; delimiter // CREATE OR REPLACE PROCEDURE p1() BEGIN SHOW CREATE TABLE t1; END // delimiter ; SET sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; call p1(); Actual result 11.0.2-opt>call p1(); +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `i` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(2) DEFAULT NULL, PRIMARY KEY (`i`), KEY `name` (`name`) COMMENT 'string' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec)   Query OK, 0 rows affected (0.001 sec)   11.0.2-opt> Expected result 11.0.2-opt>SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `i` int(11) NOT NULL, `name` varchar(2) DEFAULT NULL, PRIMARY KEY (`i`), KEY `name` (`name`) ) 1 row in set (0.000 sec)   11.0.2-opt>

            People

              shulga Dmitry Shulga
              ramesh Ramesh Sivaraman
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.