What is an SQL injection?

SQL in­jec­tions pose a sig­ni­fic­ant threat to re­la­tion­al database models and the sensitive in­form­a­tion they contain. That’s why com­pre­hens­ive pro­tec­tion against these un­au­thor­ised external access attempts—made possible by security vul­ner­ab­il­it­ies—is ab­so­lutely essential.

What is an SQL injection?

An SQL injection is a type of attack that exploits a security vul­ner­ab­il­ity in re­la­tion­al database systems that use the SQL query language to process user input. The attacker takes advantage of user inputs that are not properly escaped and contain special char­ac­ters such as double hyphens, quotation marks, or semi­colons. These char­ac­ters have special functions for the SQL in­ter­pret­er and allow the commands being executed to be ex­tern­ally ma­nip­u­lated. SQL in­jec­tions are often as­so­ci­ated with PHP and ASP ap­plic­a­tions that rely on outdated in­ter­faces. In many of these cases, the input is not ad­equately sanitised, making it a prime target for an attack.

By stra­tegic­ally inserting function char­ac­ters, an un­au­thor­ised user can inject ad­di­tion­al SQL commands and ma­nip­u­late database entries to read, modify, or delete data. In severe cases, attackers can even gain access to the system’s command line, which may allow them to take full control of the database server.

SQL injection example showing how a database attack works

Because vul­ner­able database servers can be iden­ti­fied quickly and SQL injection attacks are re­l­at­ively easy to execute, this method remains one of the most widely used tech­niques among cy­ber­crim­in­als worldwide. Attackers use various strategies, ex­ploit­ing both newly dis­covered and long-standing security flaws in the ap­plic­a­tions involved in the data man­age­ment process. To better un­der­stand how SQL injection works in practice, let’s look at two common attack methods as examples.

Example 1: Access via poorly escaped user input

To access a database, users are usually required to au­then­tic­ate them­selves first. For this purpose, scripts are commonly used to display a login form that includes a username and password field. Users fill out the form, and the script then checks whether matching entries exist in the database. By default, the database might contain a table named users with the columns username and password. In a typical web ap­plic­a­tion, the relevant script lines for database access (using Python-like pseudo­code) might look like this:

uname = request.POST['username']
passwd = request.POST['password']
sql = "SELECT id FROM users WHERE username='" + uname + "' AND password='" + passwd + "'"
database.execute(sql)

An attacker can now ma­nip­u­late the password field using an SQL injection, for example by entering password' OR 1='1, which results in the following SQL query:

sql = "SELECT id FROM users WHERE username='' AND password='password' OR 1='1'"

This gives the attacker full access to the entire user table in the database, since the password condition always evaluates to true (1='1'). If the attacker logs in as an admin, they can freely modify any of the database entries. Al­tern­at­ively, the username field can be ma­nip­u­lated in the same way.

Example 2: Data ex­trac­tion via ID ma­nip­u­la­tion

Querying in­form­a­tion from a database by ID is a practical and common method, but also opens a possible gate for SQL injection. For example, a webserver knows through a trans­mit­ted ID detail in a URL which in­form­a­tion it should recall from the database. The cor­res­pond­ing PHP script looks like this:

<?php
    $mysqli = new mysqli("localhost", "username", "password", "database");
    $id = intval($_GET['id']);
    $result = $mysqli->query("SELECT * FROM table WHERE id=$id");
    while ($row = $result->fetch_assoc()) {
        echo print_r($row, true);
    }
?>

The expected URL follows the pattern .../script.php?id=22. In this case, the table entry with the ID ‘22’ would be retrieved. If an un­au­thor­ised person has the op­por­tun­ity to ma­nip­u­late this URL and instead sends a request like .../script.php?id=22+OR+1=1, the resulting query will cause all rows in the table to be retrieved:

SELECT * FROM table WHERE id=22 OR 1=1;

How do criminals find vul­ner­able database systems?

In principle, any website or web ap­plic­a­tion that uses SQL databases without prepared queries (prepared state­ments) or other pro­tect­ive measures can be vul­ner­able to SQL in­jec­tions. Dis­covered vul­ner­ab­il­it­ies don’t stay hidden for long on the World Wide Web. In fact, there are websites that publish up-to-date lists of known security flaws—and even explain how attackers can use Google searches to find matching web projects. If a website returns detailed SQL error messages, cy­ber­crim­in­als can use those messages to identify potential vul­ner­ab­il­it­ies. For instance, adding an apo­strophe to the end of a URL that includes an ID parameter can already expose a weakness, as shown in the following example:

[DomainName].com/news.php?id=5’

A vul­ner­able website sends an error message back in the following form:

Query failed: You have an error in your SQL syntax…

Similar methods can also be used to extract the number of columns, table and column names, the SQL version, or even usernames and passwords. Ad­di­tion­ally, various tools exist that can automate both the discovery process and the execution of SQL injection attacks.

How to protect your database from SQL injection

There are various different methods that you can employ to prevent SQL injection attacks on your database system. You should deal with all of the com­pon­ents involved – the server and in­di­vidu­al ap­plic­a­tions as well as the database man­age­ment system.

Step 1: Monitor automated inputs from ap­plic­a­tions

When pro­cessing inputs from external or embedded ap­plic­a­tions, it’s essential to validate and filter the submitted values to prevent SQL in­jec­tions.

