1

I have an Android application that takes a photo, converts the bitmap to Base64 and submits the Base64 string to a MySQL database (via PHP) to be stored as a longblob. This part works great! In fact, I can download the longblob from phpMyAdmin as a perfect Base64 string and easily convert to a JPEG photo.

The problem is my PHP code for getting the blob returns an empty string:

{
    "owner":"Unknown",
    "pet_name":"Unknown",
    "last_seen":"2019-04-09 11:17:19",
    "contact":"999-888-7654",
    "description":"rubber ducky, lotsa fun",
    ***"photo":""***,
    "location":"Some location"
}

The PHP getter:

function getReports() {
    $stmt = $this->con->prepare("SELECT owner, pet_name, last_seen, contact, description, photo, location FROM Pets");
    $stmt->execute();
    $stmt->bind_result($owner, $pet_name, $last_seen, $contact, $description, $photo, $location);

    $reports = array();

    while($stmt->fetch()) {
        $report  = array();
        $report['owner'] = $owner;
        $report['pet_name'] = $pet_name;
        $report['last_seen'] = $last_seen;
        $report['contact'] = $contact;
        $report['description'] = $description;
        $report['photo'] = $photo;
        $report['location'] = $location;

        array_push($reports, $report);
    }

    return $reports;
}

An interesting side note, if instead of the above code I use the below code I DO get the complete Base64 string, but with added escape () and newline (\n) characters all throughout:

//Select everything from table
$sql= "SELECT * FROM Pets";

//Confirm results
if($result = mysqli_query($con, $sql)) {
    //Results? Create array for results and array for data
    $resultArray = array();
    $tempArray = array();

    //Loop through results
    while($row=$result->fetch_object()) {
        // Add each result in results array
        $tempArray=$row;
        array_push($resultArray,$tempArray);
    }

    //Encode array to JSON and output results
    echo json_encode($resultArray);
}

I would like to find a way to fix the above PHP code. I'm thinking perhaps my string is too long for the $photo value? Any advice would be extremely appreciated.

Update: from Selecting Blob from MYSQL, getting null I did manage to the Base64 to output now instead of an empty string. however, I still have the problem of the escape and newline characters.

Any help here?

Achraf Almouloudi
  • 756
  • 10
  • 27
JohnD
  • 61
  • 6
  • Note: The [object-oriented interface to `mysqli`](https://www.php.net/manual/en/mysqli.quickstart.connections.php) is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface where missing a single `i` can cause trouble. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is largely an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Apr 11 '19 at 01:22
  • Updated question. I did figure out how to get the base64 to output instead of the empty "". But, it still contains the escape and newline chars. – JohnD Apr 11 '19 at 01:54
  • 1
    Putting images in a database is usually a bad plan to start with, it requires many layers of encoding and decoding to get it out of the database and send it over the wire, and base64 encoding only makes things worse. Why not raw? – tadman Apr 11 '19 at 02:07
  • 1
    `it still contains the escape and newline chars` - most likely it's because you put it in that form into the database in first place. – Marcin Orlowski Apr 11 '19 at 02:11
  • 1
    Base64 isn't for storage. You might want to read [Storing image in database directly or as base64 data?](https://stackoverflow.com/questions/9722603/storing-image-in-database-directly-or-as-base64-data) – Marcus Adams Apr 11 '19 at 16:50
  • Thanks Marcus. We're doing this as a POC, but the link you shared will be much helpful if we move forward. – JohnD Apr 17 '19 at 04:45

1 Answers1

0

I managed to get the original function to output the Base64 line by casting as such:

    $stmt = $this->con->prepare("SELECT owner, pet_name, last_seen, contact, description, CAST(photo as CHAR(1000000) CHARACTER SET utf8) as photo, location FROM Pets");

While this allowed for the Base64 string to be received it still included the unwanted characters. The unwanted characters were caused by JSON_ENCODE. Below is what I used to fix it.

Basically, 1. remove the added chars and 2. tell JSON_ENCODE to not print the escape chars with JSON_UNESCAPED_SLASHES.

For the function getReports() 

function getReports() {
    $stmt = $this->con->prepare("SELECT owner, pet_name, last_seen, contact, description, CAST(photo as CHAR(1000000) CHARACTER SET utf8) as photo, location FROM Pets");
    $stmt->execute();
    $stmt->bind_result($owner, $pet_name, $last_seen, $contact, $description, $photo, $location);

    $reports = array();

    while($stmt->fetch()) {
        $report  = array();
        $report['owner'] = $owner;
        $report['pet_name'] = $pet_name;
        $report['last_seen'] = $last_seen;
        $report['contact'] = $contact;
        $report['description'] = $description;
        $photo = str_replace("\n","",$photo);
        $photo = str_replace("\\/","/", $photo);
        $photo = stripcslashes($photo);
        $report['photo'] = $photo;
        $report['location'] = $location;

        array_push($reports, $report);
    }

    return $reports;
}

And in the Api script, changed the return from 

echo json_encode($resultArray);

to

echo json_encode($resultArray, JSON_UNESCAPED_SLASHES);

Now everything works fantastic. Is this best practice? I am not sure. I'm certain storing base64 is probably not...

JohnD
  • 61
  • 6