vendredi 17 juin 2016

SQL Logging Function Formatting Issue - Appending Semi-Colon When Unnecessary

I'm trying to write a logger for SQL that will be called in a PHP script. The script is called each time an SQL query is performed, and saves the SQL into a text file (at backup/.txt). The code should check the final character of the query and, if it is a semi-colon, append it to the file. If it's not, it will add the semi-colon before adding it.

However, I'm getting a peculiar error with the code: all functions save with no issue, besides the delete function. For some reason, it is either adding a semi-colon when it is unnecessary, or somehow adding it twice.

function mysql_logger($sql) {

$sql = trim($sql);
$today = date('Y-m-d');

// Defines location for current backup file
define('SQL_BACKUP_FILE', 'backup/' . $today . '.txt');

error_reporting(E_ALL);

/* Confirms script is running on DELETE, INSERT, or UPDATE
    Queries that affect DB structure aren't performed through the site,
    so only data modification is required to be logged.
    Same with SELECT - no need to record.
    *- Not necessarily true - will need to check site more to confirm! */

if ((stripos($sql, "DELETE") !== FALSE)
    || (stripos($sql, "INSERT") !== FALSE)
    || (stripos($sql, "UPDATE") !== FALSE)) {

    // Opens file - if non-existent, creates
    $sqlbackup = fopen(SQL_BACKUP_FILE, 'a+');

    /* Attempts to lock file when opening. If it obtains the lock, stops
      other users accessing it temporarily. Saving only takes a mo, so
     not an issue to make other users wait. */

    if (flock($sqlbackup, LOCK_EX)) {
        /* If the line ends with a semi-colon (to end the SQL statement),
            will print the line to file. If it is missing, it will append it so that
            the backup file will store it okay (rare circumstance, but I know it
            happens somewhere in the code based on Rich's backups) */
        $count = count($sql);
        if ($sql[$count - 1] === ';') {
            fwrite($sqlbackup, $sql . PHP_EOL);
        }
        else {
            fwrite($sqlbackup, $sql . ';' . PHP_EOL);
        }




    // remove lock, allow other users to use file
        flock($sqlbackup, LOCK_UN);
    }
    fclose($sqlbackup);
}
// Return the SQL so that it can be used as a wrapper
return $sql;
}

It's just not playing nice, and I can't figure out why. I've attached an example of some of the code saved into the backup file..

UPDATE councils SET council_traffic_light = '1', council_traffic_light_user = '782bfae72bb49e90' WHERE id = '400';
DELETE FROM temp_council_price_link;;

The code was originally created by somebody else a year or two back, but it's been changed that significantly he doesn't recognize it.

Aucun commentaire:

Enregistrer un commentaire