The Depreceation Of The Mysql_* Extension

With the deprecation of the mysql_* extension in the current version of PHP and it’s likely removal from the next version of PHP, it got me wondering how many sites will break when the server they are hosted on migrate over to either version 5.6 or 6.0 of PHP. I would imagine that most (hopefully all) of the “off the shelf” CMSs such as wordpress, Joomla, Drupal, MediaWiki, phpBB, SMF, etc, without looking at their code will have migrated over to using either the mysqli_* extension or PDO and hopefully they all use prepared statements, but their will probably be sites running older versions of CMSs or custom CMSs that still use the mysql_* extension.

Whenever I see any examples of code here now that use the old mysql_* extension I always bring it to their attention that the mysql_* is deprecated and if I can’t see from the code snippet how they are sanitizing and escaping data, then I’ll advise them to make use of prepared statements. I always suggest they use PDO over mysqli as it’s easier to change db server software with PDO (just the connection needs changing and any query syntax specific to the old server software).

How many people have got sites or know of sites that still use the old mysql_* extension and have not started work on migrating over to either the mysqli extension or the more preferable PDO and started using prepared statements?

My gut feeling is that as sites on shared servers, as servers migrate over to php 5.6 (which is the version in which the mysql_* will not exist) or 6.0 (whenever that does come out), that many older sites that use the mysql_* extension will vanish from the net.

That is an article that explains how to migrate your code over to using PDO and prepared statements, it’s not hard, just time consuming. I’ve got 226 queries left to migrate to using PDO but I would imagine that some sites will have many more then that. So if you’ve got a site still using the old mysql_* extension, now is the time to migrate it over to PDO and to start using prepared statements. Would you rather spend time now migrating over, testing on your development server or find in a few months time when your site breaks when the server it’s on has the mysql_* extension removed and you’re panicking?

Well, I don’t have much to say about the move away from mysql_* to PDO. In my opinion PDO has been around as the supperior option for quite some time now, so I’m sure a great amount of large projects are on their way to implementing PDO if they haven’t. If if they are not, then I question the skill level and ability of the project developers to the point where I don’t think I’d be able to trust their software or the future of their project.

Having said that, I’m actually responding to the problem of having 100’s of queries to convert. In my experience, it’s best to use a data wrapper that has a consistant public interface. This way if there are any changes to how you interact with persistent data, or it’s API, all you have to do is change the back end code located in your wrapper.

This way your code is scattered with something like:

$sql->dbSelect($table, $what, $conditions);

All you need to do is keep the dbSelect back end consistent, meaning accepting and returning the same data. Now you only have one place to maintain.

This is an over simplified solution, and not the best in any way. But it’s a step in the right direction.

Personally I use Aura.Sql for the back end and have my own custom wrapper for the public interface. This way I can move to another database layer without having to break all of my existing code.

And to push it one step further, I’m actually researching data storage and have been moving away from concentrating only on SQL solutions. I’m looking into ORM’s, like Doctrine or Propel. Ideally the public interface I’m looking for will be completely removed from the storage engine I use.

Check this out, it’s about designing an application where the separete parts don’t need to know how the other is built, or what it’s doing. It just has to know what it should expect:

http://blog.8thlight.com/uncle-bob/2012/08/13/the-clean-architecture.html

I know I’ve brought the topic further down the road in the thought process than it started, but I’ve been giving this quite a bit of thought lately, and the main reason is because of the situation you point out. That is horrible, especially when the application is so coupled to it’s data source that you are not only changing one library of code, but you have to go through an application line by line changing queries?

Here’s a little write up I did a couple days ago regarding my thoughts and goals towards data storage, and where I hope my research will take me:

https://groups.google.com/forum/#!topic/alliancecms/hfkxpWS1UnA

It contains some links for further reading if you’re interested in the subject.

I feel what you’re talking about @SpacePhoenix ; . I’ve felt the pain of it, and that’s why I’m taking it as seriously as I am

Yeah, Id say having an ORM and Query builder will make things a lot easier, for legacy projects they are always slow to adapt to changes which cant be helped. Still, considering how many webhosts still run on PHP 5.2, I doubt most will switch over to PHP 5.5 and 5.6 anytime soon. The good news is that PHP 5.3 is finally the most widely used PHP versions nowadays, I mean at least its safe for a PHP software to abandon PHP 5.2 compatibility supports.

I converted all the mysql_ calls in my own code to mysqli_ calls some time ago (mostly by just adding the “i” and reversing the two parameters). That got them all to where they will continue to work once mysql_ is gone.

Updating all the calls to use more modern /more secure constructs is ongoing but no longer subject to the deadline of breaking when mysql_ is removed.

Am pretty fortunate. A few years ago I created a couple of custom objects ( one for reading , the other for writing) which handled all my mysqli connections and basic data manipulations and a third which was simply a registry of DBConns. I built most of my projects around those objects. It shouldn’t be too hard to tweak/ facade those 3 classes so that my old projects work perfectly on newer versions of PHP.

I guess it boils down to : were you doing any sort of OOP for your DB connections or not.

With my system, my only concern is improvement. My original ( current) concept was based on a one object one query paradign; that is : you passed it the components of your query/ query statement at instantiation… it put it all together, ran the query, stored the pointer, managed retrieval, etc.

