如何安全地使用保留的 SQL 名称?
我正在使用 Cakephp 3，使用 sqlserver 作为数据源服务器.我确定我的数据库连接没有问题.. 因为 home.ctp 提示我已连接到我的数据库.. 而且我也在使用迁移插件来创建我的表.. 似乎没有问题这些工具.但是在我烘焙我的 MVC 之后，我只得到了充满错误的页面..
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..
例如$bincake 烘焙所有测试
for example $bincake bake all tests
我没有发现任何错误，MVC 位于其特定文件夹 testController.php、testTable 等中.
there are no errors I found and MVC are in its specific folder, testController.php, testTable, etc.
在浏览器中本地主机:8765 ests
and in browsers localhost:8765 ests
但我得到的只是不同错误的页面..我看到了
but all I got is page of different errors.. Im seeing
错误:SQLSTATE[42000]:[Microsoft][SQL Server Native Client 11.0][SQL Server]关键字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
左侧还有更多错误.
我认为这是因为控制器有错误的查询或由 bake 生成的查询仅适用于 mysql.我只是想知道如何处理这个.有没有我忘记做的设置?请指教.我是 Cakephp 的新手，英语不是我的母语，抱歉，如果我不能正确解释我的问题.提前致谢.
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.
推荐答案
正如 Vishal Gajjar 在评论中已经提到的，您使用保留关键字 desc 作为您的列名，因此出现错误，这不是烘焙的错，是你的错.
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.
为了能够使用这样的保留字，需要正确引用列名，但是 CakePHP 3 默认不再自动引用，因为这是一个昂贵的操作.
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.
如果您坚持使用保留字，请通过 app.php 配置中的 quoteIdentifiers 选项启用标识符引用，或者使用 autoQuoting() (enableAutoQuoting() as of CakePHP 3.4) DB 驱动程序的方法.
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.
另见
- Cookbook > 数据库访问 &ORM > 数据库基础 > 标识符引用
- 食谱> 3.x 迁移指南 > 新 ORM 升级指南 > 默认禁用标识符引用
- API > CakeDatabaseDriver::autoQuoting()
- API > CakeDatabaseDriver::enableAutoQuoting()
相关文章