Switch large website from MySQL to MySQLi [duplicate]

Question

I want to switch from MySQL to MySQLi, but I have a very large website.

I read that https://wikis.oracle.com/display/mysql/Converting+to+MySQLi could help me and I read How could I change this mysql to mysqli?. It says that I could replace most of the functions with just adding an 'i' to the function, and that I should start bughunting.

But my website is very complex and large, and it would take a very long time to check if everything works. So: what is the best way to switch from MySQL to MySQLi for a very large website?

Thanks!

Answer 1

There is no easy answer to your question as practically every simple way to do this involved doing things differently when the application was written.

If you have direct calls to mysql_* functions throughout your code and no database abstraction layer where you do your queries through a helper class or function then you will need to edit every command.

You cannot just get away with adding an i to commands like mysql_query as procedurally mysqli_query() requires the first parameter to be the link to the db where with mysql_query() if a connection was given at all, it was a second parameter.

Instead of just changing mysql_query(...) to mysqli_query($link,.....) I would recommend that there is no better time to put a db abstraction layer in place. So use functions eg sql_query() that actually process your queries so in future if you need to change DB again you can just update the db specific commands in one abstraction file. That way if you write a function that wraps mysqli_query then you could be able to simply rename your mysql_query() to your helper function and let the helper function worry about putting the link in there.

Whilst that is the simplest way, it will not bind parameters or prepare statements which is a major factor in preventing sql injection vulnerabilities

Once you have changed all these commands you need to test.

If you have no automated tests written then this is probably a good time to start writing them. Even though you will need to check that every change has worked, if you do it by automated test then you can avoid that pain in future.