Error in SQL syntax when trying to upload image to BLOB field

Question

I have the following code to process form and store the image in a MySQL database.

$name=htmlentities(stripslashes($_POST['fname']));
$pname=htmlentities(stripslashes($_POST['pname']));
$email=htmlentities(stripslashes($_POST['email']));
$phone=htmlentities(stripslashes($_POST['phone']));
$des=nl2br(htmlentities(stripslashes($_POST['description2'])));
$cost=htmlentities(stripslashes($_POST['price']));
$category=htmlentities(stripslashes($_POST['category']));
$date=htmlentities(stripslashes($_POST['date22']));
$image=htmlentities(stripslashes($_POST['pic']));
$imagedata=file_get_contents($image);

$query="INSERT INTO records
VALUES('','$name','$pname','$email','$phone','$cost','$des','$category','$date','$imagedata');";

if ($connect->query($query) === TRUE) {
echo "Inserted! <a href=\"display.php\">Click here to view database     records</a>";
} else {
echo "Error: " . $connect->error;
}

When I run the code I get the following error in the SQL Syntax:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Ý¤‰;(IƒiHôBüŸ¤#Žø#&ad„„¹Ì’¼þý…dÀe‘'Ky÷ ð‰ˆË•¿ffµúßÄe%KÁ€DdÑ§ÑÊÕÂRO÷' at line 2

I have checked the column and its BLOB. I have checked the sequence of columns and they are fine. Not really sure what's going wrong.

htmlentities and stripslashes do nothing to prevent SQL injections. What is $_POST['pic'], a location of the file on your system? — chris85
– chris85, Commented Apr 4, 2016 at 18:30
@Fred-ii- By escaping using mysqli_string_real_escape() I was able to insert it into the database. But how to display it now, trying the following by doesn't work: echo '<img src="data:image/jpeg;base64,'.base64_encode( $imagedata ).'"/>'; — Arihant
– Arihant, Commented Apr 4, 2016 at 18:36

Juan Lago · Accepted Answer · 2016-04-04 18:52:03Z

2

You have to escape the image content.

There are different ways to achieve that:

1) If the PHP version that you are using is minor thant PHP 5.5 you can use the "mysql_real_escape_string" function.

$query="INSERT INTO records VALUES('','$name','$pname','$email','$phone','$cost','$des','$category','$date','" . mysql_real_escape_string($imagedata) ."');";

2) Encode the image content using the "base64_encode" function, encoding the the content to base64 is going to increase the file size, but is very safe to use.

 $query="INSERT INTO records VALUES('','$name','$pname','$email','$phone','$cost','$des','$category','$date','" . base64_encode($imagedata) ."');";

Remember to decode the content with the "base64_decode" function when you want to read or download the file.

3) Escape the double and single quotes using the "addslashes" function

 $query="INSERT INTO records VALUES('','$name','$pname','$email','$phone','$cost','$des','$category','$date','" . addslashes($imagedata) ."');";

Remember to remove the slashes when the image is read or downloaded with the "stripslashes" function.

edited Apr 4, 2016 at 18:52

answered Apr 4, 2016 at 18:44

Juan Lago

1,0681 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arihant Over a year ago

I used base64_encode. But how do I display the file now? I tried : echo '<img src="data:image/jpeg;base64,'.($imagedata).'"/>'; which doesn't works

Juan Lago Over a year ago

you have decode the content with base64_decode. I don't recommend you fill a file stream directly in the HTML using the base64 method, since it can overload your web browser and probably it is not going to work with some web browsers.

Arihant Over a year ago

Changed it to following: echo '<img src="data:image/jpeg;base64,'.base64_decode(($imagedata)).'"/>'; No luck

Juan Lago Over a year ago

The base64_decode function is executed in the PHP script when you read the content. You have to download the file to the filesystem and then linked it as normal file using the src attribute in the <img> tag

Collectives™ on Stack Overflow

Error in SQL syntax when trying to upload image to BLOB field

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related