How to get the return value for the sql execution in php

Question

I have created the below code to update the password in user form. But when I try to update the password with incorrect details, the DB is not updating but returing the success message

<?php 
if (isset($_POST['create']))
{
    $m_number   = $_POST['user_mobile_number'];
    $u_nic      = $_POST['user_nic'];
    $u_lname    = $_POST['user_login_name'];
    $u_password = $_POST['user_password'];


    $sql = "UPDATE `user` SET `user_password` = '$u_password' WHERE `user_login_name` = '$u_lname' && `user_mobile_number` = '$m_number' && `user_nic` = '$u_nic'";

    if (mysqli_query($db, $sql))
    {
        if ($sql)
        {
            ?>
                <script type="text/javascript">
                    Swal.fire(
                      'WooHoo!',
                      'Your password change successfully!',
                      'success'
                    ).then((result) => {
                              if (result.value) {
                                window.location="login.php";
                                
                              }
                            })
                </script>
            <?php
        }
        else
        {
            echo "Error";
        }
    }
    else
    {
        
    }
}

you probably need to check if UPDATE statement actually affected any rows. $sql variable will be true anyways, even if the UPDATE statement affects 0 rows. — Tedo G., Oct 14 '20 at 08:19
Make sure you have mysqli error reporting enabled [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Oct 14 '20 at 08:26
What is "incorrect details" and how it's even possible? May be just **don't** execute a query with whatever "incorrect details"? — Your Common Sense, Oct 14 '20 at 09:13

score 2 · Accepted Answer · answered Oct 14 '20 at 08:35

You need to check how many rows was affected, rather than if the query was successfully executed or not. You should also be using a prepared statement instead.

<?php 
if (isset($_POST['create'])) {
    $m_number   = $_POST['user_mobile_number'];
    $u_nic      = $_POST['user_nic'];
    $u_lname    = $_POST['user_login_name'];
    $u_password = $_POST['user_password'];


    $sql = "UPDATE `user` 
                SET `user_password` = ? 
                WHERE `user_login_name` = ?
                  AND `user_mobile_number` = ? 
                  AND `user_nic` = ?";

    $stmt = $db->prepare($sql);
    $stmt->bind_param("ssss", $u_password, $u_lname, $m_number, $u_nic);
    $stmt->execute();
    $affectedRows = $stmt->affected_rows;
    $stmt->close();

    if ($affectedRows) {
        ?>
        <script type="text/javascript">
            Swal.fire(
                'WooHoo!',
                'Your password change successfully!',
                'success'
             ).then((result) => {
                 if (result.value) {
                     window.location = "login.php";           
                 }
             })
         </script>
         ?>
    } else {
        echo "Error";
    }
}

Keep in mind that the user has to pass the EXACT match for both the mobile-number, login-name and nick to match the criteria. And if someone fakes the request, one could potentially change the password of someone else's user - so you should look into adding some more validation, or a form of CSRF token.

Important

You are storing passwords in PLAIN TEXT, which is extremely insecure and very reckless. You should be using password_hash() to hash your passwords, and use password_verify() when attempting to log in.

Its also advisable to enable MySQLi to throw exceptions on failure, so that you can catch and log these - this allows you to be more streamlined when writing your code; you don't have to check each individual action or query, but you can just wrap it all in a try/catch block.

How to get the return value for the sql execution in php

1 Answers1

Important