Okay so I've got an android app that an image to my php web service.
I have decoded my base64 string using base64_decode(). However when I execute an SQL query to insert my data into the database the script inserts this string \x5265736f75726365206964202333.
What I want to know is how I can modify my code to turn that string into an image and insert it into my PostGreSQL database which has UTF8 encoding and a bytea data column type to store the image.
PHP script
header('Content-Type: text/plain; charset=UTF-8');
$conn = pg_connect("database_string");
/* GET DATA */
$name = $_POST['name'];
$s_name = pg_escape_string($name);
$description = $_POST['desc'];
$s_desc = pg_escape_string($description);
$latitude = $_POST['lat'];
$longitude = $_POST['lng'];
$project = $_POST['project'];
$encoded_photo = $_POST['snap'];
$photo = base64_decode($encoded_photo);
header('Content-Type: image/jpeg; charset=utf-8');
$file = fopen('uploaded_image.jpg', 'wb');
fwrite($file, $photo);
fclose($file);
/* INSERT INTO DATABASE */
$res = pg_query("INSERT INTO records (photo, name, description, latitude, longitude, project) VALUES ('$file', '$s_name', '$s_desc', '$latitude', '$longitude', '$project')");
I know I'm close to my intended solution. I think I am inserting the decoded base64 string and inserting that but missing the final step to convert it to the original image taken by the android app. Apologies for my newness to android dev'ing.
I'd successfully write and recall base64 image using HTML + mySql.
Talking the example in HTML5
Below is my data on database
data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAApQA
You need to put data:image/< image format (png in my case) >
So for the viewing in page you need to add <img src=data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAApQA />
I'm hoping that this can be applied to your project.
This part:
$file = fopen('uploaded_image.jpg', 'wb');
fwrite($file, $photo);
fclose($file);
/* INSERT INTO DATABASE */
$res = pg_query("INSERT INTO records (photo, name, description, latitude, longitude, project) VALUES ('$file', '$s_name', '$s_desc', '$latitude', '$longitude', '$project')");
makes no sense. You're inserting a PHP filehandle. It's being converted to a string like Resource id #3 by PHP then converted to bytea. Not what you want.
It's totally unnecessary to write the data to a file in the first place, since you already have it in memory. Just use parameterised queries (you should always use parameterised queries) and let the DB driver take care of it via pg_query_params:
$res = pg_query_params(
'INSERT INTO records (photo, name, description, latitude, longitude, project) VALUES ($1, $2, $3, $4, %6, $6)',
array( pg_escape_bytea($photo), $s_name, $s_desc, $latitude, $longitude, $project)
);
See the docs on pg_query_params.
Regarding bytea escapes see pg_escape_bytea and pg_unescape_bytea. Note that these functions are pretty behind the times and may not recognise hex escapes correctly on output; if you have issues, issue the SQL command SET bytea_output = 'escape'; before trying to fetch values to pg_unescape_bytea.
What PHP should really be doing is using the fact that the data type of the query's first parameter is bytea to infer that it must insert $photo as raw bytes rather than text. From your comment it sounds like it is not smart enough to do this.
BTW, you really need to read this website to understand on reason why parameterized statements should always be used. See SQL Injection and the PHP manual on SQL injection. In addition to being more secure, parameterised statements are easier to get right and often faster.