Joining SQL query with aliased table fails when alias is "AS"

[GeeH]

This issue has been moved from the zendframework repository as part of the bug migration program as outlined here - http://framework.zend.com/blog/2016-04-11-issue-closures.html

---

Original Issue: https://api.github.com/repos/zendframework/zendframework/issues/7455
User: @mirfilip
Created On: 2015-04-21T09:49:59Z
Updated At: 2015-11-06T21:34:20Z
Body
Let's assume I have two tables: Users and Details. I want to produce SELECT object equivalent to:
SELECT "U".*, "AS".* FROM Users AS "U" JOIN Details AS "AS" ON "U".id = "AS".userId ... - which is a valid SQL query.
The tables names are fake here, so it can look like pushing oneself to edge cases, but when you have a table that aliasing to AS makes sense, there is a bug.

In ZF2, I produce initial query and then use it to join data:

$initialQuery->join(
    ['AS' => 'Details'],
    'U.id = AS.userId',
    [ array of columns to select, irrelevant ],
    Select::JOIN_INNER
);

When passing such a query to AbstractTableGateway::executeSelect() it produces wrongly escaped query (I provide only flawed part for clarity):
... INNER JOIN "Details" AS "AS" ON "U"."id" = AS."userId" - as you can see, ON part of JOIN is not escaped properly.

Confront this with the case that you use different alias:

$initialQuery->join(
    ['ASx' => 'Details'],
    'U.id = ASx.userId',
    [ array of columns to select, irrelevant ],
    Select::JOIN_INNER
);

produces: ... INNER JOIN "Details" AS "ASx" ON "U"."id" = "ASx"."userId"

Also, SELECT has been used here as an example, I think the problem is global with improper identifiers quoting. Note that it has nothing to do with the SQL mode of escaping. It doesn't work with SET sql_mode='ANSI_QUOTES'; set too.

Reproducible using 2.4.*, didn't try with previous tags.

---

Comment

User: @adamlundrigan
Created On: 2015-05-01T17:09:21Z
Updated At: 2015-05-01T17:09:21Z
Body
I've traced this back to Zend\Db\Adapter\Platform\AbstractPlatform::quoteIdentifierInFragment. That method takes a simplified approach to quoting: it tokenizes the fragment by '/([^0-9,a-z,A-Z$_:])/i' and quotes each match that isn't on the "safe words" list. as is on the list and so isn't quoted.

It's part of the tested behvaiour of the method so I'm not sure there's much of a solution other than:

a. Don't use AS as a table alias, or
b. Rewrite the method to recognize when AS is used as a table or field and not a keyword

---

[michalbundyra]

This repository has been closed and moved to laminas/laminas-db; a new issue has been opened at laminas/laminas-db#121.