-2

I am constructing a PHP application and I want to be able to log every sql queries in the site. Therefore, I wrote a function to execute the sql queries and log the information. The problem I have now is that for some reason, the only time where the query runs successfully is when it is returned. But it does not seem to work within the function itself. $conn->error always remains empty. Indicating that that the query ran successfully. But if ($conn->query($query) !== TRUE) returns FALSE. Any help would be much appreciated.

Here is my function where $conn is my sql connection, $site is the site settings and $alerts contains all the errors that may occur on the site.:

function SQLLogger($query,$username = null){
    global $conn,$site,$alerts;
    if($username == null){$username=$_SESSION[$site['id']];}
    if(strpos($query, 'INSERT INTO') !== false){
        $action = "inserting";
    } elseif(strpos($query, 'UPDATE') !== false){
        $action = "updating";
    } elseif(strpos($query, 'SELECT') !== false){
        $action = "selecting";
    } elseif(strpos($query, 'DELETE') !== false){
        $action = "deleting";
    }
    if ($conn->query($query) !== TRUE){
        $status = "Error";
        $error = array(
            'type' => 'error',
            'title' => 'Logger has reported an error',
            'body' => 'Error '.$action.' row: '.$conn->error,
        );
        array_push($alerts,$error);
    } else {
        $status = "Success";
    }
    $ip = $_SERVER['REMOTE_ADDR'];
    $query2 = "INSERT INTO logs (created, modified, `user`, `query`, `error`, `action`, `status`, `ipv4`) VALUES ( '".date("Y-m-d H:i:s")."', '".date("Y-m-d H:i:s")."', '".$username."', '".str_replace("'", "\\'", $query)."', '".$conn->error."', '".$action."', '".$status."', '".$ip."' )";
    if ($conn->query($query2) !== TRUE) {
        $error = array(
            'type' => 'error',
            'title' => 'Logger has reported an error',
            'body' => 'Error inserting row: '.$conn->error,
        );
        array_push($alerts,$error);
    }
    return $conn->query($query);
}
L. Ouellet
  • 133
  • 1
  • 8
  • 2
    _the only time where the query runs successfully is when it is returned_ I dont understand this statement, can you explain – RiggsFolly Feb 04 '20 at 16:21
  • 2
    This code also makes me think you are concatenating data into your queries!!! **NOT GOOD** [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenated values – RiggsFolly Feb 04 '20 at 16:22
  • 1
    You should also let us know what database (mysql? postgres?) and which extension (mysqli? PDO?) you're using. – M. Eriksson Feb 04 '20 at 16:22
  • 1
    I'd suggest using prepared statements and parameter binding **so that you don't have to ever worry about quoting issues**. Here are the instructions for [PDO](https://secure.php.net/manual/en/pdo.prepared-statements.php) and [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – aynber Feb 04 '20 at 16:23
  • 1
    Why are you running `$conn->query($query);` twice? Once in the if statement, and once at the very end. You're never returning `$alerts`. – aynber Feb 04 '20 at 16:24
  • 2
    INSERT/UPDATE/DELETE queries will return you TRUE OR FALSE but **SELECT** queries return a Mysqli_Result object which is definitely NOT Boolean and therefore not `=== TRUE` or `=== FALSE` – RiggsFolly Feb 04 '20 at 16:25
  • You may also like to consider letting the DBMS do all the work https://stackoverflow.com/questions/303994/log-all-queries-in-mysql – RiggsFolly Feb 04 '20 at 16:28
  • Thanks All! @RiggsFolly I will defenetly have a look at your suggested links. Also I am using mysqli. – L. Ouellet Feb 04 '20 at 16:46

1 Answers1

1

INSERT/UPDATE/DELETE queries will return you TRUE OR FALSE but SELECT queries return a Mysqli_Result object which is definitely NOT Boolean and therefore could never be === TRUE or === FALSE or !== false as these tests require datatypes to match.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149