Switching from simple queries to prepared statements

Question

So this is my first attempt at converting some previously used code to prepared statements in order to prevent SQL injections. I'm using mysqli procedural, as follows,

Old query:

mysqli_query($con,"UPDATE ulogs SET invalid_hits = invalid_hits + 1 WHERE user_id = $link AND date = '$date'"); 
if(mysqli_affected_rows($con) < 1) {            
mysqli_query($con,"INSERT INTO ulogs(user_id,date,invalid_hits,unique_hits,non_unique_hits,earned,ref_earned,bonus) VALUES ('$link','$date',1,0,0,0,0,0)");
}

Prepared query:

 $q1 = "UPDATE ulogs SET invalid_hits = invalid_hits + 1 WHERE user_id =? AND date =?";
$qa1 = "INSERT INTO ulogs (user_id,date,invalid_hits,unique_hits,non_unique_hits,earned,ref_earned,bonus) VALUES (?,?,1,0,0,0,0,0)";


if ($stmt = mysqli_prepare($con, $q1)) {
    mysqli_stmt_bind_param($stmt,"is", $link, $date);
    mysqli_stmt_execute($stmt);
    $ucheck = mysqli_stmt_affected_rows($stmt);
    mysqli_stmt_close($stmt);
}
if ($ucheck < 1) {
    if ($stmt = mysqli_prepare($con, $qa1)) {
        mysqli_stmt_bind_param($stmt,"is", $link, $date);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_close($stmt);
    }
}

mysqli_close($con);
exit();

And many more which get triggered under different circumstances but basically the same UPDATE first, then INSERT if nothing was updated. Apparently it worked as I based my queries on examples from php.net. Apparently... Because, after about 1 hour later, Apache was returning 500 server errors with php timing out while waiting for data from the db. Whereas with non-prepared queries such thing never happened.

So question is: have I done something wrong here? $stmt was being closed every time, along with $con so I don't know what may have caused the db to hang. Note that the server load did not went up either. It was just php-fpm reaching max clients due processes waiting for the db.

if you had this code working, then the reason is not this code. So you have to ask a question not on the deliberately working code, but on the actual problem you have - 500 error or whatever. — Your Common Sense
BTW, you should be using a single INSERT ... ON DUPLIDATE KEY UPDATE query here — Your Common Sense
@Ivan Please edit your question to include your whole source code. Also add the full error message you get from apache, you might need to look into the access_log or error_log file of apache. — Progman

Ivan Ivan · Accepted Answer · 2018-09-08T16:46:00

While I still haven't found WHY my original code for prepared statements ended up crashing the DB few hours later, I did eventually find a SIMPLE and WORKING out-of-the-box alternative by using a popular class made especially for this purpose: https://github.com/colshrapnel/safemysql

This class is pretty straightforward so even a newbie like myself was able to implement it using the examples found on their Github page.

Special thanks goes out to @YourCommonSense for pointing me in the right direction.

Switching from simple queries to prepared statements

1 Answers