SQL Injection — GBK Encoding Bypass Exposed 340k Records

Every year, SQL injection ranks in the OWASP Top 10 most critical web security risks — and it has for over two decades. It's not a niche academic threat. It's the vulnerability behind some of the most embarrassing data breaches in history: stolen user credentials, exposed credit card numbers, entire databases wiped overnight. The scary part? A single unguarded PHP script connecting to MySQL is all it takes. One $_GET variable dropped raw into a query, and an attacker owns your database.

The core problem is trust. Early PHP database code treated user input as part of the SQL command itself. When you concatenate a username from a form directly into a query string, you're handing the attacker a microphone on your database server. They can close your query early, append their own commands, comment out the rest of your SQL, and walk away with whatever they want. The fix isn't complicated — but it requires understanding why the old approach was broken, not just memorising a new syntax.

By the end of this article you'll understand exactly how SQL injection works under the hood, why prepared statements eliminate the vulnerability at the protocol level (not just cosmetically), and how to write production-grade PHP database code using both PDO and MySQLi. You'll also know the three mistakes that catch even experienced developers off guard — and how interviewers probe for this knowledge.

Why Parameterized Queries Are Not Optional for SQL Injection Prevention

SQL injection prevention in PHP is the practice of ensuring user-supplied input cannot alter the intended structure of SQL queries. The core mechanic is separating SQL logic from data: using prepared statements with bound parameters (via PDO or MySQLi) so the database engine treats input as literal values, never as executable code. Without this separation, any input—like a single quote or a GBK-encoded multibyte character—can break out of its string context and rewrite the query.

Prepared statements work by sending the query template to the database first, then sending the parameters separately. This means the database already knows the query's shape before it sees any user data. Even if an attacker passes 1 OR 1=1 or a GBK byte sequence that tricks mysql_real_escape_string() into producing a valid escape character, the database never interprets that data as SQL syntax. The key property: escaping functions are context-dependent and can fail with certain character sets (e.g., GBK), but prepared statements are immune to encoding tricks.

Use prepared statements for every database query that includes user input—GET/POST parameters, headers, cookies, file names, anything. In production, a single raw query concatenated with user data is a breach waiting to happen. The 340k-record leak referenced in the title occurred because the application used mysql_real_escape_string() with a GBK connection, allowing an attacker to craft a multibyte sequence that consumed the escape character and injected a quote. Prepared statements would have prevented that entirely.

How SQL Injection Actually Works — And Why Raw Queries Are Dangerous

To prevent something you have to understand it first. Let's look at what actually happens when PHP builds a query by gluing strings together.

Suppose you have a login form. A user types their email and password, and your PHP script checks the database. The vulnerable version looks harmless until you realise that $_POST['email'] is completely user-controlled. An attacker doesn't type their email — they type a carefully crafted string that changes the meaning of your SQL.

The input ' OR '1'='1 turns a normal login check into a query that returns every user in the table. The attacker is now logged in as the first user — likely an admin. Even worse, the input '; DROP TABLE users; -- could wipe your entire users table if your database user has that permission. The double-dash -- is a SQL comment that silences the rest of your original query so there's no syntax error to trip the attacker up.

This works because MySQL receives one big string and has no idea where your intended SQL ends and the attacker's injected SQL begins. It's all just text to the parser. That's the fundamental flaw — and the fix is separating the query structure from the data, permanently, at the driver level.

<?php
// ============================================================
// WARNING: THIS IS THE VULNERABLE PATTERN — DO NOT USE IN PRODUCTION
// It exists here so you can see exactly what you must avoid.
// ============================================================

$host     = 'localhost';
$database = 'forge_app';
$username = 'forge_user';
$password = 'secret';

// Old-school mysqli connection (no prepared statements yet)
$connection = mysqli_connect($host, $username, $password, $database);

if (!$connection) {
    die('Database connection failed: ' . mysqli_connect_error());
}

