When I interview candidates for PHP roles, there is one answer that immediately tells me a developer is relying on outdated tutorials: the belief that mysqli_real_escape_string() is the definitive way to stop SQL injection. I've seen brilliant coders fail a security screening because they think "cleaning" the string is the goal.

Here is why that mindset is dangerous. Imagine you have a query like this: "SELECT * FROM products WHERE category_id = " . mysqli_real_escape_string($conn, $_GET['id']);. If the id is 1 OR 1=1, the escaping function does absolutely nothing because there are no quotes to escape. The query becomes SELECT * FROM products WHERE category_id = 1 OR 1=1, and you've just leaked your entire database. Escaping is a band-aid; it doesn't solve the fundamental problem of mixing data with instructions.

"Escaping is Enough" vs. "Prepared Statements are the Only Real Answer"

In an interview, if you're asked how to prevent SQL injection, don't talk about escaping strings. Talk about Prepared Statements (Parameterized Queries). I want to hear you explain that prepared statements send the SQL template to the database server first, and then send the data separately. The database never evaluates the data as code, regardless of whether it contains quotes, semicolons, or OR 1=1.

// The wrong way (even with escaping in some contexts)
$id = mysqli_real_escape_string($conn, $_GET['id']);
$sql = "SELECT * FROM users WHERE id = $id"; 

// The professional way
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => $_GET['id']]);
$user = $stmt->fetch();

"Filtering Input is the Same as Escaping Output" vs. "Context-Aware Output Encoding"

Another common stumble is the "sanitize everything on the way in" approach. You'll hear people say, "I run strip_tags() on every $_POST variable." This is a mistake. If you strip tags when saving a user's bio to the database, you've permanently altered their data. What if they were writing a technical blog post about HTML? You've just destroyed their content.

The correct answer is: Filter on input, escape on output. You store the raw data (after validating it's the right type), and you escape it based on where it's going. If it's going into HTML, use htmlspecialchars(). If it's going into a JavaScript string, that's a different set of rules entirely.

// Don't do this when saving to DB:
$bio = strip_tags($_POST['bio']); 

// Do this when echoing to the browser:
echo "
" . htmlspecialchars($user['bio'], ENT_QUOTES, 'UTF-8') . "
";

"MD5 or SHA1 is Hashing" vs. "Using password_hash() for Computational Cost"

If an interviewer asks how you store passwords and you mention MD5 or SHA1, the interview is effectively over. These are fast hashes. In the world of security, fast is bad. A modern GPU can crack millions of MD5 hashes per second.

I expect you to talk about password_hash() and password_verify(). These functions use bcrypt by default, which is designed to be slow. It incorporates a salt automatically and allows for a "cost" factor. This means as hardware gets faster, you can increase the cost to keep the hashing time consistent, making brute-force attacks computationally expensive.

"Session IDs are Secret" vs. "Preventing Session Hijacking and CSRF"

You might be asked how to protect a session. A common misconception is that simply calling session_start() is enough. It's not. I'm looking for you to mention session_regenerate_id(true). Why? Because if an attacker steals a session cookie via XSS, they can impersonate the user indefinitely unless you rotate that ID upon a privilege change (like logging in).

Beyond that, be ready to discuss CSRF (Cross-Site Request Forgery). Explain that since browsers automatically send cookies with requests, an attacker can trick a logged-in user into clicking a link that triggers an action (like /logout or /transfer-funds). The fix is a CSRF token: a random string generated on the server, stored in the session, and required as a hidden field in every POST request. If the token in the request doesn't match the session, you kill the request.

📋 Practical Task

Refactoring the Vulnerable User Profile Update Form

You have been handed a legacy script that allows users to update their profile. It is riddled with the exact security flaws discussed in this lesson. Your task is to rewrite the PHP logic to make it secure.

The Vulnerable Code:

// VULNERABLE CODE - DO NOT USE
$userId = $_POST['id'];
$bio = $_POST['bio'];
$email = $_POST['email'];

// Vulnerability 1: SQL Injection
$sql = "UPDATE users SET bio = '$bio', email = '$email' WHERE id = $userId";
mysqli_query($conn, $sql);

// Vulnerability 2: XSS (This is how the bio is displayed back to the user)
echo "Your bio has been updated to: " . $bio;

Your Requirements:

• Replace the mysqli_query call with a PDO prepared statement to eliminate SQL injection.
• Implement proper output encoding when echoing the $bio variable to prevent XSS.
• Ensure the $userId is cast to an integer to prevent type-juggling issues.
• Assume a PDO connection variable named $pdo is already available.