sql server - How to safely use reserved SQL names?

Question

I'm using Cakephp 3 using sqlserver as datasource server. I am sure there's no problem with my database connection.. as home.ctp prompts that I am connected to my database.. and I'm as well using migrations plugin to create my tables.. it seems like there is no problem working with these tools. but after I bake my MVC, I only got page full of errors..

for example $bincake bake all tests

there are no errors I found and MVC are in its specific folder, testController.php, testTable, etc.

and in browsers localhost:8765ests

but all I got is page of different errors.. Im seeing

Error: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'desc'.

SELECT * FROM (SELECT Tests.id AS [Tests__id], Tests.desc AS [Tests__desc], (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS [_cake_page_rownum_] FROM tests Tests) _cake_paging_ WHERE _cake_paging_._cake_page_rownum_ <= :c0

and more errors on the left side.

I assume this is because of controllers with wrong queries or queries generated by bake is for mysql only. I just wanna know how to deal with this. is there a setting I forgot to do? please advice. I am new to Cakephp, and English is not my native language, sorry if I can't explain my question properly. thanks in advance.

Answer 1

As already mentioned by Vishal Gajjar in the comments, you are using the reserved keyword desc for your column name, hence the error, it's not bakes fault, it's yours.

In order to be able to use such reserved words, the column name needs to be quoted properly, however CakePHP 3 doesn't auto-quote by default anymore, as it's an expensive operation.

If you insist on using reserved words, enable identifier quoting via the quoteIdentifiers option in your app.php config, or enable it manually using the autoQuoting() (enableAutoQuoting() as of CakePHP 3.4) method of the DB driver.

See also

• Cookbook > Database Access & ORM > Database Basics > Identifier Quoting
• Cookbook > 3.x Migration Guide > New ORM Upgrade Guide > Identifier Quoting Disabled by Default
• API > CakeDatabaseDriver::autoQuoting()
• API > CakeDatabaseDriver::enableAutoQuoting()