PHP Prepared Statement Problems

PHP Prepared Statement Issues

You're using the variable $p up top but you're using $pas down below.

You shouldn't be doing any of that mysql_escape_blahblahblah stuff with prepared statements, anyway. Just do this directly:

$loginQuery->bind_param('ss',$_POST['email'],$_POST['pass']);

PHP prepared statement fails preparing second statement

This is most probably because there are still results waiting to be fetched from your first query. Before executing the second query, try:

$stmt->close();

This should clear the buffer allowing further queries to be run.

Prepared Statement Not working

How i did find the problem:

i altered echo $stmtToegang->error . 3; into echo $db->error . 3;
This way i found the following error:

Commands out of sync; you can't run this command now

Where i found a solution:
Answer here on SO

Problems was that this stmt-query (select type) was a part of another stmt-query (select type) because:

mysqli uses unbuffered queries by default (for prepared statements;)

So my code to debug a nested prepared statement (select-type) is as followed :

if($stmt = $mysqli->prepare($sql)) {
    <$stmt->bind_param()->execute()->bind_result() code>

    $stmt->store_result(); // store them !important

    while ($stmt->fetch()) {
        //code

        if($stmt2 = $mysqli->prepare($sql)) {
            <$stmt2->bind_param()->execute()->bind_result() code>

            $stmt2->store_result(); // store them !important

            while ($stmt2->fetch()) {
                //code
            }
            $stmt2->free_result(); // free them
            $stmt2->close(); // close them

            echo $stmt2->error; // report error
        }  else {
            echo $mysqli->error; // report error
        }
    }
    $stmt->free_result(); // free them
    $stmt->close(); // close them

    echo $stmt->error; // report error
} else {
    echo $mysqli->error; // report error
}

//edit: <$stmt->bind_param()->execute()->bind_result() code> for an better view

Hope it can help somone

PHP - Prepared Statement Query doesn't work the second time

So, adding ini_set('display_error', 1);, suggested by @user2182349, gave me a little more insight, I got "Fatal error: Call to a member function bind_param() on boolean".

After some research, I tried adding mysqli_report(MYSQLI_REPORT_ALL);, which ended up throwing "No index used in query/prepared statement".

I did some research on that to realize that it wasn't a problem, just MySQLI reporting unnecessary errors (which is what I asked it to do lol). In order to get a better, more insightful stack trace, I used mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);.

This threw "Commands out of sync; you can't run this command now". Again, more research taught me to use $preparedStmt->store_result();, in order to allow for another prepared statement to run.

Big thanks to all y'alls' help, hope this can help someone too.

Prepared statement returning False

Problem:

... I use mysqli prepare statement to insert or select data from the database, the problem is that in many cases prepare statement return false.

That's because you're running the queries in out of order fashion i.e. you're executing ->prepare() before closing the previous statement object. Given your current code, add the following error reporting code in your prepared statements,

if(!$this->_connection->prepare(...)){
    printf('errno: %d, error: %s', $this->_connection->errno, $this->_connection->error);
    die();
}

If you look at $this->_connection->error, you'll see the following error,

Commands out of sync; you can't run this command now

This issue has been documented in many forums, such as:

From the MySQL documentation,
If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.
From this SO thread,
You can't have two simultaneous queries because mysqli uses unbuffered queries by default (for prepared statements;...

Solution:

Execute the commands in correct order, (See this example code)

Open up the connection (Once: at the very beginning, not during the execution of every query)
Create a prepared statement
Bind parameters
Execute the query
Bind result variables
Fetch value into those bound variables
Close the statement object
Close the connection (Once: at the very end, not during the execution of every query)

(Follow steps 2 to 7 for executing all of your queries, though one or more steps might be optional based on of your query)

So the solution is, close the previous statement object before calling ->prepare() again. Take this method call $this->unsetPsSelectUsers(); out of the setPsSelectUsers() method and place it before the if ($this->_psSelectUsers->fetch()){...}else{...} block of isPostingAllowed() and checkUserAuthentication() methods. Furthermore, save the status of $this->_psSelectUsers->fetch() method call in a variable and use it in the subsequent if block. So your code should be like this:

