1

I currently am trying to store images from an Android application. The app converts the image from the ImageView object into a byte array and then uses Android's built in Base64.encodeToString function so that I could pass it within a HTTP Post Request to my PHP script(which conducts the Insert to Database logic).

For some reason, if in my PHP Script I try to call base64_decode before storing the image as a MEDIUMBLOB, the whole insertion process fails but if I skip the base64_decode within the PHP script, the insertion works successfully. Could anyone explain to me why? Been debugging for hours but can't seem to find out the reason

I was thinking that decoding it would help me save storage space on the DB. I'm aware of not storing images in DBs and using paths and stuff but for my current purpose, I've chosen to store it in the DB as it's much more convenient for me (it's not a huge scalable project as I am just developing something to run for a small study).

Thanks in advance!

<?php /* * Following code will create a new product row * All product details are read from HTTP Post Request */ // array for JSON response $response = array(); // check for required fields if (isset($_POST['username']) && isset($_POST['drinkName']) && isset($_POST['caption']) && isset($_POST['photo']) ) { $username = $_POST['username']; $drinkName = $_POST['drinkName']; $caption = $_POST['caption']; $photoRaw = $_POST['photo']; $photo = base64_decode($photoRaw); // include db connect class require_once __DIR__ . '/db_connect.php'; // connecting to db $db = new DB_CONNECT(); // mysql inserting a new row $result = mysql_query("INSERT INTO Memories(username, drinkName, caption, photo) VALUES('$username', '$drinkName', '$caption', '$photo')"); // check if row inserted or not if ($result) { // successfully inserted into database $response["success"] = 1; $response["message"] = "Product successfully created."; // echoing JSON response echo json_encode($response); } else { // failed to insert row $response["success"] = 0; $response["message"] = "Oops! An error occurred."; // echoing JSON response echo json_encode($response); } } else { // required field is missing $response["success"] = 0; $response["message"] = "Required field(s) is missing"; // echoing JSON response echo json_encode($response); } ?>
Improve this question
6
  • When inserting, are you escaping/preparing? Commented Feb 26, 2018 at 23:27
  • Neither, I am just getting the values directly from $_POST (for the direct approach). I would then attempt to call base64_decode on the variable (which doesn't work) but works if I don't call base64_decode on it Commented Feb 26, 2018 at 23:35
  • Binary data when being inserted in a sql statement can break the statement if you do not properly escape or prepare it. Are you getting any mysql errors? (or do you use the mysql error returns to debug?) If you could show that piece of code, it would be helpful for others to see what could be the issue there. Commented Feb 26, 2018 at 23:39
  • Hello IncredibleHat, I've just added my PHP Script in my post. I suspect the sissue might be an encoding/decoding protocol difference between android's Base64 and Php's Base64 functions but am not sure what the solution might be Commented Feb 26, 2018 at 23:48
  • Base64 is base64. What you have there is you are putting the raw binary data from $photo straight into your sql query build without any kind of escaping of the data. Also I see you are using the depreciated functions of mysql_* (those have been removed in php7 too). You'll want to start working with mysqli or pdo. Commented Feb 26, 2018 at 23:56

1 Answer 1

Reset to default
1

What you have described is a problem with how you are trying to insert the raw binary data into the database. When you say it works as base64, that is because base64 generally won't have a single-quote character in it which would break the sql query you show you are using.

To escape the value using those old mysql_* functions, you would use mysql_escape_string...

Please do not use that old mysql method!

You should migrate to mysqli which has been around for many years (your server should support it). Since it looks like your DB_CONNECT method is built around the old mysql, you will have to restructure that for mysqli. Its not too difficult.

I can provide you with an example of how to do the mysqli insert using a safely prepared statement:

$mysqli = new mysqli("localhost", "my_user", "my_password", "db_name");// db connect $stmt = $mysqli->prepare("INSERT INTO Memories (username, drinkName, caption, photo) VALUES(?,?,?,?)"); $stmt->bind_param("ssss", $username, $drinkName, $caption, $photo); $stmt->execute();

This treats the last value as a straight passthrough as a 'string' into your MEDIUMBLOB field to be inserted safely (as well as safely handle the other three variables protecting you from sql injection attacks).

An alternate way to send binary data in, in packets, is this method:

$mysqli = new mysqli("localhost", "my_user", "my_password", "db_name");// db connect $stmt = $mysqli->prepare("INSERT INTO Memories (username, drinkName, caption, photo) VALUES(?,?,?,?)"); $null = NULL; // this is just a holder to bind on $stmt->bind_param("sssb", $username, $drinkName, $caption, $null); // note the 'b' $stmt->send_long_data(3,$photo); // 3 indicates the 4th bound variable $stmt->execute();

Some notes:

  • If your images are bigger than the max_allowed_packet of mysql, you will run into some errors in that regard.
  • If your field is a BLOB it would only hold an image < 64kb. If its MEDIUMBLOB it will hold a 16mb image, but you risk run over max_allowed_packet.
  • If you run over the packet issue, you would need to build a packet loop to pass smaller chunks through the send_long_data function.
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Cheers IncredibleHat, I'll try use the mysqli set of functions and see if it works
In the interim, you can try out the mysql_escape_string... just to see if you can get that working.
Tried both approaches, unfortunately both still can't save the byte array to the BLOB column in the DB. Strange indeed as the other columns do save.
Have even gone further, instead of just echoing I've even managed to rebuild the image as a JPEG from $photo and it works. Is it just the MYSQL insertion that is not compatible? Have tried MEDIUMTEXT and MEDIUMBLOB to no avail. I don't think it would be a size issue because if managed to receive the Base64 which is 33% larger in theory right?
I updated the answer. I was fiddling with this a bit and realized I had truncated two methods into one that simply did not work right. So, I have provided both methods to pass the binary data in (tested on my server to make sure this time!).
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.