Monthly Archives: November 2010

Protect files within public folders with mod_rewrite and PHP

Here’s the problem. We have a legacy application (or a WordPress blog for the example) and we want to protect the access to the application according to our corporate single sign on. We can create a plug-in in WordPress to ensure only our single sign-on’s session cookie is activated.

With WordPress we can create a simple plug-in to perform our desired behaviour:

<?php
// wp-content/plugins/gamAuth.php
/**
 *  @package gamAuth
 *  @version 1.0.0
 */
/*
Plugin Name: gamAuth
Plugin URI: #
Description: Forces users to be authenticated
Author: Gonzalo Ayuso
Version: 1.0.0
Author URI: gonzalo122.wordpress.com
*/

function redirectExit() {;
 nocache_headers();
 header("HTTP/1.1 302 Moved Temporarily");
 header("Location: http://www.google.com");
 header("Status: 302 Moved Temporarily");
 exit();
}
function chechAuth() {
 // here we check our session
}

function ac_auth_redirect() {
 if (!chechAuth()) redirectExit();
}
if('wp-login.php' != $pagenow && 'wp-register.php' != $pagenow) {
 add_action('template_redirect', 'ac_auth_redirect');
}

This plug-in works, but what happen with the uploaded files? If we upload a PNG file and we show it in our post, our WordPress will create a direct link to the uploaded file in wp-content/uploads folder. That’s means if a non-authenticated user knows the url of the link, he will see the picture, because our simple plug-in does not affect to direct links. OK WordPress is open source. We can change the code and change the behaviour of link generation. Maybe there’s a plugin to do it (don’t hesitate on explain it to me, anyway) but if we don’t want to touch the WordPress code or maybe we cannot do it (because it’s a legacy application), here we are a simple trick to force authorization even over files within public folders, without change any line of the original application.

The trick is very simple. First we activate mod_rewrite in our server and create a .htaccess files in our wp-content/uploads folder:
An example with apache:

RewriteEngine on
RewriteBase /
RewriteRule !\.(php)$ /wordpress/wp-content/uploads/media.php

That’s means we are going to redirect every file request within or upload folder to a PHP script called media.php

And now our media.php will check the authorization.

<?php
$uri = $_SERVER['REQUEST_URI'];
$documentRoot = $_SERVER['DOCUMENT_ROOT'];
$filename = $documentRoot . $uri;
$pathParts = pathinfo($filename);
$mime = array(
 'jpe'  => 'image/jpeg',
 'jpeg' => 'image/jpeg',
 'jpg'  => 'image/jpeg',
 'png'  => 'image/png',
 'xls'  => 'application/vnd.ms-excel',
 'pdf'  => 'application/pdf',
 );
function chechAuth() {
 // here we check our session
}
$ext = strtolower($pathParts['extension']);
// there are built-in ways to check file mime type but
// they don't work fine, at least for me and I preffer to
// check it manually with the extension
if (is_file($filename) && array_key_exists($ext, $mime)) {
 if (chechAuth()) {
  header("Content-Type: " . $mime[$ext]);
  readfile($filename);
  exit();
 }
}
echo "HTTP/1.1 503 Service Unavailable";
header('HTTP/1.1 503 Service Unavailable', true, 503);

If you want to show big files such as video files, maybe you need to have a look to one of my older posts.

Now when our user tries to get wp-content/uploads/2010/11/img1.jpg from our server, the mod rewrite will forward the job internally to media.php and we will check the authorizations before showing the file,  and the user will see in the browser ‘s URL wp-content/uploads/2010/11/img1.jpg instead of media.php.
And that’s all. We can use this technique not only to protect our files. We also can dynamically add a watermark to our images without changing the original URL, show different image quality depends on the user or block a IP range to our media files.
Of course If we start an application from scratch there are better solutions but the good point of this trick is that it’s fully compliant with our legacy application and we don’t need to change anything inside.

Database connection pooling with PHP and gearman

