• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1419
  • Last Modified:

How to save 'binary' data in MySQL with PHP?

Hi all!

   For a project I must save some binary data in the database. I start with some hexadecimal values like

A5 FF B3 23 DF

etc. This can be anything and the string can get quite long. From this string I take 2 chars every time and translate this hex code into a character. So I get a long string of strange looking chars (mostly displayed as squares on the screen). This is what I want to save into the MySQL database.

So I made a nice update query, nothing fancy... just UPDATE xyz SET abc = [BINARY STRING]
But the thing doesn't seem to work... I don't get an error message and when I echo the query and copy/paste it in MySQL administrator it seems to work fine (although the data is incorrect, because it's trying to insert the squares and all that stuff, not the actual char/code).

I tried escaping it, I've tried base64 stuff etc... the thing just won't update.
Is there anything I could have missed? Are there special ways to insert this kind of data into a MySQL database with PHP?

If more information is needed, please ask; I'll provide ;).

2 Solutions
What is your table structure? You can store binary data in a BLOB - are you trying to put this in a VARCHAR? Also it would be helpful to have the error message you are receiving.

Are you using phpMyAdmin? If you you can change the table field that you store binary data in to a BLOB by selecting the table, clicking the "structure" tab, selecting the check box next to the appropriate field, and clicking the pencil icon to modify it. Then just change the "Type" drop down to BLOB.

Hopefully I didn't misunderstand you, cheers.
2 ways to do it.

For TEXT fields (max 65535 bytes), LONGTEXT fields (max 4GBytes) you must convert to base64 in PHP

$binary_data = file_get_contents($file);
$string_data = base64_encode($binary_data);

and save the contents to the column
$sql = "INSERT INTO table (text_field) VALUES('$string_data')";

For BLOB fields (MAx 65535 bytes) or LONGBLOB (Max 4 GB) just put the contents directly to the SQL
$sql ="INSERT INTO table (text_field) VALUES('".$binary_data ."')";

Have some issues about put binary content in a SQL statement. Sometimes, the binary contents simulate the char QUOTE.
Its better don't use mysal_query function. If you use some Abstraction Layer Like PDO or ADODB, it will escape correctly the content to prevent this kind of trouble.

NOTE FROM the MySQL manual: The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the max_allowed_packet variable, but you must do so for both the server and your client program
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now