Asked  7 Months ago    Answers:  5   Viewed   27 times

Playing Devil's Advocate a little here as I stopped using these functions a while ago, but the question is genuine and probably matters to a lot of SO users.

We all know that using mysql_ functions in the wrong way can be very dangerous, it can leave your website vulnerable, etc. but used correctly these functions can be protected against SQL injection and are actually a fair bit faster than the newer PDO functions.

Bearing all this in mind, why have the mysql_ functions been deprecated?

 Answers

29

The mysql extension is ancient and has been around since PHP 2.0, released 15 years ago (!!); which is a decidedly different beast than the modern PHP which tries to shed the bad practices of its past. The mysql extension is a very raw, low-level connector to MySQL which lacks many convenience features and is thereby hard to apply correctly in a secure fashion; it's therefore bad for noobs. Many developers do not understand SQL injection and the mysql API is fragile enough to make it hard to prevent it, even if you're aware of it. It is full of global state (implicit connection passing for instance), which makes it easy to write code that is hard to maintain. Since it's old, it may be unreasonably hard to maintain at the PHP core level.

The mysqli extension is a lot newer and fixes all the above problems. PDO is also rather new and fixes all those problems too, plus more.

Due to these reasons* the mysql extension will be removed sometime in the future. It did its job in its heyday, rather badly, but it did it. Time has moved on, best practices have evolved, applications have gotten more complex and require a more modern API. mysql is being retired, live with it.

Given all this, there's no reason to keep using it except for inertia.


* These are my common sense summary reasons; for the whole official story, look here: https://wiki.php.net/rfc/mysql_deprecation

Choice quotes from that document follow:

The documentation team is discussing the database security situation, and educating users to move away from the commonly used ext/mysql extension is part of this.

?

Moving away from ext/mysql is not only about security but also about having access to all features of the MySQL database.

?

ext/mysql is hard to maintain code. It is not not getting new features. Keeping it up to date for working with new versions of libmysql or mysqlnd versions is work, we probably could spend that time better.

Wednesday, March 31, 2021
 
THEK
answered 7 Months ago
98

I would highly advise to use PDO as you can do it easily. If you want to do it in mysqli it is more complicated since you can't easily bind them dynamically. To bind them dynamically look at this ugly hack

$bind_values= explode(',', $bind_values);
call_user_func_array(array($stmt, 'bind_param'), makeValuesReferenced($bind_values));
$stmt->execute();

function makeValuesReferenced(&$arr){ 
    $refs = array(); 
    foreach($arr as $key => $value) 
        $refs[$key] = &$arr[$key]; 
    return $refs; 

}
Saturday, May 29, 2021
 
Pradip
answered 5 Months ago
63

fetchAll returns an array containing all of the result set rows. So you can access to password with $data_array[0]['password'] if you used it. You may want use fetch instead.

$data_array = $stmt->fetch(PDO::FETCH_ASSOC);
Saturday, May 29, 2021
 
JakeGR
answered 5 Months ago
24

There are a few solutions to your problem.

The way with MySQLi would be like this:

<?php
$connection = mysqli_connect('localhost', 'username', 'password', 'database');

To run database queries is also simple and nearly identical with the old way:

<?php
// Old way
mysql_query('CREATE TEMPORARY TABLE `table`', $connection);
// New way
mysqli_query($connection, 'CREATE TEMPORARY TABLE `table`');

Turn off all deprecated warnings including them from mysql_*:

<?php
error_reporting(E_ALL ^ E_DEPRECATED);

The Exact file and line location which needs to be replaced is "/System/Startup.php > line: 2 " error_reporting(E_All); replace with error_reporting(E_ALL ^ E_DEPRECATED);

Tuesday, June 1, 2021
 
o_flyer
answered 5 Months ago
31

All of the mysqli functions/methods can fail in which case they will return false. I.e. if prepare() fails $stmt isn't an object you can call a method on but a bool(false). You have to check the return values and add some error handling, e.g.

$stmt = $mysqli->prepare('SELECT name FROM `rooms` WHERE r_id=?');
if ( !$stmt ) {
    printf('errno: %d, error: %s', $mysqli->errno, $mysqli->error);
    die;
}

$b = $stmt->bind_param('i', $roomID);
if ( !$b ) {
    printf('errno: %d, error: %s', $stmt->errno, $stmt->error);
}

$b = $stmt->execute();
if ( !$b ) {
  and so on and on

see http://docs.php.net/mysqli-stmt.errno et al


in this case you probably bumped into the problem that you can't create an other statement while there are still results/result sets pending for the previous statement.
see http://docs.php.net/mysqli-stmt.close:

Closes a prepared statement. mysqli_stmt_close() also deallocates the statement handle. If the current statement has pending or unread results, this function cancels them so that the next query can be executed.
Wednesday, September 1, 2021
 
Ryan Stewart
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share