getListTableColumnsSQL fails on PostgreSQL when schema name contains special characters or there tables with same names in search_path

[kolesar-andras]

I'm just building a project based on Laravel, test environment had schema was named "web-test" in postgresql. I have set database search_path to "web-test", everything worked in Laravel except changing column definitions. Migration code contains this line:

$table->text('notes')->nullable()->change();

Migration gives error:

[Doctrine\DBAL\Schema\SchemaException]
There is no column with name 'notes' on table 'reports'.

There is a column named notes in the table. I have checked Doctrine\DBAL\Schema\Table why does it throw Doctrine\DBAL\Schema\SchemaException. Dumped $_columns property in method hasColumn but it was an empty array.

I have changed schema name to "webtest" and renaming column worked.

First I have reported this issue at https://github.com/doctrine/doctrine2/issues/5545 where I was asked to give details on how to reproduce it outside the context of laravel.

I have made a test for this situation:

public function testTableWithSchemaContainingHyphen()
{
    $this->createTableAndTestForColumns('hyphentest');
    $this->createTableAndTestForColumns('hyphen-test');
}

public function createTableAndTestForColumns ($schema) {
    $this->_conn->exec('CREATE SCHEMA "' . $schema . '"');
    $this->_conn->exec('SET SEARCH_PATH="' . $schema . '"');
    $table = new \Doctrine\DBAL\Schema\Table('tablename');
    $column = $table->addColumn('id', 'integer');
    $this->_sm->createTable($table);
    $table = $this->_sm->listTableDetails('tablename');
    $this->assertTrue($table->hasColumn('id'));
    $this->_sm->dropTable($table);
    $this->_conn->exec('DROP SCHEMA "' . $schema . '"');
}

I have placed these at the end of file named below and ran phpunit:

phpunit --filter testTableWithSchemaContainingHyphen tests/Doctrine/Tests/DBAL/Functional/Schema/PostgreSqlSchemaManagerTest.php

It fails if there is a hyphen in schema name. It seems listTableDetails misses the table, returns the same empty table object that I have seen in Laravel.

[kolesar-andras]

I have localized the bug that is much bigger I have expected.

lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php

Method getTableWhereClause returns a where criteria that looks up search_path setting from pg_catalog.pg_settings, then does magic string replacements and splitting. This filter is used by getListTableColumnsSQL method that looks up columns for the table.

First problem is that schema names containing special characters like hyphen need to be quoted in search_path and these quotes are stored in settings. For example:

set search_path="hyphen-test";

Method getTableWhereClause queries my search_path:

select replace(replace(setting,'\"\$user\"',user),' ','')
from pg_catalog.pg_settings
where name = 'search_path'

Let's remove magic:

select setting from pg_catalog.pg_settings where name = 'search_path'

This query results the following:

"hyphen-test"

String starts and end with double quotes. This is used to match with pg_namespace.nspname which does not have double quotes. That's why query doen not find any columns for my table in a schema named hyphen-test.

Let's see what's happening in string replacements. The full where clause:

ANY(string_to_array((
    select replace(replace(setting,'\"\$user\"',user),' ','')
    from pg_catalog.pg_settings
    where name = 'search_path'
),','))

First replaces "$user" with current user, then deletes spaces, finally the string is split by commas. These methods work only if there are no spaces or commas in schema names or user name.

The largest problem is that columns for all tables matching these criteria are returned by the query. If the array returned contains more than one item and these tables exist, more than one table will be queried for its columns. Let's see an example:

CREATE SCHEMA foo;
CREATE TABLE foo.tablename (foo_id integer);
CREATE SCHEMA bar;
CREATE TABLE bar.tablename (bar_id integer);
SET search_path=foo,bar;

Method listTableColumns returns two columns for table "tablename": foo_id and bar_id. Just because more than one table was called the same name in two different schemas in search_path. There are no special characters in names.

Column lookup should split search_path correctly, then determine the first matching table and return the schema name that equals to pg_namespace.nspname.

[morozov]

Looks related to https://github.com/doctrine/dbal/issues/950.