WordPress.org

Make WordPress Core

Opened 9 years ago

Closed 9 years ago

Last modified 8 years ago

#2339 closed defect (bug) (invalid)

problem with article writing interface

Reported by: marcool Owned by:
Milestone: Priority: normal
Severity: normal Version: 2.0
Component: Administration Keywords:
Focuses: Cc:

Description

When i try and submit my news it works very well.
But i try to modify my news i get very weird things and my news become unreadable.

The thing is my news contains pretty much weird charaters and php code so i guess the text area can be tricked but i'm not sure if this is an FCK editor problem or a Wordpress problem

Anyway, here is the text of my news, try it and tell me what you get (same result on IE and Firefox) :

Uploading, Saving and Downloading Binary Data in a MySQL Database
by Joao Prado Maia
09/15/2000

This article is aimed at answering one of the most asked questions on the PHP mailing list and discussion forums alike: How to store binary files in a MySQL database.

I ran into this same question when asked by a possible employer testing my programming skills to create a set of scripts to upload files to a MySQL database, download files from it, and also show an image, if the file was indeed an image. Anyway, I couldn't find any articles on how to do that, so I searched a lot in the PHP mailing lists to find my answer. This article is my way of "giving back" to the community. :)

I split the article into three pages,

  • Setting up the database
  • Creating the 'upload' scripts
  • Creating the 'download' script

so everything can be explained at the correct time, and also because I added some extra features, like a download script to get the binary files back from the database. This is extremely useful for downloading files of different types from a company database, or even displaying images stored in BLOB fields.
Setting up the database

I give instructions below for MySQL databases, since it was the database I used when developing my web application.

Before starting out, I need to explain what a BLOB field is. Like a teacher would say: "A BLOB is a binary large object that can hold a variable amount of data." This essentially means that BLOB is a datatype that can hold binary content, and we can use it to store files.

In order to set up our database, we should optimize the fields on our tables to not waste any resources. This means that you shouldn't use a LONGBLOB field when you only need to upload 1.5 Kb files. Quoting from the MySQL Online Documentation:

  • TINYBLOB - A BLOB column with a maximum length of 255 (28 - 1) characters.
  • BLOB - A BLOB column with a maximum length of 65,535 (216 - 1) characters.
  • MEDIUMBLOB - A BLOB column with a maximum length of 16,777,215 (224 - 1) characters.
  • LONGBLOB - A BLOB column with a maximum length of 4,294,967,295 (232 - 1) characters.

For most applications, a MEDIUMBLOB field is more than enough, since it can hold up to 15 megs of binary data. Anyway, let's create the database and tables for our web application. We need to connect to the MySQL server:

mysql -u root -p
Enter password:

If the server is running on another host, use this:

mysql -u root -h hostname -p
Enter password:

Now to create the actual database:

mysql> CREATE DATABASE binary_files;
Query OK, 1 row affected (0.00 sec)

Ok, we now have a database to play with. We can create the tables now.

mysql> CREATE TABLE tbl_Files (

id_files tinyint(3) unsigned NOT NULL auto_increment,
bin_data longblob NOT NULL,
description tinytext NOT NULL,
filename varchar(50) NOT NULL,
filesize varchar(50) NOT NULL,
filetype varchar(50) NOT NULL,
PRIMARY KEY (id_files)
);

Now we need to create a custom user for this database/application for maximum security:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER

ON binary_files.*
TO binary_user@localhost
IDENTIFIED BY 'binary_password';

That's all. Now we can connect to the server from Apache/PHP using the user binary_user and password binary_password.


Creating the upload scripts

To make thing easier, I created an open_db.inc include file, so I don't have to keep using the same code to connect to a server and select the database. I'm a lazy programmer, and you should be one too!


<?php

$db = mysql_connect("localhost", "binary_user", "binary_password");
mysql_select_db("binary_files", $db) or die(mysql_errno() . ": " . mysql_error() . "<br>");

?>

This next script is used to upload a new file into the database. We always have to check the variable ($binFile) for contents.
Whenever someone just hits "Upload" without selecting a file, the file input field variable will hold "none". We also have to make an extra
security check on the binary content itself, since it can contain some weird characters that need a slash. This can break our
SQL statement, so we addslashes() it!



