Article
Figure 1

may2001.tar

Using PHP and MySQL as a Weblog

Anders Østergård Jensen

In this article, I will show how the embedded server-side scripting language PHP and the fast MySQL database can work as a Weblog. The logged data (e.g., date, time, browser, and IP) is stored in the database, and when the Webmaster wants to check the Weblog, he just opens a PHP file on the Web server. This file loads all data from the MySQL database and prints it out as HTML. What distinguishes PHP from Java or Javascript, for example, is that the PHP script is executed and operated on the server. The PHP parser will print the output from the script as HTML. The opposite is a client-side scripting language, which is executed and operated on the client computer.

Besides manipulating and creating dynamic Web pages, PHP also has many advanced database functions that allow the Webserver to connect to and query database servers, such as MySQL, PostgreSQL, and Oracle. In this article, I will be using MySQL because of its popularity at Web sites and hosting services. See Figure 1 for an illustration of how the PHP technique works.

The Basics

As stated before, the PHP code is included in the HTML at the server. The code starts with <?php or <? and ends with ?>, so the Web server can recognize the PHP code. Everything inside these tags, except from output, will not be visible by the client. I'll start with a traditional "Hello World" example:

<?php
// Comments can look like this
/* Or like this */

echo "Hello World";

?>

Remember, all PHP files end with a .php or .php3, depending on the settings in the httpd.conf file. The PHP syntax looks a lot like C. All commands and functions end with a semicolon. The next example demonstrates the inclusion of PHP in an HTML file and the use of variables:

<html>

<head>
<title>PHP-test</title>
</head>

<body>
This is not PHP-output<br><br>
<?php
$var = "<b>This is PHP-output</b>";
echo $var;
?>
</body>

</html>

PHP also includes some useful system/environment variables, which identify client browser, client IP, server name, etc. To make the Web server print out your browser and IP, use the following code:

<?php
echo "My browser: ";
echo $HTTP_USER_AGENT;
echo "<br>My IP: ";
echo $REMOTE_ADDR;

?>

As shown, some of these system variables could be useful in a Weblog. Later, I will show how these variables can be inserted in the database. A complete list of all system variables in the PHP scripting language can be found by using the phpinfo() function, which generates a useful document.

Creating the Database

The database functions are a little more advanced than the basic variable-to-output functions shown above. First, we need to connect to the MySQL server. This is done by using the following function:

mysql_connect($host, $username, $password);

If you are running the Web server and MySQL server at your computer, mysql_connect("localhost", "root"); is often enough. If the PHP file is hosted by an Internet hosting service, ask the sys admin for the username and password for the database. The $host variable also contains the potential for connecting to MySQL servers anywhere on the Internet. To connect to a database at the Web server, use the following function:

mysql_selectdb($db);

where $db is the name of your database. To send SQL queries, use this function:

mysql_query($query);

where $db contains your SQL commands.

Next, connect and create a table in the database that contains the fields id, date, ip, port, browser, and time. The following PHP code, which uses the functions above, will do this:

