How SQL injection happens in PHP MySQLi and how to fix it

How SQL Injection Happens in PHP MySQLi and How to Fix It

Introduction

The sign_up.php file handles user registration—collecting full names, usernames, passwords, emails, and profile images—but a flaw in how the SQL queries were constructed at lines 28-32 created a critical security risk. The variables $Username and $Email (sourced directly from $_POST) were concatenated into raw SQL strings like "select * from Users where Username = '$Username'", making the registration form a direct gateway for SQL injection attacks.

This isn't a theoretical concern. Any internet-facing registration form that constructs queries this way is actively exploitable, and the consequences range from data theft to complete database compromise.

The Vulnerability Explained

Here's the vulnerable code that was present in sign_up.php:

$chk_user = "select * from Users where Username = '$Username'";
$chk_run_user = mysqli_query($con, $chk_user);

$chk_email = "select * from Users where Email = '$Email'";
$chk_run_email = mysqli_query($con, $chk_email);

And the INSERT statement:

$insert_query = "insert into Users(Full_Name, Username, Password, Email, Language, Gender, Phone_No, Image) values('$Full_Name', '$Username', '$Password', '$Email', 'English', '$Gender', '$Phone_No', '$Image')";
mysqli_query($con, $insert_query);

Why is this dangerous? When a user submits the registration form, the $Username value is placed directly inside the SQL string. If an attacker enters a username like:

' UNION SELECT Full_Name, Username, Password, Email, Language, Gender, Phone_No, Image FROM Users--

The resulting query becomes:

select * from Users where Username = '' UNION SELECT Full_Name, Username, Password, Email, Language, Gender, Phone_No, Image FROM Users--'

This would return every user's credentials from the database. The -- comments out the trailing single quote, making the SQL syntactically valid.

The INSERT injection is even more dangerous. An attacker could inject into the $Username field during registration:

', '', '', '', '', '', ''); DROP TABLE Users;--

This could terminate the INSERT statement and execute a destructive DROP TABLE command, wiping the entire Users table.

What about mysqli_real_escape_string()? While the code may have applied this function earlier in the flow, it's insufficient protection because:

1. It doesn't protect against injection in numeric contexts
2. Character set mismatches (e.g., GBK encoding) can bypass it entirely
3. It creates a false sense of security that discourages proper parameterization
4. It requires developers to remember to apply it to every variable—one miss and you're vulnerable

The Fix

The fix replaces all three vulnerable queries with MySQLi prepared statements. Here's the transformation:

Before — Username check (vulnerable):

$chk_user = "select * from Users where Username = '$Username'";
$chk_run_user = mysqli_query($con, $chk_user);

After — Username check (secure):

$chk_stmt = mysqli_prepare($con, "SELECT * FROM Users WHERE Username = ?");
mysqli_stmt_bind_param($chk_stmt, "s", $Username);
mysqli_stmt_execute($chk_stmt);
$chk_run_user = mysqli_stmt_get_result($chk_stmt);

Before — Email check (vulnerable):

$chk_email = "select * from Users where Email = '$Email'";
$chk_run_email = mysqli_query($con, $chk_email);

After — Email check (secure):

$chk_email_stmt = mysqli_prepare($con, "SELECT * FROM Users WHERE Email = ?");
mysqli_stmt_bind_param($chk_email_stmt, "s", $Email);
mysqli_stmt_execute($chk_email_stmt);
$chk_run_email = mysqli_stmt_get_result($chk_email_stmt);

Before — INSERT (vulnerable):