<?php
if ($action == "upload") {

ok, let's get the uploaded data and insert it into the db now
include "open_db.inc";

if (isset($binFile) && $binFile != "none") {

$data = addslashes(fread(fopen($binFile, "r"), filesize($binFile)));
$strDescription = addslashes(nl2br($txtDescription));
$sql = "INSERT INTO tbl_Files ";
$sql .= "(description, bin_data, filename, filesize, filetype) ";
$sql .= "VALUES ('$strDescription', '$data', ";
$sql .= "'$binFile_name', '$binFile_size', '$binFile_type')";
$result = mysql_query($sql, $db);
mysql_free_result($result); it's always nice to clean up!
echo "Thank you. The new file was successfully added to our database.<br><br>";
echo "<a href='main.php'>Continue</a>";

}
mysql_close();

} else {
?>
<HTML>
<BODY>
<FORM METHOD="post" ACTION="add.php" ENCTYPE="multipart/form-data">

<INPUT TYPE="hidden" NAME="MAX_FILE_SIZE" VALUE="1000000">
<INPUT TYPE="hidden" NAME="action" VALUE="upload">
<TABLE BORDER="1">

<TR>

<TD>Description: </TD>
<TD><TEXTAREA NAME="txtDescription" ROWS="10" COLS="50"></TEXTAREA></TD>

</TR>
<TR>

<TD>File: </TD>
<TD><INPUT TYPE="file" NAME="binFile"></TD>

</TR>
<TR>

<TD COLSPAN="2"><INPUT TYPE="submit" VALUE="Upload"></TD>

</TR>

</TABLE>

</FORM>
</BODY>
</HTML>
<?php
}
?>


We are using the same script
to handle both actions: uploading a new file and showing the form to
add the information related to this new file. The hidden input field
called MAX_FILE_SIZE on the form is needed so the client side can limit the filesize of new files. Also, don't forget to add the extra attribute ENCTYPE="multipart/form-data" to your form, or else your script is not going to work at all.

Okay, that seemed almost too easy, right? Now what we need to do is create the download script, as well as the script that shows the list
of files currently in the database.


Creating the download scripts

First let's create the script to show the list of files currently in
our table, and then we can start working on the script to download the
files.


<?php
include "open_db.inc";

$sql = "SELECT * FROM tbl_Files ";
$sql .= "ORDER BY filename ASC";
$result = mysql_query($sql, $db);
$rows = mysql_num_rows($result);

echo "<table>\n";
echo " <tr>\n";
echo " <td>Filename</td>\n";
echo " <td>Type</td>\n";
echo " <td>Size</td>\n";
echo " <td>Description</td>\n";
echo " <td> </td>\n";
echo " </tr>\n";

for ($i = 0; $i < $rows; $i++) {

$data = mysql_fetch_object($result);
since our script is very small, i'm not going to escape out to html mode here
echo " <tr>\n";
echo " <td>$data->filename</td>\n";
echo " <td>$data->filetype</td>\n";
echo " <td>$data->filesize</td>\n";
echo " <td>" . stripslashes($data->description) . "</td>\n";
echo " <td>( <a href='download.php?id=$data->id_files'>Download</a> )</td>\n";
echo " </tr>\n";

}
mysql_free_result($result);
mysql_close($db);
?>

This should create a clean
and beautiful table with all the files on the table. For a real world
application, you would probably want to limit the number of files
displayed here, and also do some formatting on the filesize and
filetype fields. I'll leave that as an exercise for the reader.

Let's go to the download script now. It's really simple, and it is
going to work with any type of file inside the database. Whenever users
click on the "Download" links, a download dialog should open with the
filename coming from the database.


<?php
if ($id_files) {

include "open_db.inc";
$sql = "SELECT bin_data, filetype, filename, filesize FROM tbl_Files WHERE id_files=$id_files";


$result = @mysql_query($sql, $db);
$data = @mysql_result($result, 0, "bin_data");
$name = @mysql_result($result, 0, "filename");
$size = @mysql_result($result, 0, "filesize");
$type = @mysql_result($result, 0, "filetype");


header("Content-type: $type");
header("Content-length: $size");
header("Content-Disposition: attachment; filename=$name");
header("Content-Description: PHP Generated Data");
echo $data;

}
?>

Sometimes in other scripts,
text files (.txt) or even images are shown in the browser window,
instead of opening the download dialog. That's because of the HTTP
header "Content-Disposition:," which needs to have
something recognizable as the first argument. If you put a recognizable
MIME type in there, such as an image MIME type, the browser will try to
open it in the window. The same goes for text files or any other
filetype that the browser can recognize.

A lot of times people ask how they could track the number of
downloads for each file. This script can be easily changed to track the
"hits," and you could even create an extra script to show statistics of
each file.

Have fun and keep writing!

Joao Prado Maia
is a web developer living in Houston with more than four
years of experience developing web-based applications and
loves learning new technologies and programming languages.

Change History (2)

comment:1 @skeltoac9 years ago

  • Milestone set to 2.1
  • Resolution set to invalid
  • Status changed from new to closed

Encode your HTML entities. If that doesn't fix it, reopen this bug with more details. What you did, what you expected, what happened. Include example URLs.

comment:2 @Nazgul8 years ago

  • Milestone 2.1 deleted
Note: See TracTickets for help on using tickets.