cat slave diary

Month: January 2010

  • GaiaBB: Dog food coming soon

    It’s been a slow haul working on converting all the database access to PDO. There’s just so MUCH crap in there.

    So to keep things moving, I’m going to move www.gaiabb.com to run on the donated OLPCs at my home.

    More details here.

  • Parameter Pollution with JSON

    I’ve been playing around with JSON recently, and I’ve discovered that most JSON implementations allow parameter pollution. This might be obvious to JavaScript experts, it’s not immediately obvious to most folks as JSON is just so much line noise.

    {“varName”:value,”varName”:value2,”varName”:value3}

    In the systems I’ve tried injecting, value3 is the one taken. Now if you have a hand crafted JSON decoder and coupled with a simple validator that only checks the first value, say a simple regex, you’re going to get past validation fairly easily. All the other caveats regarding parameter pollution apply.

    Give it a try the next time you’re doing a gig and see if you can bypass validation and other rules. YMMV.

  • How to migrate to PDO without it hurting… much

    As we saw in the previous article, conversion to MySQLi is an awful lot of work. So let’s move to PDO.

    Step 0. Get PDO working with your database server

    Somewhere along the line, the PHP and MySQL folks decided to not be friends, so even though 99.99% of all PHP scripts require MySQL, in most cases, PDO doesn’t have MySQL support enabled.

    Check that PHP doesn’t already have PDO for MySQL ready to go:

    % php -i | grep -i pdo
