|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2009-05-15 12:16 UTC] aaa_whoknows_me at list dot ru
Description:
------------
PHP >= 5.3.0 sends user name to mysql server in an incorrect encoding in some cases.
-----------------------
In this case PHP uses UTF-8 as its default encoding. PHP sends user name in mysql server default encoding. But in some cases mysql uses different encodings for data storing and sql queries. Consider a following situation:
my.ini
[mysql]
default-character-set=utf8
[mysqld]
default-character-set=ucs2
So, 'user' in UTF-8 == '????' in UTF-16BE
Reproduce code:
---------------
$link = mysql_connect('user', 'password', 'database');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
---------------
try {
$dbh = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');
foreach($dbh->query('SELECT id FROM users;') as $row) {
print_r($row);
}
$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
}
---------------
$mysqli = new mysqli('localhost', 'user', 'password', 'database');
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT id FROM users;")) {
printf("Select returned %d rows.\n", $result->num_rows);
/* free result set */
$result->close();
}
$mysqli->close();
Expected result:
----------------
A successful connection to the mysql database.
Actual result:
--------------
Access denied for user '????'@'localhost' (using password: YES)
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||
Copyright © 2001-2025 The PHP GroupAll rights reserved. |
Last updated: Thu Nov 06 11:00:01 2025 UTC |
mysqlnd (5.3.0) doesn't read the my.ini/my.cnf at all, thus also not the [mysql] section. Yes, mysqlnd set the charset of the connection to be the charset of the server sent during the handshake. In the greeting packet the server says which its default charset is. The server interprets the username in the the charset sent by the client. Libmysql doesn't look at what the server advertises but uses its own charset, sometimes pulled from the config, sometimes what is set through mysql_options - depends how used. You can overcome mysql's behavior by doing something like $conn = new mysqli(); // no params, no connection $conn->options(MYSQLI_SET_CHARSET_NAME, "yourcharset"); $conn->connect("host", "user", "pass"...); HTH, Andrey>$conn = new mysqli(); // no params, no connection >$conn->options(MYSQLI_SET_CHARSET_NAME, "yourcharset"); >$conn->connect("host", "user", "pass"...); Unfortunatly, it didn't help. I tried to use utf8, utf-8, ucs2, utf16, utf-16 and I sent username in all these charsets in all possible combinations but it didn't work anyway.> $conn = new mysqli(); // no params, no connection $conn = mysqli_init(); > $conn->connect("host", "user", "pass"...); $conn->real_connect("host", "user", "pass"...); Regards, Carsten>$conn = mysqli_init(); >$conn->options(MYSQLI_SET_CHARSET_NAME, "utf8"); >$conn->real_connect($db_host,$db_user,$db_pass,$db_name); How can I do the same actions with PDO? $pdo = new PDO( 'mysql:host=hostname;dbname=defaultDbName', 'username', 'password', $options ); What options should I use to do the same thing as with mysqli?