How to not show SQL prepare statement errors

Question

I have a form to add a new monitoring to a User. For that I use prepare statement with mysqli.

mysqli code :

function addMonitoring( mysqli $db, string $date, int $time, int $num_user, int $num_action, string $remark ): int
{
    if(
        $stmt = $db->prepare( 'INSERT INTO monitoring (
                                                       `date`,
                                                       `time`, 
                                                       `num_user`, 
                                                       `num_action`,
                                                       `remark`
                                                      )
                                               VALUES ( ?, ?, ?, ?, ? )
                              ')
    ) {      
        $stmt->bind_param( 'siiis', $date, $time, $num_user, $num_action, $remark );
        $stmt->execute();
        $stmt->close();
        if(
            $stmt = $db->prepare( 'SELECT m.id FROM monitoring m ORDER BY m.id DESC LIMIT 1')
        )
        {
            $stmt->execute();
            $stmt->store_result();
            $stmt->bind_result( $id );
            if( $stmt->fetch() ) {
                return $id;
            }
            $stmt->close();
        }
    }
    return 0;
}

And I try to display errors for tests, so I put a string type to my $time var and I want to know how to not show the error message seen here :

photo before error

Having a message like 'An error occurred'

photo when error

Code in the view to add the monitoring

if( isset( $_POST['add'] ) ) {
    // dd($_POST);
    addMonitoring( $db, $_POST['date'], $_POST['time'], $_POST['num_user'], $_POST['action'], $_POST['remark'] );
    redirect( '/user/?num_user='.$_POST['num_user'] );
}

For exemple, if I don't use prepared statement, and I do that :

$query  = "INSERT INTO monitoring (
                                                       `date`,
                                                       `time`, 
                                                       `num_user`, 
                                                       `num_action`,
                                                       `remark`
                                                      )
                                               VALUES ( $date, $time, $num_user, $num_action, $remark )"
    $result = mysqli_query( $db, $query );
    $query  = "SELECT m.id FROM monitoring m ORDER BY m.id DESC LIMIT 1";
    
    $result = mysqli_query( $db, $query );
    return ( !( $result === false ) && ( $row = mysqli_fetch_row( $result ) ) )?$row[0]:0;

I can return a int or 0 if mysql encounter a problem, so I can display a custom message if I have '0'

see detailed answer to your question https://phpdelusions.net/articles/error_reporting — Your Common Sense, Sep 02 '22 at 14:04
@YourCommonSense, it doesnt answer my question, because this solution show us how to display custom fatal error, here I just want to show a custom **message**, and 'reset' the page. Because the error is just a bad type(string to int). But thanks for the information ! — JeanneMeoy, Sep 02 '22 at 15:02
What is "reset"? Why it isn't mentioned in your question? How it "doesnt answer" when it does EXACTLY what is asked, displaying "a message like 'An error occurred'"? What is "bad type"? Your question asks about "SQL prepare statement errors". I suppose you didn't make your mind yet, which makes your question off topic. — Your Common Sense, Sep 02 '22 at 15:15
Regarding your recent addition, returning 0 when a fatal error occurred is wrong. You are returning 0 only if your query didn't return any rows. — Your Common Sense, Sep 02 '22 at 15:20
@YourCommonSense, for your questions : The 'reset' is mentionned, I ask to show a custom message at first, because I have a text displayed then a blank page when the error shows. The but was to have a custom message like 'An error occurred' **AND** that the text from before display too. The error appears when I put a string for the variable $time, but this variable must be a integer, that's why I have this error. I'll be glad to rewrite my question if it wasnt clear enough, because I thought i was. — JeanneMeoy, Sep 02 '22 at 15:26
Yes. Make your mind please, whether your question is about type error or a database error. It is possible you are asking about input data *validation*, but it is absolutely unclear from your question. Your question is jumping from prepared statements to error handling to function parameters to strict typing and back to database errors. and now you add some "reset". It's really hard to make any sense from it. — Your Common Sense, Sep 02 '22 at 15:39
For now, the answer stands. For the errors you cannot foresee, an error handler must step it, and show a generic error message, be it a type error or a database error. In case your question is how to prevent incorrect user input, then you have to *validate* it, and show whatever custom message you like. Like, `if (!ctype_digit($time)) echo "Time must be numeric"` — Your Common Sense, Sep 02 '22 at 15:50

score -1 · Answer 1 · answered Sep 02 '22 at 13:48

You can achieve it by using Exception handling.

If you want to show readable error message to user then you can try with below changes into your code.

function addMonitoring( mysqli $db, string $date, int $time, int $num_user, int $num_action, string $remark ): int
{
    try {
        if(
            $stmt = $db->prepare( 'INSERT INTO monitoring (
                                                           `date`,
                                                           `time`, 
                                                           `num_user`, 
                                                           `num_action`,
                                                           `remark`
                                                          )
                                                   VALUES ( ?, ?, ?, ?, ? )
                                  ')
        ) {      
            $stmt->bind_param( 'siiis', $date, $time, $num_user, $num_action, $remark );
            $stmt->execute();
            $stmt->close();
            if(
                $stmt = $db->prepare( 'SELECT m.id FROM monitoring m ORDER BY m.id DESC LIMIT 1')
            ) {
                $stmt->execute();
                $stmt->store_result();
                $stmt->bind_result( $id );
                if( $stmt->fetch() ) {
                    return $id;
                }
                $stmt->close();
            }
        }
    } catch (Exception $e) {
        // If you don't want to handle exception then keep it empty. 
        // But its better to handle exception
        echo $e->getMessage(); 
    }
    
    return 0;
}

It dosen't work, I still have a blank page with the sql error, I edit my code to show the code on the view that add the monitoring — JeanneMeoy, Sep 02 '22 at 13:57
This is a rather useless suggestion. PHP would show the error message or log it properly without try-catch. Either way all these if statements are not necessary. — Dharman, Sep 02 '22 at 19:49

How to not show SQL prepare statement errors

1 Answers1