2

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

Thomas Dickey
  • 51,086
  • 7
  • 70
  • 105
Guilherme Almeida Girardi
  • 35
  • 5

1 Answers1

1

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.

Jirka Hrazdil
  • 3,983
  • 1
  • 14
  • 17
  • Perfect friend!!! Thanks for the info. I solved the problem by commenting on line 121 of Medoo.php ($commands [] = 'SET SQL_MODE = ANSI_QUOTES') and adding the ANSI_QUOTES mode directly to the MySQL configuration. The problem is with Medoo, it should not delete all modes to leave only ANSI_QUOTES, should keep the other modes and add ANSI_QUOTES. – Guilherme Almeida Girardi Nov 23 '17 at 16:39