Database logging and auditing using PHP and SQLite

Use PHP and SQLite to implement database logging and auditing

In the development of web applications, database logging and auditing are very important parts. System security and traceability can be enhanced by logging database operations and auditing data. This article will introduce how to use PHP and SQLite to implement database logging and auditing functions.

SQLite is a lightweight embedded database ideal for small web applications. PHP is a scripting language widely used in Web development, with rich database operation functions and ease of use. By combining these two tools, we can quickly implement database logging and auditing functions.

1. Create database and log tables

First, we need to create a SQLite database and create a table in it for recording database logs. This step can be accomplished using SQLite's command line tools or visual tools.

The following is an example SQL statement for creating a table named logs for recording database logs:

CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    user_id INTEGER,
    action VARCHAR(255),
    table_name VARCHAR(255),
    record_id INTEGER,
    old_value TEXT,
    new_value TEXT
);

This table contains some commonly used fields, such as log IDs , operation timestamp, user ID that performed the operation, operation type, operation table name, operation record ID, old value and new value.

2. Connecting to the database

Connecting to a SQLite database in PHP is very simple. You can use the interface provided by the PDO extension to operate the SQLite database.

The following is a sample code to connect to a SQLite database:

<?php
$database = 'path/to/database.db';
$pdo = new PDO("sqlite:$database");
?>

In this example, the $database variable is the path to the SQLite database file. Create a PDO instance through the new PDO() function, passing the DSN (data source name) of the database as a parameter.

3. Implementation of logging function

Before executing the database operation, we can add a piece of code to record the database log. The following is a sample function for logging:

function log_action($user_id, $action, $table_name, $record_id, $old_value, $new_value) {
    global $pdo;
    
    $sql = "INSERT INTO logs(user_id, action, table_name, record_id, old_value, new_value) 
            VALUES(:user_id, :action, :table_name, :record_id, :old_value, :new_value)";
    
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':user_id', $user_id);
    $stmt->bindValue(':action', $action);
    $stmt->bindValue(':table_name', $table_name);
    $stmt->bindValue(':record_id', $record_id);
    $stmt->bindValue(':old_value', $old_value);
    $stmt->bindValue(':new_value', $new_value);
    
    $stmt->execute();
}

This function accepts some parameters such as the user ID that performed the operation, the operation type, the table name of the operation, the record ID of the operation, the old value, and the new value. It inserts these parameters into the logs table to record the details of the database operation.

4. Database Operation Example

Now, let’s look at a complete example that demonstrates how to combine database operations and logging functionality. The following is a simple example to insert user information into the users table and record logs:

<?php
// 连接数据库
$database = 'path/to/database.db';
$pdo = new PDO("sqlite:$database");

// 插入用户信息
$user_id = 123;
$username = 'John Doe';
$email = 'john@example.com';
$sql = "INSERT INTO users(id, username, email) 
        VALUES(:id, :username, :email)";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':id', $user_id);
$stmt->bindValue(':username', $username);
$stmt->bindValue(':email', $email);
$stmt->execute();

// 记录日志
log_action($user_id, 'insert', 'users', $user_id, null, json_encode(['username' => $username, 'email' => $email]));

echo '用户添加成功！';
?>

In this example, we first connect to the database and then insert a user record into the users table. Then the log_action() function is called to record the log, and the user's ID, operation type, table name, record ID and old value are set to null. The new value is a JSON string containing the user name and email. Finally, a success message is output.

In this way, we can easily record the details of database operations and implement audit functions. You can adjust the fields of the log table and the logging method according to actual needs.

Summary

By combining PHP and SQLite, we can quickly implement database logging and auditing functions. System security and traceability can be enhanced by logging database operations and auditing data. I hope this article was helpful to you, and I wish you success in web application development!

The above is the detailed content of Database logging and auditing using PHP and SQLite. For more information, please follow other related articles on the PHP Chinese website!