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

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

Originally posted by @GeeH at https://github.com/zendframework/zend-db/issues/127

laminas / laminas-db

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

Comment