changing from mysql to mysqli

[lional]

Hi All

I am busy changing from mysql to mysqli on my old scrips and I have one problem that I cannot seem to solve.

<?php

// checks a given username and password combination.
function validate_credentials($user_name, $user_password) {
	$conn = mysqli_connect("localhost", "root", "", "hebron_college");
/*@mysql_select_db('hebron_college');*/

	$user_name = mysqli_real_escape_string($conn, $user_name);
	$user_password = sha1($user_password);
	
	$result = mysqli_query($conn, "SELECT * FROM users WHERE user_name = '$user_name' AND user_password = '$user_password'");
   $num = mysqli_num_rows($result);
   echo 'number' . $num;
	if (mysqli_num_rows($result) != 1){
		return false;
		
	}
	
	return mysqli_result($result, 0);
}
?>

I have done some research and I believe there is no mysqli_result function.

How would I be able to replace the mysql_result line in mysqli

[benanamen]

You researched it? Really?

 

It would help if you read the manual.

http://php.net/manual/en/class.mysqli-result.php

 

As long as you are re-writing your code you should use PDO instead. https://phpdelusions.net/pdo

 

You have more problems than just changing to mysqli. You need to use password_hash and password_verify. Sha1 is long obsolete for passwords.

 

Additionally, you never ever put variables in a query. You need to use prepared statements.

 

You also need to specify the columns you want. Do not SELECT *

 

Any code that was written with the obsolete mysql_* needs a complete rewrite from the ground up. You cannot just throw an 'i' onto mysql and call it updated code.

Edited May 23, 2017 by benanamen

[Jacques1]

I recommend you forget about mysqli and switch directly to PDO. It's much more programmer-friendly and much more versatile. I've seen quite a lot of people try to rewrite their old mysql_* code to mysqli, and it rarely worked out. mysqli is a hardcore low-level interface for nerds who love to study manuals, and let's be honest here, that's not what the average PHP programmer is.

 

The code has serious issues and is going to need more than adding an “i” to the mysql_* calls.

• You do not establish a new database connection for every single query. This is insanely inefficient and leads to duplicate code all over the place.
• Hashing passwords with SHA-1 has become a joke since standard PCs can calculate billions of hashes per second. We use bcrypt now which is available through the PHP password hash API.
• Get rid of those num_rows gymnastics. Just fetch the data.

This is how a sane version of your code might look like:

<?php

/**
 * Checks user credentials and obtains the user ID
 *
 * @param PDO    $database_connection the connection to the database holding the user records
 * @param string $name                the username
 * @param string $password            the plaintext password
 *
 * @return integer|null the user ID, or null if the credentials are wrong
 */
function authenticate_user(PDO $database_connection, $name, $password)
{
    // use a prepared statement to prevent SQL injection attacks as well as syntax problems
    $user_stmt = $database_connection->prepare('
        SELECT
            user_id,  -- what do you want? the ID? then explicitly select it
            user_password
        FROM
            users
        WHERE
            user_name = :name
    ');
    $user_stmt->execute([
        'name' => $name,
    ]);
    $user = $user_stmt->fetch();

    if ($user && password_verify($password, $user['user_password']))
    {
        return $user['user_id'];
    }
    else
    {
        return null;
    }
}

[cyberRobot]

How would I be able to replace the mysql_result line in mysqli

 

In case you are not quite ready to switch to PDO (which I would also recommend changing to), you could do something like this:

$result = mysqli_query($conn, "SELECT * FROM users WHERE user_name = '$user_name' AND user_password = '$user_password'");
if($row = mysqli_fetch_row($result)) {
    return $row[0];
} else {
    return false;
}

 

Note that you may need to modify the query so that the column you are interested in appears first. Since the above solution (and mysql_result) only returns one column, you might as well specify the column. For example:

SELECT id FROM users WHERE user_name = '$user_name' AND user_password = '$user_password'