Wordpress, the engine that is behind the post you are currently reading, is easy to upgrade with most hosting environments. However, upgrades can get tricky in locked-down enterprise environments.
For the most secure environment, the MYSQL database user that is used by Wordpress (hardcoded in your wp-config.php file) to retrieve and set information should not have Drop, Alter, and Create database privileges. If it did, malicious code would have an easier time disrupting the database. This is a problem because upgrading Wordpress usually involves some major additions or changes to the database structure, for which these privileges are needed.
So what is the solution? Basically it is running the upgrade as the root user. But, hardcoding root credentials in your wp-config.php file for a little bit is not good security practice, even if you change it back afterward. What you need to do is run an SQL script with all the database statements that are required for upgrade directly on the MYSQL server, or by using the MYSQL command line client.
Capturing the upgrade sequence is a little more difficult. Most enterprise installations have a development environment with looser security, possibly only accessible from inside the firewall of your organization. If your development environment is setup with a database user that does have alter, drop, and create credentials you’re in good shape.
Open up the /wp-includes/wp-db.php file and find the query() function. This is the function that Wordpress calls before each and every database call. On the first line of the function, add this line:
error_log($query);
This will output each query into your PHP error log file. Empty this file before attempting an upgrade on your development environment. Wordpress upgrades involve replacing all core files, and then logging in to the admin interface and upgrading the database by clicking a prominent upgrade button. After you have done this, the error log file will contain all the queries run by the upgrade.
There are more queries in here than are necessary to upgrade Wordpress. Delete all select queries, leaving a combination of alter, drop, create, or insert statements. Rename the error log file upgrade.sql and you’ve got the entire database upgrade in a ready to run script.
Publish the changed files up to your live environment. Your site will stay up even if a database change is required in the back end – this is a feature of Wordpress allowing for easier upgrades without site downtime. Next, run your upgrade.sql script with root privileges, or send it to your DBA team to run. You’re all upgraded, and you kept your site secure the entire time.
This same process can be used for upgrades to Wordpress Plugins, which often also require Alter, Create, and Drop statements to their tables.
Security is an important part of PHP programming, and PHP provides several tools for securing database queries and HTML display. However, knowing which function to use and when to use it can be somewhat confusing, as there’s many details to pay attention to. It’s important not to leave your website open to cross-site scripting or SQL injection attacks.
This example and explanation will focus on a common web scenario – a user submitting data via a form, being asked to confirm it, and then being displayed the data after it is stored in the database.
1. Initial User Input
In this scenario, a user will be presented an input box for which they can add a review about a particular product on a public facing website. The user is not allowed to use html, similar to the restrictions on product reviews on Amazon.com. The user inserts their review into the input box and then clicks submit.
2. User Input Preview and Cross Site Scripting Prevention
Now, we want to show the user their review and have them confirm it before we add it to the database. This is a prime example of a situation that leaves a website open for a XSS (cross-site scripting) attack. We don’t know what the user entered on the previous page, and they could be purposely entering malicious content. Because what they entered in going to be output to the screen, if they input raw PHP code, it could get executed unknowingly by our server. To prevent this, we need to html encode all potentially dangerous characters for display on the screen.
I would recommend using htmlentities() to do this, and use the ENT_QUOTES option as shown below, which means that both single and double quotes will also be encoded. Because we also decided that we wouldn’t allow HTML, we’ll want to strip that HTML before displaying the results, to give the user an accurate preview. Before doing any of this, we’ll have to grab the submitted data from the submitted form – this tutorial won’t cover this, but there are plenty of web tutorials available for doing this.
<?php
//strip HTML tags from input data
$input_data = strip_tags($input_data);
//turn all characters into their html equivalent
$preview_data = htmlentities($input_data, ENT_QUOTES);
//...display $preview_data
?> 3. Database Insert and SQL Injection Protection
Now, let’s say the user previews above data, and clicks accept, which will send this data to another script for entry into the database. Protecting your database from SQL injection requires different steps than protecting against cross-site scripting.
It is very likely that you don’t want to store the user’s data in HTML encoded form. Let’s say you are using a varchar(32) column in your database, and you had an input box that was 32 characters long. If you were trying to store the HTML equivalent of this in your database, you would need a column that was much larger to guarantee that data didn’t get lost. This is because the HTML equivalent of a single character is often many characters long. For example, a double quote (”) becomes ", and an ampersand (&) becomes &. We need to guard against single quotes, because these can cause an SQL injection problem, depending on how your database is setup. Consider this example:
<?php
$name = "George'); DELETE FROM mytable; INSERT INTO mytable (name) VALUES ('you got hacked";
$sql = "INSERT INTO mytable (name) VALUES ('$name')";
//...run the $sql query
?>If your SQL server allows more than one SQL command on a single query request, you’ve just lost lots of data. While it’s a good idea to lock down your database server so it doesn’t allow this, you always want to secure your code independently of the database, in case you change your hosting setup later.
To secure your script, you can use the addslashes() function which will escape both single and double quotes, by adding backslashes before them, to prevent multiple queries from being executed. You’ll also want to test for the length of the input field, as forged form requests are easy as can be using tools like Firebug. Here is example code that will accomplish that:
<?php
//escape trouble characters
$name = addslashes($name);
//make sure not longer than expected length
$name = substr($name, 0, 32);
$sql = "INSERT INTO mytable (name) VALUES ('$name')";
//...run the $sql query
?>Note that there is the possibility of valid user input being cut off when being inserted into the database. If the user used all 32 characters in the input box, and a single quote was one of them, then the above code would trim off the last character. You may want to prevent this by using a 28 max character input box for a varchar(32) column, giving the user 4 opportunities to use an escaped character without having their input cut off.
4. Retrieving and Displaying the Data From the Database
Now that the data is safely in the database, everything is safe right? Wrong. We elected not to store HTML encoded data from users. This means that we are still at risk from a Cross Site Scripting attack every time we query and then display the data. What you’ll probably want to do is create a function that sanitizes database data before being displayed on the screen. No only should you HTML encode the data, but you’ll also want to remove the backslashes you added earlier, as these aren’t meant to be displayed. Here’s what your function could look like:
<?php
function sanitize_data($input_data) {
return htmlentities(stripslashes($input_data), ENT_QUOTES);
}
?>
Overview
After adding PostgreSQL compatibility to my PHPSimpleChat library, I became interested in its performance versus MYSQL. In my workplace, a large New York based media corporation, MYSQL is quickly becoming the database of choice ahead of the costly Sybase. MYSQL certainly has the momentum of the industry behind it, but what about PostGreSQL? Having advanced features, a solid community following, and easy installation on all major operating systems, PostGreSQL shouldn’t be left out of the race.
Performance
The feature sets of MYSQL 5 and PostGreSQL 8 are roughly equal when using MYSQL’s InnoDB table type, with PostGreSQL having just a few more features. After meeting my needs feature-wise, my next biggest concern is performance. The sites I work with are often content management system (cms) based in popular platforms like Drupal and Wordpress. Complicated Drupal sites can execute several thousand queries for a logged in user for each page view and need a high-performance database to support it. Below, I’ve setup several tests using a single database connection to benchmark both MYSQL and PostGreSQL executing the same queries. In addition to testing MYSQL’s InnoDB table type, which uses row level locking, I also tested MYSQL’s MyISAM and Memory table types. MyISAM was the standard before InnoDB, but uses table level locking, which can cause backups in a highly active insert and update environments. Memory tables are stored entirely in memory, requiring no disk access for queries, but are truncated on every server restart.
Test 1: Select = int with integer index
 Setup: 1000 row table setup identically in both MYSQL and PostGreSQL with an auto-incrementing primary key column (serial type column in PostGreSQL) with 1000 consecutive selects of random rows.
