mysqli_stmt::execute() returning false and stateme

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.

Answer 1

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.