// Imagine these come from a login form POST — user-controlled input
$emailFromForm    = $_POST['email']    ?? '';
$passwordFromForm = $_POST['password'] ?? '';

// THE VULNERABILITY: raw user input is concatenated directly into SQL
// An attacker can set $emailFromForm = "' OR '1'='1" to bypass the password check
$unsafeQuery = "SELECT id, email, role
                FROM users
                WHERE email    = '$emailFromForm'
                AND   password = '$passwordFromForm'";

// With the injected input, this actually executes:
// SELECT id, email, role FROM users
// WHERE email = '' OR '1'='1'
// AND password = 'anything'
// '1'='1' is always TRUE, so every row matches — instant bypass.

$result = mysqli_query($connection, $unsafeQuery);

if ($result && mysqli_num_rows($result) > 0) {
    $user = mysqli_fetch_assoc($result);
    // Attacker is now authenticated as this user — often the first admin
    echo "Logged in as: " . $user['email'] . " (role: " . $user['role'] . ")";
} else {
    echo "Invalid credentials.";
}

mysqli_close($connection);
?>

The Right Fix: PDO Prepared Statements That Make Injection Structurally Impossible

Prepared statements fix SQL injection at the protocol level. Here's what that means in practice: when you use a prepared statement, your PHP code sends the SQL template to MySQL first — with placeholders where data will go. MySQL parses and compiles that template into an execution plan. Then, separately, your data values are sent. By the time the data arrives, the query structure is already locked in. MySQL treats everything in those data slots as a literal value, never as SQL syntax — no matter what characters it contains.

PDO (PHP Data Objects) is the recommended approach because it's database-agnostic. You can switch from MySQL to PostgreSQL or SQLite by changing one line. MySQLi works too, but it's MySQL-only. Both use the same prepared statement concept under the hood.

There are two placeholder styles in PDO: named placeholders (:email) and positional placeholders (?). Named placeholders are more readable, especially when you have many parameters. Positional are slightly more concise. Pick one style and stay consistent within a project.

Notice in the code below that execute() receives an array of actual values. PDO sends those values over a separate channel from the SQL text. Even if the value contains '; DROP TABLE users; --, MySQL sees it as a string value for the email column — not SQL commands. The injection attempt is completely inert.

<?php
// ============================================================
// SECURE VERSION — PDO with prepared statements
// This is production-grade code. Use this pattern.
// ============================================================

$host     = 'localhost';
$dbName   = 'forge_app';
$dbUser   = 'forge_user';
$dbPass   = 'secret';

// PDO DSN (Data Source Name) — tells PDO which driver and database to use
$dsn = "mysql:host=$host;dbname=$dbName;charset=utf8mb4";

// Connection options that every production app should set
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // Throw exceptions on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // Return rows as associative arrays
    PDO::ATTR_EMULATE_PREPARES   => false,                   // Use REAL prepared statements, not emulated ones
];

try {
    $pdo = new PDO($dsn, $dbUser, $dbPass, $options);
} catch (PDOException $connectionError) {
    // Log the real error internally — never expose DB details to the user
    error_log('DB connection failed: ' . $connectionError->getMessage());
    die('Service temporarily unavailable. Please try again later.');
}

// Collect user input — in real apps, you'd also validate format here
$emailInput    = $_POST['email']    ?? '';
$passwordInput = $_POST['password'] ?? '';

// --- STEP 1: Define the SQL template with named placeholders ---
// MySQL receives and compiles THIS string. No user data is in it yet.
$sqlTemplate = "SELECT id, email, password_hash, role
                FROM   users
                WHERE  email = :email
                LIMIT  1";

// --- STEP 2: Prepare the statement ---
$statement = $pdo->prepare($sqlTemplate);