Results: Memory tables were the fastest type, though not by much compared to InnoDB’s other offerings. PostGreSQL’s selects lagged behind.
Test 2: Select = ’string’ with indexed varchar column
 Setup: 1000 row table setup identically in both MYSQL and PostGreSQL with an auto-incrementing primary key column (serial type column in PostGreSQL) and a varchar(32) (character varying in PostGreSQL) populated with random 2 character strings. The test does 1000 consecutive selects on the varchar column looking for random 2 character matches.
Results: Memory tables were the fastest type again, slightly ahead of InnoDB. MyISAM was noticeably slower in indexed string comparisons than it was with integer selects. PostGreSQL’s again lagged behind.
Test 3: Select like ‘%string%’ with indexed varchar column
 Setup: 1000 row table setup identically in both MYSQL and PostGreSQL with an auto-incrementing primary key column (serial type column in PostGreSQL) and a varchar(32) (character varying in PostGreSQL) populated with random 8 character strings. The test does 1000 consecutive selects on the varchar column looking for random 2 character matches within the 8 characters using the wildcard operator.
Results: Memory tables fastest. PostGreSQL surprisingly was the next fastest by a significant percentage over InnoDB and MyISAM, with InnoDB being the slowest by far.
Test 4: Inserts
 Setup: I did the insert test for inserting both integers and varchars into a table with an additional auto-incrementing primary key column, but the results were so similar that I’m only going to show the results from the integer insert test. The test did 100 consecutive inserts into the table.
