mysqli, prepared statements, and functions

[lance.gliser]

Hello everybody. I tried searching for an answer, and google, and here, and found nothing related.

Sorry if this is a repeat. It seems like it should be stupidly easy.

 

I'm trying to learn to use Prepared Statements for the added SQL injection protection, but it's not going well.

I've got everything wired, it can, and does work to use Prepared Statements. Here's working code:

$mysqli = new mysqli($dbserver, $dbuser, $dbpassword, $dbname);
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

if($stmt = $mysqli->prepare("SELECT password FROM users WHERE email = ?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $_POST["email"]);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($password);

    /* fetch value */
    $stmt->fetch();

   echo($_POST["email"] . " - " . $password);

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();

When I have this code on my index.php it works. I get the database hit for my password.

 

However, when I try to make it into a function, it stops working.

I've narrowed it down to the fact that $stmt = $mysqli->prepare is not resolving. Comes out negative every time.

 

In index.php

doLogin($_SESSION["email"]);

 

In functions.php

function doLogin($email) {
$mysqli = new mysqli($dbserver, $dbuser, $dbpassword, $dbname);
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

if($stmt = $mysqli->prepare("SELECT password FROM users WHERE email = ?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $email);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($password);

    /* fetch value */
    $stmt->fetch();

   echo($email . " - " . $password);

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();
}

 

Only two things change:

1) The variable is being passed to the function, rather than being called through $_POST.

I don't think can be the problem. The function fails to even prepare the statement. That comes up before the variable.

 

2) The page it's on changed, making it a function.

 

 

Can anyone explains what I'm doing wrong on this?

[trq]

None of these $dbserver, $dbuser, $dbpassword, $dbname variables are defined within your function.

[lance.gliser]

For a good reason!

I have those defined in config.php I'm just keeping the sensative info out of this thread.

 

I've continued working on this.

Come up with more info for you all if it helps.

 

It works as long as I keep this in index.php:

$mysqli = new mysqli($dbserver, $dbuser, $dbpassword, $dbname);
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

 

Making this in functions.php:

function doLogin($mysqli, $email) {
if($stmt = $mysqli->prepare("SELECT password FROM users WHERE email = ?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $email);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($password);

    /* fetch value */
    $stmt->fetch();

return $password;
    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();

}

 

So, what is it about the database connection I've missed?

[lance.gliser]

And now to add more confusion...

I've been testing out more stuff.

 

It seems if I include the database connection code not wrapped into a function into index.php it works. Saw that last time.

If I add to functions.php, it doesn't work. Wrapped in a function call or not.

Yet for some idiotic reason it works in config.php!

 

$mysqli = new mysqli($dbserver, $dbuser, $dbpassword, $dbname);
if (mysqli_connect_error()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

Just chilling in config.php. Same as it did in index.php. Yet it doesn't work in functions.php.

 

O and in index.php:

<?php
require("./functions.php");
require("./config.php");
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

Both seem to have the same priority to me.

 

 

[trq]

For a good reason!

I have those defined in config.php I'm just keeping the sensative info out of this thread.

 

Then you need to include config.php within the function.

 

You really need to have a look at scope. This line (in your function)....

 

$stmt->bind_result($password);

 

$password is not defined within your function.

[lance.gliser]

From the examples I've seen about bound_result you just have to name one for them to go into. Was I mistaken?

I just started learning about this today. I got most of my info from: http://devzone.zend.com/node/view/id/686

Bound Results

 

Bound result prepared statements allow the value of variables in a PHP script to be tied to the value of fields of data in a query result set.

 

The process of setting up this binding is:

 

    * Create a query

    * Ask the MySQL server to prepare the query

    * Bind PHP variables to columns in the prepared query

    * Ask the MySQL server to execute the query

    * Request that a new row of data be loaded into the bound variables.

 

Here is a simple code snippet that illustrates this process:

<?php
$mysqli = new mysqli("localhost", "user", "password", "world");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) {
    $stmt->execute();

    /* bind variables to prepared statement */
    $stmt->bind_result($col1, $col2);

    /* fetch values */
    while ($stmt->fetch()) {
        printf("%s %s\n", $col1, $col2);
    }

    /* close statement */
    $stmt->close();
}
/* close connection */
$mysqli->close();

?> 

 

A quick question about includes. If config requires functions, will functions.php need to require config?

[lance.gliser]

Ok, I may have figured it out.

I looked your suggestion on scope. It seems to work a bit differently than the java I've been trained on.

Perhaps I need to pass the mysqli connect arguements to the function to make it work?

[lance.gliser]

Yep, that was it.

That'll teach me for making assumptions about knowing how similar languages are.

As soon as I declared those variables as global inside the dbConnect() function it found them and things worked perfectly.

For reference, here's the final code:

 

index.php

<?php
require("./functions.php");
require("./config.php");
$mysqli = dbConnect();
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Jade Design</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php
$passhash = passHash($_POST["password"]);
echo($_POST["email"] . "<br />" . $passhash . "<br />");
$pass = doLogin($mysqli, $_POST["email"]);
echo("did it work: " . $pass);
?>

<form action="index.php" method="post">
<p>Email: <input name="email" type="text" size="30" maxlength="100" /></p>
<p>Password: <input name="password" size="30" type="password" /></p>
<input name="submit" type="submit" value="Submit" />
</form>
</body>
</html>

 

functions.php

<?php
function dbConnect() {
global $dbserver, $dbuser, $dbpassword, $dbname;
$mysqli = new mysqli($dbserver, $dbuser, $dbpassword, $dbname);
if (mysqli_connect_error()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
return $mysqli;
}

function doLogin($mysqli, $email) {
if($stmt = $mysqli->prepare("SELECT password FROM users WHERE email = ?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $email);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($password);

    /* fetch value */
    $stmt->fetch();

return $password;
    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();

}
?>

 

 

---- Solved Link ----

http://www.phpfreaks.com/forums/index.php/topic,118755.0.html