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

Posted on 2008-11-05
Last Modified: 2013-12-13
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 ;).

Question by:HidDS
    LVL 9

    Accepted Solution

    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.
    LVL 11

    Assisted Solution

    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

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now