Chapter 5: The Database Layer: A Stored Procedure Gateway

Introduction: PHP as a Secure Gateway

In our unified application, the PHP code is never allowed to construct or execute raw SQL queries. Instead, the database itself, through a comprehensive library of MariaDB Stored Procedures, contains all data-related business logic. The role of the PHP application is to act as a secure gateway.

The EXECUTE Only Security Model

The single most important architectural decision in our unified application is how it interacts with the database. Our approach is built on a simple but powerful rule: the application is never trusted with direct access to data. In our setup, the database user that PHP connects with has its privileges stripped to the absolute minimum. It does not have SELECT, INSERT, UPDATE, or DELETE permissions on any tables. The only permission granted is EXECUTE on a specific, approved list of stored procedures.

A Typical Application vs. Our Gateway

• In a typical application, the PHP code might build an SQL query like UPDATE adverts SET title = ?. For this to work, the application's database user must be granted the UPDATE privilege on the entire adverts table, creating a broad attack surface.
• In our architecture, the PHP code simply makes a request: $db->sp('advertUpdateTitle', "'{$adId}', '{$newTitle}'");. The application's database user only needs EXECUTE permission on that one procedure.

This makes traditional SQL injection attacks a practical impossibility.

The Database Class: Our Custom Gateway

The bridge between our PHP application and the stored procedures is a single, custom class named Database that extends PHP's built-in MySQLi class.

A Note on PDO vs. MySQLi: A Deliberate Choice

Many experienced PHP developers, trained in the modern era, will immediately ask, "Why extend MySQLi and not use PDO?" After all, the standard advice for years has been to use PDO (PHP Data Objects) for all database interactions, and for good reason: it provides a consistent, abstract interface for connecting to many different types of databases.

However, a deeper architectural understanding involves knowing when to deliberately choose a different path. Our choice to use MySQLi is not made out of ignorance of the standard, but a specific engineering decision to use the most specialised tool for a specialised job.

Think of it this way: PDO is a Swiss Army knife, while MySQLi is a scalpel.

The Swiss Army knife (PDO) is fantastic if you need a general-purpose tool that can work with a dozen different database systems (PostgreSQL, SQLite, Oracle, etc.). Its abstraction layer is its greatest strength.

The scalpel (MySQLi) is designed to do one job with extreme precision. It is built specifically for MySQL and its derivatives like MariaDB.

Furthermore, PDO's central promise of database portability is often a mirage for any non-trivial application. While it abstracts the PHP API calls (like prepare() and execute()), it cannot abstract the SQL language itself. A complex query or stored procedure written to leverage the unique features of PostgreSQL will fail on MariaDB. For any serious application, you are already committing to a specific SQL dialect, which makes a general-purpose abstraction layer like PDO less compelling.

MySQLi, on the other hand, has a specific and robust API (more_results(), next_result()) designed to handle the complete feature set of MySQL and MariaDB stored procedures without ambiguity.

This is not a security decision; both are completely secure when used correctly with prepared statements. We are choosing the specialised tool because our architecture is intentionally and deeply "married" to MariaDB, making MySQLi the most direct and logical choice.

Our architecture is intentionally and deeply "married" to MariaDB to leverage its advanced stored procedure features. The database portability that PDO offers is an irrelevant benefit for us; we have no intention of switching database vendors. By choosing MySQLi, we gain a more direct, often marginally faster API and guaranteed access to every vendor-specific feature that MariaDB offers.

It's also important to bust a common myth: this is not a security decision. Both PDO and MySQLi are completely secure when used correctly with prepared statements. The choice is purely about architectural philosophy. We are choosing the specialised scalpel over the general-purpose Swiss Army knife because it is the best and most direct tool for our specific, database-centric design.

Conceptual Code

<?php
class Database extends MySQLi
{
    public function __construct()
    {
        // Read credentials from $_SERVER variables set by Apache.
        $host = $_SERVER['DB_HOST'] ?? 'localhost';
        $user = $_SERVER['DB_USER'] ?? 'root';
        $pass = $_SERVER['DB_PASS'] ?? '';
        $db   = $_SERVER['DB_NAME'] ?? 'default_db';
        parent::__construct($host, $user, $pass, $db);
        if ($this->connect_error) {
            throw new Exception('Database connection failed: ' . $this->connect_error);
        }
    }
    public function sp(string $procedureName, string $params = ''): array
    {
        $sql = "CALL {$procedureName}({$params})";
        $result = $this->query($sql);
        if ($result === false) {
            return [];
        }
        $data = $result->fetch_all(MYSQLI_ASSOC);
        $result->free();
        while ($this->more_results() && $this->next_result()) {}
        return $data;
    }
}

The code presented here is a conceptual overview of our Database class. The definitive, production-ready version, which includes critical security enhancements like prepared statements, robust error handling, and more flexible methods for passing parameters, is covered in detail in Part 3: The Database.

A Practical Example: Fetching a User

For user identification, we use UUIDs (CHAR(36)) instead of auto-incrementing integers to prevent attackers from enumerating users.

1. The PHP Call in the Model PHP

   <?php
   // In a file like 'UserProfileModel.php'
   $userUuid = 'f47ac10b-xxxx-xxxx-xxxx-0e02b2c3d479';
   $userData = $db->sp('userGetByUuid', "'{$userUuid}'");

2. The Stored Procedure in MariaDB SQL

   -- The userGetByUuid Stored Procedure
   CREATE PROCEDURE `userGetByUuid`(IN `p_userUuid` CHAR(36))
   BEGIN
   SELECT
   `userId`, `username`, `email`, `dateCreated`
   FROM `users`
   WHERE `userId` = p_userUuid
   LIMIT 1;
   END

   Conclusion: A Fortified Data Layer

   By centralising all database interactions into a single gateway class and moving all SQL logic into stored procedures with an EXECUTE only permission model, we have created a truly fortified data layer, built to be robust, secure, and maintainable.