MySQL export to CSV with blob field

Question

I have an issue with exporting BLOB to a csv file using php. Obviously blob is not a normal field, but it is on occasion used. But here's what I need, I need this script to work by taking a table array and export each row in csv format. Blob is screwing this up. I've tried to base64_encode the blob, but I believe that would export the blob incorrectly.

Here is what I have so far:

function exportcsv($tables) {
    foreach ($tables as $k => $v) {
        $fh = fopen('sql/'.$v.'.csv', 'w');
        $sql = mysql_query("SELECT * FROM $v");
        while ($row = mysql_fetch_row($sql)) {
            $line = array();
            foreach ($row as $key => $v) {
                $line[] = base64_encode($v);
            }
            fputcsv($fh, $line, chr(9)); //tab delimiting
        }
        fclose($fh);
    }
}

Any help would be appreciated.

EDIT: Here is an image of the blob export WITHOUT base64_encode(). https://www.strongspace.com/shared/crypok1lxb

So, will base64 protect the format of blob when the need to reimport arises?

shouldn't you use base64_decode ? im not an expert on BLOBS but i am sure you don't want to encode it to see the real text if you follow what i mean.. — Dany Khalife, Nov 22 '11 at 02:06
can you perhaps show a sample of the output you have so that we can see what's wrong ? — Dany Khalife, Nov 22 '11 at 02:08
Hehe. Sorry, the blob is just binary (well, encrypted passwords). So it's really unreadable with any type of export anyway. But the goal to base64_encode() is to all the fputcsv() to work because the blob has all types of random characters that the blob fields would damage the export because it would contain \r \t and \n's. So encoding would prevent those odd issues, and it does prevent them. — frustratedtech, Nov 22 '11 at 02:11
okay i see but what do you mean when you say 'I believe that would export the blob incorrectly.' ? if you could specify more details as to what you're expecting from the code and what it's actually giving you, perhaps then i can help you out :) — Dany Khalife, Nov 22 '11 at 02:21
Thanks Dany. Addy an image to show what I'm talking about. That is without base64. So my biggest question is, will base64 encode any character correctly and decode it correctly? Cuz blob is a mixture of anything and everything. — frustratedtech, Nov 22 '11 at 02:27
in that case im not really sure, but the way i work passwords is generate a hash of the real password + a salt and then store the hash and the salt on DB (i usually have a different salt for each user for added security) then when i need to check a password i generate the hash of the input pwd + the store salt and i compare it to the hash in DB. so i hope a php expert joins in :) otherwise you can have a look at this : http://stackoverflow.com/questions/1289061/best-way-to-use-php-to-encrypt-and-decrypt — Dany Khalife, Nov 22 '11 at 02:46

score 3 · Accepted Answer · edited May 23 '17 at 12:11

The purpose of Base64 encoding is to make binary data survive transport through transport layers that are not 8-bit clean, so yes, you are on the right track with this.

What you really need to be worried about is that fputcsv() function. What are the dangerous items you need to check for?

Tabs, because this is your delimiter
New line characters, because it seems you are also parsing by line

According to this link, Base 64 encoding only spans A-Z, a-z, 0-9, + and /, which means you are safe and the new line in the image you posted is probably an artifact from word wrapping in the IDE you are using. Now all that you have to remember is that when you want to import this data back into the DB, don't expect to just use mysqldump or mysql < to pipe it in. You're going to have to create another php function that uses base64_decode to get the data back into it's original state.

You should also be aware that you are currently encoding all of the fields, not just the blob data. Base64 encoding takes up some ~33% more space as an FYI so you may want to define your tables with some bit indicating if the field is a blod and you want to enable B64 encoding on it.

Thank you for this information. Upon various tests it appears the information is exact. So using base64 to encode a blob then decode it, works as it should. Plus this helps get past the csv issues. Thanks! — frustratedtech, Nov 23 '11 at 04:14

MySQL export to CSV with blob field

1 Answers1