// --- STEP 3: Execute with the actual data values ---
// These values are sent separately — MySQL treats them as data, NEVER as SQL.
// Even if $emailInput = "' OR '1'='1", it's just a string being compared to the email column.
$statement->execute([':email' => $emailInput]);

// --- STEP 4: Fetch the result ---
$matchedUser = $statement->fetch();

if ($matchedUser && password_verify($passwordInput, $matchedUser['password_hash'])) {
    // password_verify() safely compares the plain input against the stored bcrypt hash
    echo "Welcome back, " . htmlspecialchars($matchedUser['email']) . "!";
    echo " Your role is: " . htmlspecialchars($matchedUser['role']);

    // In a real app: session_start(); $_SESSION['user_id'] = $matchedUser['id'];
} else {
    // Deliberately vague — don't tell attackers whether the email or password was wrong
    echo "Invalid email or password.";
}
?>

MySQLi Prepared Statements and Binding — When You're Working With MySQLi

PDO is the modern default, but you'll encounter MySQLi — especially in WordPress plugins, legacy codebases, and older hosting environments. MySQLi's prepared statement API is slightly more verbose, using bind_param() to explicitly declare the data types of each parameter.

The type string in bind_param() is critical and often misunderstood. Each character represents a parameter: s for string, i for integer, d for double (float), b for blob. If you bind an integer as a string, MySQL coerces it — usually harmless, but it's sloppy. More importantly, getting this right proves you understand what's actually happening at the driver level.

MySQLi also supports get_result() which returns a familiar result object, but it requires the mysqlnd driver (which is standard on PHP 7+). The bind_result() alternative works without mysqlnd but is clunkier for multi-column results.

The security guarantee is identical to PDO: the SQL template and the data are sent to MySQL separately. Injection is structurally impossible regardless of what the user submits.

<?php
// ============================================================
// SECURE MySQLi — prepared statements with bind_param()
// Common in WordPress ecosystems and legacy PHP applications
// ============================================================

$host     = 'localhost';
$dbUser   = 'forge_user';
$dbPass   = 'secret';
$dbName   = 'forge_app';

// Object-oriented MySQLi style (preferred over procedural)
$mysqli = new mysqli($host, $dbUser, $dbPass, $dbName);

if ($mysqli->connect_errno) {
    error_log('MySQLi connect error: ' . $mysqli->connect_error);
    die('Service temporarily unavailable.');
}

// Set charset explicitly — always do this to avoid encoding-based injection vectors
$mysqli->set_charset('utf8mb4');

// Imagine this is a search feature — user searches for members by username
$searchTerm = $_GET['username'] ?? '';
$minAge     = (int) ($_GET['min_age'] ?? 18); // Cast to int immediately — never trust input

// SQL template — two placeholders: one string, one integer
$sqlTemplate = "SELECT id, username, email, age
                FROM   members
                WHERE  username LIKE ?
                AND    age      >= ?
                ORDER  BY username ASC
                LIMIT  50";

// Prepare the statement
$statement = $mysqli->prepare($sqlTemplate);

if (!$statement) {
    error_log('Prepare failed: ' . $mysqli->error);
    die('Search unavailable.');
}

// Wrap the search term in % wildcards for a LIKE search
// This is safe — the wildcards are added BEFORE binding, so they're part of the data value
$likePattern = '%' . $searchTerm . '%';

// bind_param(types, ...variables)
// 'si' = first param is string (s), second is integer (i)
// Variables are bound by REFERENCE — they're evaluated at execute() time
$statement->bind_param('si', $likePattern, $minAge);

// Execute — data travels to MySQL separately from the SQL template
$statement->execute();

// get_result() requires mysqlnd driver (standard on PHP 7+)
$results = $statement->get_result();

if ($results->num_rows === 0) {
    echo "No members found matching '" . htmlspecialchars($searchTerm) . "'.";
} else {
    echo "Found " . $results->num_rows . " member(s):\n\n";

    while ($member = $results->fetch_assoc()) {
        // htmlspecialchars() prevents XSS when displaying data back in HTML
        echo "- " . htmlspecialchars($member['username'])
             . " (" . htmlspecialchars($member['email']) . ")"
             . " Age: " . (int)$member['age'] . "\n";
    }
}

