Correct usage of MySQL LOAD_FILE()

Hi all, I apologize if this is a dense question, but I'm having a bit of trouble using MYSQL LOAD_FILE() in conjunction with prepared statements in order to upload an image BLOB. As a result, I'm having to resort to using to separate queries, one to prepare a statement for details, and another, which doesn't prepare the statement to insert my BLOB. Here's an example of the query I've tried:

function add_new_video() {
    $image = $_FILES['thumbnail_file']['tmp_name']; // pass this file name to getimagesize() to determine the mime-type
    $size_array = getimagesize($image);
    $thumbnail_mimetype = $size_array['mime'];
    $thumbnail_contents = file_get_contents($image);
    $thumbnail_filesize = $size_array[3];
    $thumbnail_filename = $_FILES['thumbnail_file']['name'];

 $title = $_POST['title'];
 $summary = $_POST['summary'];
        // Checkbox...   
        if(!empty($_POST['demo_reel'])) {
  $demo_reel = $_POST['demo_reel'];
 }
 else {
  $demo_reel = 0;
 }

 $query = "INSERT INTO videos (title, summary, thumbnail_filename, thumbnail_filesize, thumbnail_mimetype, thumbnail_contents, demo_reel) VALUES(?, ?, ?, ?, ?, LOAD_FILE($image), ?)";
 if($stmt = $this->conn->prepare($query)) {
  $stmt->bind_param('sssssi', $title, $summary, $thumbnail_filename, $thumbnail_filesize, $thumbnail_mimetype, $thumbnail_contents, $demo_reel);
  $stmt->execute();
  if($stmt->affected_rows == 1) {
   return true;
  }
  else {
   return false;
  }
 }
}

Unfortunately, this query fails, and I can't seem to get any errors out of it. Conversely, here's my current query, which works, but doesn't use prepared statements, and is less secure:

    $video_filename = $_POST['file_name'];
    $video_number = $_POST['number'];
    $title = $_POST['title'];
    $summary = $_POST['summary'];
    if(!empty($_POST['demo_reel'])) {
 $demo_reel = $_POST['demo_reel'];
   }
   else {
 $demo_reel = 0;
   }


    $image = $_FILES['thumbnail_file']['tmp_name']; // pass this file name to      
    getimagesize() to determine the mime-type
 $size_array = getimagesize($image);
 $thumbnail_mimetype = $size_array['mime'];
 $thumbnail_filesize = $size_array[3];
 $thumbnail_contents = addslashes(file_get_contents($image));
 $thumbnail_filename = $_POST['number'] . '.jpg';

    $query = "INSERT INTO videos (`video_filename`, `video_number`,
    `thumbnail_contents`, `title`, `summary`, `demo_reel`, `thumbnail_filename`,     
    `thumbnail_filesize`, `thumbnail_mimetype`) VALUES ('$video_filename', 
    '$video_number', '$thumbnail_contents', '$title', '$summary', '$demo_reel', 
    '$thumbnail_filename', '$thumbnail_filesize', '$thumbnail_mimetype')";

  if($result = $this->conn->query($query)) {  
     return true;
  }
  else {
    return false;
  }

As all of the details are currently unescaped, and I would rather not go through the process of using nl2br() and back again, I'm thinking of two queries: one using prepared statements for the $_POST variables, and then another using addslashes() and regular statements for the file. I'd like to be able to do the entire insert in one prepared statement. Any help and understanding is greatly appreciated!

ansaurus

tags:

views:

answers:

Correct usage of MySQL LOAD_FILE()

related questions