尝试将图像上传到 BLOB 字段时出现 SQL 语法错误 [英] Error in SQL syntax when trying to upload image to BLOB field

问题描述

我有以下代码来处理表单并将图像存储在 MySQL 数据库中.

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;
}

当我运行代码时，我在 SQL 语法中收到以下错误:

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

错误:您的 SQL 语法有错误；检查手册对应于您的 MySQL 服务器版本以使用正确的语法靠近'ݤ‰;(IƒiHôBüŸ¤#Žø#&ad„„¹Ì'¼þý…dÀe''Ky÷ð‰ˆË•¿ffµúßÄe%KÁ€DdѧÑÊÕÂRO÷'在第 2 行

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

我已经检查了该列及其 BLOB.我检查了列的顺序，它们很好.不太确定出了什么问题.

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.

推荐答案

你必须对图像内容进行转义.

You have to escape the image content.

有不同的方法可以实现:

There are different ways to achieve that:

1) 如果您使用的 PHP 版本低于 PHP 5.5，您可以使用mysql_real_escape_string"函数.

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) 使用base64_encode"函数对图片内容进行编码，将内容编码为base64会增加文件大小，但使用起来非常安全.

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) ."');";

当您要读取或下载文件时，请记住使用base64_decode"函数对内容进行解码.

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

3) 使用addslashes"函数对双引号和单引号进行转义

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) ."');";

记得用stripslashes"功能读取或下载图片时去掉斜线.

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

这篇关于尝试将图像上传到 BLOB 字段时出现 SQL 语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！