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
– Logan Wayne, Commented May 12, 2016 at 8:35
Never store plain text passwords! Please use PHP's built-in functions to handle password security. If you're using a PHP version less than 5.5 you can use the password_hash() compatibility pack. Make sure that you don't escape passwords or use any other cleansing mechanism on them before hashing. Doing so changes the password and causes unnecessary additional coding. — Jay Blanchard
– Jay Blanchard, Commented May 12, 2016 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.
– Stephane B., Commented May 12, 2016 at 13:11

Community · Accepted Answer · 2017-05-23 12:07:55Z

4

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

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered May 12, 2016 at 8:43

Logan Wayne

5,98716 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephane B. Over a year ago

thanks this worked. For some reason I also had to remove the type "string" in my function signature for it to work.

Community · Accepted Answer · 2017-05-23 11:59:50Z

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, 2017 at 11:59

CommunityBot

11 silver badge

answered May 12, 2016 at 8:37

Rosh Donniet

4183 silver badges10 bronze badges

2 Comments

apokryfos Over a year ago

The use of global is not recommended. Better pass the $conn variable as a parameter.

Jay Blanchard Over a year ago

Why are you guessing?

zhenjun huang · Accepted Answer · 2016-05-12 09:14:25Z

0

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

answered May 12, 2016 at 9:14

zhenjun huang

3410 bronze badges

1 Comment

Jay Blanchard Over a year ago

You guess? Why would you guess?

Collectives™ on Stack Overflow

Use parameter of php function as column name in SQL query

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related