benchmarking .htaccess performance

Introduction:

One of the common tips to increasing Apache performance is to turn off the per-directory configuration files (aka .htaccess files) and merge them all into your main Apache server configuration file (httpd.conf).

Jeremy raised an interesting question about when the performance loss caused by using many htaccess files is offset by the ease of maintenance. He’s arguing – and I agree – that it makes sense to keep the configuration locally inside .htaccess files, despite the performance loss as these are easier to maintain.

It’s fairly logical that the multiple .htaccess file route will be slower – for every node in the request URI, the webserver has to look for an .htaccess file and merge the rules found in every one. So, we’re going to have to have a filesystem seek’n’read for every subdirectory.

However, is this a major issue? How much of a performance hit is there? Let’s find out:

Set-up:

Ok. Let’s make two docroots each with the same structure and files.

  1. htdocs_access – the .htaccess version. This has one .htaccess file in the leaf directory.

  2. htdocs_config – the httpd.conf version. This has the same rule as the above, but the rule is in the server-wide httpd.conf file and htaccess support is turned OFF (AllowOverride None).

Next, we need to get the .htaccess/httpd.conf files to do something ( mainly so we can see if Apache’s merged them in ). So, we’ll make a number of files in the last random directory (the leaf node), and give half of them the extension .foo, and the other half .bar. We’ll then tell Apache to process the .bar’s with PHP, and the .foo’s as text. All files will have the same content:

Here’s the (python) code I used to generate this structure:

import os

# where we'll place the generated structure
  
staging = '/Users/simon/server'
htdocs_access = os.path.join(staging, 'htdocs_access')
htdocs_config = os.path.join(staging, 'htdocs_config')

# how deep to go!
dir_depth = 10

# how many files in the leaf node of the dir.
num_files = 50

# what content to put in the files
content = ""

# the actual htaccess file
htaccess = ""
AddHandler application/x-httpd-php .bar
""  
# make directory structure
  
dir = ""
for dirnum in range(0, dir_depth):
    dir = os.path.join( dir, str( dirnum ) )
    hta = os.path.join( htdocs_access, dir )
    htc = os.path.join( htdocs_config, dir )
    os.makedirs( hta )
    os.makedirs( htc )

    # make the files:
    for filenum in range( 0, num_files ):
        # assign the file types -- half .foo, and half .bar
        if filenum % 2 == 0:
            filename = '%d.foo' % filenum
        else:
            filename = '%d.bar' % filenum
        f = open( os.path.join( hta, filename ), 'w+' )
        f.write( content )
        f.close()

        f = open( os.path.join( htc, filename ), 'w+' )
        f.write( content )
        f.close()

    # now, add the .htaccess file inside the lead htdocs_access dir
    f = open( os.path.join( hta, '.htaccess' ), 'w+' )
    f.write( htaccess )
    f.close()

# and we'll place it in the root of the htdocs_config dir as
# httpd.conf to remind ourselves to add it to the httpd.conf file
  
f = open( os.path.join( htdocs_config, 'httpd.conf' ), 'w+' )
f.write( htaccess )
f.close()

Here’s what we end up with:

0/
1/
2/
3/
4/
5/
6/
7/
8/
9/
0.foo
1.bar
10.foo
11.bar
(...etc...)
6.foo
7.bar
8.foo
9.bar

Where htdocs_access has a .htaccess file in 9/ and htdocs_config doesn’t.

Server Configuration:

Here are the two httpd.conf files for the configurations:

htdocs_config httpd.conf:

ServerRoot "/usr/local/apache2"
PidFile logs/httpd.pid
Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
DirectoryIndex index.html
AccessFileName .htaccess
HostnameLookups Off
AcceptMutex fcntl
StartServers 5
MinSpareServers 5
MaxSpareServers 5
MaxClients 100
MaxRequestsPerChild 10

User nobody
Group #-1
DocumentRoot "/Users/simon/server/htdocs_config"
LoadModule php5_module modules/libphp5.so
Listen 8111
<directory>
    Options Indexes FollowSymLinks
    AllowOverride None
    AddHandler application/x-httpd-php .bar
</directory>

htdocs_access httpd.conf:

ServerRoot "/usr/local/apache2"
PidFile logs/httpd.pid
Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
DirectoryIndex index.html
AccessFileName .htaccess
HostnameLookups Off
AcceptMutex fcntl
StartServers 5
MinSpareServers 5
MaxSpareServers 5
MaxClients 100
MaxRequestsPerChild 10

### Section 2: 'Main' server configuration
  
User nobody
Group #-1
DocumentRoot "/Users/simon/server/htdocs_access"
LoadModule php5_module modules/libphp5.so
Listen 8111
<directory>
    Options Indexes FollowSymLinks
    AllowOverride All
