# What is an SQL injection?

SQL injections pose a significant threat to relational database models and the sensitive information they contain. That’s why comprehensive protection against these unauthorised external access attempts—made possible by security vulnerabilities—is absolutely essential.

## What is an SQL injection?

An SQL injection is a type of attack that exploits a security vulnerability in [relational database systems](https://www.ionos.co.uk/digitalguide/hosting/technical-matters/relational-databases/) that use the [SQL](https://www.ionos.co.uk/digitalguide/server/know-how/what-is-sql/) query language to process user input. The attacker takes advantage of user inputs that are **not properly escaped** and contain **special characters** such as double hyphens, quotation marks, or semicolons. These characters have special functions for the SQL interpreter and allow the commands being executed to be externally manipulated. SQL injections are often associated with PHP and ASP applications that rely on outdated interfaces. In many of these cases, the input is not adequately sanitised, making it a prime target for an attack.

By strategically inserting function characters, an unauthorised user can inject additional SQL commands and manipulate 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 vulnerable database servers can be identified quickly and SQL injection attacks are relatively easy to execute, this method remains one of the most widely used techniques among cybercriminals worldwide. Attackers use **various strategies**, exploiting both newly discovered and long-standing security flaws in the applications involved in the data management process. To better understand 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 authenticate themselves 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 application, the relevant script lines for database access (using Python-like pseudocode) might look like this:

```python
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 **manipulate the password field using an SQL injection**, for example by entering `password' OR 1='1`, which results in the following SQL query:

```python
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. Alternatively, the **username field** can be manipulated in the same way.

### Example 2: Data extraction via ID manipulation

Querying information 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 transmitted ID detail in a URL which information it should recall from the database. The corresponding **PHP script** looks like this:

```php
<?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 unauthorised person has the opportunity to manipulate 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:

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

## How do criminals find vulnerable database systems?

In principle, any website or web application that uses SQL databases without prepared queries ([prepared statements](https://www.ionos.co.uk/digitalguide/websites/web-development/prepared-statements-in-phpmysql/)) or other protective measures can be vulnerable to SQL injections. Discovered vulnerabilities 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, cybercriminals can use those messages to identify potential vulnerabilities. For instance, adding an apostrophe to the end of a URL that includes an ID parameter can already expose a weakness, as shown in the following example:

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

A vulnerable 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. Additionally, 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 components involved – the server and individual applications as well as the [database management system](https://www.ionos.co.uk/digitalguide/hosting/technical-matters/database-management-systems-dbms/).

### Step 1: Monitor automated inputs from applications

When processing inputs from external or embedded applications, it’s essential to validate and filter the submitted values to prevent SQL injections.

**1. Validate data types**

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

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

Similar checks should be implemented for strings, dates, or other specific formats.

**2. Filter special characters**

Special characters can create security vulnerabilities, especially 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:

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

**4. Use prepared statements**

One of the most effective ways to prevent SQL injections is by using **prepared statements**. In this approach, [SQL commands](https://www.ionos.co.uk/digitalguide/server/configuration/sql-commands/) and parameters are sent separately, so malicious code cannot be executed. Here’s a sample implementation in PHP using PDO (PHP Data Objects):

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

The database management system automatically ensures that the input is processed securely.

### Step 2: Ensure comprehensive server protection

The security of the server on which your database management system runs also plays a crucial role in SQL injection prevention. A key measure is to **harden the operating system** by following these best practices:

- Install or enable only the applications and services that are essential for running the database.
- Remove any unused or unnecessary 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 permissions necessary.

Depending on your web project’s security requirements, you should consider additional protective measures:

- **[Intrusion Detection Systems (IDS)](https://www.ionos.co.uk/digitalguide/server/security/intrusion-detection-system-ids/) and [Intrusion Prevention Systems (IPS)](https://www.ionos.co.uk/digitalguide/server/security/intrusion-prevention-system-ips/)**: These systems use various detection methods **to identify attacks early**, issue alerts, and—when using IPS—automatically initiate countermeasures.
- **Application Layer Gateway (ALG)**: An ALG monitors and filters traffic between applications and web browsers directly at the application level.
- **Web Application Firewall (WAF)**: A WAF specifically protects web applications from SQL injection and [Cross-Site Scripting (XSS)](https://www.ionos.co.uk/digitalguide/websites/web-development/what-is-xss-aka-cross-site-scripting/) by blocking or sanitising suspicious requests.
- **Zero Trust Approach**: This modern security model ensures that every access attempt—regardless of its origin—is verified and authenticated before access is granted.
- **Firewall Rules and Network Segmentation**: These are essential for minimising the attack surface in the long term.
- **Regular IT security audits and [penetration tests](https://www.ionos.co.uk/digitalguide/server/know-how/penetration-testing-the-complete-network-check/)**: These help detect and fix vulnerabilities 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 unnecessary components and kept up to date. Remove any stored procedures 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 permissions.

In line with the use of prepared statements, it is strongly recommended **not to use** the `mysql` PHP module (which was removed in PHP 7). Instead, opt for `mysqli` or PDO to ensure better security and compatibility. A secure `mysqli` query might look like this:

```php
$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 combination with `password_verify()` to protect credentials securely. A secure implementation might look like this:

```php
$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](https://bobby-tables.com/ "Bobby Tables website") uses an xkcd webcomic to humorously illustrate the dangers of insecure **user input in databases**. In the comic, a mother receives a phone call from her son (affectionately 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: attempting to enter Robert into the student database caused the **entire student table to be deleted**. The mother isn’t too sympathetic—she simply hopes the school has learned its lesson and will sanitise database inputs going forward.

The comic clearly highlights the catastrophic consequences that can result from failing to properly validate and sanitise user input in database applications.


This is a markdown version of: [https://www.ionos.co.uk/digitalguide/server/security/sql-injection-fundamentals-and-safeguards/](https://www.ionos.co.uk/digitalguide/server/security/sql-injection-fundamentals-and-safeguards/) for AI/LLM consumption.