How to Refactor PHP MySQLi Functions for Security

by

Summary

The analyzed code snippet implements basic database abstraction functions for a REST API using the mysqli extension. It provides two primary utility functions: one for retrieving data (adatokLekerdezese) and one for modifying data (adatokValtoztatasa). While functional for a local learning environment, the pattern demonstrates significant architectural anti-patterns regarding connection management, security, and error handling that would fail in a production-grade distributed system.

Root Cause

The technical issues stem from an imperative, procedural approach to resource management:

  • Connection Exhaustion: The functions instantiate a new mysqli object every single time they are called. In a high-concurrency environment, this leads to the “Too many connections” error.
  • SQL Injection Vulnerability: The functions accept a raw $muvelet (operation/query) string. This allows an attacker to pass malicious SQL directly into the function, bypassing any data sanitization.
  • Hardcoded Credentials: Database credentials (localhost, root, etc.) are embedded directly within the logic, violating the principle of separation of configuration from code.
  • Lack of Transactional Integrity: There is no support for atomic operations; if a series of related queries are needed, one could fail while others succeed, leaving the database in an inconsistent state.

Why This Happens in Real Systems

In real-world development, these patterns emerge due to:

  • Rapid Prototyping Pressure: Developers often prioritize “making it work” over “making it scale” to meet immediate deadlines.
  • Lack of Dependency Injection Knowledge: Without understanding how to inject a single database instance into multiple services, developers default to creating new connections locally.
  • Tutorial-Driven Development: Many early-stage tutorials teach the simplest possible implementation, which ignores the complexities of connection pooling and prepared statements.

Real-World Impact

If this code were deployed to a production environment, the consequences would be severe:

  • Security Breach: A single unvalidated input could lead to a full database dump or unauthorized administrative access via SQL injection.
  • Service Downtime: Under even moderate load, the overhead of the TCP handshake for every single query would saturate the database server’s connection limit, causing a complete denial of service (DoS).
  • High Latency: The cumulative time spent establishing and tearing down connections increases the Request/Response latency, degrading the user experience.

Example or Code

A senior engineer would refactor this using a Singleton or Dependency Injection pattern with Prepared Statements:

class Database {
    private static $instance = null;
    private $connection;

    private function __construct() {
        $this->connection = new mysqli("localhost", "user", "pass", "db");
        if ($this->connection->connect_error) {
            throw new Exception("Connection failed: " . $this->connection->connect_error);
        }
    }

    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance->connection;
    }
}

function safeQuery($sql, $params = []) {
    $db = Database::getInstance();
    $stmt = $db->prepare($sql);

    if (!$stmt) {
        throw new Exception($db->error);
    }

    if (!empty($params)) {
        $types = str_repeat('s', count($params));
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    return $stmt->get_result();
}

How Senior Engineers Fix It

Senior engineers apply architectural patterns to ensure stability and security:

  • Use Prepared Statements: Always separate the SQL command from the data using placeholders (?) to eliminate SQL Injection.
  • Implement Connection Pooling: Use a single, long-lived connection or a pool of connections to minimize the overhead of handshakes.
  • Environment Variables: Move all credentials to a .env file or a secret management service (like AWS Secrets Manager).
  • Global Exception Handling: Instead of returning error strings, use Try/Catch blocks and custom Exception classes to handle failures gracefully.
  • Layered Architecture: Separate the database logic (Data Access Layer) from the API logic (Controller Layer).

Why Juniors Miss It

Juniors often miss these critical flaws because:

  • Focus on Functional Correctness: They focus on “Does the data appear on the screen?” rather than “Is this code safe and scalable?”.
  • The “Happy Path” Bias: They test with valid, predictable inputs and rarely simulate malicious actors or high-concurrency loads.
  • Tooling Overload: They may rely on AI or tutorials that provide “copy-paste” solutions without explaining the underlying resource lifecycle management.

Leave a Comment