I have a php app that successfully makes SQL requests against MySQL servers. Nevertheless the same app breaks against DO managed database whenever the requests include double-quotes.

The following request returns an error 1054 with managed database while it works with classic MySQL servers.

select * from users where username="demo" 
=> error 1054

select * from users where username='demo'
=> ok

Is there a way to configure DO managed database to accept double-quotes?

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
1 answer

This is related to the sql_mode used. There’s a sqlmode setting, ‘ANSIQUOTES’ that will make the “ an identifier character.

e.g.

mysql> set session sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where c3="foo";  <-- double quotes operating the same as `
ERROR 1054 (42S22): Unknown column 'foo' in 'where clause'

mysql> select * from t1 where c3='foo';  <-- single quotes works
+----+----+----+------+
| id | c1 | c2 | c3   |
+----+----+----+------+
|  3 |  5 |  6 | foo  |
+----+----+----+------+
1 row in set (0.00 sec)

mysql> set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where c3="foo";
+----+----+----+------+
| id | c1 | c2 | c3   |
+----+----+----+------+
|  3 |  5 |  6 | foo  |
+----+----+----+------+
1 row in set (0.00 sec)

I’ve removed the ANSI_QUOTES sql-mode setting at a session level.

You can change the global value under the settings tab of your instance(s).

Hope this solves your issue!

Andrew

Submit an Answer