fix DELETE sql query - Githubissues

doctrine / data-fixtures

Doctrine2 ORM Data Fixtures Extensions

http://www.doctrine-project.org

MIT License

2.77k stars 224 forks source link

fix DELETE sql query #197

Closed DenysMedvid closed 9 years ago

DenysMedvid commented 9 years ago

Hello.

Fix for sql error if table name is "order"

  An exception occurred while executing 'DELETE FROM order':                                                                                                                                                         
  SQLSTATE[42000]: Syntax error or access violation: 1064 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 'order' at line 1

kingcrunch commented 9 years ago

This only works for MySQL

DenysMedvid commented 9 years ago

Ok, thanks you for quick response.

kingcrunch commented 9 years ago

Hi @BuCeFaL

Me again :smile: There should be something like $connection->quoteIdentifier(), which should (you may guess) quote the identifier according the current connection. I cannot proof the correct method names right now though. I should've written this in the first place, but missed that :no_mouth:

DenysMedvid commented 9 years ago

Fix it with quoteIdentifier

stof commented 9 years ago

this change is weird. the table names are already retrieved using getQuotedTableName.

I think the issue is that your ORM mapping was not telling the ORM to use a quoted identifier.

Forcing quoting here causes issues in 2 cases:

if the mapping already configures quoting properly, you are not quoting it twice
if the identifier is not configured to be quoted, you are now forcing to be quoted, and this changes the behavior of the query, potentially making it use a different table than the one used by the ORM (which would not quote the identifier in this case): http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html#.Veco4bOaClN

DenysMedvid commented 9 years ago

I think, better solution is left all as was. Anyway it is a very bad idea use reserved keywords in table names. I revert my "force quote".

maryo commented 8 years ago

It should also be reverted here since it is a bug. It causes double-quoting. If you want your table to be escaped, use backtics in table name definition like @ORM\Table(name="order"). And in this case, your change quotes it again so it tries to purge using DELETE FROM ""order"" (Postgres example, postgres uses double quotes, not backtics).