How can I prevent SQL injection in PHP?

sql

sql-injection

prepared-statements

php-security

byAlex Kataev·Aug 21, 2024

Safeguard your application against SQL injection assaults using PDO:

$stmt = $pdo->prepare("INSERT INTO users (username) VALUES (?)");
$stmt->execute([$username]); // Look ma, no SQL injection!

Or with MySQLi:

$stmt = $mysqli->prepare("INSERT INTO users (username) VALUES (?)");
$stmt->bind_param("s", $username); // Thwarts SQL injection, just like wearing a mask thwarts the common cold
$stmt->execute();

Always remember, bind user input as parameters. Concatenation in queries is like playing with fire 🔥.

The Magic of Prepared Statements

Prepared statements or parameterized queries are here to save your day. They separate SQL code from data, hence, any user input is strictly treated as data. They're the safety goggles of SQL operations.

Why Prepared Statements?

Security: They provide a robust shield against SQL injection.
Performance: They excel in scenarios involving repeated queries.
Readability: They keep your SQL queries clean and prevent it from turning into spaghetti.

Dynamic SQL Helpers

For dynamic SQL commands, whitelist your inputs. It's like bouncer at a club who know who is allowed in.
With PHP 8.2's execute_query, you can eliminate all the hustle of prepared statements.

Validate and Sanitize

Validate your input: Make sure whoever enters your club is of legal age.
Sanitize your input: Just like sanitizers, keep the harmful stuff away from your database.

Beyond Prepared Statements

Deprecate deprecated functions: Chuck mysql_* functions away. They are as safe as storing passwords in a text file.
Error reporting: Wrap your database connection in a try catch, it's like caring enough to ask "how are you doing, friend?".
Keep PHP updated: Stay updated, stay safe!

PDO & MySQLi settings tweaks

Emulated prepares are a big no: Get real with genuine prepared statements.
Character encoding: A wrong character set can leave your application vulnerable.
PDO exceptions: Let's throw and not suppress errors for better visibility and debugging.

Rule of Thumb:
Raw Queries      = 👎🔓 (You are practically inviting everyone to the party)
Prepared Queries = 👍🔒 (VIP pass needed for the party!)

explain-codes / Sql / How can I prevent SQL injection in PHP?

Linked

Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?



Can parameterized statement stop all SQL injection?



Sql - IF EXISTS UPDATE ELSE INSERT INTO



How to escape simple SQL queries in C# for SqlServer



Mysql where NOT IN name array?



Doctrine 2: Update query with query builder



How to add plus one (+1) to a SQL Server column in a SQL Query



The Magic of Prepared Statements PDO & MySQLi settings tweaks