How to encapsulate a secure connect() function database connection class? How to implement an efficient connect() database connection method in PHP that prevents SQL injection?

M66 2025-05-24

In PHP development, database connection is a basic and critical link. An efficient and secure database connection class can not only simplify the development process, but also effectively prevent security risks such as SQL injection. This article will introduce how to encapsulate a secure connect() function and implement a database connection method that prevents SQL injection.

1. Necessity to encapsulate database connection classes

Although it is convenient to directly use native mysqli_connect() or PDO to connect to the database, if it lacks encapsulation, the code is easy to repeat and difficult to maintain. Through object-oriented encapsulation, it can be implemented:

Unified management of database connection configuration
Improve code reusability
Centrally handle exceptions and errors
Prevent SQL injection with preprocessing statements

2. Select PDO as the connection method

It is recommended to use PDO (PHP Data Objects) as the database connection interface in PHP, for reasons:

Supports multiple databases, and has high code portability
Built-in preprocessing statements to prevent SQL injection
Perfect exception handling mechanism

3. Sample code: a safe and efficient connect() class

 <?php

class Database {
    private $host = 'localhost';
    private $dbname = 'testdb';
    private $username = 'root';
    private $password = 'password';
    private $pdo;
    private $error;

    // Constructor，Initialize the connection
    public function __construct() {
        $dsn = "mysql:host={$this->host};dbname={$this->dbname};charset=utf8mb4";
        $options = [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // throw an exception
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Default associative array
            PDO::ATTR_EMULATE_PREPARES   => false,                  // Disable simulation preprocessing，Using native preprocessing
        ];

        try {
            $this->pdo = new PDO($dsn, $this->username, $this->password, $options);
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
            // Log or process errors
            die("Database connection failed: " . $this->error);
        }
    }

    // Unified execution of query（SELECT）
    public function query($sql, $params = []) {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    // Unified execution of addition, deletion and modification（INSERT/UPDATE/DELETE）
    public function execute($sql, $params = []) {
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($params);
    }

    // Get the last insertID
    public function lastInsertId() {
        return $this->pdo->lastInsertId();
    }
}

?>

IV. Use examples

 <?php

$db = new Database();

// Security query example：prevent SQL injection
$userId = 123;
$result = $db->query("SELECT * FROM users WHERE id = :id", ['id' => $userId]);

// Safe Insert Example
$name = 'Zhang San';
$email = 'zhangsan@m66.net';
$insertSql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$db->execute($insertSql, ['name' => $name, 'email' => $email]);

?>

5. Why can this design prevent SQL injection?

Using PDO preprocessing statements, SQL statements are separated from data, and user input is not directly spliced into SQL strings
Parameter binding allows the database driver to automatically handle special character escape
Disable simulated preprocessing and enhance security with native preprocessing

6. Summary

Encapsulating a database connection class with a connect() function, selecting PDO as the underlying connection, and combining preprocessing statements is an efficient and secure solution to prevent SQL injection in PHP. The design of this class is simple and easy to scale, and can meet most business needs, and is a recommended practice for modern PHP development.

connect
PDO