mysqli_stmt::execute() returning false and statement not executing

Question

I have a simple login system protection mechanism by recording the user's IP, failed attempt number and last attempt time to a MySQL database table named bannedusers. However when I attempt to use the following code below to insert a new entry into the database the execute() function returns false and fails to execute.

Code as follows:

private $con;

function updateTable($IP, $attempt, $exists){
    $time = time();

    //The following statement is actually in the constructor, moved here for completeness
    $this->con = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME); //All these constants are predefined and verified to be correct.

    if($this->con->connect_error){
        return true; //If there is a connection error, just let the user log in... We'll deal with this later
    }

    //Another function already determines if the entry exists or not.
    if(!$exists){
        //ip, retrycount, attempttime are the name of the fields. IP is a 40-char wide VARHCAR, retrycount is a tinyint and attempttime is a big int.
        $query = "INSERT INTO bannedusers (ip, retrycount, attempttime) VALUES (?,?,?)";

        if($stmt = $this->con->prepare($query)){

            //This following statement executes without throwing errors and returns true.
            $stmt->bind_param('sii', $IP, $attempt, $time);

            $successful = $stmt->execute();
            $stmt->close();

            if(!$successful){
                //Causes a small dialog to appear telling you the query failed.
                echo "<script type='text/javascript'>alert('Failed query!');</script>";
            }
        }
    }else{
        //Unrelated code omitted.
    }
}

I'm rather new to php and MySQL and through research I have found that the SQL syntax apparently needs quotation marks around the fields for the VALUE section of the query like:

$query = "INSERT INTO bannedusers (ip, retrycount, attempttime) VALUES ('?','?','?')";

but I have found here that it actually stops the query from working (still tried it and got a error on bind_param()). I've tried changing the type to 'sii' or 'sss' or 'ssi' all which resulted in the query failing. I've tried adding a semicolon at the end of the SQL query but that changed nothing. In all cases the "failed query!" dialog box pops up with no other error (except the one mentioned above give I use quotation marks around the VALUES fields.

Any help is appreciated.

Update:

It turned out that $ip was somehow null before being passed into the function which only causes a error if the MySQL error level is raised to MYSQLI_REPORT_ALL.

Your Common Sense · Accepted Answer · 2016-09-13T17:43:58.917

5

Either set mysqli into Exception mode

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

or always check the result of every mysqli operation and throw mysqli error manually:

$result = $stmt->execute();
if (!$result) {
    throw new Exception($mysqli->error);
}

this is the only way to know what's wrong with your execute();

I have found that the SQL syntax apparently needs quotation marks around the fields for the VALUE

Of course it is wrong. SQL syntax apparently needs quotation marks around strings only.

edited Sep 13 '16 at 17:43

answered Aug 14 '13 at 19:07

Your Common Sense

154,967
38
205
325

`mysqli_report(MYSQLI_REPORT_STRICT);` will enable exceptions so you don't have to throw them yourself. [source](http://www.php.net/manual/en/mysqli-driver.report-mode.php) – mpen Aug 14 '13 at 19:09
Used function mysqli_report(MYSQLI_REPORT_STRICT), no errors. Checked the return value of $this->con->error and it is blank. Using phpmyAdmin to check database, records aren't being added. – initramfs Aug 14 '13 at 19:17
@CPUTerminator double-check it. It've never seen a case when a function returned an error but con->error was empty. How do you check it? Can you see any other errors? – Your Common Sense Aug 14 '13 at 19:23
Turns out php was screwing my over with string references and caused $ip to become null (before it was passed into this function). It also turns out that mysqli_report(MYSQLI_REPORT_STRICT) != mysqli_report(MYSQLI_REPORT_ALL) where MYSQLI_REPORT_ALL actually throws a exception whereas MYSQLI_REPORT_STRICT does not. While the mysqli object had no errors, the operation itself did throw a exception with the fact that $ip was null and the fields cannot be null. Thanks for showing me the mysqli_report(); function, never heard about it before. – initramfs Aug 14 '13 at 19:27
I believe in the `Exception()` it should say `Exception($result->error);` or `Exception($stmt->error);` NOT `Exception($mysqli->error);` am I correct? – Branndon Sep 13 '16 at 17:40
@Branndon nope, it should be exactly as I wrote. Why do thou think otherwise? – Your Common Sense Sep 13 '16 at 17:44
Because nowhere in the question, or in your example provided was `$mysqli` initiated. When I changed it locally when testing this script, using `$result` instead, I believe it worked. @YourCommonSense – Branndon Sep 15 '16 at 10:07
Ok it turns out for me $stmt->error was what had the error message in mine – relipse Jan 27 '22 at 16:09

mysqli_stmt::execute() returning false and statement not executing

1 Answers1

Linked