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);
}
?>
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:
max_allowed_packet of mysql, you will run into some errors in that regard.max_allowed_packet.send_long_data function.