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 parameterized statement stop all SQL injection?



Sql - IF EXISTS UPDATE ELSE INSERT INTO



Mysql, better to insert NULL or empty string?



Increment field of mysql database using codeigniter's active record syntax



Is this the proper way to do boolean test in SQL?



Sql standard to escape column names?



Modify table: How to change 'Allow Nulls' attribute from not null to allow null



The Magic of Prepared Statements PDO & MySQLi settings tweaks