Configure Command =>  '[...snip...]'
PDO
PDO support => enabled
PDO drivers => mysql, sqlite, sqlite2
pdo_mysql
PDO Driver for MySQL => enabled
pdo_mysql.cache_size => 2000 => 2000
pdo_mysql.default_socket => /tmp/mysql.sock => /tmp/mysql.sock
pdo_sqlite
PDO Driver for SQLite 3.x => enabled
    The default socket location is fine for a development workstation, but not so good for a production host. Change php.ini and my.cnf to make it safer, such as /var/run/mysql/mysql.sock
    If your PHP installation doesn’t show the above, whip out the compiler – there’s plenty of articles on the Interweb on how to get PDO and MySQL going. For now, let’s assume PDO and MySQL are good to go.
    Step 1. Conversion
    As in our previous article, it’s very tempting to just go through each file and make a 1:1 change from MySQL to PDO. That is actually a losing scenario.
    1. My non-trivial app has 1853 SQL queries littered through the code. At about 15 minutes to 30 minutes per query, with no test case, that’s about a year’s work with no change to the overall complexity of the app nor any improvements to the overall data architecture. Changing from one to the other is sure to introduce bugs.
    2. GaiaBB only has 13 tables. Using a DAO approach, with a list and search helper method for each (i.e forum list, search forum), that’s 6 * 13 = 78 properly written, tested DAO methods that need to be written from scratch. This is a saving of over 10 months work compared to just getting in there and getting my hands dirty.
    3. I can add security business requirements once to the DAO, such as fine grained access control, input validation, audit, and dirty output sanitization, thus fixing all my access control issues and dirty data issues in the same small piece of code. So each file that is converted to DAO gets more secure with no additional coding

    You’re probably wondering about “dirty output sanitization”. Sanitization is for the weak minded! No seriously, my database is 7 years old. There’s crud in there – I can’t trust it to be safe or good. There’s some nice tools out there like Arshan’s Scrubbr to scan and clean a database, but Scrubbr is not going to be able to decode BBCode and check to see if there’s a nasty [color] based XSS or CSRF attack in there. Additionally, some versions of my software and some buggy versions of MySQL == binary blob crappiness. Blobs coming out sometimes (but not always) need to be stripslashed(). Additionally, some versions of XMB used client side checks to prevent files of the wrong type to be uploaded. I’ve found a JavaScript malicious file in my production database. So it’s worth adding checks so that I don’t serve bad things to folks. You can’t do this if you have 100+ attachment related SELECT statements alone, not without lots of bugs and lots of code. Going the DAO way, means I can do it once, for every single use of the attachment sub-system.

    There’s gotta be a downside.

    It’s not all sunshine and roses. You should not open both a PDO and old style connection to the database for busy servers like mine – it more than doubles the time to serve the average script, the poor little database server gets whacked, and performance will drop.

    In GaiaBB, there’s a significant 7240 lines of shared code read in every single time a script does anything – header.php, functions.php, db.php, validation.php, constants.php, config.php, cache.php, english.lang.php, mail.class.php. So to only open ONE database connection requires these files to be ported to PDO first. But if I convert these files, every remaining single file totalling about 80,000 lines of PHP will also need to be converted.

    So my solution for GaiaBB is to create a side-by-side approach, using kernel.php for converted files instead of header.php. kernel.php includes PDO ready files rather than the old files. This temporarily makes GaiaBB approach the 90,000 line mark, but in the long run, it will allow me to make many of the scripts smaller and more object orientated. Once converted to DAO, I can simply eliminate many security checks within the code of each page, as the DAO will simply throw an exception if you’re not privileged to do something with the data you’re trying to work with.

    So my main piece of advice for you contemplating converting to PDO is to consider your data architecture. The old MySQL way is awful, buggy and insecure. Let’s exterminate it, but instead of standing still for months at a time, add in freebies like access control, audit, input validation and output sanity checking.

  • Converting your PHP app to MySQLi prepared statements

    Okay, you’ve got like a zillion SQL queries in your PHP app, and probably 95% of them have a WHERE clause, and you need to make them safe so people will still download and use your app. Because if you don’t fix your injection issues, I will rain fire on your ass.

    These are the steps you need to take to convert to prepared statements.

    Step 0. PHP 4 is dead. Upgrade to PHP 5

    All of the code in these samples is PHP 5 only. I will be using SPL and MySQLi, which are installed primarily on PHP 5.2.x installations. PHP 4 cannot be made safe, so it’s time to upgrade. This is non-negotiable in my view.

    If you’re using register_globals, you have to stop. Do not use a function to register them for you in PHP 5, it’s time to do proper input validation. This will actually take you longer than converting all your queries to prepared statements.

    To get a handle on this issue, what you need to do is:

    1. Turn on error_reporting(E_ALL | E_STRICT);

    2. Find all isset() and empty() and unless they are actually testing for a variable you’ve set, get rid of them. isset() and empty() are not validation mechanisms, they just hide tainted globals from view

    3. Go to php.ini, and turn register_globals off. It should already be off

    4. If your code has a construct like extract($GLOBALS) or some other mechanism to register globals, get rid of it

    5. php -l file.php. This will give you a first pass which you will need to clean up

    6. Use PHP Eclipse or PDT in Eclipse or the Zend IDE in Eclipse. This will give you warnings if you have uninitialized variables. Go to the properties, and make this into an error. Clean up all uninitialized variables

    7. Start each script like this:

    // Canonocalize
    $dirty_variable = canonicalize($_POST['variable']);
    // Validate
    $variable = validate($dirty_variable);
    // Use the variable
    $stmt = $db->prepare("SELECT * FROM table WHERE id = ?");
    $stmt->prepare("i", $variable);
    $stmt->execute();
    // and finally, if you need to output that sucker:
    echo htmlescape($variable, $locale, $encoding); // $locale is probably 'en', and $encoding is probably UTF-8 or ISO 8859-1

    Obviously, you need to canonicalize – that is make it the simplest possible form. If you have no idea about this extremely important topic, please consult the OWASP Developer Guide. Validation is essential. This replaces isset() and empty() and other mechanisms, with actual validation requirements. If you’re expecting an array of integers, make sure it’s an array of integers! If they have to be in a certain range, make it so. If the validation fails, put up an error message and do not proceed! This stuff is CS101, so please make sure you do this reliably for all variables without exception.

    Step 1. Make sure your hoster has MySQLi

    If your hoster is still running PHP 4, you need to see if they have the ability to run PHP 5. Most likely, your PHP 4 installation will not have ANY prepared statement compatible interface, like MySQLi or PDO. Of course, PDO is PHP 5 only… and it has a cool interesting feature – it emulates prepared statements for those databases that do not have support for it. But that’s for another post.

    How do you check? Use phpinfo(). Create this small file somewhere with a random file name and upload it to your host:

    <?php phpinfo(); ?>

    Run the file, and note if you have the MySQLi interface. If you don’t, you can’t upgrade to prepared statements. It’s time to wage holy war on your hoster to make sure they install PHP 5.2.7 with MySQLi and PDO with MySQL 5 client libs for you … and all your other shared hosting friends.

    Changing over to MySQLi

    The simplest part of this process is to move to MySQLi from MySQL:

    Instead of

    $db = mysql_connect($db…

    You have two choices: stay with functional MySQLi, or move to OO MySQLi. I think the latter is better, but that will be another post.

    $db = mysqli_connect($db..

    Now, this is where it’s important! You MUST check the value of $db for errors before continuing. You probably have this code today, but it’s important to realize that if $db == false, you didn’t get a connection.

    if ( $db == false ) {

    // Print up an error and stop

    }

    Simple Conversion

    You may be tempted to just use the MySQLi extension, and move all your queries to place holder versions. That’s okay, but it can be a lot of work. Trust me, I’ve tried.

    Although it seems like the easiest possible choice, converting MySQL queries to MySQLi’s prepared statements has a couple of issues.

    Gotchya: There’s no easy way to bind lots of results when you use select *

    With MySQL query, fetch_array will simply bind the current result set row to an associative array, and you can access it trivially. Most PHP apps use this data access pattern extensively, like this:

    while ( $row = mysql_fetch_array($query) ) {

    // do some work with $row

    $blah = $row[‘column’];

    }

    Which brings us to the next gotchya:

    Gotchya: There’s no fetch_array()

    I don’t know why MySQLi does not have this most common of all the data access patterns, but it’s a right pain to fix. So let’s get a function to emulate fetch_array, including using anonymous field names as per above.

    Okay, so we’ve decided that MySQLi sucks the proverbials… so in the next article, let’s talk about migrating non-trivial PHP apps to PDO.