</directory>

Results:

Benchmarking was done with “ab” the Apache Benchmark program, which was set to access one page 1,000 times with 10 concurrencies. Each configuration was benchmarked five times in random order (to minimise the effect of any running background processes etc).

<th colspan="2">
  htdocs_config
</th>

<th colspan="2">
  htdocs_access
</th>
<th>
  Time Taken (s):
</th>

<th>
  Requests per Second:
</th>

<th>
  Time Taken (s):
</th>

<th>
  Requests per Second:
</th>
<td>
  12.683213
</td>

<td>
  78.84
</td>

<td>
  13.21618
</td>

<td>
  75.66
</td>
<td>
  12.854491
</td>

<td>
  77.79
</td>

<td>
  13.574916
</td>

<td>
  73.67
</td>
<td>
  11.777676
</td>

<td>
  84.91
</td>

<td>
  13.163296
</td>

<td>
  75.97
</td>
<td>
  13.668398
</td>

<td>
  73.16
</td>

<td>
  12.26475
</td>

<td>
  81.53
</td>
<td>
  13.76753
</td>

<td>
  76.47
</td>

<td>
  13.264527
</td>

<td>
  75.39
</td>
<th>
  12.9
</th>

<th>
  78.23
</th>

<th>
  13.1
</th>

<th>
  76.4
</th>

So – we’re looking at a difference of around 2.3% extra requests per second when htaccess files are disabled. This is really quite trivial, and should only be worried about when you’re really loaded.

Issues:

There are a number of areas where this could be improved:

  • Try different directory depths i.e. the more nested the directory is, the slower it should be under the .htaccess scenario. In contrast, if there’s only 2 or 3 levels then it should be faster.
  • Have multiple .htaccess files in the intermediate nodes to see how Apache handles the merging of these files. Here we’ve just used one .htaccess file, and we should probably see further slowdowns if Apache has to merge some complicated rule sets.
  • Access different files – I just requested one file repeatedly, so we might be getting a lot of interference from any caching systems (harddrive, ram, php caches etc) that I forgot about. Additionally, requesting multiple URI’s is a more realistic test case for a webserver.

process a url query string in PHP

PHP Function to parse a url and extract its arguments.

  
  
function process_url( $url ) {
    $processed_url = parse_url( $url );
    $query_string = ;
    # split into arguments and values
    $query_string = explode( '&', $processed_url[ 'query' ]);
    $args = array( ); // return array
    foreach( $query_string as $chunk ) {
        $chunk = explode('=', $chunk );
        # it's only really worth keeping if the parameter has an argument.
        if ( count( $chunk ) == 2 ) {
            list( $key, $val ) = $chunk;
            $args[ $key ] = urldecode( $val );
        }
    }
    return $args;
}

$url = 'http://www.google.co.nz/search?q=simon+rocks!&start=0&ie=utf-8&oe=utf-8&client=firefox-a&rls=org.mozilla:en-US:official';

$result = process_url( $url );

print_r( $result );

Will result in something like this:

  
Array 
(
    [q] => simon rocks!
    [start] => 0  
    [ie] => utf-8
    [oe] => utf-8
    [client] => firefox-a
    [rls] => org.mozilla:en-US:official
)
  

Protecting MySQL from SQL Injection Attacks with PHP.