Handling Database connections with PHP is pretty straightforward. Just open database connection, perform the actions we need, and close it. There’s a little problem. We cannot create a pull of database connections. We need to create the connection in every request. Create and destroy, again and again. There are some third-party solutions like SQL relay or pgpool2 (if you use PostgreSQL like me). In this post I’m going to try to explain a personal experiment to create a connection pooling using gearman. Another purpose of this experiment is create a prepared statements’ cache not only for the current request but also for all ones. Let’s start.

This is an example of SELECT statement with PDO and PHP

$dbh = new PDO('pgsql:dbname=pg1;host=localhost', 'user', 'pass');
$stmt = $dbh->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();

Basically the idea is to use a gearman worker to perform every database operations. As far as I known we cannot pass PDO instances from gearman worker to gearman client. Even with object serialization (PDO objects cannot be serialized). That’s a problem.

My idea is use the same interface than using PDO but let the database work to the worker and obtaining a connection id instead of a real PDO connection.

That’s is the configuration class. We can see It’s defined one database connection and two gearman servers at the same host:

class PoolConf
{
    const PG1 = 'PG1';
    static $DB = array(
        self::PG1 => array(
            'dsn'      => "pgsql:dbname=gonzalo;host=localhost",
            'username' => 'user',
            'password' => 'pass',
            'options'  => null),
    );

    static $SERVERS = array(
        array('127.0.0.1', 4730),
        array('127.0.0.1', 4731),
    );
}

We start the workers:

gearmand -d --log-file=/var/log/gearman --user=gonzalo -p=4730
gearmand -d --log-file=/var/log/gearman --user=gonzalo -p=4731

How many workers we need to start? Depends on your needs. We must realize gearman is not a pool. It’s a queue. But we can start as many servers as we want  (OK it’s depends on our RAM) and create a poll of queues. We need to remember that if we start only one gearman server we only can handle only one database operation each time (it’s a queue) and it will be a huge bottleneck if the application scales. So you need to assess your site and evaluate how many concurrent operations you normally have and start as many gearman server as you need.

Maybe is difficult to explain but the final outcome will be something like that:

use Pool\Client;
$conn = Client::singleton()->getConnection(PoolConf::PG1);

$sql = "SELECT * FROM TEST.TBL1";
$stmt = $conn->prepare($sql);

$stmt->execute();
$data = $stmt->fetchall();
echo "<p>count: " . count($data) . "</p>";

We must take into account that $stmt is not a “real” PHP statement. The real PHP statement is stored into a static variable within the worker. Our $stmt is an instance of Pool\Server\Stmt class. This class has some public methods with the same name than the real statement (because of that it behaves as a real statement), and internally those methods are calls to gearman worker. The same occurs with $conn variable. It’s not a real PDO connection. It’s am instance of Pool\Server\Connection Class.

// Pool/Server/Stmt.php
namespace Pool\Server;

class Stmt
{
    private $_smtpId = null;
    private $_cid    = null;
    private $_client = null;

    function __construct($stmtId, $cid, $client)
    {
        $this->_stmt = $stmtId;
        $this->_cid = $cid;
        $this->_client = $client;
    }

    public function execute($parameters=array())
    {
        $out = $this->_client->do('execute', serialize(array(
            'parameters' => $parameters,
            'stmt'       => $this->_stmt,
            )));
        $error = unserialize($out);
        if (is_a($error, '\Pool\Exception')) {
            throw $error;
        }
        $this->_stmt = $out;
        return $this;
    }

    public function fetchAll()
    {
        $data = $this->_client->do('fetchAll', serialize(array(
            'stmt' => $this->_stmt,
            )));
        return unserialize($data);
    }
}

The worker. The following code is an extract. You can see the full code here

# Create our worker object.
$worker= new GearmanWorker();
foreach (PoolConf::$SERVERS as $server) {
    $worker->addServer($server[0], $server[1]);
}

\Pool\Server::init();

