Change to MySQLI?


#1

So i have tried many times to solve this error message.

The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /home/gathznzw/public_html/lib.php on line 55
[27-Aug-2018 01:01:37 America/New_York] PHP Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /home/gathznzw/public_html/lib.php on line 55

I have tried many times to solve it and mysqli breaks for me, i don’t know if it’s just “I don’t know what i’m doing” or if it’s the “open source” games Lib.php that is breaking it… here’s the code to change.

function opendb() { // Open database connection.

include('config.php');
extract($dbsettings);
$link = mysql_connect($server, $user, $pass) or die(mysql_error());
mysql_select_db('gathznzw_dk_') or die(mysql_error());
return $link;

}

function doquery($query, $table) { // Something of a tiny little database abstraction layer.

include('config.php');
global $numqueries;
$sqlquery = mysql_query(str_replace("{{table}}", $dbsettings["prefix"] . "_" . $table, $query)) or die(mysql_error());
$numqueries++;
return $sqlquery;

}

Any help is appreciated!


#2

And what have you come up with to solve it? All I see if deprecated code, without an attempt to do anything.


#3

All the changes i made were deleted days ago to replace with the still working version of this code


#4

You should be making and testing major changes to an offline copy of your web site.

It is not enough to just convert older code to use a different database extension, you must insure that the queries are secure to protect stored user information and protect your web site from being taken over by hackers.

Along with the deprecation and removal of the mysql extension, magic_quotes, which provided some protection against sql special characters in external data from breaking the sql syntax (which is how sql injection is accomplish) was already removed in php5.4.

The simplest and surest way off adding back this protection is to change any query that has external/unknown data values being put directly into it, into a prepared query.

The php PDO extension is also much simpler and more consistent then the php mysqli extension, especially concerning prepared queries, and for the case of writing a general purpose query method (see below), is several times faster than the msyqli extension since it takes almost no code to do this using the PDO extension, whereas it takes a large amount of code with the mysqli extension.

The overall simplest way of converting old code (or writing new code) is to extend the PDO class and add a general purpose query method, that accepts the sql query statement and an optional array parameter of input data. The code in the query method runs a non-prepared query if there is no input data and prepares and executes the query if there is input data, supplying the array of data as a parameter to the execute method call.

To convert any query that has external/unknown data being put directly into it to use this general purpose query method, you would just replace each php variable with a ? place-holder, remove any single-quotes around the variable, remove any quotes/concatenation dots, and make an array consisting of the variable(s) you just removed from the query.

An example -

// existing query
$sql = "some query that has php variable(s) in it '$some_variable'";

// converted query and call the general purpose query method
$sql = "some query that has php variable(s) in it ?";
$stmt = $pdo->query($sql,[$some_variable]);

When converting old mysql_ based code, while it is not the optimum solution, you can perform a search and replace on the msyql_ functions in your code to slightly rename them, then write user functions for these newly named functions that use PDO statements internally. Edit: in fact, I recall seeing a link on a php help forum recently to some functions that adequately did this. Perhaps you can search and find something that will work for you.