This is intended as a brief guide to protecting your MySQL database from SQL injection attacks. Unfortunately, a large amount of the code that I’ve seen written by people on forums, and in countless crappy PHP tutorials lurking around on the net, and in the many websites that display the magic breeding slashed-quote (''' see below) show that many people just do not understand what’s going on and how to protect themselves against SQL injection attacks.

In fact, the only reason that many websites are ‘protected’ is due to magic quotes, and given that this is due to be disabled in the forthcoming PHP6, then there’s going to be some major problems cropping up.

I’ll talk about the problem of SQL injection, the half-hearted attempt to fix it with these ‘magic quotes’, and what you should really be doing EVERY TIME you send user inputted data to your database.

The Problem What is SQL Injection:

As the name suggests, SQL Injection is quite simply, when the user injects SQL into your application. How does this happen? Say we have a nice simple login form that takes a username and password, and checks if that’s in the database. If it is, the user is logged into an admin section or something. The code for this could look something like this:

 
// user and password come from a simple POST'd form
  
$user = $_POST[ 'user' ];
$password = $_POST[ 'password' ];

$query = "SELECT name, age, credit_card FROM usertable WHERE username = '$user' AND password = '$password' ";

$result = mysql_query( $query );

// check if mysql found anything, and get the record if it did
  
if ( mysql_num_rows( $result ) > 0 ) {
    $data = mysql_fetch_assoc( $result );
    echo 'Hello '.$user.'!';
    echo 'Your credit card number is '.$data[ 'credit_card' ].'';
} else {
    echo 'Incorrect Username or Password! Go Away!';
}

Ok. This works, BUT it’s about as safe as juggling with scalpels. If I enter “simon” as my username, and “secret” as my password, then the query that goes to MySQL looks like this:

SELECT name, age, credit_card FROM usertable WHERE username = 'simon' AND password = 'secret'

and I get logged in quite happily. Fantastic.

The problem comes when I start entering other characters. Let’s say that the next user who tries to login is Peter O’Reilly. Naturally he’ll want a username something like PeterO’Reilly. If we plug that into our query we get this:

SELECT name, age, credit_card FROM usertable WHERE username = 'PeterO'Reilly' AND password = 'secret' 

MySQL blasts along quite happily and hits username='PeterO’, and then it gets this “Reilly” thing which it doesn’t know what to do with and this happens:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Reilly' and password="secret"; " at line 1

Nice. We have a broken website, Pete can’t login, and he’s left with misgivings about our web programming skills.

Even worse ' what happens if I enter my password as this?

' or 1=1 ; --

The query that gets sent to MySQL will look like this:

SELECT name, age, credit_card FROM usertable WHERE username = 'simon' AND password = " or 1=1 ; " '

What does this mean? It tells MySQL to find all rows with a username equal to “simon” and a password equal to an empty string OR “1=1”. To represent that a bit more logically:

( username = “simon” and password = "” ) || ( 1 = 1 )

Now, 1=1 is always going to be true, so this is equal to:

( false ) || ( true )

Which means that ALL the records in the table will get returned. Our login processer above is going to log me on with someone else’s credentials – in fact, those of the first record returned.

Keep in mind, however, that we don’t need to escape numbers, and we shouldn’t put quote marks around them (it’s not standard SQL)- if a variable is a number, then it’ll be fine.

The crap attempt to fix it ( magic quotes ):

How to fix this? We need to be escape these quote characters ( both single and double quotes, as well as backslashes). This is done by putting a slash in front of them, e.g. so a ' becomes a ', and MySQL can work out that that quote mark is “protected” by the slash, and is part of the value and ignores it. So, Peter’s attempt to login becomes:

SELECT name, age, credit_card FROM usertable WHERE username = 'PeterO\'Reilly' AND password = 'secret';

and my attempt to hack my way in becomes:

  
SELECT name, age, credit_card FROM usertable WHERE username = 'simon' AND password = '\' or 1=1 ; -- ';
  

MySQL now thinks my password is the string

' or 1=1 ;

and I won’t be able to login.

So where do we get these slashes? Since around PHP version 3.06, PHP tries to do this for you, with a setting called “magic_quotes”. What this does is to automatically add slashes to anything coming in via HTTP get or post requests and via cookies. You can also do this manually using the addslashes() function.

But do not rely on this!

  • It could be turned off or on, or not present in your version (and it won’t be in PHP6). Therefore YOU CANNOT RELY ON IT, AND HAVE TO HANDLE THIS YOURSELF.
  • This means that if you rely on magic quotes, then your code is not portable. Unfortunately, escaping things with a slash is one of those irritating non-standard MySQL features. Most other databases which follow the SQL standards (like Postgres), escape things with another single quote ( O’Reilly => O"Reilly ).
  • It’s crappy. It doesn’t work well with extended characters, and these can be used to get around the slashes. See Chris Shiflett’s discussion of this problem
  • It’s irritating ' it pollutes your data with stuff that the user didn’t enter. This is the major cause of the magic breeding slashed-quote.

(Aside) The Magic Breeding Slashed-Quote:

I’m sure you’ve all seen websites that have this really annoying habit of messing up their user's post's quote marks ( just like that ). I’m calling this the magic breeding slashed quote, because these things propagate like crazy. What’s happening here is that the hard working web developer is adding slashes to the data they send to their database ' great! BUT, they’re not checking for magic quotes, so PHP is escaping the ' once to ', and then when the website runs addslashes() again, php sees a backslash AND a single quote which need escaping ( remember that the three characters that get escaped are , ‘, and " ). This therefore becomes \'. MySQL comes along and sees an escaped backslash AND an escaped single quote.

Here’s what’s happening:

  1. user input: O’Reilly
  2. magic quotes: O'Reilly
  3. addslashes: O\'Reilly
  4. MySQL processes this to: O'Reilly

and we end up with O'Reilly stored when we really want O’Reilly.

I’ve actually seen applications which quite happily store O'Reilly, and stripslashes() before they display the data ' this is just blindingly stupid.

Fixing it.

So, we need a way of escaping data that isn’t crappy, isn’t as prone to character set issues, and isn’t prone to magic breeding slashed quotes.

There are two ways to do this.

  • Use better slashes ( PHP4, old mysql client library using the mysql_* functions )
  • Use a better technique ' bound parameters ( PHP5 with the new mysqli_* client library)

Using better slashes ' mysql_real_escape_string( ) (PHP4, mysql_* )

If you’re using the old mysql client library ( i.e. the mysql_* functions ), then you have to use the hideously named mysql_real_escape_string() function. This takes into account the character set of the database connection and should handle things appropriately.

Note: mysql_real_escape_string needs an active database connection, or anything sent to it will disappear ( WTF? ), or it will generate an error.

BUT we still need to check for the evil magic_quotes setting, which and remove it. We can do this with the get_magic_quotes_gpc() function ( “gpc” refers to Get, Post, and Cookies which magic quotes operates on ).

So ' something like this:

// remove the pesky slashes from magic quotes if it's turned on
  
function clean_string( $value, $DB ) {
    if ( get_magic_quotes_gpc() ) {
        $value = stripslashes( $value );
    }
    // escape things properly
    return mysql_real_escape_string( $value, $DB );
}

$string = "O'Reilly";
// where $db is your active database connection resource id.
$safe_string = clean_string( $string, $db );

There’s a function described in the PHP manual called quote_smart, that handles this and handles both strings and integers:

// Quote variable to make safe
function quote_smart($value) {
    if ( get_magic_quotes_gpc() ) {
        $value = stripslashes( $value );
    }
    // Quote if not a number or a numeric string
    if ( !is_numeric( $value ) ) {
        $value = "'" . mysql_real_escape_string($value) . "'";
    }
    return $value;
}

Note that you’ll need to implement this yourself, and you’ll have to rewrite your queries to not have quotes in them e.g.:


$variable = "O'Reilly";
  
$variable = quote_smart( $variable );
  
// note that we haven't surrounded $variable with quote marks in
  
// the query below since quote_smart does that for us.
  
$query = "SELECT x, y, z FROM tablename WHERE user = $variable";
  

However, this leaving quote marks out of the query irritates me enough, that I generally just type-cast anything which should be a number to a number:

function clean_int($i) {
    if ( is_numeric( $i ) ) {
        return ( int ) $i;
    }
    // return False if we don't get a number
    else {
        return False;
    }
}

Warning:

This is NOT foolproof. In fact, if the attacker can change the character set on the fly, then this whole system can be avoided. Ilia Alshanetsky has an excellent write up on this.

Fixing it with better technique ' bound parameters ( PHP5, MySQLi ):

So ' the best solution? Use bound parameters. To use these you’ll need to be using the improved mysqli library that comes with PHP5. This technique differs slightly in that you define a query “template” first with placeholders, and then “bind” the parameters to it, and the mysqli library takes care of the appropriate escaping for us:

  
$variable = "O'Reilly";
  
// prepare the query
  
$query = $mysqli->prepare( "SELECT x, y, z FROM tablename WHERE user = ?" );

// bind a parameter ' here the first parameter is a short string that specifies the type that the
  
// subsequent arguments should be:
  
// 's' means a string
  
// 'd' means a double
  
// 'i' means an integer
  
// 'b' is a blob
  
$query->bind_param( 's', $variable );

// execute query:
  
$query->execute( );

// so if we had a more complex query, which updated the user info // with "favorite_color" (a string), "age" ( an integer ) and
  
// "description", a blob:

$query = $mysqli->prepare( "UPDATE tablename SET favorite_color = ?, age = ?, description = ? WHERE user = ?" );
  
// we would have a bind looking like this:
  
$query->bind_param( 'sibs', 'red', 27, $some_blob, $variable );
  
$query->execute();
  

Another benefit of this method is that it’s faster to transfer data to the db server. Harrison Fisk has a good discussion of these here.

Another thing to keep in mind:

Now, properly using mysql_real_escape_string or prepared statements should keep you pretty safe, but there are a few characters you might also want to watch out for:

The Percentage Sign (%)

The percentage symbol is commonly used by MySQL to perform LIKE queries ' this WON’T get escaped. If your application is doing LIKE comparisons, and your database is large, then it’s worth checking for this specifically to avoid a friendly user entering “%” and making your database grind to a halt ' e.g.

  
$user_input = '%';
  
$query = "SELECT x,y,z FROM tablename WHERE user LIKE '%$user_input%';
  
// becomes LIKE %%% -> and returns all rows in tablename.
  

Edit: 15th August, 2006 '

James Laver has written a nice lightweight database access class for MySQLi which takes care of the binding of parameters for you.

Edit: 9th November, 2006 '

Fixing a link, thanks Peter 🙂

-Simon

All Posts by Category or Tags.