$worker->addFunction('getConnection', 'getConnection');
$worker->addFunction('prepare', 'prepare');
$worker->addFunction('execute', 'execute');
$worker->addFunction('fetchAll', 'fetchAll');
$worker->addFunction('info', 'info');
$worker->addFunction('release', 'release');
$worker->addFunction('beginTransaction', 'beginTransaction');
$worker->addFunction('commit', 'commit');
$worker->addFunction('rollback', 'rollback');

while (1) {
    try {
        $ret = $worker->work();
        if ($worker->returnCode() != GEARMAN_SUCCESS) {
            break;
        }
    } catch (Exception $e) {
        echo $e->getMessage();
    }
}

function fetchAll($job)
{
    echo __function__."\n";
    $params = unserialize($job->workload());
    $stmtId = $params['stmt'];
    return serialize(\Pool\Server::fetchAll($stmtId));
}

function execute($job)
{
    echo __function__."\n";
    $params = unserialize($job->workload());
    $stmtId = $params['stmt'];
    $parameters = $params['parameters'];
    return \Pool\Server::execute($stmtId, $parameters);
}
...

The heart of the worker is \Pool\Server class. This class performs every real PDO operations and stores statements and connections into static private variables.

And now we can use the database pool reusing connections and prepared statements. You can see here a small performance test of reusing prepared statements in an older post.

I’ve also implemented a small error handling. Errors in the worker are serialized and thrown on the client simulating the normal operation of standard PDO usage.

Now a set of examples:

Simple queries. And a simple error handling:

include('../conf/PoolConf.php');
include('../lib/Pool/Client.php');
include('../lib/Pool/Server.php');
include('../lib/Pool/Exception.php');
include('../lib/Pool/Server/Connection.php');
include('../lib/Pool/Server/Stmt.php');

use Pool\Client;
$conn = Client::singleton()->getConnection(PoolConf::PG1);

$sql = "SELECT * FROM TEST.TBL1";
$stmt = $conn->prepare($sql);

$stmt->execute();
$data = $stmt->fetchall();
echo "<p>count: " . count($data) . "</p>";

try {
    $sql = "SELECT * TEST.NON_EXISTENT_TABLE";
    $stmt = $conn->prepare($sql);

    $stmt->execute();
    $data = $stmt->fetchall();
    echo "<p>count: " . count($data) . "</p>";

} catch (Exception $e) {
    echo "ERROR: " . $e->getMessage();
}

print_r(Client::singleton()->info(PoolConf::PG1));

Now with bind parameters:

<?php
include('../conf/PoolConf.php');
include('../lib/Pool/Client.php');
include('../lib/Pool/Server.php');
include('../lib/Pool/Exception.php');
include('../lib/Pool/Server/Connection.php');
include('../lib/Pool/Server/Stmt.php');

use Pool\Client;
$conn = Client::singleton()->getConnection(PoolConf::PG1);

$data = $conn->prepare("SELECT * FROM TEST.TBL1 WHERE SELECCION=:S")->execute(array('S' => 1))->fetchall();

echo count($data);

print_r(Client::singleton()->info(PoolConf::PG1));

And now a transaction:

<?php
include('../conf/PoolConf.php');
include('../lib/Pool/Client.php');
include('../lib/Pool/Server.php');
include('../lib/Pool/Exception.php');
include('../lib/Pool/Server/Connection.php');
include('../lib/Pool/Server/Stmt.php');

use Pool\Client;
$conn = Client::singleton()->getConnection(PoolConf::PG1);

$conn->beginTransaction();
$data = $conn->prepare("SELECT * FROM TEST.TBL1 WHERE SELECCION=:S")->execute(array('S' => 1))->fetchall();
$conn->rollback();

print_r(Client::singleton()->info(PoolConf::PG1));

Conclusion.

That’s a personal experiment. It works, indeed, but probably it’s crowed by bugs. You can use it in production if you are a brave developer :).

Source code.
The full sourcecode is available here at google code

Follow

Get every new post delivered to your Inbox.

Join 992 other followers