Php/mysql display BLOB field data?

**GigoloShawn** · 08-28-2008, 09:45 AM

A blob field is usually considered to be binary data.

You're gonna want to escape that.

If you want to display text, you should use TEXT or TINYTEXT and not BLOB for storage.

**acctman** · 08-28-2008, 10:43 AM

Originally posted by GigoloShawn

A blob field is usually considered to be binary data.

You're gonna want to escape that.

If you want to display text, you should use TEXT or TINYTEXT and not BLOB for storage.

unfortunately this script was coded by someone else, i'm trying to clean up all the problem areas that I find. I need to decode the binary unserialize(base64_decode($result)) didnt work... any suggestions? Once I figure out how to decode and retrieve the data I've setup 4 separate fields in which I plan to populate all current user data too and eliminate the BLOB field

this code below just shows me the encoded info, i need to decode it

$res = mysql_query("SELECT m_addtn FROM s_viz WHERE m_id = '39'");
$result = mysql_fetch_array($res);

echo var_dump($result);

**GigoloShawn** · 08-28-2008, 10:49 AM

If it was serialized, it'd likely be base64 encoded afterwards; why it was stored as a blob well, that's kind of dumb. Try print_r($result) to see what's in your array.

Or, you can always:

PHP Code:

foreach ($result as $arg=>$val) {
  echo "$arg is $val<br>";
}

How do you know its base64? Is it terminating with a few =? If not, it may not be base64.

**BigBen** · 08-28-2008, 10:57 AM

See how the insert is done and what kind of encoding is being used.

**Tempest** · 08-28-2008, 11:47 AM

What bigben said... sometimes you'll compress text data and store it in a blob

**HomerSimpson** · 08-28-2008, 11:56 AM

what are you saving in database as blob.
If you are saving whole files (images etc...) in database that's bad so try to avoid that any way you can.

**acctman** · 08-28-2008, 11:57 AM

Originally posted by GigoloShawn

If it was serialized, it'd likely be base64 encoded afterwards; why it was stored as a blob well, that's kind of dumb. Try print_r($result) to see what's in your array.

Or, you can always:

PHP Code:

foreach ($result as $arg=>$val) {
  echo "$arg is $val<br>";
}

How do you know its base64? Is it terminating with a few =? If not, it may not be base64.

for displaying the data in the script it has <? $en['quests'] = unserialize(base64_decode($en['mm_addtn'])); ?>

with print or echo
Array ( [0] => YTo0OntpOjM7czo4OiJCaXNleHVhbCI7aTo2O3M6ODoiSW52b2 x2ZWQiO2k6NDtzOjIxOiJJIEFEREVEIFNPTUUgTkVXIFBJQ1Mi O2k6MTtzOjI5OiJUSEVZIFIgSU4gVEhFIEVYUExJQ0lUUyA0IE 5PVyI7fQ== [m_addtn] => YTo0OntpOjM7czo4OiJCaXNleHVhbCI7aTo2O3M6ODoiSW52b2 x2ZWQiO2k6NDtzOjIxOiJJIEFEREVEIFNPTUUgTkVXIFBJQ1Mi O2k6MTtzOjI5OiJUSEVZIFIgSU4gVEhFIEVYUExJQ0lUUyA0IE 5PVyI7fQ== )

with foreach statement
0 is YTo0OntpOjM7czo4OiJCaXNleHVhbCI7aTo2O3M6ODoiSW52b2 x2ZWQiO2k6NDtzOjIxOiJJIEFEREVEIFNPTUUgTkVXIFBJQ1Mi O2k6MTtzOjI5OiJUSEVZIFIgSU4gVEhFIEVYUExJQ0lUUyA0IE 5PVyI7fQ==
m_addtn is YTo0OntpOjM7czo4OiJCaXNleHVhbCI7aTo2O3M6ODoiSW52b2 x2ZWQiO2k6NDtzOjIxOiJJIEFEREVEIFNPTUUgTkVXIFBJQ1Mi O2k6MTtzOjI5OiJUSEVZIFIgSU4gVEhFIEVYUExJQ0lUUyA0IE 5PVyI7fQ==

**acctman** · 08-28-2008, 12:01 PM

Originally posted by HomerSimpson

what are you saving in database as blob.
If you are saving whole files (images etc...) in database that's bad so try to avoid that any way you can.

the m_addtn BLOB field is all test/data... the original coder did it that way. I'm just trying to clean things up. In the end I'll insert the text data in 4 separate fields the way it should of been done originally. First task is to decode the data from the BLOB and retrieve it, then insert into m_status, m_ori, m_turn1 and m_turn2

