All geek questions in one place

How to safely use reserved SQL names?

I am using Cakephp 3 using sqlserver as a data source. I am sure there is no problem connecting to the database. Since home.ctp asks me to connect to my database .. and I also use the migrations plugin to create my tables .. it seems like there is no problem working with these tools. but after I baked my MVC, I only got an error page.

e.g. $ bin \ cake bake all tests

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

and in browsers local: 8765 \ tests

but all i have is a page with different errors. I see

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 incorrect queries or queries generated with bake, only for mysql. I just want to know how to deal with this. are there any settings that i forgot to make? Please advise. I am new to Cakephp and English is not my native language, sorry if I can not explain my question correctly. thanks in advance.

0
source share
3 answers

As mentioned in the comments of Vishal Gajjar, you are using the reserved desc keyword for your column name, hence the error, this does not bake the error, this is yours.

In order to be able to use such reserved words, the column name must be correctly specified, however CakePHP 3 does not autocopy by default, as this is an expensive operation.

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

see also

+8
source

You can use this code before the problem request:

 $this->Tests->connection()->driver()->autoQuoting(true);

and when you're done, you can turn off automatic quoting:

 $this->Tests->connection()->driver()->autoQuoting(false);

Poor performance will only be with a problem request.

+1
source

Use this:

 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

If you use a keyword, use it in square brackets [ ]

-1
source

Source: https://habr.com/ru/post/1232304/

More articles:


All Articles