Use parameter of php function as column name in SQL query

Question

This is for a school project. I am new in PHP and MySQL. I am trying to write a function in PHP with one string parameter, which will become the column name of a SQL query which is used in a prepare() statement inside this function.

Here is the code:

function checkCredentials($dbFieldToCheck) {
  $statement = $conn->prepare("SELECT id FROM user WHERE ".$dbFieldToCheck." = ? AND password = PASSWORD(?)");
  if (!$statement) die("Prepare failed: (" . $conn->errno . ") " . $conn->error);
  $statement->bind_param("ss", $_POST["username/email"], $_POST["password"]);
  $statement->execute();
  $result = $statement->get_result();
  $row = $result->fetch_assoc();
  return $row;
}

Then I call this function twice in two different variables:

$row1 = checkCredentials('email');
$row2 = checkCredentials('username');

Finally I do something with these two variables (not useful here I guess).

I have tried different ways to write the parameter in the SQL statement also different ways to write it in the function call. Now I am even starting to think that my function is not even called at all...

What I want to achieve works if I just execute the code inside the function two times with the column name hard coded. But this is not nice :)

Any help is very much appreciated. Thanks!

Answer 1

Add to the parameter of your function the submitted POST data and your connection variable. When you call your function, it will look like this:

checkCredentials($dbFieldToCheck, $conn, $_POST["username/email"], $_POST["password"]);

Your function will look something like this:

function checkCredentials(string $dbFieldToCheck, $conn, $username, $password) {

    $statement = $conn->prepare("SELECT id FROM user WHERE ".$dbFieldToCheck." = ? AND password = PASSWORD(?)");
    if (!$statement) die("Prepare failed: (" . $conn->errno . ") " . $conn->error);
    $statement->bind_param("ss", $username, $password);
    $statement->execute();
    $result = $statement->get_result();
    $row = $result->fetch_assoc();
    return $row;

}

You can use global to call variables inside your function (you can refer here for the cons of using global).