Fetch only one field in php and mysqli

Question

I have the following code

if (isset($_POST['change'])) {
    $current = mysqli_real_escape_string($con, $_POST['current']);
    $password = mysqli_real_escape_string($con, $_POST['password']);
    $cpassword = mysqli_real_escape_string($con, $_POST['cpassword']);

    if(strlen($password) < 6) {
        $error = true;
        $password_error = "Password must be minimum of 6 characters";
    }
    if($password != $cpassword) {
        $error = true;
        $cpassword_error = "Password and Confirm Password doesn't match";
    }

    if(mysqli_fetch_field(mysqli_query($con, "SELECT password FROM users WHERE id = '" . $_SESSION['usr_id'] . "' LIMIT 1")) != md5($current)) {
        $error = true;
        $confirm_error = "Your actual password is not correct";
    }
}

The problem is here:

mysqli_fetch_field(mysqli_query($con, "SELECT password FROM users WHERE id = '" . $_SESSION['usr_id'] . "' LIMIT 1"))

It gives me the error

PHP Catchable fatal error: Object of class stdClass could not be converted to string in /.../password.php on line 27

I have tried with

mysql_result(mysqli_query($con, "SELECT password FROM users WHERE id = '" . $_SESSION['usr_id'] . "' LIMIT 1"), 0)

but it not works, it gives me

PHP Fatal error: Uncaught Error: Call to undefined function mysql_result() in /.../password.php:27

I don't want to use mysqli_fetch_array() and a while loop. I have searched for a function or something similar and i have found something but nothing worked for me.

Answer 1

I dont think this is the best solution for you but base on what you are asking you might want to use mysqli_fetch_assoc.

$result = mysqli_query($con, "SELECT password FROM users WHERE id = '" . $_SESSION['usr_id'] . "' LIMIT 1");
$row = mysqli_fetch_assoc($result);

 if( $row['password']!= md5($current)) {
        $error = true;
        $confirm_error = "Your actual password is not correct";
 }

Mention in the comments below that you should use parameterized query. For users password use password hashing library like http://www.openwall.com/phpass/ OR built in password_hash function in PHP 5 >= 5.5.0, PHP 7.

To parameterize your current query and avoid possible sql injection attacks try the following code.

$mysqliConnection = new mysqli("localhost", "my_user", "my_password", "my_dbname");

/* check connection */
if ($mysqliConnection->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqliConnection->connect_errno . ") " . $mysqliConnection->connect_error;
}

$sql = "SELECT password FROM users WHERE id = ? LIMIT 1";
$stmt = $mysqliConnection->prepare($sql);
$stmt->bind_param("i", $_SESSION['usr_id']);
$stmt->execute();
$stmt->bind_result($password);
$stmt->store_result();
$row = $stmt->fetch()

Now you could use $password variable to check the password hashing you have used. I hope this helps.

Answer 2

You need to read the documentation for mysqli_fetch_field():

Returns the definition of one column of a result set as an object. Call this function repeatedly to retrieve information about all columns in the result set.

This function doesn't return the value in one column of the result set, it returns metadata about that column. Like the column name, the table name, the data type, the max length, etc.

If you capture the field and dump it, you see:

$passhash = mysqli_fetch_field(mysqli_query($con, "SELECT password FROM users WHERE id = '" . $usr_id . "' LIMIT 1"));
print_r($passhash);

Output:

stdClass Object
(
    [name] => password
    [orgname] => password
    [table] => users
    [orgtable] => users
    [def] => 
    [db] => test
    [catalog] => def
    [max_length] => 32
    [length] => 65535    <-- I used TEXT for the password column 
    [charsetnr] => 8
    [flags] => 16
    [type] => 252
    [decimals] => 0
)

Notice that it's returned as an object, not a scalar value. So you can't compare it to your md5($current) directly. Nor does it even have the value you're looking for.

Here's how I would write the code you're trying to do:

$sql = "SELECT password FROM users WHERE id = ? LIMIT 1";
$stmt = mysqli_prepare($con, $sql);
$stmt->bind_param($stmt, "i", $_SESSION['usr_id']);
$stmt->execute();
$result = $stmt->get_result();
$match = false;
while ($row = $result->fetch_assoc()) {
    if ($row['password'] == md5($current) {
        $match = true;
    }
}
if (!$match) {
    $error = true;
    $confirm_error = "Your actual password is not correct";
}

Your other error:

Call to undefined function mysql_result()

The mysql_result() function is deprecated and it has been removed in PHP 7. It wouldn't work together with mysqli_query() anyway, since it's part of a different API, and these two APIs don't mix.

mysqli_result (note the mysqli, not mysql) is the name of a resource class, not a function.