Search code examples
phpmysqlsql-insertquotingmedoo

INSERT by ANSI_QUOTES mode

I'm having a problem trying to do an INSERT, it's succeeding even though it shouldn't.

My table structure: table structure

Note: given1, given2 and given3 are required fields.

In my application I execute the following method (https://github.com/catfan/Medoo):

$this->medoo->insert('teste', ['dado2' => 11, 'dado3' => 'teste']);

This should not be accepted because data2 is of type SET, and data3 is of type INTEGER`. Even so, the insertion succeeds. Moreover, data1 is not passed as an argument, even though it is a required field.

I checked the MySQL log and got the following entries:

SET SQL_MODE=ANSI_QUOTES;
INSERT INTO "teste" ("dado2", "dado3") VALUES ('11', 'teste');

Running this SQL manually in the database, I discovered that the problem is in using ANSI_QUOTES. The database somehow accepts the insert, and instead of issuing an error message it issues a warning:
print screen of the return message

I think that is need to modify the Medoo source code or report this problem to MySQL. I do not know what to do.

MySQL version is 5.7.14
I use the MySQL Workbench 6.3.6

Solution

  • When mySQL server is set in STRICT mode, it produces error when you try to insert values that do not fit into a column. By calling SET SQL_MODE=ANSI_QUOTES; this strict mode is most likely disabled.

    You can try to contact the author of Medoo framework or rather use another framework or just PDO to access your database.

    See mysql docs on server modes for more information.