SQL Injection and Prevention

How SQL Injection Changes a Query

SQL injection occurs when untrusted input is combined with SQL text as executable structure. The database cannot know which characters the developer intended as data. Attackers may alter predicates, add expressions or exploit another unsafe statement.

// Intended idea
SELECT student_id,name FROM students WHERE email = [one data value]

The secure design keeps the SQL template and data values on separate channels. It also enforces authorization, because a perfectly parameterized query can still disclose another student's record if its WHERE clause lacks the permitted class or tenant boundary.

Injection can affect SELECT, INSERT, UPDATE, DELETE, authentication, reporting, search, ORDER BY, import filters and administration code—not only login forms.

Recognize the Vulnerable Pattern

$email = $_GET['email'] ?? '';
$sql = "SELECT student_id,name,email
        FROM students WHERE email='$email'";
$rows = $pdo->query($sql)->fetchAll();

If input contains SQL syntax, concatenation can change the predicate. A classic test payload such as ' OR 1=1 -- can turn a single-record lookup into a broader query where parsing permits it. Quoting or removing a few characters is not a complete defense: encodings, SQL modes, numeric contexts and missed code paths differ.

Use Native PDO Prepared Statements for Values

$pdo = new PDO(
 'mysql:host=db.internal;dbname=school;charset=utf8mb4',
 $dbUser,$dbPassword,[
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::ATTR_EMULATE_PREPARES => false,
 ]
);

$stmt=$pdo->prepare(
 'SELECT student_id,name,email
  FROM students
  WHERE class_id=:class_id AND email=:email'
);
$stmt->execute([
 ':class_id'=>$authorizedClassId,
 ':email'=>$validatedEmail,
]);
$row=$stmt->fetch();

The payload is sent as a value, so it cannot close the quoted context and create a new predicate. The authorized class is part of the query. Configure exceptions, a correct utf8mb4 connection and native prepares; test the actual driver/server behavior.

Never log the DSN password or return the exception text to the browser.

Handle Dynamic Identifiers with Fixed Mappings

Parameter markers represent data values, not table names, column names, SQL keywords or ASC/DESC. Map a small request vocabulary to hard-coded SQL fragments:

$sortMap=[
 'name'=>'student_name',
 'date'=>'admission_date',
 'id'=>'student_id',
];
$directionMap=['asc'=>'ASC','desc'=>'DESC'];

$sort=$sortMap[$_GET['sort']??'name']??'student_name';
$direction=$directionMap[
 strtolower($_GET['direction']??'asc')
]??'ASC';

$sql="SELECT student_id,student_name
      FROM students WHERE class_id=:class_id
      ORDER BY `$sort` $direction";
$stmt=$pdo->prepare($sql);
$stmt->execute([':class_id'=>$authorizedClassId]);

Only mapping values—not raw request text—enter the SQL structure. Backtick-quoting arbitrary input is not authorization. The same rule applies to table selection, aggregate, operator, JOIN and report column lists.

Parameterize LIKE, IN Lists and LIMIT Correctly

// LIKE: wildcard policy is explicit
$stmt=$pdo->prepare(
 'SELECT student_id FROM students
  WHERE student_name LIKE :pattern'
);
$stmt->execute([':pattern'=>'%'.$searchText.'%']);

// IN: one marker for each validated value
$ids=array_values(array_filter(
 $requestedIds,fn($v)=>filter_var($v,FILTER_VALIDATE_INT)!==false
));
if(!$ids) { $rows=[]; }
else {
 $marks=implode(',',array_fill(0,count($ids),'?'));
 $stmt=$pdo->prepare(
  "SELECT student_id,name FROM students
   WHERE class_id=? AND student_id IN ($marks)"
 );
 $stmt->execute(array_merge([$authorizedClassId],$ids));
 $rows=$stmt->fetchAll();
}

$stmt=$pdo->prepare(
 'SELECT student_id,name FROM students
  WHERE class_id=:class ORDER BY student_id LIMIT :limit'
);
$stmt->bindValue(':class',$authorizedClassId,PDO::PARAM_INT);
$stmt->bindValue(':limit',min($requestedLimit,100),PDO::PARAM_INT);
$stmt->execute();

Limit list length and result size to control denial-of-service risk. Decide whether user-supplied %/_ are wildcards or literal search characters and escape consistently when literals are required.

Add Authorization, Least Privilege and Safe Errors

• Use a dedicated user@host runtime account, never root.
• Grant only required tables/actions; separate migration, report and backup identities.
• Enforce row/tenant/class ownership in every query or trusted data-access layer.
• Verify TLS identity and keep secrets outside code/source control.
• Disable multi-statements unless a reviewed feature requires them.
• Apply request size, timeout, pagination and rate limits.
• Return generic errors with a correlation ID; protect detailed logs.

SELECT CURRENT_USER(),CURRENT_ROLE();

Stored procedures are not automatically safe: unsafe dynamic SQL inside a procedure can still inject, and a powerful DEFINER can increase impact. Views and routines require deliberate SQL SECURITY and privilege review.

Review least privilege and database security.

Test the Boundary and Monitor Attempts

1. Create an isolated test database with synthetic data.
2. Inventory every code path that builds SQL, including jobs and admin reports.
3. Test quotes, comment markers, Unicode, numeric input, NULL and long values.
4. Verify dynamic sort/filter choices reject unknown values.
5. Assert a normal request succeeds and injection-shaped data returns no extra rows.
6. Assert cross-class/tenant IDs remain denied.
7. Use static analysis, code review and authorized dynamic security testing.
8. Monitor repeated syntax errors, denied requests and unusual result/export volume.

Do not store complete malicious payloads when they could contain personal data or log-control characters. Normalize/limit log fields and alert on patterns without leaking secrets.

SQL Injection Review Checklist

1. No request/session/file value concatenated into SQL.
2. Every data value bound with the correct type.
3. Identifiers, directions and operators use fixed allowlists.
4. IN creates one marker per bounded item.
5. LIKE wildcard behavior is deliberate.
6. Authorization boundary appears in the query.
7. Runtime account has minimum privileges.
8. Native prepares, charset and error mode configured.
9. User errors reveal no SQL/schema/secrets.
10. Positive, negative and cross-tenant tests automated.

Continue with prepared statements, GRANT/REVOKE and transactions.

Official References

• MySQL 8.4: Prepared Statements
• MySQL 8.4: Security Guidelines
• PHP Manual: PDO::prepare

Marker limits, access-control guidance and PDO behavior were checked against the official MySQL and PHP documentation.