// Always close the statement and connection
$statement->close();
$mysqli->close();
?>

Beyond Queries: Input Validation, Output Encoding, and Least Privilege

Prepared statements are your primary defence — but a real production application uses defence in depth. Think of it as layers: if one layer fails, the next one catches it.

Input Validation means rejecting data that doesn't fit your expected format before it touches the database. If a field expects a UK postcode, validate it with a regex. If it expects an integer ID, cast it with (int) immediately after reading from $_GET or $_POST. This isn't a substitute for prepared statements — it's an additional filter that catches garbage data early and makes your code's intent explicit.

Output Encoding is separate but critical. htmlspecialchars() converts <, >, &, and quotes into HTML entities. This prevents stored XSS — where an attacker injects JavaScript into your database that then executes in other users' browsers. Always encode output when rendering to HTML. Never echo database values raw.

Database Least Privilege is the most overlooked layer. Your PHP application's database user should only have the permissions it actually needs. A read-heavy app needs SELECT only. A typical CRUD app needs SELECT, INSERT, UPDATE, DELETE — but never DROP, CREATE, or GRANT. If an attacker somehow bypasses everything else, a restricted DB user stops them from deleting tables or creating backdoor accounts.

<?php
// ============================================================
// HARDENED UPDATE EXAMPLE — Input validation + prepared statement
// + output encoding + principle of least privilege in action
// ============================================================

// --- Input Validation Layer ---
// Validate BEFORE touching the database. Fail fast with clear errors.

$userId      = filter_input(INPUT_POST, 'user_id', FILTER_VALIDATE_INT);
$newUsername = trim(filter_input(INPUT_POST, 'username', FILTER_SANITIZE_SPECIAL_CHARS) ?? '');
$newEmail    = filter_input(INPUT_POST, 'email', FILTER_VALIDATE_EMAIL);
$newAge      = filter_input(INPUT_POST, 'age', FILTER_VALIDATE_INT, [
    'options' => ['min_range' => 13, 'max_range' => 120]
]);

$validationErrors = [];

if ($userId === false || $userId === null) {
    $validationErrors[] = 'Invalid user ID.';
}
if (strlen($newUsername) < 3 || strlen($newUsername) > 30) {
    $validationErrors[] = 'Username must be 3-30 characters.';
}
if ($newEmail === false) {
    $validationErrors[] = 'Invalid email address format.';
}
if ($newAge === false) {
    $validationErrors[] = 'Age must be between 13 and 120.';
}

if (!empty($validationErrors)) {
    // Return errors before any database work happens
    http_response_code(422);
    foreach ($validationErrors as $error) {
        // htmlspecialchars() here because we're outputting to HTML
        echo htmlspecialchars($error) . "\n";
    }
    exit;
}

// --- Database Layer — PDO prepared statement ---
$dsn = 'mysql:host=localhost;dbname=forge_app;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, 'forge_app_writer', 'writerpass', $options);
    // Note: forge_app_writer only has SELECT, INSERT, UPDATE, DELETE
    // It CANNOT DROP tables, CREATE users, or GRANT permissions

    $updateStatement = $pdo->prepare(
        "UPDATE users
         SET    username = :username,
                email    = :email,
                age      = :age,
                updated_at = NOW()
         WHERE  id = :userId"
    );

    $updateStatement->execute([
        ':username' => $newUsername,
        ':email'    => $newEmail,
        ':age'      => $newAge,
        ':userId'   => $userId,
    ]);

    $rowsAffected = $updateStatement->rowCount();

    if ($rowsAffected === 1) {
        // Output encoding before rendering to HTML — prevents stored XSS
        echo "Profile updated for: " . htmlspecialchars($newUsername);
    } else {
        echo "No profile found with that ID, or no changes were made.";
    }

} catch (PDOException $dbError) {
    // Log full error internally — show nothing useful to the attacker
    error_log('Profile update error: ' . $dbError->getMessage());
    http_response_code(500);
    echo 'An error occurred. Please try again.';
}
?>

