actridge/Spot — Issue #10: Entity with column names that are reserved keywords
Author: TheSavior
Created: 2012-01-11 06:15:14+00:00
State: open

If I have an entity that uses a reserved keyword in mysql, when I try to save, update, or insert the entity, nothing happens. No exception is thrown, no error is added to the log, and the changes aren't made to the database.

Looking into just the create method of the PDO\Abstract file, I see a few issues. Firstly, you have it in a try catch, but you only rethrow the exception if it is a very specific one, and you supress all others.

The specific issue we have is a syntax error, which is thrown as a PDO 42000 exception. (We shouldn't check for this one specifically either, it should be a few specific ones, and then a general rethrow).

The reason it is a syntax error is because mysql can't use keywords as column names without help.

For example, a query that looks like this:

INSERT INTO ____ (Key1, Key2, Load) Values(.... fails because Load is a keyword. A good fix is to "escape" the column names so that it looks like:

INSERT INTO ____(`Key1`, `Key2`, `Load`) ....

I know mysql will except table names and column names in that format.

I changed the create function in my copy to be:

$sql = "INSERT INTO " . $datasource .
" (`" . implode('`, `', array_keys($data)) . "`)" .
" VALUES(:" . implode(', :', array_keys($binds)) . ")";

which adds in the tildes.

I imagine there are many many more places where those tildes need to be added as well as the exception checking needs to be modified.