PHP-BLOB没有插入MySQL数据库


PHP - BLOB not inserting into MySQL database

我正试图创建一个页面,让某人可以上传文档或图像,并将其存储在MySQL数据库中,然而,由于某些原因,文档/图像的内容实际上并没有插入到我创建的表中。奇怪的是,表中的其余列都被填充了,只有一个LONGBLOB列没有填充。

以下是表单的HTML代码:

<form method="post" enctype="multipart/form-data">
        <input type="submit" value="Submit" />
        <input type="hidden" name="MAX_FILE_SIZE" value="2000000" />
        <label>Select Event:</label>
           <select name="eventID">
              <?php
                 foreach($data as $e)
                 {
                     echo "<option value='" . $e["eventID"] . "'>" . $e["name"] . "</option>";
                 }
              ?>
          </select>
          <label>Your ID:</label>
          <input name="contributorID" type="number" min="0" />
          <label>Your Abstract:</label>
          <textarea name="abstract" rows="10" cols="50"></textarea>
          <label>Attachment:</label>
          <input type="file" name="attachment" />
</form>

我相信我的表单是正确的,并确保最大值的隐藏字段和编码都是正确的。

这是处理表单POST的PHP代码:

$abstract = $this->model("Abstracts");
if (!empty($_FILES["attachment"]) && ($_FILES["attachment"]["error"] == 0))
{
    $fileName = $_FILES["attachment"]['name'];
    $tmpName  = $_FILES["attachment"]['tmp_name'];
    $fileSize = $_FILES["attachment"]['size'];
    $fileType = $_FILES["attachment"]['type'];
    //$fp = fopen($tmpName, 'r');
    $content = file_get_contents($tmpName);
    $content = addslashes($content);
    //fclose($fp);
    if(!get_magic_quotes_gpc())
    {
       $fileName = addslashes($fileName);
    }
    $abstract->insertAbstract($_POST["contributorID"], $_POST["eventID"], $_POST["abstract"], $content, $fileName, $fileType, $fileSize);
}

Abstracts类的insertAbstract方法:

public function insertAbstract($contributorID, $eventID, $abstract, $attachment = null, $name = null, $type = null, $size = null)
{
    $conn = new Credentials;
    $this->connection = $conn->conn;
    if (!empty($attachment))
    {
        $query = $this->connection->prepare("CALL sp_PopulateAbstractAttachments(?,?,?,?,?)");
        $query->bind_param("ibssi", $abstractID, $attachment, $name, $type, $size);
        $query->execute();
        $query->close();
    }
    mysqli_close($this->connection);
}

存储过程背后的SQL:

CREATE PROC...
(abID INT, att LONGBLOB, n VARCHAR(500), t VARCHAR(30), s INT)
BEGIN
    DELETE
    FROM abstractattachments
    WHERE abstractID = abID;
    INSERT INTO abstractattachments
    (abstractID, attachment, name, type, size)
    VALUES (abID, att, n, t, s);
END

该行插入了正确的abIDnts,但attn似乎没有。在phpMyAdmin中,该值在表中只是空的。如果我错了,请纠正我,但它不应该显示价值吗?只是为了再次检查我是否正确,我创建了一个脚本来下载一个文件,但当我打开文件时,它显示为一个空文件。

这是脚本:

public function downloadAbstract()
{
   //instantiates connection object from credentials.php
   $conn = new Credentials;
   $this->connection = $conn->conn;
   //manually change attachmentID parameter for script
   $query = "SELECT name, type, size, attachment FROM AbstractAttachments WHERE attachmentID = 22";
   $this->result = mysqli_query($this->connection, $query);
   list($name, $type, $size, $attachment) = mysqli_fetch_array($this->result);
   header("Content-length: $size");
   header("Content-type: $type");
   header("Content-Disposition: attachment; filename=$name");
   echo $attachment;
   mysqli_close($this->connection);
   exit;
}

我在这里不知所措,所以欢迎任何指导。我已经尽可能地压缩了代码。