The Hidden Danger: Dynamic Queries in ORDER BY, LIMIT, and Table Names

You think prepared statements cover everything? They don't. Parameters only work for data values — not for SQL structural elements like column names, table names, or ORDER BY directions. When you inject user input into these positions, prepared statements can't help you. The attacker owns your query structure.

The real problem: developers assume parameterization is a magic bullet. It's not. That LIMIT and OFFSET in your pagination? Still vulnerable. That sortable table column? Also vulnerable. The fix is strict whitelisting. Map user input to known valid values before it ever touches SQL. If the user wants to sort by 'email', you check: is 'email' in your approved list of column names? Yes — use it. No — default to 'id'. Same for LIMIT. Cast it to an integer. If it fails, use a sensible default.

This isn't about being clever. It's about accepting that parameterized queries have sharp edges. Know where they end and build hard checks for what's left.

// io.thecodeforge
$allowedSorts = ['id', 'name', 'email', 'created_at'];
$sort = $_GET['sort'] ?? 'id';
$sort = in_array($sort, $allowedSorts, true) ? $sort : 'id';

$allowedDirs = ['ASC', 'DESC'];
$dir = strtoupper($_GET['dir'] ?? 'ASC');
$dir = in_array($dir, $allowedDirs, true) ? $dir : 'ASC';

$stmt = $pdo->prepare("SELECT id, name, email FROM users ORDER BY {$sort} {$dir} LIMIT :limit");
$stmt->bindValue(':limit', (int) ($_GET['limit'] ?? 50), PDO::PARAM_INT);
$stmt->execute();

Defense in Depth: Why Input Validation Is Your Second Wall, Not Your First

I hear this every week: 'But we validate all inputs, so SQL injection is impossible.' Wrong. Input validation is a mitigation, not a cure. You validate for business rules, not security. The real cure is structural separation of code and data — prepared statements. Validation stops garbage. Prepared statements stop injection.

Here's the order I teach juniors: First, parameterize everything that can be parameterized. Second, validate what can't — column names, sort orders, file paths. Third, encode output for its context (HTML entities for HTML, hex for URLs). Fourth, apply least privilege to your database user. That application user does not need DROP TABLE or ALTER privileges.

Why this order? Because validation fails sometimes. A regex that allows 'Robert'); DROP TABLE Students;--' through? It happens. But if that string never becomes SQL code because you used a parameter, it's just a weird name in the database. Validation is your backup parachute, not your primary flight system. Build your layers in the right order.

// io.thecodeforge
// Layer 1: Parameterized query (primary defense)
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->execute([
    ':name' => $_POST['name'],
    ':email' => filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)
]);

// Layer 2: Input validation (secondary — catches garbage)
if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
    log_warning('Invalid email attempt: ' . $_POST['email']);
    http_response_code(400);
    exit;
}

// Layer 3: Output encoding for display
$safeName = htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8');
echo "<div>Welcome, {$safeName}</div>";

// Layer 4: Database user has SELECT, INSERT, UPDATE only — no DROP/DELETE on app tables

Prepared Statements: The Only Reliable Defense