1. Validate data types

Each input should match the expected data type. For example, if a numeric input is required, a simple val­id­a­tion in PHP might look like this:

if (filter_var($input, FILTER_VALIDATE_INT) === false) {
    throw new InvalidArgumentException("Invalid input");
}

Similar checks should be im­ple­men­ted for strings, dates, or other specific formats.

2. Filter special char­ac­ters

Special char­ac­ters can create security vul­ner­ab­il­it­ies, es­pe­cially in SQL or HTML contexts. A safe approach is to use htmlspecialchars() for HTML input and PDO::quote() for SQL queries.

3. Avoid revealing error messages

Direct error messages that expose technical details about the database or system should be avoided. Instead, display a generic message such as:

echo "An error occurred. Please try again later.";
error_log("Unexpected error encountered. See system log for details.");

4. Use prepared state­ments

One of the most effective ways to prevent SQL in­jec­tions is by using prepared state­ments. In this approach, SQL commands and para­met­ers are sent sep­ar­ately, so malicious code cannot be executed. Here’s a sample im­ple­ment­a­tion in PHP using PDO (PHP Data Objects):

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindParam(':id', $user_id, PDO::PARAM_INT);
$stmt->execute();

The database man­age­ment system auto­mat­ic­ally ensures that the input is processed securely.

Step 2: Ensure com­pre­hens­ive server pro­tec­tion

The security of the server on which your database man­age­ment system runs also plays a crucial role in SQL injection pre­ven­tion. A key measure is to harden the operating system by following these best practices:

• Install or enable only the ap­plic­a­tions and services that are essential for running the database.
• Remove any unused or un­ne­ces­sary user accounts.
• Ensure all relevant system and software updates are installed promptly.
• Apply the principle of least privilege to ensure that users and services are granted only the minimum per­mis­sions necessary.

Depending on your web project’s security re­quire­ments, you should consider ad­di­tion­al pro­tect­ive measures:

• Intrusion Detection Systems (IDS) and Intrusion Pre­ven­tion Systems (IPS): These systems use various detection methods to identify attacks early, issue alerts, and—when using IPS—auto­mat­ic­ally initiate coun­ter­meas­ures.
• Ap­plic­a­tion Layer Gateway (ALG): An ALG monitors and filters traffic between ap­plic­a­tions and web browsers directly at the ap­plic­a­tion level.
• Web Ap­plic­a­tion Firewall (WAF): A WAF spe­cific­ally protects web ap­plic­a­tions from SQL injection and Cross-Site Scripting (XSS) by blocking or san­it­ising sus­pi­cious requests.
• Zero Trust Approach: This modern security model ensures that every access attempt—re­gard­less of its origin—is verified and au­then­tic­ated before access is granted.
• Firewall Rules and Network Seg­ment­a­tion: These are essential for min­im­ising the attack surface in the long term.
• Regular IT security audits and pen­et­ra­tion tests: These help detect and fix vul­ner­ab­il­it­ies at an early stage.

Step 3: Harden the database and use secure code

Just like your operating system, your database should be stripped of all un­ne­ces­sary com­pon­ents and kept up to date. Remove any stored pro­ced­ures you don’t need, and disable all unused services and user accounts. Create a dedicated database account intended solely for web access, and assign it only the minimum required per­mis­sions.

In line with the use of prepared state­ments, it is strongly re­com­men­ded not to use the mysql PHP module (which was removed in PHP 7). Instead, opt for mysqli or PDO to ensure better security and com­pat­ib­il­ity. A secure mysqli query might look like this:

$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_error) die("Connection failed");
$stmt = $mysqli->prepare("SELECT password FROM users WHERE username = ?");
$stmt->bind_param("s", $_POST['username']);
$stmt->execute();
$stmt->bind_result($hashedPassword);
if ($stmt->fetch() && password_verify($_POST['password'], $hashedPassword)) {
    echo "Login successful";
} else {
    echo "Invalid login credentials";
}
$stmt->close();
$mysqli->close();

Passwords should never be stored directly in a database or retrieved in plain text. Instead, use a hashing method like password_hash() in com­bin­a­tion with password_verify() to protect cre­den­tials securely. A secure im­ple­ment­a­tion might look like this:

$mysqli = new mysqli("localhost", "username", "password", "database");
$stmt = $mysqli->prepare("SELECT password FROM users WHERE username = ?");
$stmt->bind_param("s", $_POST['username']);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
if ($row && password_verify($_POST['password'], $row['password'])) {
    echo "Login successful!";
} else {
    echo "Incorrect username or password.";
}

What do bobby tables have to do with SQL injection?

The website bobby-tables.com uses an xkcd webcomic to hu­mor­ously il­lus­trate the dangers of insecure user input in databases. In the comic, a mother receives a phone call from her son (af­fec­tion­ately known as Little Bobby Tables)’s school. The caller asks if her son is really named Robert'); DROP TABLE Students;--, to which she responds yes. The reason for the call soon becomes clear: at­tempt­ing to enter Robert into the student database caused the entire student table to be deleted. The mother isn’t too sym­path­et­ic—she simply hopes the school has learned its lesson and will sanitise database inputs going forward.

The comic clearly high­lights the cata­stroph­ic con­sequences that can result from failing to properly validate and sanitise user input in database ap­plic­a­tions.