Syntax error when inserting into database

Question

I am trying to insert a pdf file into my database, using php. I know that is typically not good practice, but I need to none the less.

So here is the schema for the table I am trying to insert the pdf into:

    create table manager_certificate(
    manager_id int,
    certificate_id int,
    certificate blob,
    primary key(manager_id, certificate_id),
    foreign key(manager_id) references manager(id)
) ENGINE=InnoDB;

I have an html page that allows the use to select the file, and has an upload button that call the php file that should upload the file to the database.

html:

<!DOCTYPE html>
<head>
    <title>MySQL file upload example</title>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
    <form action="manager_upload.php" method="post" enctype="multipart/form-data">
        <input type="file" name="uploaded_file"><br>
        <input type="submit" value="Upload file">
    </form>
    <p>
        <a href="list_files.php">See all files</a>
    </p>
</body>
</html>

php:

<?php
// Check if a file has been uploaded
if(isset($_FILES['uploaded_file'])) {
    // Make sure the file was sent without errors
    if($_FILES['uploaded_file']['error'] == 0) {
        // Connect to the database
        $dbLink = new mysqli('dbclass.cs.nmsu.edu', 'corbinc', 'corbinc430', 'cs482corbinc');
        if(mysqli_connect_errno()) {
            die("MySQL connection failed: ". mysqli_connect_error());
        }

        // Gather all required data
        $data = $dbLink->real_escape_string(file_get_contents($_FILES  ['uploaded_file']['tmp_name']));

        // Create the SQL query
        $query = "
            INSERT INTO 'manager_certificate'('certificate')
            VALUES ( '{$data}' )";

        // Execute the query
        $result = $dbLink->query($query);

        // Check if it was successfull
        if($result) {
            echo 'Success! Your file was successfully added!';
        }
        else {
            echo 'Error! Failed to insert the file'
               . "<pre>{$dbLink->error}</pre>";
        }
    }
    else {
        echo 'An error accured while the file was being uploaded. '
           . 'Error code: '. intval($_FILES['uploaded_file']['error']);
    }

    // Close the mysql connection
    $dbLink->close();
}
else {
    echo 'Error! A file was not sent!';
}

?>

So I have tried the above code and I am getting the following error:

Error! Failed to insert the file
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 ''manager_certificate'('certificate')
            VALUES ( '%PDF-1.5\n%ÐÔÅØ\n3 0 ' at line 1

How can I fix this error? Is the problem in my schema? Or the php maybe?

Did you look at this similar SO Post: http://stackoverflow.com/questions/4813913/how-to-store-pdf-files-into-mysql-as-blob-from-php — Lee Loftiss, Nov 14 '13 at 01:39
Try removing your `real_escape_string` function call. Your PDF is a binary file, not a string. — Machavity, Nov 14 '13 at 01:42
Have you considered uploading a file to the server and store it on there, while MySQL contains a pointer (like a URI) to the file? That would be way better than attempting to insert an entire file as blob into a column. — Terry, May 07 '16 at 16:20

score 0 · Answer 1 · answered Nov 14 '13 at 01:42

0

You shouldn't have quotes around your table or field names, only for string data. Try INSERT INTO manager_certificate (certificate) ....

answered Nov 14 '13 at 01:42

Cuagau

548
3
7

With that fixed (along with some hard coded values for the other fields and the real_escape_string function call removed[see above commet]) I now get another error message: 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 '1, 1, certificate) VALUES ( '%PDF-1.5 %ÐÔÅØ 3 0 obj < – user2297613 Nov 14 '13 at 01:53

score -1 · Answer 2 · answered Jul 10 '16 at 15:13

Here is a working sample code for your problem.

<?php 
// CREATE TABLE files (
//     id   INT           AUTO_INCREMENT PRIMARY KEY,
//     mime VARCHAR (255) NOT NULL,
//     data BLOB          NOT NULL
// );
class BlobDemo {

    const DB_HOST = 'localhost';
    const DB_NAME = 'test';
    const DB_USER = 'root';
    const DB_PASSWORD = '';

    /**
     * PDO instance
     * @var PDO 
     */
    private $pdo = null;

    /**
     * Open the database connection
     */
    public function __construct() {
        // open database connection
        $conStr = sprintf("mysql:host=%s;dbname=%s;charset=utf8", self::DB_HOST, self::DB_NAME);

        try {
            $this->pdo = new PDO($conStr, self::DB_USER, self::DB_PASSWORD);
            //for prior PHP 5.3.6
            //$conn->exec("set names utf8");
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }

    /**
     * insert blob into the files table
     * @param string $filePath
     * @param string $mime mimetype
     * @return bool
     */
    public function insertBlob($filePath, $mime) {
        $blob = fopen($filePath, 'rb');

        $sql = "INSERT INTO files(mime,data) VALUES(:mime,:data)";
        $stmt = $this->pdo->prepare($sql);

        $stmt->bindParam(':mime', $mime);
        $stmt->bindParam(':data', $blob, PDO::PARAM_LOB);

        return $stmt->execute();
    }

    /**
     * select data from the the files
     * @param int $id
     * @return array contains mime type and BLOB data
     */
    public function selectBlob($id) {

        $sql = "SELECT mime,
                        data
                   FROM files
                  WHERE id = :id;";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array(":id" => $id));
        $stmt->bindColumn(1, $mime);
        $stmt->bindColumn(2, $data, PDO::PARAM_LOB);

        $stmt->fetch(PDO::FETCH_BOUND);

        return array("mime" => $mime,
            "data" => $data);
    }


    /**
     * close the database connection
     */
    public function __destruct() {
        // close the database connection
        $this->pdo = null;
    }

}
$blobObj = new BlobDemo();
if(isset($_POST['submit']))
{

    $blobObj->insertBlob($_FILES['uploaded_file']['tmp_name'],"application/pdf");
}
?>
<!DOCTYPE html>
<head>
    <title>MySQL file upload example</title>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
        <input type="file" name="uploaded_file"><br>
        <input type="submit" name="submit" value="Upload file">
    </form>
    <p>
      <?php 
      // to display the pdf from database
   //    $a = $blobObj->selectBlob(3);
            // header("Content-Type:" . $a['mime']);
            // echo $a['data'];
      ?>
    </p>
</body>
</html>

Hope you can workout a solution from this. Thanks.

Syntax error when inserting into database

2 Answers2