Saving self-generated binary data in MySQL?

[ArticSun]

Hi all,

 

  I've got this application that saves certain data in one long hex. string (say 500 chars). For certain reasons, I need to save this data binary in the MySQL database. So for every hex code I use chr() to translate it.

 

After this I got a string which, when echood, looks like complete garbage, with a lot of unidentified chars etc. When I put this string in an update query however, it just won't execute. I tried everything from base64 encoding till straight forward escaping, nothing works... no error message either (using a MySQL db over ODBC). When I echo the query, copy it, and execute it in for example phpMyAdmin, it works. However he will then just insert the strange chars on screen in stead of the actual value.

 

So... the query is good. The connection is good (other queries work)... so why won't this update query work like intended?

 

If more information is required, please ask. It's all a bit fuzzy to me too .

 

Thanks!

 

P.s. sorry for any bad English...

[corbin]

You could just insert the hex string straight into the DB:

 

 

INSERT INTO some_table (some_blob_column) VALUES (0x01);

[ArticSun]

Hi corbin,

 

  Thanks for your reply, unfortunately it doesn't work... when I do something like:

 

UPDATE table SET (some_blob_field) = '0F2B4FFF6B' WHERE id = 16;

 

It just saves the hex-string as is, and not as binary data. I've also tried to add '0x' in front of each hex-pair, but this is also just saved as a string in the blob field.

 

Could there be any other way? Should I use double qoutes (") in stead of single ('), of might there be something else wrong?

 

Hope someone can help me out, I'm really stuck on this one.

 

Thanks!

[fenway]

I was under the impression that addslashes() on binary data would work.

[ArticSun]

When I use addslashes I get a query like:

 

UPDATE `exercise_programs` SET Exercises = "\00\0p\0pp ÐàpP\0\0\0\0\0`ÿÿÿð\0Ð\0\0\0\0\0\0 \0\0\0°\0 \0\0\0\0 \00\0\0\0à\0@\0\0\0à\0`\0\0\0@\0€\0\0€\0\0\0À\0À\0\0\0\0@\0Ð\0\00\0à\0\0° \0\0\0\0@0\0\0\0\0P\0\0\0@\0P\0\0\0\0\0\0\0\0\0\0À\0 \0\0\0\0 \0`\0\0\0\0À\0\0\0°\0\0\0\0@\0 \0\0\0\0 \00\0\0\0\0\0\0`\0\0\0@\0\0\0À\0\0\0À\00\0 \0\0\0à\0P\0\0\0\0\0\0\0\0\0`\0\0\0\0€\0 \00\0\0\0@\0°\0\0\0p\0\0\0\0\0@\0 \0\0\0€\00\0\0\0\0ð\0P\0\0\0\0 \0°\0\0\0 ðPP \0\0P\0 \0\0\0\0\0\0\0\0€\0\00€P0@\0\0 P00\0\0\00\0\0\0\0`\0p\0\0\0\0\0\0\0\0\0P\0@\0\0\0À\0`\0\0\0\0À\0€\0\0€ \0\0\0\0@0\0\0\0\0@\0\0\0\0`\0€\0\0\0\0\0\0\0\0\0°\0@\0\0\0À\0`\0\0\0\0À\0p\0\0\0€\0€\0\0€ \0\0\0\0@0\0\0\0\0@\0\0\0\0`\0p\0\0\0\0\0\0\0\0\0°\0@\0\0\0À\0`\0\0\0\0À\0€\0\0° \0\0\0\0@0\0\0\0\0@ÀPp\0P€@Pà0ðà\0\0@\0\0\0\0@\0P\0\0\0\0\0\0\0\0\0\0À\0 \0\0\0\0 \0`\0\0\0\0À\0\0\0°Àp\0`P@0\0\0\0\0\00\0@\0 \0\0\0Ð\0p\0\0\0@\0\0\0\0\0\0\0\0\0\0\0Àðð\0 à@\00\0\0\0\0Ð\00\0 \0\0\0\0\0\0Ð\0\0°\0à\0\0° @ðÐàÀ\0\0`\00\0\0\0\0\0@\0\0\0\0\0\00\0\0\0@\0`\0\0\0\0À\0€\0\0€\0\0\0\0\0@\0\0\0\0\0\00\0\0\0@\0`\0\0\0\0À\0€\0\0€\0\0\0\0\0@\0\0\0\0\0\00\0\0\0@\0`\0\0\0\0À\0€\0\0" WHERE `Program_id` = 114 AND `Person_id` = 986

 

(which is what the query echos like... ).

Still it saves a completely different string in the blob field comparing to the one I started with. This while I only changed one bit (exactly) in the data.

 

I'm using chr() on every 2 chars in the hex-string (1 hex character) to translate it to binary data, is this how you should do this? Or should I use chr() on the whole string, or not at all?

 

Thanks

 

[corbin]

Hi corbin,

 

   Thanks for your reply, unfortunately it doesn't work... when I do something like:

 

UPDATE table SET (some_blob_field) = '0F2B4FFF6B' WHERE id = 16;

 

It just saves the hex-string as is, and not as binary data. I've also tried to add '0x' in front of each hex-pair, but this is also just saved as a string in the blob field.

 

Could there be any other way? Should I use double qoutes (") in stead of single ('), of might there be something else wrong?

 

Hope someone can help me out, I'm really stuck on this one.

 

Thanks!

 

 

You don't use quotes.

 

 

UPDATE table SET (some_blob_field) = 0F2B4FFF6B WHERE id = 16;

 

 

Binary is literal data.  Just like you wouldn't escape a number, you wouldn't escape hex-representation of binary.