首先,使用addslashes()可能会损坏存储的文件。您不想这样做,因为参数化查询负责转义:

绑定变量与查询分开发送到服务器,因此不会干扰查询。解析语句模板后,服务器在执行时直接使用这些值绑定参数不需要转义,因为它们从未直接替换到查询字符串中必须向服务器提供绑定变量类型的提示,以创建适当的转换。

我们在评论中发现的另一个问题是,您的数据库未配置为允许将如此大的参数传递给它。您可以尝试使用send_long_data()将参数以较小的块传递给数据库。

    $query = $this->connection->prepare("CALL sp_PopulateAbstractAttachments(?,?,?,?,?)");
    $query->bind_param("ibssi", $abstractID, null, $name, $type, $size);
    foreach (str_split($attachment, 10240) as $chunk) {
        $query->send_long_data(1, $chunk);
    }
    $query->execute();
    $query->close();

(完全公开,我以前从未使用过send_long_data()。我一直将文件存储在文件系统中,并建议也这样做!)

最后,您执行的每个数据库函数——打开连接、准备语句、绑定参数和执行语句——都应该检查其结果是否存在错误。即使没有错误,MySQL仍然可以产生非致命的警告,这可能导致没有插入坏值。

我不擅长使用sql过程,在mysql数据库中创建表后尝试以下代码,它就会工作。如果它真的解决了你的问题,请将其标记为正确答案。。。

sql

create table customer_account (photo_id int(55) primary key auto_increment,photo longblob,name varchar(88),account_no integer(86),address varchar(72),email varchar(45));

html

<html>
  <body>
    <font color=green size=4>upload Passport Photograph of aCustomer<BR>
    Using PHP Uploads Technology</font></b><br>     
    <form method="post" action="upload.php"  enctype="multipart/form-data">
      <table class="main">
        <tr>
          <td class="header" colspan="2">General Account Opening Form Along with Passport Photograph of the Customer<BR>
            Using PHP Uploads Technology
          </td>
        </tr> 
        <tr>
          <td>Photo</td>
          <td><input type="file" name="photo_upload" class="long"></td>
        </tr>
        <tr>
          <td>Name</td>
          <td><input type="text" name="name" class="long"></td>
        </tr>
        <tr>
          <td>Account Number</td>
          <td><input type="text" name="account_no" class="long"></td>
        </tr>
        <tr>
          <td>Address</td>
          <td><input name="address" type="text"></td>
        <tr>
           <td>Email</td>
            <td><input name="email" type="TEXT"></td>
        </tr>
        <tr>
          <td colspan="2"><input type="submit" value="Register!" /></td>
        </tr>
      </table>
    </form>
  </body> 
</html> 

上传.php

<?
$mysqli = new mysqli("localhost", "nickolysis_username", "nick_password", "photo_database");
$photo_upload='';// variable photo is set to photo_upload
$name=mysql_real_escape_string($_POST['name']);
$account_no=mysql_real_escape_string($_POST['account_no']);
$address=mysql_real_escape_string($_POST['address']);
$email=mysql_real_escape_string($_POST['email']);
$stmt = $mysqli->stmt_init();
// read image with variable read_customerphoto and then insert into database    
move_uploaded_file($_FILES['photo_upload']['tmp_name'],"customerphoto.img");
        $read_customerphoto = fopen("customerphoto.img","rb");
        $photo = addslashes(fread($read_customerphoto,filesize("customerphoto.img")));
if($stmt->prepare("insert into customer_account(photo,name,account_no,address,email) VALUES (?, ?, ?, ?, ?)")) {
  $photo_id= mysql_insert_id();
  $stmt->bind_param('ssiss',$photo,$name,$account_no,$address,$email);
  $stmt->execute();
  // Close statement object
  $stmt->close();
  print "<font size=4 color=green>upload is successful</font> ";
}
else{
  print "error";
}
/* close connection */
$mysqli->close();
?>