Just thought I'd write a quick post about this. I know, I know it's 2016 and anyone still running code hitting MySQL databases via the mysql_ functions should probably be beaten with sticks, but there's a lot of it still out there in the wild and some poor PHP devs will have to clean it up. I just wanted to write a bit about my experiences when recently moving some large websites coded in 2003 on to a PHP 7 / MySQL 5.7 server.
The awkward bit... why a re-write isn't an option
First I'll admit that what I wanted to do was move all of the code to PDO and use prepared statements to do it properly. The problem with that is that the websites in question are littered with thousands of raw queries of varying complexity that are all running through the mysql api, thousands of calls to mysql_query, mysql_fetch_array, mysql_num_rows etc etc. The websites in question are generating revenue but it's declining steadily, they're not holding any sensitive user data and they're not a big part of the future strategy of the business that owns them.... so, there's not much appetite (or money) for a ground up re-write here.
My findings...
Search replace mysql_ with mysqli_ - bish bash bosh, job done
This was my initial plan - on the face of it it seemed perfect, a few carefully crafted search / replace commands and it's done. The problem with this is that the mysql_ and mysqli_ api functions aren't like for like replacements. For the most part the mysqli functions require the connection instance to be passed in as a parameter whereas the good old mysql equivalents didn't enforce this, they just used the last open mysql connection if nothing was passed in. That's not such a big deal though you can just throw the connection identifier up into the GLOBAL space and reference that (nobody said this was going to be a pretty solution) - except you can't just do this and tack it on the end of each function call as for the most part the parameters are now swapped around. So before you know it that simple search replace you were about to dispatch is looking like something that could do untold damage as it tries to re-arrange all of these functions across thousands of files.
There must be an easier way...
Clearly that wasn't going to work so I decided to dig deep into my programming knowledge and come up with a clever solution. That seemed like hard work after 5 minutes of thinking so I Google'd with the aim of leveraging (copying and pasting from) Stack Overflow (love that @ThePracticalDev picture).
I read through a bunch of threads about the issue and the usual righteous rants from experts who'd never code this way, and let's face it probably aren't going to be asked to solve something like this as they're flat out working on multi-million line code bases. I eventually stumbled on this github repo with a promising looking converter for just this purpose.
I've used this on a few sites now and it's done the job very nicely. I've modified a few things (mainly the way it treats mysql_select_db) but it's saved me countless hours. As mentioned above the solution isn't pretty but it might just save you some time, especially on projects where the code isn't mission critical but would be nice to have running on PHP7. It's also a good opportunity to scour that code for vulnerable lines that aren't handling user input as carefully as they could be. Something I've done before is search out all POST GET COOKIE references, dump them into a file and go through each one tracing it through to any queries, making sure it's type cast or sanitised before getting near the SQL lexer. It's time consuming but it's better that you discover that un-sanitised variable before a bot does.
Here are some other things you might have to deal with on this upgrade path:
- eregi_ functions: These regex functions also are also gone in PHP 7, replace with preg functions, easy to do.
- MySQL Strict Mode: 5.7 defaults to Strict mode, older projects and websites that aren't coded for it will be a total PITA to convert, I've disabled it in my.cnf and enable it per session for other sites sharing the same server.
- PHP short tags: These can trip you up on a move from early 5.x versions where they were probably enabled. Ideally you should root these out and switch them, it's one of the easier jobs.
Some commands you might find helpful:
find -type f -print0 | xargs -0 grep -i 'eregi('
find -type f -print0 | xargs -0 grep -i 'eregi_'
//recursively find all eregi functions
find -type f -print0 | xargs -0 grep -i 'mysql_'
//same for mysql functions
find -type f -print0 | xargs -0 grep -rn "<?[^p]"
//find php short tags
I hope that's of some use to someone faced with the task of converting those ancient PHP projects that none of us like to talk about. Ideally none of this kind of code will ever be written again, but rather than delete it all, rewrite it all or deny its existence it can live on on PHP 7 servers!
If anyone needs help with this kind of stuff I can get involved and help you get it done, I won't question the quality of the code, I promise.
