0

Problem overview

I have a function that updates a user's username in my SQL database. I am trying to add some error handling (like a return true/false) but I don't know how to go about doing that.

The current function

function updateUid($conn, $usersUid, $rowId) {
    $sql = "UPDATE users SET usersUid = ? WHERE usersId = ?";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        //error - bad SQL call
        exit();
    }
    mysqli_stmt_bind_param($stmt, "ss", $usersUid, $rowId);
    mysqli_stmt_execute($stmt);
    return true;
    mysqli_stmt_close($stmt);
}

As you can see, right now it's just returning true without regard for weather or not the function worked.

What I've tried

I tried using mysqli_stmt_get_result($stmt) but I don't think I'm using it correctly:

function updateUid($conn, $usersUid, $rowId) {
    $sql = "UPDATE users SET usersUid = ? WHERE usersId = ?";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        //error - bad SQL call
        exit();
    }
    mysqli_stmt_bind_param($stmt, "ss", $usersUid, $rowId);
    mysqli_stmt_execute($stmt);
    $resultData = mysqli_stmt_get_result($stmt);
    if ($row = mysqli_fetch_assoc($resultData)) {
        return $row;
    } else {
        $result = false;
        return $result;
    }
    mysqli_stmt_close($stmt);
}

As far as I can tell, mysqli_stmt_get_result() is returning nothing, which makes sense since I'm not fetching data, but rather placing it.

Desired outcome

As previously stated, I would really like to be able to get a true/false return as to whether the function worked or not. What is the best method for testing the result of a SQL injection in PHP?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Calvin Bonner
  • 540
  • 2
  • 16
  • 1
    `mysqli_stmt_close($stmt);` will never be executed if it's after `return`. – Barmar Mar 29 '21 at 15:23
  • 1
    Why would your function return true or false? That sounds like a terrible design. If the function worked then nothing should be returned. If it failed then also nothing should be returned and the code should trigger an exception, which mysqli does automatically – Dharman Mar 29 '21 at 15:29
  • 1
    Please remove all the `if` statements you have there and enable automatic error reporting instead [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Mar 29 '21 at 15:29
  • 1
    `mysqli_stmt_close($stmt);` is redundant. Your statement is created inside a local scope and will never leak outside the function. You don't need to close it – Dharman Mar 29 '21 at 15:32
  • If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo & https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection – Dharman Mar 29 '21 at 15:32
  • *"What is the best method for testing the result of a SQL injection in PHP?"* This sentence makes no sense. Please explain more clearly what exactly you want to do – Dharman Mar 29 '21 at 15:33
  • 1
    What you're going to do with this true/false value from this function? – Your Common Sense Mar 29 '21 at 15:46

1 Answers1

1

mysqli_stmt_affected_rows() will tell you how many rows were updated.

<?php
function updateUid($conn, $usersUid, $rowId) {
    $sql = "UPDATE users SET usersUid = ? WHERE usersId = ?";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        //error - bad SQL call
        exit();
    }
    mysqli_stmt_bind_param($stmt, "ss", $usersUid, $rowId);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_affected_rows($stmt) > 0;
    mysqli_stmt_close($stmt);
    return $result;
}

Note that this can be 0 for two reasons:

  1. No rows match $rowId
  2. The usersUid in the matching row is already equal to $usersUid, so nothing changed.

See PHP, MySQL - can you distinguish between rows matched and rows affected? for how you can change the way you connect to the DB so that this returns the number of rows that were matched by the WHERE condition instead of the rows that were actually updated.

Barmar
  • 741,623
  • 53
  • 500
  • 612