Inserting Binary into MySQL BLOB

Question

$serv = "xxx";
$user = "xxx"; 
$pass = "xxx"; 
$db = "xxx"; 

$imgloc = "../images/bg.jpg"; 
$image = fopen($imgloc, 'rb'); 
$imageContent = fread($image, filesize($imgloc)); 

$conn = new mysqli($serv, $user, $pass, $db); 

$sql = "INSERT INTO `image`(`advert_id`,`img`) VALUES('1','" . $imageContent . "');"; 
$conn->query($sql);

I'm using the above code to try to insert binary into my MySQL database but nothing is being sent to the database. The $imageContent just appears in the database as null but if I echo $imageContent it seems to show binary data.

advert_id is just a int field and img is a BLOB

You are vulnerable to sql injection attacks. you can't just stuff random binary garbage into a query string and expect things to work. — Marc B
– Marc B, Commented May 11, 2015 at 18:19
php.net/manual/en/mysqli.prepare.php and php.net/manual/en/mysqli-stmt.bind-param.php — AbraCadaver
– AbraCadaver, Commented May 11, 2015 at 18:19
As a tangential comment, I would recommend making sure that you REALLY have a good use case for storing images blobs in MySQL. In a lot of cases, this might not be a good idea when compared to simply storing file references in the database. — Mike Brant
– Mike Brant, Commented May 11, 2015 at 18:54

Community · Accepted Answer · 2017-05-23 10:28:21Z

The reason why your code isn't working is because you need to escape your data.

$imageContent = fread($image, filesize($imgloc)); 
$imageContent = mysqli_real_escape_string($conn, $imageContent);

You are not seeing the syntax error, similar to:

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 '8 16@#54' at line 1...

Because you are not checking for errors.

Visit http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php, then use the following at the top of your file:

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// rest of your code

This will signal syntax errors.

Use mysqli with prepared statements, or PDO with prepared statements

Plus, as Mike Brant said in comments, and I quote:

"As a tangential comment, I would recommend making sure that you REALLY have a good use case for storing images blobs in MySQL. In a lot of cases, this might not be a good idea when compared to simply storing file references in the database."

Mike speaks the truth. Your database will increase dramatically over time, therefore storing a copy of your files in a folder then making a reference to it, is usually a better idea, but that is entirely up to you.

Read the following Q&A's on Stack:

Collectives™ on Stack Overflow

Inserting Binary into MySQL BLOB

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related