Prepared statements are the gold standard for preventing SQL injection because they separate SQL logic from data. Instead of concatenating user input into a query string, you send a template to the database server, which compiles it with placeholders. Then you send the actual values separately, ensuring they are never interpreted as SQL code. This structural separation makes injection structurally impossible, regardless of encoding tricks like GBK. In PHP, both PDO and MySQLi support prepared statements. The key is to always use them for any query that includes user input, even if you think the input is safe. For example, never do this: $sql = "SELECT FROM users WHERE id = $_GET[id]"; Instead, use a prepared statement: $stmt = $pdo->prepare('SELECT FROM users WHERE id = :id'); $stmt->execute(['id' => $_GET['id']]); This simple pattern eliminates the entire class of injection vulnerabilities. Many developers mistakenly believe that escaping functions like mysqli_real_escape_string are sufficient, but they can be bypassed with certain character sets (like GBK) or edge cases. Prepared statements are the only defense that works at the database level, before any encoding or escaping is applied. They are not optional; they are mandatory for any production PHP application that interacts with a database.

<?php
// Correct: Using PDO prepared statement
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $_POST['email']]);
$user = $stmt->fetch();

// Incorrect: Raw query concatenation (vulnerable)
// $sql = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";
// $result = $pdo->query($sql);
?>

Input Validation Layers in Modern PHP

While prepared statements prevent SQL injection at the database layer, input validation adds a critical second line of defense. Modern PHP applications should implement multiple validation layers: client-side (JavaScript), server-side (PHP), and database-level constraints. Server-side validation is non-negotiable because client-side validation can be bypassed. Use PHP filters (filter_var, filter_input) for basic type and format checks. For complex rules, consider using validation libraries like Symfony Validator or Respect\Validation. Always validate on the server even if you trust the source. For example, validate that an email is actually an email, that an ID is numeric, and that a string length is within expected bounds. However, validation should never be used as a substitute for prepared statements; it is an additional layer. A common mistake is to validate input and then still use it unsafely in a query. Validation reduces the attack surface but does not eliminate injection risk. Combine validation with prepared statements for defense in depth. Also, validate output encoding to prevent XSS, but that's a separate concern. In summary, validate early, validate often, but never trust validation to make raw queries safe.

<?php
// Server-side validation example
$email = $_POST['email'] ?? '';
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
    die('Invalid email format');
}

// Still use prepared statement even after validation
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);

// Validate numeric ID
$id = $_GET['id'] ?? 0;
if (!ctype_digit($id)) {
    die('Invalid ID');
}
$stmt = $pdo->prepare('SELECT * FROM posts WHERE id = :id');
$stmt->execute(['id' => (int)$id]);
?>

Modern Best Practices: ORMs, Query Builders, and RAW SQL

Modern PHP frameworks like Laravel, Symfony, and Doctrine provide ORMs and query builders that abstract raw SQL and automatically use prepared statements. For example, Laravel's Eloquent ORM generates parameterized queries under the hood: User::where('email', $email)->first(); This is safe because the framework handles binding. Query builders like Doctrine's DBAL also use prepared statements. However, developers sometimes fall back to raw SQL for complex queries or performance reasons. When using raw SQL with an ORM, you must still use prepared statements. For example, in Laravel, use DB::select('SELECT * FROM users WHERE email = ?', [$email]); Never concatenate. ORMs are not magic; they can be misused. For instance, using whereRaw() with concatenation is dangerous. The best practice is to use the ORM's query builder as much as possible, and when raw SQL is unavoidable, always use parameter binding. Also, be aware that some ORM features like lazy loading can generate additional queries, but those are also parameterized. In summary, ORMs and query builders are safe when used correctly, but they do not eliminate the need for understanding prepared statements. Always verify that your ORM is using parameterized queries, especially when using raw methods.

<?php
// Laravel Eloquent (safe)
$user = User::where('email', $request->input('email'))->first();

// Laravel Query Builder (safe)
$users = DB::table('users')->where('email', $request->input('email'))->get();

// Raw SQL with binding (safe)
$users = DB::select('SELECT * FROM users WHERE email = ?', [$request->input('email')]);

// Dangerous: raw concatenation (never do this)
// $users = DB::select("SELECT * FROM users WHERE email = '" . $request->input('email') . "'");
?>