Php/Pdo/Mysql: Inserting into Mediumblob Stores Bad Data

PHP/PDO/MySQL: inserting into MEDIUMBLOB stores bad data

This seems like a bug to me: why should the charset of the connection have any effect on data for a binary column, particularly when it's been identified as binary to PDO itself with PARAM_LOB?

I do not think that this must be a bug. I can imagine that whenever the client talks with the server and says that the following command is in UTF-8 and the server needs it in Latin-1, then the query might get re-encoded prior parsing and execution. So this is an encoding issue for the transportation of the data. As the whole query prior parsing will get influenced by this re-encoding, the binary data for the BLOB column will get changed as well.

From the Mysql manual:

What character set should the server translate a statement to after receiving it?

For this, the server uses the character_set_connection and collation_connection system variables. It converts statements sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8). collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.

Or on the way back: Latin1 data from the store will get converted into UTF-8 because the client told the server that it prefers UTF-8 for the transportation.

The identifier for PDO itself you name looks like being something entirely different:

PDO::PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API. (Ref)

I'm no MySQL expert but I would explain it this way. Client and server need to negotiate which charsets they are using and I assume they do this for a reason.

Saving Files as blob in database ajax php pdo

As per PHP/PDO/MySQL: inserting into MEDIUMBLOB stores bad data, try using the following line to construct your PDO object:

$dbh = new PDO($dsn, $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES latin1 COLLATE latin1_general_ci"));

Explanation

I think there are, as Ben M notes in the linked question, two bad design decisions at work here.

There is this concept of a connection charset. The idea is that the SQL text can be in any charset and is then converted upon retrieval by the SQL server.

This does not work that well with binary data as it is not text and, thus, must not, by definition, be in any charset, but is still transferred using string literals.

This issue can be worked around by quoting BLOB data during transfer (either using the BASE64_* functions or by hex-escaping) and, indeed, that is what many people are doing.

The second design decision is in PDO/PHP: PDO does not do any charset conversion (it can’t, because strings in PHP are inherently charset-agnostic) so PHP is the only (or one of the few languages) where the choice of the SQL transfer charset is actually important because it needs to match the encoding the input strings are actually in.

In other languages, the transfer charset just needs to be expressive enough to encompass any characters that might be used in strings. In today’s world of emojis, this, most likely is only guaranteed by unicode charsets (utf-8 and the like). However, none of these are binary-safe (in that not every possible combination of bytes yields a valid string) so even if we could work around the PHP issue, we’d still be left with problem #1.

In an ideal world, SQL commands would always be in the ASCII charset during transfer and every string value would have a charset argument, of which “binary” could be a possible value, supplied with it. MySQL actually has such a construct for strings, which it calls an “introducer”. “_binary”, however, does not seem to be a valid value.

This charset information would then be used by the other end to convert the string value into its the native charset (either the column’s for client-to-server transfers or the programming language’s string charset for server-to-client transfers).

That way, the only thing that would have to be escaped in BLOB values would be the string delimiter (" or ').

PDO Blob Insert Missing Data

Finding out the issue, this post seems rather silly now, but incase anyone runs into this issue in the future...

There's an error with old versions of PHPMyAdmin where it doesn't correctly show or download data in blob columns.

Working around 1 MB max_allowed_packet when reading MEDIUMBLOB in PDO MySQL

I worked around this by making a loop that uses MySQL's SUBSTRING function to read smaller chunks of the value in a loop, where each chunk is smaller than a packet.

<?php
// [connection setup omitted]
$stat_stmt = $db->prepare("
SELECT `cache_id`, LENGTH(`value`) FROM `cache_items`
WHERE `name` = :n AND `expires` > CURRENT_TIMESTAMP
ORDER BY `cache_id` DESC LIMIT 1
");
$read_stmt = $db->prepare("
SELECT SUBSTRING(`value` FROM :start + 1 FOR 250000)
FROM `cache_items`
WHERE `cache_id` = :id
");

// Find the ID and length of the cache entry
$stat_stmt->execute($stat_stmt, [':n'=>$name]);
$files = $stat_stmt->fetchAll(PDO::FETCH_NUM);
if (!$files) {
    exit;
}
list($cache_id, $length) = $files[0];

// Read in a loop to work around servers with small MySQL max_allowed_packet
// as well as the fact that PDO::PARAM_LOB on MySQL produces a string instead
// of a stream
// https://bugs.php.net/bug.php?id=40913
$length_so_far = 0;
$body = [];
while ($length_so_far < $length) {
    $read_stmt->execute([':id'=>$cache_id, ':start'=>$length_so_far]);
    $piece = $read_stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    if (!$piece) {
        exit;
    }
    $piece = $piece[0];
    if (strlen($piece) < 1) {
        exit;
    }
    $length_so_far += strlen($piece);
    $body[] = $piece;
}
echo implode('', $body);

BLOB data fails to be written to a database

I got it working by using send_long_data method.
(in these examples I have removed some irrelevant columns for the example).

Option 1: Use send_long_data method for passing binary data

The benefit of using b according to https://www.php.net/manual/en/mysqli-stmt.bind-param.php:

Note:

If data size of a variable exceeds max. allowed packet size
(max_allowed_packet), you have to specify b in types and use
mysqli_stmt_send_long_data() to send the data in packets.

//...

if(isset($_POST['file_one_submit'])){
    $post = 'file_one_input';

    $id = 1;
    $file = NULL;

    $stmt = $conn -> prepare("REPLACE INTO `images` (id, file) VALUES(?, ?);");
    $stmt->bind_param("ib", $id, $file);
    
    //send_long_data(int $param_num, string $data): bool
    $stmt->send_long_data(1, file_get_contents($_FILES[$post]['tmp_name'])); 
    $stmt->execute();
}
?>

<form action="file.php" method="post" enctype="multipart/form-data">
    <input type="file" name="file_one_input" />
    <input type="submit" name="file_one_submit" value="Upload" />
</form>

Option 2: Change b to s

I think it is better to use b, but if I changed b to s (string) this also seems to be working:

//...

if(isset($_POST['file_one_submit'])){
    $post = 'file_one_input';

    $id = 1;
    $file = file_get_contents($_FILES[$post]['tmp_name']);

    $stmt = $conn -> prepare("REPLACE INTO `images` (id, file) VALUES(?, ?);");
    $stmt->bind_param("is", $id, $file);
    $stmt->execute();
}
?>

<form action="file.php" method="post" enctype="multipart/form-data">
    <input type="file" name="file_one_input" />
    <input type="submit" name="file_one_submit" value="Upload" />
</form>

Inserting and retrieving array data into and from MySQL using PHP pdo

Since you have set the cms as multiple select form

<SELECT NAME = "cms[]" multiple>
<option></option>
<option>#1</option>
<option>#2</option>
<option>#3</option>
<option>#4</option>
</SELECT>

The value of $_POST['cms'] will be an array containing the selected values.

You can do:

1) Depending on the mysql You are running, You can setup the "cms" column to JSON type and store the value in JSON format

2) You can store the value as text and do manipulations after reading the values. (Not recommended).

$stmt->bindParam(':cms', json_encode($_POST['cms']));

3) You can create a new one to many table, that will contain update_num and cms. For every chosen value, make a new insert in that table

---

Related Topics