Database Responding Oddly to Queries via PHP

Question

I've currently been pulling my hair out on this problem for about a full day now. I am developing my first application. I'm actually pretty far into it (having established the login page several weeks ago, worked perfectly). Last night, I ran my app and it worked perfectly. I backed it up to my flash drive, tested it once more, and closed my laptop down for the night.

This morning I opened up the app to begin the documentation process on it -- this is a project for my capstone so I have to document/video the outcome each week -- and suddenly my tester account couldn't be authenticated. I tried registering a new one via the app, and it doesn't post the data to the database. Naturally, I assumed the database/server was having issues and contacted my host; in a two-hour long process, they verified that the database was working and it was simply in my code which hadn't been touched since the night before when it ran perfectly.

I've read that this sometimes happens with authentication through FireBase (i believe this is what it is called) and OAuth. I'm using neither and just doing the basic database reading/writing. I've not found any useful information on what to do through Google, probably due to the fact that the problem is just too complicated for a quick search... or I just don't know how to word it correctly.

<?php
$connection = mysqli_connect("localhost", "SENSORED", "SENSORED", "SENSORED");

$username = $_POST["username"];
$password = $_POST["password"];


$statement = mysqli_prepare($connection, "SELECT * FROM user WHERE username = ? AND password = ?");
mysqli_stmt_bind_param($statement, "ss", $username, $password);
mysqli_stmt_execute($statement);


mysqli_stmt_store_result($statement);
mysqli_stmt_bind_result($statement, $user_id, $name, $username, $email, $company_id, $phone, $password, $leaveTime, $sickTime, $rateOfPay);

$response = array();
$response["success"] = false;  

while(mysqli_stmt_fetch($statement)){
    $response["success"] = true;  
    $response["user_id"] = $user_id;
    $response["name"] = $name;
    $response["username"] = $username;
    $response["email"] = $email;
    $response["company_id"] = $company_id;
    $response["phone"] = $phone;
    $response["password"] = $password;
    $response["leaveTime"] = $leaveTime;
    $response["sickTime"] = $sickTime;
    $response["rateOfPay"] = $rateOfPay;
}

echo json_encode($response);
?>

I did set up a connection test, which verified that the PHP is indeed connecting to the database and querying it. It can tell how many tables I have and, for example, how many usernames I have in the user table. However, when I enter:

"SELECT * FROM user WHERE username = 'test'"

being that there is a username that is just 'test', it cannot find a match. However, I can see from looking at the database that test is indeed a username and does exist in that table.

I currently am getting no results via PHP. However, if I query via PHPmyadmin, it displays the row that contains 'test' as a username. Obviously, my coding is correct, but I'm not sure what could be interfering with selecting it from the PHP since it is establishing a connection and reading from the database. Not to mention the fact that it isn't wanting to write the registration information to the database when there are no holds on that.

I've gone as far in looking for a solution as creating a whole new database with different credentials and such and receive the same problem. Google has been no help in the search for an answer, and I haven't managed to find a similar question/problem on here.

Any idea how a working database connection and reading/writing abilities can completely disappear without being edited overnight?

Answer 1

this is how I would do it. As I said i haven't used MySqli in a number of years so I had to do some googling, and I am not 100% sure everything is correct as I can't really test it.

But here goes.

 //turn on output buffering - useful for debugging
 ob_start();
//I would use the object interface
$mysqli = new mysqli("localhost", "SENSORED", "SENSORED", "SENSORED");

//your not checking the connection success
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$username = $_POST["username"];

//are you storing passwords in plain text?
$password = password_hash($_POST["password"]);


/*
 I would not pick the password from the DB, the DB is case insensitive,
 unless you use collation UTF8_bin or such.
 Also it's harder to debug, as you don't know if the user exists or the 
 password matching is wrong.
*/
$stmt = $mysqli->prepare($connection, "SELECT * FROM user WHERE username = ?");

//bind the inputs
$stmt->bind_param("s", $username);

//execute the query
$res = $stmt->execute();

  //fetch results as an associative array
$row = $res->fetch_assoc(); 

//set default
$response["success"] = false; 

/*
 We expect exactly 1 row be returned. More then 1 should
 be impossible if your username is unique (which is should be)
  but just in case any issues happen we should check that it's exactly
  1 return row, no more no less.
*/
if($res->num_rows() != 1) {
    //always be cordial in your error messages to users
    $response['message'] = "We're sorry we could not find user {$username}"; 
    return $response;
}

//check the password hash using an approved crypto-logically safe method
if(!hash_equals($password, $$row['password']) {   
    $response['message'] = "Unfortunately the password you entered is incorrect";
    return $response;
}

//remove the password / we no longer need it.
//security is paramount, no need to risk exposing it, even when its hashed
//eg. I just spent the weekend fixing 5 wordpress sites that were hacked
unset($row['password']); 


$response['message'] = "Success!";
//put the contents of the buffer into debug. only do this on localhost
//you could switch this using the HOST or server IP
$response['debug'] = ob_get_clean();

//combine the row with the response
//caution if key matched in rows exists in response value in row 
//will be replaced with that of response ( it shouldn't be a problem in this case )
$response += $row;

//set the correct content type, this allows browsers to 
//know this is json, and prevents having to do JSON.parse() in some cases
header('Content-type:application/json');

//encode the responce
echo json_encode($response);

I put some notes in there such as this

//always be cordial in your error messages to users

There was once a young web developer who had a client that complained his error messages were to "cold". But this young web developer took his clients criticism to heart and remembered to always be extra nice to end users. ( that was around 2009). That said, it hasn't improved my spelling.

One thing I ran across while refactoring this, is I noticed you are not encrypting the password. I don't know if this is by mistake or by design.

ob_start and ob_get_clean allow you to echo or print_r stuff between them without breaking the JSON response. Normally if you create output then it will be outside of your json data

 here is some output
 {"success" : false}

Which will cause errors on the client side. With output buffering the above will look like this

{"debug" : "here is some output", "success" : false}

I suppose it's optional, but it's the right way to do JSON or even any content type other than HTML, including (or especially) file downloads.

Hope it helps. Even if you cant use the code in this form, it may help seeing it written a different way. Sometimes you can be to close to the problem and overlook some simple mistake. Looking at it in another format and having to process the logic of it, may shed light on something you missed.