Database Error Handling problem in CodeIngniter

Question

I use CodeIgniter as my web application framework. I used a simple Try/Catch and I sent a sample value to test it, and it failed!

I know I can use $this->db->escape() function to solve my data problem but I just want to know: Why TRY/CATCH can not catch this error!

Controler code:

    $this->load->model('user_model');
    $result = $this->user_model->test_user("tes'ti");

Model code:

function test_user($username){
    try {
        $query_str = "SELECT * FROM tbl_user WHERE username = '".$username."'";
        $result = $this->db->query($query_str);
        return $result;

    } catch (Exception $e) {
        return;

    }
}

Output:

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ti'' at line 1

SELECT * FROM tbl_user WHERE username = 'tes'ti'

Let me know, where I made a mistake, if I did!

Thank you for your time and helping others. ;)

Answer 1

@Monica, not sure if this helps, but you should know that the CI database functions never throw any errors. They just return true or false. Therefore @Sarfraz is right, you must either check for true/false yourself and/or throw Exceptions yourself.

Also, your exception handling code does nothing. This means it will continue running any scripts coming after it, including scripts that work with the recordset that just failed.

Answer 2

Instead of getting code igniter db errormsg (html), i wanted to get a json display, then inside the ajax request i just check for success

I rewrote the DB_driver.php in the

function display_error($error = '', $swap = '', $native = FALSE)


//commented
//echo $error->show_error($heading, $message, 'error_db');

//this is the magic
$data['success'] = false;
$data['heading'] = $heading;
$data['message'] = $message;
$data['type'] = 'error_db';

echo json_encode($data);
exit;

this will create a json with success value as false

then i just check for false...

Works for all my ajax request .... just and idea ...

Answer 3

The reason you are getting an error is due to 'tes**'ti' the **'. use '$username."'".$username1

Where $username = 'tes'
and $username1 = 'ti

Answer 4

For me, the code by @Sarfraz did not work, at least in CI 3.1.5 the problem was that $this->db->query has a bunch of wrapping functions that immediately call the error handlers built into code igniter. If you want to throw a new RuntimeException, then use $this->db->simple_query method instead.

on an aside the OP should consider the active records, something like:

$result = $this->db->select()
    ->from('tbl_user')
    ->where('username',$username)
    ->get()
    ->result();

should get around these problems because codeigniter active record framework should escape the queries for you.

Answer 5

You need to throw an exception if there was some mysql error:

try {
    $query_str = "SELECT * FROM tbl_user WHERE username = '".$username."'";
    $result = $this->db->query($query_str);

    if (!$result)
    {
      throw new Exception('error in query');
      return false;
    }        

    return $result;

} catch (Exception $e) {
    return;
}