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!

You might have declared `$conn` outside of your function. You can use `global $conn` to call the variable inside your function. — Logan Wayne, May 12 '16 at 08:35
**Never store plain text passwords!** Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure that you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, May 12 '16 at 12:39
@Jay Blanchard. Yes I know. I actually removed a comment next to the password that said "Do not do this is production!" :) Thanks for your link. — Stephane B., May 12 '16 at 13:11

score 3 · Accepted Answer · edited May 23 '17 at 12:07

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).

thanks this worked. For some reason I also had to remove the type "string" in my function signature for it to work. — Stephane B., May 12 '16 at 12:21

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

My guess is that your function doesn't know about $conn because it wasn't declared in its scope.
Your function should begin like this :

global $conn;

See the manual for more information about variable scope.

EDIT :

As apokryfos pointed out in the comments, the use of global is not recommended. It would be better to pass $conn as a parameter to checkCredentials.

See the accepted answer to this question for details. Quote :

A function call should not have to rely on anything outside

edited May 23 '17 at 11:59

Community

1
1

answered May 12 '16 at 08:37

Rosh Donniet

398
1
10

5

The use of `global` is not recommended. Better pass the `$conn` variable as a parameter. – apokryfos May 12 '16 at 08:38
Why are you guessing? – Jay Blanchard May 12 '16 at 12:39

score 0 · Answer 3 · answered May 12 '16 at 09:14

I guess you can modify your code like this:

$servername = "localhost:3306";//replace with your mysql server address
$username = "username";//replace with your mysql user
$password = "password";//replace with your mysql password

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";


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;
}
checkCredentials('email');

Using PHP with MYSQL is not complex, this doc can help you get the basic idea how they work together (http://www.w3schools.com/php/php_mysql_connect.asp).

You guess? Why would you guess? – Jay Blanchard May 12 '16 at 12:39 — Jay Blanchard, May 12 '16 at 12:39

Use parameter of php function as column name in SQL query

3 Answers3