Converting larger number of sites from mysql to mysqli/PDO

Are there any tools out there to help me update a large number of old sites that were built with the old mysql functions?

 

There's this tool, not sure how well it works I've never tried it, could be worth a look though.
https://github.com/philip/MySQLConverterTool

 

Thanks i'll take a look - have over 50 sites, was only going to do a few that make money, but i know one day i will do a PHP/Mysql update and they will all stop working

 

Another option is to use a wrapper -where once the extension is removed, you place the library and it takes care of translating the existing removed methods into PDO or MySQLI. Here is one that I wrote for PDO:

https://github.com/AzizSaleh/mysql

However, if it is possible to re-write the code I advise doing that.

 

Just be warned most 'tools' and 'wrappers' do not ACTUALLY fix what's 'wrong' with the old mysql_ functions in the first place; that the connection is by it's nature global in scope, and that the code typically blindly pastes data values into the query string.

Part of the conversion to either mysqli or PDO should involve switching ANY query that passes data from variables as data to prepared queries with bind or execute-passed parameters -- and I'm HIGHLY skeptical of any automated tool providing that in a meaningful fashion. The tools and wrappers just perpetuate the bad practices which resulted in those old functions being told to kiss off in the first place!

It's like when target was deprecated so people started throwing scripttardery at recreating the functionality; COMPLETELY missing the point!

There's a difference between making it not hemorrhage errors or warnings, and actually fixing the code. Most "tools" will only do the former.

 

Somebody on stackoverflow is implying that swapping mysql_ to mysqli_ could be sufficient. How accurate is that?

http://stackoverflow.com/questions/1390607/how-could-i-change-this-mysql-to-mysqli (scroll to the first answer).

I have one site that will eventually require some updating, so I was just wondering if I can just swap those, debug it and forget about it.

 

there are a few commands that won't have 1:1 equivalents and will take some rewriting -- it should work with a bit of translation, but again it defeats the entire point of converting in the first place...

Since everyplace you see idiotic halfwit insecure nonsense like this (even the extra variable for nothing is a waste!):

$query="SELECT * FROM table WHERE id = '" . mysql_real_escape_string($_POST['id']) . "'";
$result = mysql_query($query);

Code (markup):

What SHOULD be done is this: (assuming $mydb is an initialized mysqli object)

$statement = $mydb->prepare('
	SELECT *
	FROM table
	WHERE id = ?
');

$statement->bind_param('i', $_POST['id']);
$statement->execute();

Code (markup):

Auto-sanitizing and if the server is configured properly the query is sent separately from the data. $statement and even $mydb can also be local variables restricting the scope of database access, further letting one secure things if you know what you are doing.

mysqli does have a procedural equivalent, but it's a wrapper for the object based version and generally speaking as a wrapper it just adds unneccessary overhead. Put on the big boy pants and do it right.

Still, as procedural mysqli:

$statement = mysqli_prepare($mydb, '
	SELECT *
	FROM table
	WHERE id = ?
');

mysqli_stmt_bind_param($statement, 'i', $_POST['id']);
mysqli_stmt_execute($statement);

Code (markup):

... and that doesn't even get into things like binding parameters. Really the behaviors that mysqli has that mimic the old mysql_ functions? Don't use them, it defeats the entire point of mysql_ going the way of the dodo.

Personally, I prefer PDO.

[code]$statement = $db->prepare('
	SELECT *
	FROM table
	WHERE id = ?
');
$statement->execute([$_POST['id']);

Code (markup):

But that's because you can pass an array of values to ->execute and it no longer ties you to JUST mysql.

 

Would you mind elaborating on how that is insecure and how it is different when using PDO?

From my understanding PDO has been using MySQL's native escaping functionality (same as mysql_real_escape_string) by default (unless you have
PDO::ATTR_EMULATE_PREPARES set to false).

 

I wouldn't use a tool. Personally if you have a large number of web sites using an obsolete programming style odds are there are other dated styles on those sites.... #1 you should look at these web sites to see if they are vulnerable to SQL injection attacks... if not, don't bother to change it. If so, you are going to have to invest some time and manual labor in to updating the code if it's important to you.

 

Which is one of the first values I set when using PDO... If for no other reason than emulated prepares being ridiculously inefficient when doing POEM (prepare once, execute mostly) and is STILL broken on passing more than one value to LIMIT.

Pasting values into the query string is stupid, always has been. Having the connection global in scope is stupid, always has been.

NOT as stupid as say... putting the connection info into undeletable global scope using DEFINE like turdpress does, but it's right up there on the herpafreakingderp scale.

After decades of working in other database scripting languages, seeing PHP dev's slap values into query strings left me wanting to pimp-slap whoever thought that was a good idea... much like seeing people wasting extra variables on NOTHING does. Hell even Clipper or dBase when you say the variable name you're saying THE VARIABLE NAME, not appending the value in the variable to the string!

I still think that's a functionality PHP is missing, being smart enough to have a query model where if there's a $ in the query string, it treats the label that follows as a automatic bindparam to that variable WITHOUT just blindly copying the value from that var.