$insert_query = "insert into Users(Full_Name, Username, Password, Email, Language, Gender, Phone_No, Image) values('$Full_Name', '$Username', '$Password', '$Email', 'English', '$Gender', '$Phone_No', '$Image')";
if(mysqli_query($con, $insert_query)){

After — INSERT (secure):

$insert_stmt = mysqli_prepare($con, "INSERT INTO Users(Full_Name, Username, Password, Email, Language, Gender, Phone_No, Image) VALUES(?, ?, ?, ?, 'English', ?, ?, ?)");
mysqli_stmt_bind_param($insert_stmt, "sssssss", $Full_Name, $Username, $Password, $Email, $Gender, $Phone_No, $Image);
if(mysqli_stmt_execute($insert_stmt)){

How this solves the problem: The ? placeholder tells MySQL's query parser to treat the bound value as data only, never as SQL syntax. Even if an attacker submits ' OR 1=1-- as their username, the database engine will literally search for a user named ' OR 1=1-- rather than interpreting it as SQL logic. The query structure is fixed at prepare time and cannot be altered by input values.

Note the "sssssss" type string in the INSERT bind—each s indicates that the corresponding parameter should be treated as a string type, providing an additional layer of type safety.

Prevention & Best Practices

1. Always use prepared statements: In PHP MySQLi, use mysqli_prepare() + mysqli_stmt_bind_param(). In PDO, use $pdo->prepare() + $stmt->execute(['param' => $value]). Never concatenate variables into SQL strings.

2. Adopt an ORM or query builder: Frameworks like Laravel's Eloquent or Doctrine automatically parameterize queries, making SQL injection nearly impossible through normal usage.

3. Apply the principle of least privilege: The database user for your web application should only have SELECT, INSERT, UPDATE, and DELETE permissions—never DROP, ALTER, or GRANT. This limits damage even if injection occurs.

4. Validate input types: Before the query, validate that emails look like emails, usernames match expected patterns (/^[a-zA-Z0-9_]+$/), and phone numbers are numeric. This is defense-in-depth, not a replacement for parameterization.

5. Use static analysis in CI/CD: Tools like Semgrep can flag mysqli_query() calls that contain variables in the query string, catching these issues before they reach production.

6. Store passwords with proper hashing: While not the focus of this fix, the code stores $Password directly. It should use password_hash($Password, PASSWORD_BCRYPT) before insertion.

Key Takeaways

• Never trust mysqli_real_escape_string() as your primary defense — the sign_up.php registration form was still exploitable despite its use because parameterized queries are the only reliable SQL injection prevention.
• All three queries in the registration flow were vulnerable — the username check, email check, AND the INSERT statement all concatenated user input, meaning an attacker had multiple injection points to choose from.
• The "sssssss" type specification in mysqli_stmt_bind_param() adds type enforcement — each parameter is explicitly declared as a string, preventing type confusion attacks.
• Registration forms are high-value targets — they accept multiple user inputs (7 fields in this case) and perform multiple database operations, creating a large attack surface.
• The fix preserves existing behavior — mysqli_stmt_get_result() returns a result set compatible with the same mysqli_num_rows() checks used downstream, so no other code changes were needed.

How Orbis AppSec Detected This

• Source: User-supplied $_POST['Username'] and $_POST['Email'] parameters from the registration form in sign_up.php
• Sink: mysqli_query($con, $chk_user) at sign_up.php:28 where the concatenated string is executed as a SQL query
• Missing control: No parameterized query preparation; user input flows directly into the SQL string despite mysqli_real_escape_string() being insufficient
• CWE: CWE-89 — Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')
• Fix: Replaced all three mysqli_query() calls that used concatenated strings with mysqli_prepare() + mysqli_stmt_bind_param() + mysqli_stmt_execute() parameterized statements

Orbis AppSec automatically detected this vulnerability and opened a pull request with the fix. Try Orbis AppSec on your repositories to find and fix issues like this automatically.

Conclusion

SQL injection remains one of the most dangerous and prevalent web vulnerabilities—OWASP consistently ranks it in the top 10. This sign_up.php fix demonstrates that the solution is straightforward: replace string concatenation with prepared statements. The MySQLi extension has supported mysqli_prepare() since PHP 5.0 (released in 2004), yet developers continue to write concatenated queries out of habit or unfamiliarity.

If you're maintaining PHP code, search your codebase for patterns like "SELECT * FROM table WHERE column = '$variable'" — every instance is a potential SQL injection waiting to be exploited. The migration to prepared statements is mechanical, preserves existing behavior, and eliminates an entire class of vulnerabilities.

References

• CWE-89: SQL Injection
• OWASP SQL Injection Prevention Cheat Sheet
• PHP Manual: mysqli_prepare()
• PHP Manual: mysqli_stmt_bind_param()
• Semgrep PHP SQL Injection Rules
• fix: add parameterized queries in sign_up.php