So where my own object worked of this paradigm:

$posts=new RMCO ($table, $fields, [$where], [$ordLim],[$aux])
// the object would then build a query string  such as: "SELECT [B]*[/B] FROM `[B]roundTable[/B]` WHERE [B]`someID` IN  (50,15,5,10) AND  `ord`>15[/B] ORDER BY [B]`Name` DESC[/B]  ( the bold parts were passed via strings)

( and yes, I did have procedures to sanitize my strings before I instantiated my object)

THE PDO parading favours reusable queries , in essence:

$posts=new RMCO ($query,[$vars])
// this is the prepped statement 
// $query= "SELECT :somevar FROM :anotherVar WHERE  `someID` IN  (:someRange)  AND :someElse..." 
// this are the variables to be inserted
//  $vars = array(":somevar"=>'*', ':anotherVar'=>'roudTable', ':someRange'=>'50,15,5,10', ':someElse'=>15...)   

but I see that as a choice for future dev.

@dresden_phoenix: Good stuff.

My current implementation is a bit abstracted, meaning the public interface tries to focus on the data that needs to be manipulated, and on the actual manipulation that I want to do (fetch from the database, alter, insert, delete). I let the back end of the class methods worry about implementation. This way if I need to switch databases I can just alter the back end and not have to worry about changing the way I interact with it (that’s the plan at least).

Here’s an example of how you would use it:

 
$tableName = 'users'; 
 
$tableData = [ 
    'first_name' => 'John', 
    'last_name' => 'Doe', 
]; 
 
$sql->dbInsert($tableName, $tableData); 

I’ve come come to the conclusion lately that this is still very specific to the storage type (a MySQL table in this case), the how the data is represented in the storage type (columns and rows) and the method of data manipulation (sql queries).

I’m researching right now so I can design a way to pull back even farther from the back end implementation and just focus on what the developer wants to do with the data.

Here’s an example of what I’d like the end results to look like:

 
$entity = new Entity('user'); // The type of thing that's represented. Could be anything, i.e. a user, a blog post, a poll, a session cookie, an ad, etc... 
 
$entity->list('firstName'); // Will create an array of all first names in the persistent storage 
$entity->add([ 
    'firstName' => 'John', 
    'lastName' = 'Doe' 
]); 
 
$entity->remove->(['lastName' => 'Doe']); 

This is a very simplistic example, and will probably not be anything like the end result of what I create, but if you’ll notice, this could actually be used on any persistent storage system, i.e. sql database, NoSQL database, xml files, json, yaml, flat file, any cloud service, etc…

All you need to do is make and alter the back end for the storage system without having to change your production code. It also makes things easier for the developer, because now you only have to focus on what data you have, and on what you need to do with it, not on how to do those manipulations.

One issue that I’m concerned with is advanced queries where you have multiple data sources and need to relate them in a complicated manner, like using complicated queries and joins. Right now the best solution I have thought of was to have some type of interface where you can tap into the actual storage system and manipulate what you need to, but I might be able to come up with a more abstract and flexible way that’s independent of the storage system in use.

I was using mysqli with a wrapper round it but decided to migrate over to PDO as my site might one day end up on a server that doesn’t use MySQL. If I were to carry on using mysli with a wrapper I’d have to change all the database class in addition to any query that might use MySQL specfific syntax. Once I migrate over to PDO, if I end up in that situation I’ll still have to change any query that uses MySQL specific syntax but other then that It’d be just a single line change onlym just where I create an instance of PDO.

With a wrapper, if a move to a server without MySQL is forced with little warning, that could be hours or days downtime, by using PDO in that situation it would just be a few hours downtime.

I read with interest this thread and each and every post confirms that using a PHP Framework is far better than “re-inventing the wheel”.

I have just checked and these are the database drivers supplied with the framework that I selected to use way back in 2006.

Drivers: cubrid, mssql, mysql, mysqli, oci8, odbc, pdo, postgre, sqlite, sqlsrv.

The drivers have been extensively tested, debugged and updated.

The Active Record Class must be used and time spent in learning is well worth the effort:

http://ellislab.com/codeigniter/user-guide/database/active_record.html

Your mission today: Investigate a PHP Framework

I don’t think the problem is catastrophic to legacy projects. If mysql_* functions are gone then there’s nothing preventing anyone to provide a small library to bring back the functions and translate what they do to mysqli. It would be necessary to put a few require statements where necessary but still less work than having to change all occurrences of mysql_* functions.

Well for legacy projects, most aint gonna upgrade to PHP 5.5 and later versions anyway. Its strongly advised not to use mysql deprecated functions for new projects, and for newbies learning PHP/MySQL they better stick to MySQLi and PDO classes.

It has been flagged for removal for several years now. That’s plenty of time for any system to have made the necessary changes. It isn’t like the changes required to switch from mysql_ calls to mysqli_ calls is all that great. A short script to run against all the PHP code on a server could make 99% of the necessary changes automatically so even the biggest of projects could have been converted in a few of hours at any time since the proposed removal was announced. The only excuse for not having converted yet is laziness.

Or having been away from the game for a while and not being aware.