<?php
mysql_connect("localhost", "root");
mysql_selectdb("mydb");
mysql_query("
CREATE TABLE weblog (

ip VARCHAR(255),
port VARCHAR(255),
browser VARCHAR(10),
time VARCHAR(255),
id INT NOT NULL AUTO_INCREMENT,
PRIMARY key(id),
date TIMESTAMP(8)
)");
?>

We've now created the fields for the PHP system variables for the Weblog. The date field will generate a date by the MySQL server each time data is inserted. The current time will be generated by the PHP command date("H:i:s"); and inserted into the time field. The id is a unique number for each inserted set of data and is automatically generated by the MySQL server whenever data is inserted. Executing the the code on the server won't give any output, and you can't know whether the table is created or not. To solve this problem, modify your code to this:

<?php
mysql_connect("localhost", "root") || \
  die("The <b>mysql_connect()</b> failed!");
mysql_selectdb("mydb") || die("The \
  <b>mysql_selectdb()</b> failed!");
if(
mysql_query("
CREATE TABLE weblog (
ip VARCHAR(255),
port VARCHAR(255),
browser VARCHAR(10),
time VARCHAR(255),
id INT NOT NULL AUTO_INCREMENT,
PRIMARY key(id),
date TIMESTAMP(8)
)")
) {
echo "The table was created!";
}
else {
echo "The table was not created!";
}
?>

If someting fails, the die() functions will print out the text located inside the brackets. For example, if the mysql_connect() fails, you will see the output "The mysql_connect() failed!" in your browser. The if-then-else statement will tell you whether the table was created by putting the mysql_query() into an if-operator.

Using the Database

The next step is to insert some PHP code in the Web page where you want the Weblog (e.g., index.php). The Weblog will fetch the necessary information from the client, establish the database connection, and insert the retrieved data into the database. The code should look like:

<?php
$time = date("H:i:s");
mysql_connect("localhost", "root");
mysql_selectdb("mydb");
mysql_query("INSERT INTO weblog(ip, browser, port, time) " . " \

  VALUES('$REMOTE_ADDR', '$HTTP_USER_AGENT', '$REMOTE_PORT', \

  '$time')");
?>

When the client loads the document, the Web server will do the following:

Create the $time variable
Connect to the database
Select the mydb database
Send the SQL-query, which inserts the information retrieved from the client into the table named Weblog

Printing the Output

Finally, we need to know which data is stored in the database. We could start the MySQL shell from the command line and edit the data, but it would be easier to open a PHP document on the Web server from any browser and view the data. To do this, we need a PHP procedure that does the following:

Connects to the database
Selects the mydb database
Retrieves an array that contains the data
Prints out the array

The code should look like:

<?php
mysql_connect("localhost, root");
mysql_selectdb("mydb");
$query = mysql_query("SELECT * FROM weblog ORDER BY id DESC");
while($row == mysql_fetch_array($query)) {
$ip = $row['ip'];
$browser = $row['browser'];
$port = $row['port'];
$time = $row['time'];
$date = $row['date'];
$id = $row['id'];
echo "No. <b>$id</b> on <b>$date - $time</b><br>
IP: <b>$ip</b><br>
Browser: <b>$browser</b><br>
Port: <b>$port</b><br><br>";
}
?>

The while loop will retrieve an array named "$row" by using the $query variable to connect to the database. Inside the while loop, a set of variables are created to make the procedure of the variable/array name more manageable. Finally, the data is printed in HTML format.

Securing PHP/MySQL

As the Webmaster or Web server administrator, you must secure the vulnerable parts of the PHP/MySQL structure. First, users at the Web server should have access only to their own databases, protected with unique usernames and passwords. The root account should also be password protected, because the default installation of MySQL (e.g., from a Linux distribution) is installed without a password for root. Use mysqladmin to create and administer accounts for the server. This is a useful interface to control installed databases and user accounts, and it is installed by the standard MySQL installation. Check out the MySQL online manual (http://www.mysql.com/) for a complete explanation of the commands and utility options.

To make the client browser safe for user input (e.g., a forum or a guestbook on a PHP-based site), PHP contains a set of functions that can strip client database input for HTML and PHP commands. PHP input is dangerous because it can contain internal database queries. For example, HTML tags (such as <blink>) and PHP commands are removed from user input by using the strip_tags() function:

<html>
<head>
<title>Example using strip_text()</title>
</head>

<body>
<h1>This is <b>HTML-formatted</b> input</h1>

<?php
$string = "<h1>This is not <b>HTML-formatted</b> input</h1>";

echo strip_tags($string);

?>

</body>
</html>

The parsed document will generate a big headline (<h1>) and an unformatted line of text. Thus, a Web site visitor using form input has no chance of inserting HTML or PHP commands into a string or database. Characters used for HTML codes, such as "<", ">", "&", etc. can be converted into HTML entities by using the htmlspecialchars() function. This disables formatting of the characters and preserves the meaning of the character. The following figure diplays the conversion:

< becomes &lt
> becomes &gt
& becomes &amp
" (a double quote) becomes &quot

Use htmlspecialchars() like this:

<?php
$string = "<h1>This is not formatted HTML-code</h1>";
echo htmlspecialchars($string);
?>

The PHP output will look like:

&lt;h1&gt;This is not formatted HTML-code&lt;/h1&gt

To secure data inserted to a database by an SQL query, it is also necessary to remove the single quotes, double quotes, etc. from the inserted string. This is done by using the addslashes() function, which adds slashes to these special characters. This forces the SQL database to ignore the characters and disables a client from manipulating SQL queries. Use addslashes(); like:

<?php
$string = "<b>'Everything inside single-quotes is ignored'</b>";
echo addslashes($string);
?>

It is also necessary to quote all meta-characters, which can be done by using the quotemeta() function. This will quote the following characters with a backslash:

. \\ + * ? [ ^ ] ( & )

Use the function:

<?php
$string = "+++++++++ quotemeta() quotes all meta-characters \
  &&&&&&&";
quotemeta($string);
?>

In a forum, it is sometimes necessary to convert line breaks into a <br> tag. This makes the output more readable, but still contains the possibility to remove all other HTML tags. This is done by using the nl2br() function:

<?
$string = "newline.....
newline........
newline........
";
echo nl2br($string);
?>

Thus, the forum user is able to insert line breaks without using any HTML tags.

When a database connection between the Web server and MySQL server is established, the user name and password are sent in plain text. To avoid the possibility of a misconfigured Web server or corrupt configuration of PHP, it is a good idea to store all variables needed for the database connection in an include file. Whenever a PHP file establishes a connection to a MySQL server via mysql_connect(), it will use the user name, password, and host. These are stored as variables in an include file. The include file should look like:

<?php
$host = "localhost";
$username = "myusername";
$password = "mypassword";
?>

And the main document should look like this:

<?php
require("include-filename");
mysql_connect($host, $username, $password);

/* etc.... */
?>

The require() function fetches all variables from the include file and stores them for use in the main document. The most secure PHP environment is when PHP Safe Mode is activated. This is done by editing the php.ini file, which is located normally in /etc and enabling the following line:

safe_mode = On

This will cause the Web server to perform additional checks of a PHP script before functions, which contain possible security problems, are executed. For example, the filesystem functions such as fopen(), file(), and include() are only able to manipulate files that are owned by the owner of the script. safe_mode_exec_dir = <dir> will force PHP to execute a binary only from a specified directory.

Securing the PHP CGI Module

If PHP is running at the server as a CGI module, the Web server is vulnerable to some client-side attacks. PHP is built to prevent some basic CGI-interpreter attacks like this one:

http://localhost/cgi-bin/php?/etc/passwd

PHP still has some problems with PHP files on the Web server redirecting to the installed CGI module (/cgi-bin/php). A simple redirection for the CGI module looks like:

http://localhost/directory/secret/file.php ----> \
   http://localhost/cgi-bin/php?directory/file.php

The directory existing at the Web server is used as GET input to the CGI module, and thereafter parsed. Unfortunately, if the direct POST-path to /cgi-bin/php is entered from the browser, no user permissions are checked, and non-public files in the Web server root are viewable from the client. This attack is prevented by using the --enable-force-cgi-redirect during module compilation.

Conclusion

I hope that this article helped to show how a set of relatively simple scripting commands and a MySQL database can work as a Weblog. The security of PHP is an important part of the language, and the functions and security options described in this article can help secure a Web site and make it more difficult for crackers to break into a system.

PHP is distributed under the GNU/GPL and MySQL is free for private use. If you want to learn more about the PHP language, go to the official Web site at:

http://www.php.net/

The site provides documentation, an online manual, and the latest downloadable sources and binaries. There are also other great PHP Web sites on the Internet (such as http://www.phpdeveloper.com/) that contain other useful articles. The MySQL database is owned by the Swedish company T.c.X DataKonsultAB. MySQL can be downloaded for many platforms from:

http://www.mysql.com/

Anders is a Web designer and has developed a lot of PHP-based Web applications. He is a member of the Danish SunSITE staff and works with Linux and FreeBSD. Anders is also a member of his local Linux User Group in Denmark, MVJLUG (www.MVJLUG.dk).