How do I store binary data in MySQL?
+6
A:
For a table like this:
CREATE TABLE binary_data (
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description CHAR(50),
bin_data LONGBLOB,
filename CHAR(50),
filesize CHAR(50),
filetype CHAR(50)
);
Here is a PHP example:
<?php
// store.php3 - by Florian Dittmer <[email protected]>
// Example php script to demonstrate the storing of binary files into
// an sql database. More information can be found at http://www.phpbuilder.com/
?>
<html>
<head><title>Store binary data into SQL Database</title></head>
<body>
<?php
// code that will be executed if the form has been submitted:
if ($submit) {
// connect to the database
// (you may have to adjust the hostname,username or password)
MYSQL_CONNECT("localhost","root","password");
mysql_select_db("binary_data");
$data = mysql_real_escape_string(fread(fopen($form_data, "r"), filesize($form_data)));
$result=MYSQL_QUERY("INSERT INTO binary_data (description,bin_data,filename,filesize,filetype) ".
"VALUES ('$form_description','$data','$form_data_name','$form_data_size','$form_data_type')");
$id= mysql_insert_id();
print "<p>This file has the following Database ID: <b>$id</b>";
MYSQL_CLOSE();
} else {
// else show the form to submit new data:
?>
<form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data">
File Description:<br>
<input type="text" name="form_description" size="40">
<input type="hidden" name="MAX_FILE_SIZE" value="1000000">
<br>File to upload/store in database:<br>
<input type="file" name="form_data" size="40">
<p><input type="submit" name="submit" value="submit">
</form>
<?php
}
?>
</body>
</html>
This code looks like PHP3 (or maybe 4), which register_globals enabled. You don't want to run this code, and it will also not work on a semi up to date PHP installation (which is version 5).
Till
2008-09-14 14:21:17
-1 for addslashes() where mysql_real_escape_string() is needed. Can we please stop giving people code with SQL injection vulnerabilities in it? (No, addslashes() is NOT good enough.)
chaos
2008-12-30 06:28:21
+18
A:
The answer above is correct but I think there is a lot of confusing additional detail there. The basic answer is "in a BLOB column". BLOB is short for Binary Large OBject and that column type is specifically for handling binary data.
Mat
2008-08-01 12:16:22
You're famous! http://blog.stackoverflow.com/2009/01/reputation-bounty-for-unanswered-questions. On another note, bounty-accepted answers are supposed to be permanent, so... why isn't this one?
Kyle Cronin
2009-01-27 13:50:49
A:
The "answer above" referred to in the green answer is no longer the answer above: presumably the order has changed since then. Probably best not to use relative references to other answers. Also, how to refer to another answer?
Flubba
2008-08-02 11:19:35
+4
A:
While you haven't said what you're storing, and you may have a great reason for doing so, often the answer is 'as a filesystem reference' and the actual data is on the filesystem somewhere.
http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html
Issac Kelly
2008-08-02 14:57:13
+1
A:
The question also arises how to get the data into the BLOB. You can put the data in an INSERT statement, as the PHP example shows (although you should use mysql_real_escape_string instead of addslashes). If the file exists on the database server, you can also use MySQL's LOAD_FILE
Scott Noyes
2008-08-27 15:13:31
A:
Flubba is right about using "above" or "below" to refer to an answer. We can use the "link" link after an answer - this uses a named anchor to point to the answer within the page.
Michael Hinds
2008-09-06 15:02:34
A:
A much better storage implementation in available here: http://www.dreamwerx.net/phpforum/?id=1 You'll run into issues with Florian's implementation.
DreamWerx
2008-09-17 09:53:05
A:
I strongly recommend against storing binary data in a relational database. Relational databases are designed to work with fixed-size data; that's where their performance strength is: remember joel's old article http://www.joelonsoftware.com/articles/fog0000000319.html on why databases are so fast? because it takes exactly 1 pointer increment to move from a record to another record. If you add BLOB data of undefined and vastly varying size, you'll screw up performance.
Instead, store files in the file system, and store file names in your database.
Alex
2008-09-17 20:37:34