PHP / MySQL Basic Security
Protecting your website and data from malicious behavior
Allmar Technologies, January 24th 2007
Many websites and web pages display dynamic content in some shape or form. Using dynamic content on your website keeps your web pages fresh and also alleviates some of the maintenance. However, a poorly implemented solution can expose sensitive information to prying eyes and result in your website or data being compromised.
In this article we will explore some of the common pitfalls that beginners make when putting a website together from a security perspective. This article will not demonstrate how to find these weaknesses in other websites, nor will we point to external websites where the proper security measures have not been taken. Instead, you will just have to take our word that these techniques of exposing your website's sensitive information exists and be aware that not everybody visiting your website has good intentions. As always we welcome your feedback regarding this article and would be delighted to answer questions regarding your particular situation.
Your Website's Document Root
When you or your web hosting provider configures your web server, a sub directory of your directory structure is deemed your web server's document root. This document root setting states the directory and subsequent directories from which your web server will serve pages.
For example, on a typical shared hosting account your document root would appear something like this:
/home/username/public_html/
and on a dedicated server it could be configured like this:
/var/www/html/
Regardless of the type of account you have, this directory appears to the web server as the root directory of your website and will serve any files located in this directory or sub directories of this directory for general public viewing. It is very important to understand this point, so I will reiterate again. Any file located in or within subsequent directories of your document root is available for the entire world to view! Of course, as always, there are exceptions to this rule but it is better to error on the side of caution when proceeding to publish files to your website.
So now that we have an understanding of what is viewable to the world, you need to think about the data and information that you are willing to openly share with the world. Some information about your website environment is very sensitive information and you need to protect that information. I am sure you already take precautionary measures with your passwords and are very selective with whom you share your passwords with. Yet still there are literally hundreds of thousands of websites out there today that publish their passwords and sensitive information for the world to view.
These weaknesses or holes are usually exposed unknowingly by inexperienced webmasters. Particularly when they are creating php files that display dynamic content or installing open source software. It appears that they fall into a false sense of security because php code is "hidden" from web browsers and it only renders the x/html code. So with this sense of security and assurance behind them, they freely place user names, passwords and query statements right into their source code files. Which, of course, are located where? Thats right, within the document root for the world to view. With these security holes your website is now subject to being hijacked, erased, replaced, and SQL injections.
A very simple and effective front line defense for protecting your website from such an attack is simply not to put that sensitive information in your source code to begin with. Instead, place that information in an external file located outside of your document root, hidden from the rest of the world.
Here is an example of a homepage with security holes:
/home/username/public_html/index.php
...
// get the top ten point leaders
$link = mysql_connect("localhost", "username", "password");
if(!$link) {
die("Could not connect: ".mysql_error());
}
$db_selected = mysql_select_db("database", $link);
if(!$db_selected) {
die("Can not use database: ".mysql_error());
}
$result = mysql_query("select * from table order by points desc limit 10");
if(!$result) {
die("Invalid query: ".mysql_error());
}
while($row = mysql_fetch_assoc($result)) {
// display results
}
...
This code will retrieve the top ten point leaders and display the results on your web page but it is filled with security holes, it exposes all kinds of sensitive information.
A better solution is this:
/home/username/public_html/index.php
...
// get the top ten point leaders
include("../php-includes/myDbFunctions.inc.php");
$leaders = GetTopTen();
for($i = 0; $i < sizeof($leaders); $i++) {
// display results
}
...
/home/username/php-includes/myDbFunctions.inc.php
...
// establish db connection
function ConnectToDatabase() {
$link = mysql_connect("localhost", "username", "password");
if($link && mysql_select_db("database")) {
return $link;
} else {
return (FALSE);
}
}
function GetTopTen() {
$leaders = array();
$index = 0;
ConnectToDatabase();
$query = "select * from table order by points desc limit 10";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
$leaders[$index] = array("field1" => $row['field1'],
"field2" => $row['field2'],
"field3" => $row['field3'] );
$index++;
}
mysql_close();
return $leaders;
}
...
Thats it, by simply separating this information and placing the sensitive information into a file that is outside of your document root you have started to secure your source code. In addition, you now have two reusable functions that are ready for execution in the future should you later want to connect to your database or recall the top ten point leaders.
Creating MySQL User Accounts
Another very simple front line defense for protecting your data from being damaged by malicious behavior is to create multiple MySQL user accounts and limiting the permissions of those accounts.
This idea is exactly the same as why you create user accounts on your server. When you log into your server, you do not login as root, you login under your user account. If you require additional privileges then you would temporarily su over to root perform the task and then exit back to your user account. The same concept should apply when you are executing queries from your website.
The majority of the queries that you perform from your website are SELECT statements because you need to display some dynamic content. It only makes sense then to create a user account that has read only access to the data in your database for those situations. There simply is no need to grant this user with write and administrative privileges since they are only reading data anyways. By only allowing this user with read rights, if you are a victim of SQL injections then at least you have protected your data from being overwritten, deleted or dropped all together.
In the situations where you need to perform UPDATE, INSERT and DELETE queries you would then and only then connect to your database with this user who has both read and write privileges. Again, this user still does not have administrative privileges and you should also close the connection immediately after performing the query.
Creating MySQL user accounts very from system to system. Especially now with the popularity of shared hosting and control panels replacing the ability of users being able to login to their server via ssh. So I will not attempt to create step by step instructions on how to create these separate accounts on your system but rather simply refer you to your control panel's documentation or your web hosting providers knowledge guide.
Summary
By following these very simple techniques, you are on your way to creating a more secure environment for your data and information. We hope you found this article both informative and educational. If you have any questions or comments regarding this article, we would be glad to hear from you and offer further assistance as needed.
