Bug #70259

DBAL - SQLParser mssql - escape '

Added by Guy Couronné about 4 years ago. Updated about 3 years ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2015-10-01
Due date:
% Done:

0%

TYPO3 Version:
6.2
PHP Version:
5.4
Tags:
Complexity:
medium
Is Regression:
No
Sprint Focus:

Description

TYPO3 : 6.2.14

Hi,

I'm using DBAL in a scheduler task for connecting to a foreign database in MSSQL.

In \TYPO3\CMS\Dbal\Database\SqlParser, there is a problem in method getValueInQuotesMssql.

For exemple, this is my query :

$databaseConnectionDbal->exec_SELECTquery('*', 'EmploiPerso', 'Nom = 'D''Amour' AND Prenom = 'Denis'', '', '', '');

For the where_clause, the first time it goes into getValueInQuotesMssql, this is the print_r :

Array ( [__METHOD__] => TYPO3\CMS\Dbal\Database\SqlParser::getValueInQuotesMssql [__LINE__] => 73 [parseString] => 'D''Amours' AND Prenom = 'Denis' [quote] => ' )

It's good for the moment.
After the replacement of ' by \ (line 82) and the explode by quote into $parts, it shows this :
Array ( [__METHOD__] => TYPO3\CMS\Dbal\Database\SqlParser::getValueInQuotesMssql [__LINE__] => 99 [parseString] => 'D\'Amours' AND Prenom = 'Denis' [quote] => ',  [parts] => Array ( [0] => D\ [1] => Amours [2] => AND Prenom = [3] => Denis [4] => ) )

It's still good.
However, the first result buffer is :
D\'Amours
with strippedSlahes results of
D'Amours

This cause an error (debug DB in BE):

0     SELECT * FROM "EmploiPerso" WHERE "Nom" = 'D'Amours' AND "Prenom" = 'Denis'
1     SQLState: 42000
Error Code: 102
Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'Amours'.
SQLState: 42000
Error Code: 105
Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Unclosed quotation mark after the character string ''

To resolve this, I modify :

$buffer .= $quote;

by

$buffer .= $quote . $quote;

And now it's working.

History

#1 Updated by Morton Jonuschat about 3 years ago

  • Category changed from 999 to Database API (Doctrine DBAL)
  • Status changed from New to Closed

Solved on TYPO3 8/master by the migration to Doctrine DBAL.

Also available in: Atom PDF