Storing Video Blob into a database?

Question

OK before you go off at me this is for testing purposes and YES i do know that its not a good idea but I'm just testing it. (Learning NEW things)

OK Now Once the Video is recorded it use's jQuery.post to send it to uploadvideo.php but how would i retrieve it on that page? Or could we just store it straight into the database? from one page instead of sending it?

function postVideoToServer(videoblob) {

    var data = {};
    data.video = videoblob;
    data.metadata = 'test metadata';
    data.action = "upload_video";
    jQuery.post("http://mysite.com/uploadvideo.php", data, onUploadSuccess);
}

On uploadvideo.php i have this

<?php

require("connect.php");

$video = $_POST["video"];
$up = mysql_query("INSERT INTO video VALUES ('$video')");

?>

But it does not seem to work any help will be great.

Answer 1

You're not escaping the data before inserting it into a query, so you've got an SQL injection vulnerability. Binary files like videos tend to make this abundantly clear: they're going to contain characters that would normally be prohibited in SQL statements, and they're not ashamed of that fact.

To remedy this, we'll do two things:

1. Use a BLOB (Binary Large OBject) column type (if you're not already doing so).
2. Use a prepared statement (to protect against SQL injection).

Let's start by trying to insert a binary file from the disk. We'll use PDO for the database because it makes prepared statements easy. First we need to make our prepared statement:

// assuming PDO object in $db
$stmt = $db->prepare("insert into videos values (:video);");

Rather than having PHP interpolate our variable's value in there with $, we're using :. : in PHP strings doesn't have any special meaning, but to SQL, it's a placeholder. Now we can tell PDO what value we'd like to put in there, and the database will get it and know it's a value without trying to interpret it as SQL at all. You can put integers, strings, and other kinds of objects in there in a fairly straightforward way, but what's interesting here is that we're dealing with a large binary file. Unlike other kinds of values where you just pass in the value, PDO wants a file handle here so it doesn't have to store all the data in memory at once. Let's open a file and bind that value:

$file = fopen("video.mp4", "rb");
$stmt->bindValue("video", $video, PDO::PARAM_LOB);  // LOB = Large OBject

Now that we've filled in all the placeholders, we can execute the statement:

$stmt->execute();

After the statement has executed, we can close the file, since we're no longer using it:

fclose($file);

---

So that's how you insert BLOBs into the database from disk, but that hasn't answered your question, where you get it from a POST request.

Let's consider a slightly more traditional example, without any fancy AJAX. Let's say we had a plain old form element with a plain old input element with a type of file. File submissions get written into a temporary file. The filename is then stored into an appropriate place in $_FILES. This temporary file can then be moved to somewhere more convenient or we could do something with it.

Since the data is written into a temporary file, it's still a file nonetheless, and we can insert it just the same, except just opening a different file:

$file = fopen($_FILES["video"]["tmp_name"], "rb");

---

That's good to know, too, but it still doesn't answer your question. What's the real way to do it?

Well… that's tricky. Your data is coming from JavaScript, where strings represent Unicode text, not binary data. If you try to send any code point over 127 over the wire, it'll get encoded somehow, not really corrupting your data, but changing it in an unexpected (though reversible) way.

Newer browsers provide Blob, and Uint8Array objects that make it so JavaScript can handle binary data, and modify XMLHttpRequest and add FormData so it, too, can handle binary data.

So what's to be done on the JavaScript side?

I'll assume you want to use a standard file input as before, but let it upload without refreshing the page. There are certainly other ways to do it, but they are out of scope. Use your search engine of choice to search for XMLHttpRequest 2.

1. Get the File. HTML5 adds the files property to file inputs, letting you access the currently selected file[s]. Since we're probably not dealing with multiple-file file inputs (another HTML5 addition), we'll just get the zeroth item:

   var file = document.getElementById('video').files[0];
   

2. Make a FormData and fill it with your data. As mentioned before, FormData is a rather recent addition, and only newer browsers support it. For the browsers that do support it, though, it makes your life rather easy:

   var data = new FormData();
   data.append('video', file);
   

3. Do the request. We can use XMLHttpRequest in the traditional way, except passing send the FormData rather than a string:

   var xhr = new XMLHttpRequest();
   xhr.open("POST", "upload_video.php", true);
   xhr.send(data);
   

Your video is now being uploaded! Wait, what? What do we do on the PHP side? It turns out that doing it this way will appear to the server in the same way it would appear if it were using a plain form without AJAX. That's right; no modifications are necessary. It's nice when it works like that, huh?

^{Now since the server-side interfaces are identical, I trust you to make it accessible to people with JavaScript disabled. It's not that hard; just add a plain form with a plain file element as you did in the second phase.}

---

We've gone over a few things here, so just to reiterate the main technologies and techniques we've used:

• We used BLOB columns to hold binary data.
• We used PDO to communicate with the database and use prepared queries.
• We bound a file as a large object parameter to a prepared query.
• We used the uploaded file as the file to upload.
• We used HTML5 functionality and the XMLHttpRequest 2 interface to send binary data using AJAX.