Preventing SQL injection

What Is SQL Injection and Why Should It Be Taken Seriously?

SQL Injection is one of the oldest and at the same time most dangerous attacks on websites. In this attack, the attacker injects their own SQL code into database queries by sending specific inputs to forms, URL parameters, or even HTTP headers. If your application places user input directly into a query without sanitization or validation, the attacker can read, modify, delete data, or even take over the entire database.

According to OWASP statistics, SQL injection has been on the list of the top ten web security risks for years. A small mistake in writing a query can lead to the leakage of sensitive user information, theft of passwords, or complete destruction of data. In this article, we will show in simple language with practical examples why parameterized queries are the only real and definitive solution to counter this attack.

Understand the Mechanism of SQL Injection Attacks

To understand the importance of parameterized queries, we first need to see how the attack works. Suppose you have a simple login form where the user enters a username and password. Your PHP code might look like this:

<?php
$username = $_POST['username'];
$password = $_POST['password'];

$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($conn, $query);
?>

Now, if the attacker enters the following value in the username field:

' OR '1'='1' -- 

The final query becomes:

SELECT * FROM users WHERE username = '' OR '1'='1' -- ' AND password = ''

The -- part in SQL means "everything to the end of the line is a comment," so the password condition is completely ignored. The expression '1'='1' is always true, so the query returns all records from the users table, and the attacker logs into the account without knowing the password. This is the simplest type of attack; more advanced attacks can extract data, drop tables, or even read information from other tables using UNION SELECT.

Common Types of SQL Injection

• In-band SQLi: The attacker sees the result directly in the HTTP response. This includes two types: Error-based and Union-based.
• Blind SQLi: The result is not displayed directly, but the attacker guesses information by observing differences in application behavior (e.g., time delays or error messages).
• Out-of-band SQLi: Data is sent to the attacker's server through another channel such as DNS or HTTP requests.

All these types share one common point: the application interprets user input as part of the SQL command. The fundamental solution is to distinguish between "command" and "data."

Why Are Traditional Defense Methods Not Enough?

Many developers use methods such as escaping characters or filtering input to prevent SQL injection. But these methods are inherently flawed and have failed many times.

The Problem with mysqli_real_escape_string

The mysqli_real_escape_string function escapes special characters like ', ", and \ with backslashes. However, if the database character set is not properly configured, the attacker can use tricks like GBK or UTF-8 to bypass this escaping. The famous attack with %bf%27 in MySQL with the GBK character set shows that this method is not reliable on its own.

The Problem with Input Filtering

Some developers try to remove keywords like SELECT, UNION, or DROP from input. This method is also defeatable; the attacker can use encoding techniques, repeated spaces, comments, or mixed-case letters to bypass filters. For example, SeLeCt or SELECT/**/FROM easily pass through simple filters.

The important point is that security should not be based on a "blacklist" but rather on a "whitelist" and the separation of command from data. Parameterized queries do exactly this.

What Is a Parameterized Query and How Does It Work?

A parameterized query (or prepared statement) is a method in which the structure of the SQL query is defined first, and then values are sent separately using placeholders. The database interprets these values as "data," not as part of the "command." This inherent separation makes SQL injection technically impossible.

Practical Example with PHP and MySQLi

Let's rewrite the same login form example with a parameterized query:

<?php
$username = $_POST['username'];
$password = $_POST['password'];

// Preparing the query with placeholders
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
$result = $stmt->get_result();
?>

In this code, the ? symbol acts as a placeholder. The bind_param function specifies the data type (here s for string) and the value. The MySQL database receives these values as raw data and never executes them as SQL commands. Even if the attacker enters the value ' OR '1'='1' -- , this string is treated as a normal value for the username field and has no effect on the query structure.

Example with PDO (PHP Data Objects)

PDO is a more modern way to work with databases and supports parameterized queries as a standard:

<?php
$pdo = new PDO("mysql:host=localhost;dbname=mydb;charset=utf8mb4", $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->execute(['username' => $_POST['username'], 'password' => $_POST['password']]);
$user = $stmt->fetch();
?>

Here, named placeholders (:username and :password) are used, which increases code readability. The execute function receives values as an array, and PDO automatically sends them correctly to the database.

Parameterized Queries in Other Languages and Frameworks

This pattern is not limited to PHP. In all modern languages and frameworks, parameterized queries are recognized as the golden standard of security.

• Python (with psycopg2 or sqlite3): Use %s or ? as placeholders.
• Node.js (with mysql2 or pg): Use ? or $1.
• Java (with JDBC): Use PreparedStatement.
• Frameworks like Laravel, Django, Rails: By default, they use Query Builder or ORM that automatically applies parameterized queries.

If you are using a framework, there are almost always ready-made methods for this. For example, in Laravel, DB::select('SELECT * FROM users WHERE id = ?', [$id]) automatically uses a parameterized query.

Common Mistakes and Troubleshooting Tips

Even when using parameterized queries, mistakes can occur that compromise security. Here are some common cases.

Mistake: Using Parameterized Queries Only for Part of the Query

Some developers only parameterize WHERE values but build the table or column name directly from user input. Consider this example:

<?php
$table = $_GET['table']; // user input
$stmt = $pdo->prepare("SELECT * FROM $table WHERE id = ?");
$stmt->execute([$id]);
?>

Here, the attacker can change the table value to users; DROP TABLE users; --. Parameterized queries only work for values, not for identifiers. For table and column names, you must use a whitelist:

<?php
$allowedTables = ['users', 'products', 'orders'];
if (!in_array($table, $allowedTables)) {
    die("Invalid table name");
}
?>

Mistake: Forgetting to Set the Character Set

If you do not properly set the database connection character set, even parameterized queries can be vulnerable in some cases. Always use utf8mb4 and set it in the DSN for PDO:

$pdo = new PDO("mysql:host=localhost;dbname=mydb;charset=utf8mb4", $user, $pass);

Mistake: Trusting ORM Without Understanding How It Works

Many ORMs allow you to write raw queries. If you use this feature, you must follow the same parameterized query rules. For example, in Laravel, the whereRaw method allows you to write raw queries; if you place user input directly into it, the same SQL injection risk exists.

Complementary Defense Layers

Parameterized queries are the front line of defense, but defense in depth requires having other layers as well.

• Input Validation: Even with parameterized queries, validate inputs for type, length, and format. For example, the email field should be checked with a regex.
• Least Privilege for Database: The application's database user should not have DROP or ALTER access. Only grant necessary permissions (SELECT, INSERT, UPDATE, DELETE).
• Encrypting Sensitive Data: Hash passwords with password_hash and never store them as plain text.
• Logging and Monitoring: Record suspicious SQL injection attempts in logs and receive alerts.

Conclusion: Make Parameterized Queries a Habit

SQL injection is a serious threat that affects millions of websites every year. Traditional methods like escaping characters and filtering input are temporary and incomplete solutions. The only real and definitive solution is using parameterized queries (prepared statements), which provide an inherent separation between command and data.

Apply this pattern in all layers of your application: login forms, search, filters, sorting, and anywhere you interact with the database. If you use a framework, take advantage of its built-in features, and if you write raw code, always use prepared statements. By doing this, you have taken one of the most important steps to secure your website.

If you are looking for a secure infrastructure to host your website, ServerNet provides web hosting and cloud server services with a focus on security that can help you run secure applications.