Use prepared statements to check user override credentials, user override rights, and delete MySQL table Record

Question

I am trying to build an override feature so users can manually remove a MySQL table row if they have the correct rights to do so. The user is prompted to input the same credentials used for program login as well as the uniqueID for the row that needs to be removed. Upon hitting the 'Submit' function, I run a series of if statements/ MySQL SELECT statements to check credentials, user rights and finally row Deletion with the result output as an alert.

However, my alert shows up blank and the row is not removed so I know there is a problem with my if statements. Upon testing, I believe the problem is when I try to use the previous query's results to run the next if statement logic.

How do I properly determine if the MySQL query returned a row using prepared statements?

All help is appreciated! Thank you!

My CODE:

 if ((isset($_POST['overrideUsername'])) and (isset($_POST['overridePassword'])) and (isset($_POST['overrideUniqueID']))) {



    $overridePasswordInput = $_POST['overridePassword'];

    $overrideUsername = $_POST['overrideUsername'];
    $overridePassword = ENCODE(($overridePasswordInput).(ENCRYPTION_SEED));
    $roleID = '154';
    $overrideUniqueID = $_POST['overrideUniqueID'];


    //connect  to the database 
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if(mysqli_connect_errno() ) {
        printf('Could not connect: ' . mysqli_connect_error());
        exit();
    }

    $conn->select_db($dbname);

    if(! $conn->select_db($dbname) ) {
        echo 'Could not select database. '.'<BR>';
    }

    $sql1 = "SELECT users.id FROM users WHERE (users.login = ?) AND (users.password = ?)";

    $stmt1 = $conn->prepare($sql1);
    $stmt1->bind_param('ss', $overrideUsername, $overridePassword);
    $stmt1->execute();
    $stmt1->bind_result($userID);     
    //$result1 = $stmt1->get_result();

    if ($stmt1->fetch()) {


        $sql2 = "SELECT * FROM rolestousers WHERE (rolestousers.userid = ?) AND (rolestousers.roleid = ?)";

        $stmt2 = $conn->prepare($sql2);
        $stmt2->bind_param('ss', $userID, $roleID);
        $stmt2->execute();
        $stmt2->store_result();     

        if ($stmt2->fetch()) {

            $sql3 = "DELETE * FROM locator_time_track_out WHERE locator_time_track_out.uniqueid = ?";

            $stmt3 = $conn->prepare($sql2);
            $stmt3->bind_param('s', $overrideUniqueID);
            $stmt3->execute();
            $stmt3->store_result();     

            if ($stmt3->fetch()) {

                echo 'Override Successful! Please scan the unit again to close it out.';

            } else {

                echo 'Could Not Delete Record from the table.';

            }//End $sql3 if.

        } else {

            echo 'User does not have override permission. Please contact the IT Department.';

        }//End $sql2 if.


    } else {

        echo 'Your login information is incorrect. Please try again. If the issue persists, contact the IT Department.';

    }//End $sql1 if.


//Free the result variable. 
  $stmt1->free();
  $stmt2->free();
  $stmt3->free();

  $stmt1->close();


//Close the Database connection.
 $conn->close();


}//End If statement

NOTE: I am definitely sure my DB connection information is correct. The issue resides after I connect into the database. I have also tested the code using only the first if statement and get the blank alert so I'm not making it past the first if statement.

EDIT:: My php Script was definitely failing, but even earlier than expected, at the following code:

$overridePassword = ENCODE(($overridePasswordInput).(ENCRYPTION_SEED));

So my issue is that I need to properly compare the password and encryption seed information. However, the previous programmer used the following line to do the same process (which is obviously unsafe):

$querystatement = "SELECT id, firstname, lastname, email, phone, department, employeenumber, admin, usertype FROM users WHERE login=\"".mysql_real_escape_string($user)."\" AND password=ENCODE(\"".mysql_real_escape_string($pass)."\",\"".mysql_real_escape_string(ENCRYPTION_SEED)."\")";
    $queryresult = $this->db->query($querystatement);

I will need to fix this issue before I can even test the functionality of the if logic using prepared statements.

Yes, I'm not getting any errors in the log nor am I receiving any errors when inspecting the page during the process. — rdimouro, Jan 16 '17 at 16:29
Possible duplicate of [Example of how to use bind\_result vs get\_result](http://stackoverflow.com/questions/18753262/example-of-how-to-use-bind-result-vs-get-result) — Lelio Faieta, Jan 16 '17 at 16:49
If you check [this accepted answer](http://stackoverflow.com/questions/18753262/example-of-how-to-use-bind-result-vs-get-result) you will see you cannot use `bind_result` with a `* SELECT statement` but only if you pass the specific names of the columsn — Lelio Faieta, Jan 16 '17 at 16:51
For testing purposes, I commented out all nested if statements and did this test statement: `"SELECT users.id FROM users WHERE (users.login = '[myfirstname.mylastname]'";` (I used a login name I know exists) and I still can't get a result alert. — rdimouro, Jan 16 '17 at 17:21

Nishant Nair · Answer 1 · 2017-01-16T16:41:54.100

0

Your are passing wrong variable for delete query

$stmt3 = $conn->prepare($sql3);

Please refer [ http://www.plus2net.com/php_tutorial/pdo-delete.php ]

edited Jan 16 '17 at 16:41

answered Jan 16 '17 at 16:29

Nishant Nair

1,999
1
13
18

Thank you for catching that as that would have caused problems once actually getting to that part of the code. However, I'm still having the same issues. My alert should not be blank as the if statement logic should display either a successful or failed response. It's as if the program can't read "if ($stmt1->fetch()) {...}" at all. – rdimouro Jan 16 '17 at 16:31
you are pasing `$stmt3 = $conn->prepare($sql2);` use above code for executing delete query – Nishant Nair Jan 16 '17 at 16:33
// use `exec()`or `rowCount()` because delete dosen't return any value. Because `fetch()` will not for for delete. Due to which it is skipping if statement. – Nishant Nair Jan 16 '17 at 16:37
I've put that in too but my code is still not making it to that If statement. It's still getting stuck on the first if statement. – rdimouro Jan 16 '17 at 16:49
print `$_POST` before if statement. Check if value present or not – Nishant Nair Jan 16 '17 at 16:51
Sry for not including that info but yes I tested all $_POST[] values before posting this question. They are posting successfully. – rdimouro Jan 16 '17 at 16:56

Use prepared statements to check user override credentials, user override rights, and delete MySQL table Record

1 Answers1