php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #75736 PDO's constructor parameter for DSN prevents setting collation
Submitted: 2017-12-26 18:37 UTC Modified: 2021-04-09 14:13 UTC
Votes:3
Avg. Score:3.0 ± 0.0
Reproduced:2 of 2 (100.0%)
Same Version:1 (50.0%)
Same OS:1 (50.0%)
From: eagle at compu-lex dot de Assigned:
Status: Wont fix Package: PDO MySQL
PHP Version: 5.6.32 OS: Debian 8 (Jessie)
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If this is not your bug, you can add a comment by following this link.
If this is your bug, but you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: eagle at compu-lex dot de
New email:
PHP Version: OS:

 

 [2017-12-26 18:37 UTC] eagle at compu-lex dot de
Description:
------------
PHP's MySQL PDO driver can't set collations without an init command, although the "charset=" parameter was introduced in PHP 5.3.6 to enable specifying the character set without the need for an init command. (See https://secure.php.net/manual/en/ref.pdo-mysql.connection.php)

See Anthony Rutledge's answer over at https://stackoverflow.com/questions/25807202/how-to-specify-collation-with-pdo-without-set-names/32553447#32553447 for further details regarding this issue.

Test script:
---------------
<?php
$db = new PDO('mysql:host=HostnameOrIpHere;dbname=DbNameHere;charset=utf8',
              'DbUserHere',
              'DbPasswordHere',
              array(PDO::ATTR_EMULATE_PREPARES => false,
                    PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION));
echo $db->query("SELECT COLLATION('foo')")->fetch(PDO::FETCH_NUM)[0];
?>

Expected result:
----------------
PDO's MySQL driver should allow for setting the collation explicitly, i. e. via proper value for the "charset" parameter, e. g. changing test script's 2nd line to

$db = new PDO('mysql:host=HostnameOrIpHere;dbname=DbNameHere;charset=utf8_unicode_ci',

or by adding another parameter, e. g. "collation", for this should result in this output: utf8_general_ci

But since MySQL always uses "charset_collation" (optionally combined with "_ci" for case-insensitive comparison), I suggest only modifying which would be backwards compatible: utf8 -> works as before, utf8_unicode[_ci] -> utf8 for charset and unicode[_ci] for collation, if present.

Actual result:
--------------
Test script's expected output, utf8_general_ci

Workaround: using PDO::MYSQL_ATTR_INIT_COMMAND with a value of "SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'" (including quotes) as a third array element in the test script above, results in utf8_unicode_ci - but this should be considered as a temporary workaround as PHP introduced the "charset=" parameter to eliminate the need for setting it via init command.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-04-09 14:13 UTC] dharman@php.net
-Status: Open +Status: Wont fix
 [2021-04-09 14:13 UTC] dharman@php.net
I'm sorry but it is highly unlikely we will ever implement this feature request. If you want, you can submit a PR yourself. Reasons, why this is not going to be implemented in PHP anytime soon, are:
1. MySQL C API doesn't provide any function to set the connection collation to anything other than the default charset collation. 
2. The benefit of such a feature would be extremely tiny. The connection collation is only used in a comparison of string literals with themselves. This is a very narrow use case, for which exists a very simple workaround. Use 'SET NAMES'. 
3. This is not the responsibility of the client API. Setting charset is required for the data transfer and escaping to work properly. Collation isn't required by the client API for anything. Introducing such functionality would unnecessarily complicate client API or PDO. I do not see any reason to do so.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Thu Jul 29 03:01:28 2021 UTC