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

by

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.

More Related Contents:

  1. Insert form in MySQL with PDO [duplicate]
  2. Undefined index $_POST
  3. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  4. Getting raw SQL query string from PDO prepared statements
  5. How do I set ORDER BY params using prepared PDO statement?
  6. “Invalid parameter number: parameter was not defined” Inserting data
  7. How can I store and retrieve images from a MySQL database using PHP?
  8. Having issue with matching rows in the database using PDO
  9. Insert Blobs in MySql databases with php
  10. How can I properly use a PDO object for a parameterized SELECT query
  11. How do I return integer and numeric columns from MySQL as integers and numerics in PHP?
  12. PDO with INSERT INTO through prepared statements [closed]
  13. PDO::fetchAll vs. PDO::fetch in a loop
  14. PHP Connection failed: SQLSTATE[HY000] [2002] Connection refused
  15. displaying an image stored in a mysql blob
  16. Real escape string and PDO [duplicate]
  17. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  18. PDO Connection Test
  19. PHP PDO vs normal mysql_connect
  20. PHP PDO and MySQLi [duplicate]
  21. Php : Convert a blob into an image file
  22. PDO fetch one column from table into 1-dimensional array
  23. php:Store image into Mysql blob, Good or bad?
  24. Getting a PHP PDO connection from a mysql_connect()?
  25. PDO error: SQLSTATE[HY000]: General error: 2031
  26. Having issue with matching rows in the database
  27. Use of PDO in classes [duplicate]
  28. Check if row exists in the database before inserting
  29. PHP/SQL Insert Error when using Named Placeholders
  30. Is a BLOB converted using the current/default charset in MySQL?

Leave a Comment