Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PostgreSQLSchemaManager does not resolve single-quote quoting when retrieving schema information #6357

Open
sbuerk opened this issue Apr 16, 2024 · 0 comments

Comments

@sbuerk
Copy link
Contributor

sbuerk commented Apr 16, 2024

Bug Report

Q A
Version 3.8.3, 4.0.1

Summary

PostgreSQL uses a single-quote to quote a single-quote,
ending in double single-quotes ''. This is not reversed
correctly when retrieving the schema information, ending
in having a php string value with double single-quotes in it.

For example, if a default json vale {"key": "value with a single quote ' in string value"}
should be used as field default value.

Current behaviour

Retrieving the schema information, the column default retrieved with
Column->getDefault() returns:

{"key": "value with a single quote '' in string value"}

instead of

{"key": "value with a single quote ' in string value"}

How to reproduce

Adding this test to tests/Functional/Schema/PostgreSQLSchemaManagerTest.php

    public function testJsonDefaultValue(): void
    {
        $testTable = new Table('test_json');
        $testTable
            ->addColumn('foo', Types::JSON)
            ->setDefault('{"key": "value with a single quote \' in string value"}');
        $this->dropAndCreateTable($testTable);

        $columns = $this->schemaManager->listTableColumns('test_json');

        self::assertSame(Types::JSON, $columns['foo']->getType()->getName());
        self::assertSame(
          '{"key": "value with a single quote \' in string value"}',
          $columns['foo']->getDefault()
        );
    }

demonstrates how this can be reproduced, ending up in a error with following missmatch:

-'{"key": "value with a single quote ' in string value"}'
+'{"key": "value with a single quote '' in string value"}'

A corresponding native SQL would be:

CREATE TABLE test_json (
  foo JSON DEFAULT '{"key": "value with a single quote '' in string value"}'
);

Expected behaviour

Column->getDefault() should return

{"key": "value with a single quote ' in string value"}
sbuerk added a commit to sbuerk/dbal that referenced this issue Apr 16, 2024
`PostgreSQLSchemaManager::_getPortableTableColumnDefinition()` is
modified to call the private method `parseDefaultExpression()` on
default values for `JSON` field to correctly normalize doubled
single-quotes in json string value.

A test to cover this case is added.

Fixes doctrine#6357
sbuerk added a commit to sbuerk/dbal that referenced this issue Apr 16, 2024
`PostgreSQLSchemaManager::_getPortableTableColumnDefinition()` is
modified to call the private method `parseDefaultExpression()` on
default values for `JSON` field to correctly normalize doubled
single-quotes in json string value.

A test to cover this case is added.

Fixes doctrine#6357
reviewtypo3org pushed a commit to TYPO3/typo3 that referenced this issue Apr 16, 2024
Default value support for TEXT, JSON and BLOB fields
has been added with #103578 by implementing the use
of default value expression for MySQL. That required
to add custom normalization on data schema reads to
be comparable.

MySQL requires to use a single-quote to quote a single
quote in a value string, and due to the expression way
this needs to be properly decoded now in two steps:

* Revert escape sequences in the retrieved default value
* Unquote the unescaped retrieved default value

JSON field defaults shows a similar issue for double
quotes in the json value and can be fixed in the same
way.

Added test revealed, that Doctrine DBAL has an issue
with double single-quotes for PostgreSQL too. To fix
this the issue has been reported [1] and a pull-request
provided [2].

This change ensure correct unescaping and unquoting of
the retrieved column default value for TEXT, JSON and
BLOB column types for MySQL connections, enriched with
further tests.

The extended PostreSQLSchemaManager now clones method
`_getPortableTableColumnDefinition()` to incorporate
the bugfix directly until a fixed Doctrine DBAL version
has been released.

[1] doctrine/dbal#6357
[2] doctrine/dbal#6358

Resolves: #103610
Related: #103578
Releases: main
Change-Id: Icb39cdb8c87ae7907f84e5c38adcde4ef545ed1b
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/83745
Tested-by: Stefan Bürk <[email protected]>
Tested-by: Garvin Hicking <[email protected]>
Reviewed-by: Christian Kuhn <[email protected]>
Reviewed-by: Stefan Bürk <[email protected]>
Reviewed-by: Garvin Hicking <[email protected]>
Tested-by: core-ci <[email protected]>
Tested-by: Christian Kuhn <[email protected]>
TYPO3IncTeam pushed a commit to TYPO3-CMS/core that referenced this issue Apr 16, 2024
Default value support for TEXT, JSON and BLOB fields
has been added with #103578 by implementing the use
of default value expression for MySQL. That required
to add custom normalization on data schema reads to
be comparable.

MySQL requires to use a single-quote to quote a single
quote in a value string, and due to the expression way
this needs to be properly decoded now in two steps:

* Revert escape sequences in the retrieved default value
* Unquote the unescaped retrieved default value

JSON field defaults shows a similar issue for double
quotes in the json value and can be fixed in the same
way.

Added test revealed, that Doctrine DBAL has an issue
with double single-quotes for PostgreSQL too. To fix
this the issue has been reported [1] and a pull-request
provided [2].

This change ensure correct unescaping and unquoting of
the retrieved column default value for TEXT, JSON and
BLOB column types for MySQL connections, enriched with
further tests.

The extended PostreSQLSchemaManager now clones method
`_getPortableTableColumnDefinition()` to incorporate
the bugfix directly until a fixed Doctrine DBAL version
has been released.

[1] doctrine/dbal#6357
[2] doctrine/dbal#6358

Resolves: #103610
Related: #103578
Releases: main
Change-Id: Icb39cdb8c87ae7907f84e5c38adcde4ef545ed1b
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/83745
Tested-by: Stefan Bürk <[email protected]>
Tested-by: Garvin Hicking <[email protected]>
Reviewed-by: Christian Kuhn <[email protected]>
Reviewed-by: Stefan Bürk <[email protected]>
Reviewed-by: Garvin Hicking <[email protected]>
Tested-by: core-ci <[email protected]>
Tested-by: Christian Kuhn <[email protected]>
derrabus pushed a commit that referenced this issue Apr 16, 2024
<!-- Fill in the relevant information below to help triage your pull
request. -->

|      Q       |   A
|------------- | -----------
| Type         | bug
| Fixed issues | #6357

#### Summary

`PostgreSQLSchemaManager::_getPortableTableColumnDefinition()` is
modified to call the private method `parseDefaultExpression()` on
default values for `JSON` field to correctly normalize doubled
single-quotes in json string value.

A test to cover this case is added.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants