Prevent SQL Injection in PHP: Prepared Statements Guide

Never put raw user input directly into SQL — preventing sql инъекция php (php sql injection) starts with using prepared statements php (PDO or MySQLi) and parameterized queries, plus input whitelisting and least‑privilege database accounts. Prepared statements separate SQL code from data so payloads like value'); DROP TABLE table;-- can’t turn into executable SQL; complement them with strong input validation, password hashing, and regular testing for sql инъекции.

Contents

• Preventing SQL injection in PHP (sql инъекция php)
• Prepared statements in PHP (prepared statements php)
• Passwords and authentication (php security)
• Input validation, whitelisting, and escaping (methods of protection from sql injections)
• Least privilege, DB configuration, and runtime defenses (защита от sql инъекций)
• Testing, scanning and detection (sql инъекции тестирование)
• Quick checklist
• Sources
• Conclusion

Preventing SQL injection in PHP (sql инъекция php)

If you concatenate $_POST or any user input into an SQL string, you give the attacker a chance to change the query’s meaning. The unsafe example from your question shows this exactly:

$unsafe_variable = $_POST['user_input']; 
mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

When an attacker submits value'); DROP TABLE table;-- the database executes two statements: the INSERT and the DROP. The fix is simple in principle: never mix data with SQL syntax. Instead, use parameterized queries so the database treats user values strictly as data, not commands. For an authoritative treatment of the risk and the recommended defense, see the PHP manual on SQL injection and the OWASP prevention cheat sheet linked below.

Prepared statements in PHP (prepared statements php)

Prepared statements (parameterized queries) are the primary defense. You write the SQL with placeholders, then bind values. The database engine (or driver) handles escaping and typing for you.

PDO example (recommended pattern):

$pdo = new PDO(
 'mysql:host=localhost;dbname=mydb;charset=utf8mb4',
 $dbUser,
 $dbPass,
 [
 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
 PDO::ATTR_EMULATE_PREPARES => false, // use native prepares when possible
 ]
);

$stmt = $pdo->prepare('INSERT INTO `table` (`column`) VALUES (:value)');
$stmt->execute(['value' => $_POST['user_input']]);

MySQLi example:

$mysqli = new mysqli('localhost', $dbUser, $dbPass, 'mydb');
$stmt = $mysqli->prepare('INSERT INTO `table` (`column`) VALUES (?)');
$stmt->bind_param('s', $_POST['user_input']);
$stmt->execute();

Notes and gotchas:

• Turn off PDO emulation (PDO::ATTR_EMULATE_PREPARES => false) so placeholders are handled by the server when supported; this avoids subtle issues with binary data and escaping.
• Prepared statements prevent injection for values, but NOT for SQL identifiers (table/column names) or SQL fragments — those must be handled by whitelisting (see below).
• Avoid the old ext/mysql functions entirely; they’re removed from modern PHP and don’t support prepared statements. The PHP manual and many practical guides cover prepared statements in detail: see the PHP manual and a practical guide on prepared statements linked below.

For implementation examples and step‑by‑step guidance, see the PHP manual and this practical article on using prepared statements in PHP: https://www.php.net/manual/en/security.database.sql-injection.php and https://accuweb.cloud/resource/articles/prevent-sql-injection-in-php-with-prepared-statements

Passwords and authentication (php security)

Passwords deserve special handling. Don’t store raw passwords or compare them directly in SQL. Hash on write and verify in PHP:

// When creating an account
$hash = password_hash($password, PASSWORD_DEFAULT);
$stmt = $pdo->prepare('INSERT INTO users (username, password_hash) VALUES (:u, :h)');
$stmt->execute(['u' => $username, 'h' => $hash]);

// When checking login
$stmt = $pdo->prepare('SELECT password_hash FROM users WHERE username = :u');
$stmt->execute(['u' => $username]);
$row = $stmt->fetch();
if ($row && password_verify($passwordInput, $row['password_hash'])) {
 // authenticated
}

Why this matters: even with prepared statements, storing cleartext passwords is catastrophic if your database is leaked. Hashing (with password_hash/password_verify) separates credential handling from SQL injection concerns, and prepared statements still protect the queries you run.

Input validation, whitelisting, and escaping (methods of protection from sql injections)

Prepared statements deal with data, but you should still validate and restrict input to reduce risk and logic errors.

• Prefer whitelists over blacklists. If a parameter must be an integer, use FILTER_VALIDATE_INT or cast and check bounds.
• Example: $id = filter_input(INPUT_POST, ‘id’, FILTER_VALIDATE_INT);
• For values like emails or dates, validate format with filters or regex.
• For dynamic identifiers (table or column names) you can’t bind them. Use a strict whitelist mapping:

$allowedTables = ['users' => 'users', 'orders' => 'orders'];
$tableKey = $_POST['table_key'] ?? '';
if (!isset($allowedTables[$tableKey])) { throw new Exception('Invalid table'); }
$sql = "SELECT * FROM `{$allowedTables[$tableKey]}` WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$id]);

• Escaping functions such as mysqli_real_escape_string() exist, but escaping is a fallback — not a primary defense. OWASP lists parameterized queries as the top mitigation and escaping as a last resort: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

Small changes (length limits, rejecting unexpected characters) reduce attack surface and make business logic safer. But don’t substitute validation for prepared statements — do both.

Least privilege, DB configuration, and runtime defenses (защита от sql инъекций)

You want layers. Even if an attacker finds a way to inject SQL, the damage should be limited.

• Use a dedicated database account with only the required permissions (SELECT, INSERT, UPDATE) — never use the DB root/administrator account from your app.
• Avoid APIs that execute multiple statements from one string (e.g., mysqli_multi_query) unless absolutely necessary. Prevent multi-statement execution so a single input can’t chain a DROP command.
• Configure the DB server securely and log suspicious queries. Monitor error rates and abnormal patterns.
• Consider a Web Application Firewall (WAF) for additional filtering and automated attack mitigation.
• Stored procedures can help if they don’t build SQL dynamically. They’re not a silver bullet, but when combined with parameterization they can reduce risk.

These operational controls are recommended alongside prepared statements and input validation to create defense in depth.

Testing, scanning and detection (sql инъекции тестирование)

How do you know your app is safe? You test.

• Automated scanners and penetration tests (DAST) can find obvious injection points; manual code review and SAST help catch developer mistakes.
• Add tests to your CI: run static checks and some dynamic testcases that assert your prepared statements behave as expected.
• Review logs for failed queries and unusual inputs; implement alerting for repetitive syntax errors that look like probing.
• Community threads and how‑tos discuss common pitfalls and tests — practical discussions are useful as reference: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php

Testing complements best practices. If you’ve prepared and validated everywhere, attacks should fail harmlessly.

Quick checklist

• Use prepared statements (PDO or MySQLi) for all SQL that includes user data.
• Disable PDO emulation of prepares (set PDO::ATTR_EMULATE_PREPARES => false).
• Never use ext/mysql or string‑concatenated queries with user input.
• Validate and whitelist inputs; cast types; enforce length limits.
• Don’t allow user input to control identifiers (table/column names) unless chosen from a strict whitelist.
• Hash passwords with password_hash() and verify with password_verify().
• Run the app with a least‑privilege DB user.
• Avoid multi‑statement execution and mysqli_multi_query unless you absolutely need it.
• Add security tests to CI and perform periodic penetration testing.
• Read OWASP’s SQLi prevention guidance and the PHP manual for practical details.

Sources

• Prevent SQL Injection in PHP with Prepared Statements
• PHP Manual — SQL Injection
• OWASP SQL Injection Prevention Cheat Sheet
• Stack Overflow — How can I prevent SQL injection in PHP?
• PHP MySQL Prepared Statements: MySQLi and PDO (example guide)

Conclusion

Preventing sql инъекция php (php sql injection) is straightforward in principle but requires consistent practice: use prepared statements php for all data, validate and whitelist input, run your app with least privilege, and test regularly. Do those things and most SQL injection attacks won’t get past your defenses — and if something slips through, layered controls (limited DB privileges, logging, WAFs) will keep the blast radius small.