Results: Memory tables were predictably the fastest, followed by MyISAM, and then PostGreSQL. Its worth noting that the sequence of PostGreSQL inserts were five times slower than MyISAM and Memory tables. The InnoDB inserts are very slow to the other table types, but its worth noting that is because of the row-level locking. In a highly active read and write environment, InnoDB may actually perform better than the other table types. This is because it does not have to wait for the insert to finish, when a select comes into the queue. With table types that lock on the table level, select queries can get backed up waiting for each insert to complete.
Overall Results
Memory tables are the fastest overall, but are impractical in many situations where data-integrity is a concern. Applications like chat rooms, or quick lookup apps may be ideal for memory tables. MySQL’s default database in version 5 is InnoDB because of its blend of performance and feature set. MyISAM may be faster in some situations, but with multiple request threads in high-write environments, InnoDB is usually faster. PostGreSQL’s performance was surprisingly unimpressive with the sole exception of a varchar wildcard lookup. Unless the performance of PostGreSQL significantly increases with its next release, MYSQL will remain my open-source database of choice.
Administering MySQL is not an easy task. The cryptic my.ini database settings file is hard to set correctly. Also, using the MySQL command line is rather intimidating if you don’t know exactly what you’re doing. Thankfully the good people at MySQL have provided us (the general public) with the MySQL GUI Tools to solve both of these problems for their community MySQL database server. If you haven’t used these tools before, you’re not alone – they are not included with the MySQL distribution and are often overlooked. They are available for Windows, Mac, and Linux and you can download the package here: http://dev.mysql.com/downloads/gui-tools/5.0.html.
One popular database administration tool is PHPMyAdmin. While very useful, PHPMyAdmin is web based and therefore requires a working PHP enabled server to function properly. MySQL GUI tools are very useful in this regard – they install anywhere (even the database server itself) and access your databases through a visual client. No web server needed!
The MySQL GUI Tools consist of the MySQL Administrator, Query Browser, Migration Toolkit, and the Tray Monitor. The most interesting and useful of the two (in my opinion) are the Administrator and the Query Browser.
The Administrator combines many useful features into one interface. You can completely administer your database settings through its menus – it will update the my.ini file for you automatically. It also gives you hints as to what these values should be. You can also administrate database users, view database activity, view database logs. The most useful feature of the Administrator is the ability to backup and restore databases using an easy user interface. You can even go as far to schedule backups, and the program will insert an item into your Windows scheduled tasks that will trigger a database backup at intervals of your choice. There are many commercial applications that charge for this capability.
AÂ screenshot of the database settings feature of MySQL Administrator
AÂ screenshot of the backup feature of MySQL Administrator
The MySQL Query Browser allows the execution of queries and the browsing of database structure of multiple databases at once. You can open up new tabs and have the results of different queries displayed in each one. You can even bookmark a query so it can be executed later. This feature would be perfect for the manual execution of various maintenance queries by database administrators.
You are also allowed to edit data using this tool, and it is done in a very intelligent way. You must go into edit mode before you are allowed to change data, preventing stray clicks from compromising data. When an item is changed, the surrounding box turns red. Your changes are not executed however until you click the apply changes button, adding a way of backing out of your changes easily.
A screenshot of the MySQL Query Browser
The entire MySQL GUI Tools package shares a common connection manager allowing you to keep multiple database’s connection information saved and at your fingertips.
Today during the course of some work, I came across a situation where I had to delete all tables from a MySQL database. Normally when working locally or on a machine where I have full access, I execute the “DROP DATABASE my_data” and then “CREATE DATABASE my_data” statements to completely delete all the tables in it. This works perfectly in this situation, as these statements will also drop all tables, leaving you with a fresh new database.
However, the database account I was issued on this MySQL server did not have the permissions to drop and create a database. In MySQL there is no
“DROP ALL TABLES” statement – this doesn’t exist in MySQL. There were over 100 tables that I had to delete, making manually executing the drop table statment for each table both time consuming and error prone.
To resolve the issue I came up with the following PHP script that will do this operation for you when executed.
Requirements: PHP 4+ and the MySQL extension enabled.
Note:Â If you are using views, this script will be unable to drop them along with the tables. If you don’t know what views are, don’t worry about this notice.
/* fill in your database name */
$database_name = "my_db";
/* connect to MySQL */
if (!$link = mysql_connect("db_host_name", "username", "pass")) {
die("Could not connect: " . mysql_error());
}
/* query all tables */
$sql = "SHOW TABLES FROM $database_name";
if($result = mysql_query($sql)){
/* add table name to array */
while($row = mysql_fetch_row($result)){
$found_tables[]=$row[0];
}
}
else{
die("Error, could not list tables. MySQL Error: " . mysql_error());
}
/* loop through and drop each table */
foreach($found_tables as $table_name){
$sql = "DROP TABLE $database_name.$table_name";
if($result = mysql_query($sql)){
echo "Success - table $table_name deleted.";
  }
  else{
    echo "Error deleting $table_name. MySQL Error: " . mysql_error() . "";
  }
}Remember not to leave this script sitting on your server longer than needed, as it is a serious security risk.
Planning on storing IP addresses in MYSQL or any other similar database for your PHP application? If you are like most, you probably were thinking of storing the IP addresses as strings in a varchar(15) row. Well, there actually is a much faster and more efficient way of storing IP addresses. The secret lies in PHP’s ip2long() and long2ip() functions which convert a dotted IP address to and from it’s long integer representation. The IP address can then be stored in the database as an int(11) format. When you need to work with the IP address later, simply convert back to the dotted format using the long2ip() function.
The long format is much faster to query for in MYSQL because you are searching an integer index as opposed to a string index. Make sure you don’t use “like” in your query or you could lose this speed increase, and make sure you include an index on this column.
Example code in PHP:
$ip_address="212.253.52.1";
$converted_address=ip2long($ip_address);
$sql="SELECT FROM ip_table WHERE ip = $converted_address";
$mysql_query($sql); In addition to a query speed increase, storing IP addresses as int(11) also has a disk space savings. In my test with 50,000 unique IP addresses, storing them as int(11) used 40% less disk space for the combined data and index than the varchar(15) format. Take note!
|