public function isPostingAllowed() {
    $this->setPsSelectUsers($this->_connection->prepare("Select * from userpermissions where userpermissions.UserId = ? and userpermissions.PermissionId = 1"));
    if(!$this->_psSelectUsers){
        printf('errno: %d, error: %s', $this->_connection->errno, $this->_connection->error);
        die();
    }
    $this->_psSelectUsers->bind_param('i',$this->UserId);
    $this->_psSelectUsers->execute();
    $status = $this->_psSelectUsers->fetch();
    $this->unsetPsSelectUsers();
    if ($status){
        return true;
    }else{
        return false;
    }
}

private function setPsSelectUsers($stmt){
    $this->_psSelectUsers= $stmt;
}

private function unsetPsSelectUsers() { 
    if (isset($this->_psSelectUsers)) { 
        $this->_psSelectUsers->close(); 
        unset($this->_psSelectUsers);
    }
}

public function checkUserAuthentication() {
    $this->setPsSelectUsers($this->_connection->prepare("SELECT UserLogInName FROM Users WHERE UserLogInName=? AND UserPassword=?"));
    if(!$this->_psSelectUsers){
        printf('errno: %d, error: %s', $this->_connection->errno, $this->_connection->error);
        die();
    }
    $this->_psSelectUsers->bind_param('ss',$this->UserLogInName, $this->UserPassword);
    $this->_psSelectUsers->execute();
    $status = $this->_psSelectUsers->fetch();
    $this->unsetPsSelectUsers();
    if ($status){
        return true;
    }else{
        return false;
    }
}

Moreover, you don't have to use this setPsSelectUsers() method anymore, you can directly use the property $_psSelectUsers in your methods like this:

$this->_psSelectUsers = $this->_connection->prepare(...);

prepare() outputs bool false, statement looks correct to me

You have this:

$stmtupdate = $conn->prepare("UPDATE $table SET ? = ? WHERE email = ?");
$stmtupdate->bind_param("sis", $column, $value, $email);

This results in this SQL:

UPDATE $table
SET @var1 = @var2 WHERE email = @var3

substituted for example with

UPDATE $table
SET 'column' = 123 WHERE email = 'something'

But you want something like

UPDATE mytable
SET mycolumn = 123 WHERE email = 'something'

So there are two mistakes: You want a table name, not $table. And you want a column name, not a string bind variable value.

Use string concatenation:

$stmtupdate = $conn->prepare("UPDATE ".$table." SET ".$column." = ? WHERE email = ?");
$stmtupdate->bind_param("is", $value, $email);

(I hope I'm not mistaken. It's been some time since I used PHP with SQL.)

Problem with multiple prepared statements

Note that the mysqli extension has been replaced by PDO, which is simpler to use and better behaved. You should use it rather than mysqli. If you need a PDO tutorial, try "Writing MySQL Scripts with PHP and PDO". Even if you switch, you may still wish to figure out what is going wrong, in which case read on.

That particular error simply means you're not dealing with a mysqli_stmt in $select_something or $select_something_else. Functions in many extensions (especially DB extensions) return false when they fail, rather than a resource or object. That's probably what's happening here. Follow proper error handling procedure by testing the return value of functions that could fail. Use mysqli::error to get a description of why a MySQLi function/method is failing.

You may be running across a limitation of mysqli and the MySQL driver: the lower level mysql_use_result() or mysql_store_result() must be called on results before they can be used. By default, mysqli_stmt::execute probably calls mysql_use_result, which means the old result must be closed before a new query is run (which is mentioned in the documentation for mysqli::query()). Proper application of mysqli_stmt::store_result may fix the problem.

Note that you shouldn't need to call mysqli::stmt_init(), which is present (as mysqli_stmt_init) to support procedural programming. Instead, you can use mysqli::prepare().

Mysql prepared statement LIKE issue

You need to include the wildcards into the value you bind to the parameter to make LIKE work properly e.g.

$tagname = '%' . $_POST['search'] . '%';

PHP Update Prepared Statement Issue

You still have a bit of a mix of PDO and MySQLi although only in your call to bindParam now, which you are calling as if it was MySQLi::bind_param. Also in your last edit the query string got messed up with the addition of Values=? I'm not sure why you did that? Anyway, this should do what you want:

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // prepare sql and bind parameters
    $stmt = $conn->prepare("UPDATE test SET title=:title WHERE id=:id");
    $stmt->bindParam(':title', $title);  
    $stmt->bindParam(':id', $id);

    // Update a row
    $title = $_POST['title'];
    $stmt->execute();
    echo "Row updated";
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;