[bug] NotORM doesn't quote table names

[Michal-Mikolas]

Hello,
I think I find bug / lack in NotORM.

Problem

I am trying to get data from MySQL table named order. So, when I write PHP code:

$notorm->order()->fetch();

NotORM creates SQL query:

SELECT * FROM order

and MySQL can't parse it ("order" is SQL reserved keyword), so error appears:

Syntax error near 'order' at line 1

Solution

NotORM should quote table names and column names like this:

$notorm->order()->fetch();  //SELECT * FROM `order`

Hotfix

If anyone has the same problem, this hotfix will help:

$notorm->{'`order`'}()  // add quotes around table name manually

But for related table queries this is still not enough:

$order = $notorm->{'`order`'}()->fetch(); 

$order->order_products()->fetch(); 

created queries:

SELECT * FROM `order`; 

SELECT * FROM order_products WHERE (order_products.`order`_id IN (1));  // bad column name "`order`_id"

Thank for answer,
Michal Mikoláš

[Michal-Mikolas]

"->via() method" also not working

If I try to fix related table query with ->via() method, there is no change:

$order = $notorm->{'`order`'}()->fetch(); 

$order->order_products()->via('order_id')->fetch();  // SELECT * FROM order_products WHERE (order_products.`order`_id IN (1));

As you can see, generated SQL is still same with bad column name: ``order_id.

Thanks.

[brandonlamb]

Quoting tables can have a hidden problem as some RDBMs use UPPER_CASE while some use lower_case table / object names. Also columns could be defined as customerName, CustomerName, customername. By quoting these, they must actually match the defined case.

So if your table was created as CUSTOMER_ADDRESS and you tried
select * from 'customer_address'
It may fail?

[Michal-Mikolas]

All my tables and columns are lower case, separated by underscore. As you can see in my example codes above.

Problem is that NotORM never quotes table names for me in actual version.

[vrana]

This a conscious deficiency in NotORM. PDO doesn't support quoting
identifiers and I don't think that NotORM is the right layer to add this.

I've fixed the bug with via().

Jakub

On 06/11/2014 10:16 AM, Michal Mikoláš wrote:

Hello,
I think I find bug / lack in NotORM.

Problem

I am trying to get data from MySQL table named |order|. So, when I write
PHP code:

$notorm->order()->fetch();

NotORM creates SQL query:

SELECT * FROM order

and MySQL can't parse it ("order" is SQL reserved keyword), so error
appears:

|Syntax error near 'order' at line 1
|

Solution

NotORM should quote table names and column names like this:

$notorm->order()->fetch(); //SELECT * FROM order

Hotfix

If anyone has the same problem, this hotfix will help:

$notorm->{'order'}() // add quotes around table name manually

But for related table queries this is still not enough:

$order = $notorm->{'order'}()->fetch();

$order->order_products()->fetch();

created queries:

SELECT * FROM order;

SELECT * FROM order_products WHERE (order_products.order_id IN (1)); // bad column name "order_id"

Thank for answer,
Michal Mikoláš

—
Reply to this email directly or view it on GitHub
#88.

[Michal-Mikolas]

Thanks for fixing via() method.

---

I don't think that NotORM is the right layer to add this

I think this is the only layer when I decided to use NotORM. Because when I try start quoting tables out of NotORM,

$articles = $notorm->{'`article`'}();

NotORM stops working:

$articles->post()  // error in SQL, bad column "`article`_id" in table "post"

---

Anyway... What is the proper way to work with table called order with NotORM?

Thanks.

[vrana]

The best solution is to not have such table in the first place. Some problems might be fixed by via, some with a custom Structure.