Easy way to switch for mysqli instead of mysql (adodb)

November 12, 2017 at 4:26 pm

Still many scripts makes use of ADODB database abstraction layer for PHP and msyql database type.
However since PHP version 5.5 Mysql is depreciated and completely left in PHP version 7.x.

ADODB does support MySqli datatbase type, which is supported by all newer PHP versions, but often this requires (at least) to change old “mysql_real_escape_string” to “mysqli_real_escape_string” function in all php files, in mutliple possible places.

Simple, but it’s hard to imagine to do it manually for hundreds possible php files and many possible lines in each.

If you have access to your server’s ROOT (dedicated and VPS servers), you can do it using one line SSH command for all files fast and 100%!

Most popular SSH clent is PuTTY, available to download free at http://www.putty.org/
You need to know your server’s ROOT access (password) and SSH port (it’s 22 most often).
Now you can open PuTTY terminal and connect your server as ROOT.
Find your website directory and navigate to, eg.
cd /home/domain_name/public_html

Now you can use command which will replace “mysql_real_escape_string” to “mysqli_real_escape_string” in all files recursively. Here it is:

grep -rl "mysql_real_escape_string" * -R | xargs sed -i 's/mysql_real_escape_string/mysqli_real_escape_string/g'

Check (edit) any php file if function name was replaced indeed.
Finally you need to find in your script where addodb connection is initialized and set new “mysqli” database type instead of “mysql”.
This can be a line like one below:

$conn = ADONewConnection('mysql');

Unlike mysql_real_escape_string(), mysqli_real_escape_string() function requires 2 parameters, where first is mysqli resource ID. Unforunately ADODB php layer doesn’t give option to extract such ID in simple way. Solution is to use “qstr()” ADODB function instead of mysqli_real_escape_string(). Webmaster must only be sure that his query doesn’t contain quotes for the string to use as qstr parameter (or opposite, can manually update mysqli driver code not to use quotes in mysqli driver function).

Different scripts may work different,can be mix of mysql and mysqli, can use functions in different way.
In video scripts like AVS or Clipshare that we are familiar so well, you can easily change database type in file “/include/config.db.php”. To use well working ADODB qstr function, you need to replace all “mysql_real_escape_string” using command like:

grep -rl "mysql_real_escape_string" * -R | xargs sed -i 's/mysql_real_escape_string/$conn->qstr/g'

Since ADODB qstr function automatically adds quotes arounf query, and AVS/Clipshare script already does same in php code, the easiest way will be to modify mysqli “qstr” function and remove quotes form it.
In scripts that additionally make use code or functions with plain mysql connection, this must be modified to use mysqli. In AVS/Clipshare script for example this is sessions class, conversion class and in several other files/places where database query does not use ADODB.

Basically it’s not very difficult to switch your script to use mysqli instead of mysql with ADODB library, but requires some experience and knowledge about script’s code.


This is not complete solution for most of scripts, including AVS or Clipshare script.
Many scripts apart of ADODB sql commands may also use plane sql connection and commands. In 2 mentioned scripts this happens at least two php classes. Such classes must be rewritten for mysqli usage. Without good php experience we do not suggest to do it yourself.

Being honest I am scared to post full working solution for a script like AVS or Clipshare. First of all there can be some custom code which requires individual solution, and second, we cannot be responsible what some user does with his website code and prefer to avoid claims or requests that we cannot fulfill.
But if you really need to switch to mysqli, we are ready to offer such service for reasonable fee.