Is a BLOB converted using the current/default charset in MySQL?

by

Short Answer:

Simply delete or comment out line below, and it will always work, no matter which database encoding is really in use (utf8, latin1, etc):

$pdo->exec('SET CHARACTER SET utf8');

Long Answer:

This is not PDO bug, this is MySQL bug.

When actual database encoding is latin1, but you use:

SET CHARACTER SET utf8

(or vice versa: actual is utf8, but you use latin1 – important part is that it is different), then, as far as I can tell, MySQL will try to perform charset conversion for all traffic between client and server (even for BLOB!).

If you DO NOT use SET CHARACTER SET statement, from what I see for scripts (PHP/PDO or Perl/DBI) connection charset by default is set to be the database charset, and in that case no implicit conversion takes place.

Obviously, this automatic conversion is what kills BLOBs, which do not want any conversion to happen.

I have tested this on both PHP/PDO and Perl/DBI, and issue is easily reproducible: both will fail if using database with latin1 encoding and using SET CHARACTER SET utf8 (or vice versa).

If you want to be fully UTF8 compatible, you should change encoding of your database using:

ALTER DATABASE mydb CHARSET utf8;

With this, everything will be using UTF8, and BLOBs will also work fine.

Minimal file that causes this corruption problem is blob.bin with single byte 0xFF. On Linux, you can create this test file using printf command:

printf "0xFF" > blob.bin

Now, test scripts that reproduce the problem:

PHP test code:

<?php
$dbh = new PDO("mysql:host=127.0.0.1;dbname=test");
# If database encoding is NOT utf8, uncomment to break it:
# $dbh->exec("SET CHARACTER SET utf8");

$blob1 = file_get_contents("blob.bin");
$sth = $dbh->prepare(
    "INSERT INTO pdo_blob (the_blob) VALUES(:the_blob)"
);
$sth->bindParam(":the_blob", $blob1, PDO::PARAM_LOB);
$sth->execute();

$sth = $dbh->prepare(
    "SELECT the_blob FROM pdo_blob ORDER BY id DESC LIMIT 1"
);
$sth->execute();

$blob2 = null;
$sth->bindColumn(1, $blob2, PDO::PARAM_LOB);
$sth->fetch();

if ($blob1 == $blob2) {
    echo "Equal\n";
} else {
    echo "Not equal\n";
    $arr1 = str_split($blob1);
    $arr2 = str_split($blob2);
    $i=0;
    for ($i=0; $i<count($arr1); $i++) {
        if ($arr1[$i] != $arr2[$i]) {
            echo "First diff: " . dechex(ord($arr1[$i])) . " != "
                                . dechex(ord($arr2[$i])) . "\n";
            break;
        }
    }
}
?>

Perl test code:

#!/usr/bin/perl -w

use strict;
use DBI qw(:sql_types);

my $dbh = DBI->connect("dbi:mysql:host=127.0.0.1;dbname=test");
# If database encoding is NOT utf8, uncomment to break it:
# $dbh->do("SET CHARACTER SET utf8");
open FILE, "blob.bin";
binmode FILE;
read(FILE, my $blob1, 100000000);
close FILE;
my $sth = $dbh->prepare(
    "INSERT INTO pdo_blob (the_blob) VALUES(?)"
);
$sth->bind_param(1, $blob1, SQL_BLOB);
$sth->execute();
my ($blob2) = $dbh->selectrow_array(
    "SELECT the_blob FROM pdo_blob ORDER BY id DESC LIMIT 1"
);
print ($blob1 eq $blob2 ? "Equal" : "Not equal") , "\n";

More Related Contents:

  1. Is “SET CHARACTER SET utf8” necessary?
  2. PHP / PDO echo data from database into a table
  3. PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)
  4. How to prevent duplicate usernames when people register?
  5. Error when preparing a multiple insert query
  6. Fixing broken UTF-8 encoding
  7. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  8. MySQL check if a table exists without throwing an exception
  9. pdo prepared statements with wildcards
  10. Storing and displaying unicode string (हिन्दी) using PHP and MySQL
  11. Insert multiple rows with PDO prepared statements
  12. PDO + MySQL and broken UTF-8 encoding
  13. Do SQL connections opened with PDO in PHP have to be closed
  14. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  15. php code to test pdo is available?
  16. PDO fetchAll group key-value pairs into assoc array
  17. What is the advantage of using try {} catch {} versus if {} else {}
  18. Alternative for mysql_num_rows using PDO
  19. ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it
  20. Unknown database error in PHP but it exists in PHPMyAdmin
  21. How do detect that transaction has already been started?
  22. Binding parameters for WHERE IN clause with PDO [duplicate]
  23. return one value from database with mysql php pdo
  24. Trying to access array offset on value of type bool
  25. return multiple response data in one response
  26. How to get a query error from prepare() in PDO PHP?
  27. How can I pass an array of PDO parameters yet still specify their types?
  28. PHP PDO how to run a multiple query request?
  29. PHP: mysql v mysqli v pdo [closed]
  30. The ultimate emoji encoding scheme

Leave a Comment