**Varius** · 08-28-2008, 12:18 PM

Originally posted by BigBen

See how the insert is done and what kind of encoding is being used.

Yup, this would be how to solve it.

Just find what's done to the text before it's inserted, then reverse that when it comes out. A BLOB field itself doesn't do its own encrypting or anything.

On the small chance there is nothing being done to the data before it's put into the database, check for any Triggers in MySQL as there could be one that ON INSERT or ON UPDATE encrypts the value in that field.

**acctman** · 08-28-2008, 12:57 PM

Originally posted by Varius

Yup, this would be how to solve it.

Just find what's done to the text before it's inserted, then reverse that when it comes out. A BLOB field itself doesn't do its own encrypting or anything.

On the small chance there is nothing being done to the data before it's put into the database, check for any Triggers in MySQL as there could be one that ON INSERT or ON UPDATE encrypts the value in that field.

this is what it's doing when it processes a update/insert into that field.

$addtn = base64_encode(serialize($_POST['edit_add']));
$q = 'm_addtn="'.$addtn.'"'.$q;

i tried this piece of code (below) but just received a T_String error
print_r unserialize(base64_decode($results));

**Varius** · 08-28-2008, 01:04 PM

Originally posted by acctman

this is what it's doing when it processes a update/insert into that field.

$addtn = base64_encode(serialize($_POST['edit_add']));
$q = 'm_addtn="'.$addtn.'"'.$q;

i tried this piece of code (below) but just received a T_String error
print_r unserialize(base64_decode($results));

is $results there the array though (recordset returned)? If so that's your error, you need to unserialize(base64-decode()) on the actual string (data of that one field), not the whole returned result (recordset).

if you want paste the few lines of code you have above that where it shows the fetching of the row, etc...

**acctman** · 08-28-2008, 01:44 PM

Originally posted by Varius

is $results there the array though (recordset returned)? If so that's your error, you need to unserialize(base64-decode()) on the actual string (data of that one field), not the whole returned result (recordset).

if you want paste the few lines of code you have above that where it shows the fetching of the row, etc...

i just tried the code below, I'm getting a blank page this time around. there is two array 0 and m_addtn

$res = mysql_query("SELECT m_addtn FROM s_viz WHERE m_id = '39'");
$result = mysql_fetch_array($res);

$decode = unserialize(base64_decode($results['m_addtn']));
print_r($decode);

**GigoloShawn** · 08-28-2008, 02:22 PM

Originally posted by acctman

i just tried the code below, I'm getting a blank page this time around. there is two array 0 and m_addtn

$res = mysql_query("SELECT m_addtn FROM s_viz WHERE m_id = '39'");
$result = mysql_fetch_array($res);

$decode = unserialize(base64_decode($results['m_addtn']));
print_r($decode);

Life happened. That is base64, allright.

Looking at the foreach, it said it was a sub-branch [0], so, try:

PHP Code:

$decode = unserialize(base64_decode($results['m_addtn']['0']));
print_r($decode);

**GigoloShawn** · 08-28-2008, 02:29 PM

Dammit, I waited too long to edit:

I just saw your very last post.

You're setting $result, and resting $results; otherwise, you're on the right track. No idea why you'd be doing mysql_array("...",MYSQL_BOTH), but, whatever.

**jimbona** · 08-28-2008, 02:42 PM

try doing a redecode after youve dumped the array

PHP Code:

foreach ($result as $arg=>$val) { 
  echo "$arg is ".base64_decode($val)."<br>\n\n"; 
}

**Varius** · 08-28-2008, 04:18 PM

as GigoloShawn said above, you are using the variable $results when it is $result heh.

This line should produce what you want:

echo unserialize(base64_decode($result[0][0]));

(feel free to use the field name instead of that second 0 though, to make your code easier to read):

echo unserialize(base64_decode($result[0]['m_addtn']));

**acctman** · 08-28-2008, 04:21 PM

Originally posted by GigoloShawn

Dammit, I waited too long to edit:

I just saw your very last post.

You're setting $result, and resting $results; otherwise, you're on the right track. No idea why you'd be doing mysql_array("...",MYSQL_BOTH), but, whatever.

thanks for spotting my error, i tried out MYSQL_BOTH works pretty good.

thanks everyone for helping me out

**d-null** · 08-28-2008, 11:03 PM

bumping some good expertise

**mrkris** · 08-28-2008, 11:44 PM

For future reference,

Code:

<pre><?php print_r($data); ?></pre>

Having it aligned in browser will help you follow nested data structures much easier.

Php/mysql display BLOB field data?

Php/mysql display BLOB field data?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment