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 support => enabled
PDO drivers => mysql, sqlite, sqlite2
PDO Driver for MySQL => enabled
pdo_mysql.cache_size => 2000 => 2000
pdo_mysql.default_socket => /tmp/mysql.sock => /tmp/mysql.sock
PDO Driver for SQLite 3.x => enabled
- 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.
- 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.
- 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
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.