SQL Injection: How It Works and Prevention

SQL Injection

SQL injection is one of the oldest and most dangerous web vulnerabilities. An attacker inserts malicious SQL code into a query, allowing them to read, modify, or delete database data, bypass authentication entirely, or in some cases execute commands on the underlying server. Despite being fully preventable, it remains one of the most commonly exploited vulnerabilities on the web.

What Is SQL Injection

SQL injection (SQLi) occurs when user-supplied input is included directly in a SQL query without sanitisation or parameterisation. The database cannot distinguish between the intended SQL structure and the attacker's injected input, so it executes the combined string as a single query. The attacker's input effectively becomes part of the query logic, allowing them to alter what the query does and what data it returns.

The vulnerability exists because many applications construct SQL queries by concatenating strings, treating user input as if it is trusted SQL code rather than raw data. When a user submits a form field, URL parameter, or API value that contains SQL syntax, that syntax is interpreted and executed by the database. This is the fundamental mistake that SQL injection exploits.

// Vulnerable PHP code - user input concatenated directly into the query
$query = "SELECT * FROM users WHERE email = '" . $_GET['email'] . "'";

// Normal user input:
// email = alice@example.com
// Query: SELECT * FROM users WHERE email = 'alice@example.com'
// Result: returns Alice's record only ✓

// Attacker input:
// email = ' OR '1'='1
// Query: SELECT * FROM users WHERE email = '' OR '1'='1'
// Result: '1'='1' is always true → returns ALL users in the database ✗

// Attacker input for login bypass:
// email = admin@example.com' --
// Query: SELECT * FROM users WHERE email = 'admin@example.com' --'
// The -- comments out the rest of the query including any password check
// Result: logs in as admin without knowing the password ✗

// Attacker input for data destruction:
// email = '; DROP TABLE users; --
// Query: SELECT * FROM users WHERE email = ''; DROP TABLE users; --'
// Result: deletes the entire users table ✗

Types of SQL Injection

SQL injection attacks vary in how the attacker receives the results of their injected queries. Some techniques return data directly in the page response, while others require the attacker to infer information through indirect signals. Understanding the different types helps you appreciate why SQL injection is dangerous even when error messages are hidden and query results are not displayed.

What Attackers Can Do with SQL Injection

The impact of a successful SQL injection attack ranges from embarrassing to catastrophic, depending on the application, the database user's permissions, and what data the database contains.

• Authentication bypass: Inject ' OR '1'='1 or admin'-- into a login form to gain access without knowing any password. The injected condition makes the WHERE clause always evaluate to true, matching any user.
• Data exfiltration: Extract entire tables including usernames, hashed or plaintext passwords, email addresses, credit card numbers, personal information, and any other data stored in the database.
• Data modification: Update records to escalate a user's privileges, change account details, alter prices in an e-commerce database, or plant malicious content in stored fields that is later served to other users.
• Data destruction: Drop tables, truncate records, or delete specific rows. A single successful injection on an unprotected database can destroy years of accumulated data in milliseconds.
• Operating system access: On databases like Microsoft SQL Server, the xp_cmdshell stored procedure allows SQL queries to execute operating system commands. An attacker with sufficient database privileges can use SQL injection to run arbitrary commands on the server.
• Lateral movement: Database credentials and connection strings discovered through SQL injection can be used to pivot to other systems, escalating a single web vulnerability into a broader network compromise.

How to Prevent SQL Injection

SQL injection is entirely preventable. Every SQL injection vulnerability that exists in production code is the result of a developer not using the correct approach to query construction. The following defences, applied consistently, eliminate SQL injection as an attack vector.

Parameterised Queries in Practice

Parameterised queries are the single most important defence against SQL injection. Every major programming language and database library supports them. The principle is the same across all languages: the SQL structure is written with placeholders, and the actual user values are passed separately so they are never interpreted as SQL.

// Vulnerable - never do this
$query = "SELECT * FROM users WHERE email = '" . $_GET['email'] . "'";

// Safe - parameterised query with PDO
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$_GET['email']]);
$user = $stmt->fetch();

// Safe - named parameters for clarity
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND role = :role");
$stmt->execute([':email' => $_GET['email'], ':role' => 'admin']);
$user = $stmt->fetch();

// PostgreSQL with node-postgres
const result = await pool.query(
  'SELECT * FROM users WHERE email = $1 AND active = $2',
  [req.body.email, true]
);

// MySQL with mysql2
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE email = ? AND role = ?',
  [req.body.email, 'admin']
);

// The ? placeholders ensure user input is always treated as data
// An attacker cannot inject SQL through req.body.email

# psycopg2 (PostgreSQL)
cursor.execute(
    "SELECT * FROM users WHERE email = %s AND active = %s",
    (request.form['email'], True)
)

# SQLAlchemy ORM - parameterised automatically
user = session.query(User).filter(
    User.email == request.form['email']
).first()

# SQLAlchemy raw query with parameters
result = session.execute(
    text("SELECT * FROM users WHERE email = :email"),
    {"email": request.form['email']}
)

SQL Injection in Different Contexts

SQL injection vulnerabilities appear not just in WHERE clauses but anywhere user input influences a query. Some contexts are harder to parameterise and require extra care.

• ORDER BY clauses: Most database drivers do not allow parameterising column names or sort directions. If users can specify a sort column, validate the value against a strict whitelist of permitted column names rather than using the raw input.
• Table and column names: Identifiers such as table and column names cannot be parameterised. If they must be dynamic, validate against a whitelist and use the database's identifier quoting mechanism.
• IN clauses with multiple values: Dynamically generating an IN clause with a variable number of placeholders requires care but is fully achievable by generating the correct number of placeholders programmatically before executing.
• Second-order injection: Data stored in the database from one request is later used unsafely in a query in another request. The initial storage may look safe, but the subsequent query construction is vulnerable. Parameterise every query regardless of where the data came from.

Frequently Asked Questions

1. Is SQL injection still common despite being well known?
   Yes. SQL injection consistently appears in the OWASP Top 10 list of most critical web application security risks, and it ranks among the most frequently exploited vulnerabilities in real-world breaches despite being fully preventable. The reasons it persists include legacy codebases written before parameterised queries were standard practice, developers who learned to construct queries through string concatenation without understanding the security implications, and code that mixes safe and unsafe query construction in ways that are easy to miss during review. Automated scanning tools like SQLMap can find and exploit SQL injection vulnerabilities in seconds, meaning any exposed vulnerable endpoint is quickly discovered and abused.
2. Does using an ORM protect against SQL injection?
   Using an ORM for standard CRUD operations provides strong protection because ORMs generate parameterised queries internally for their built-in methods. However, most ORMs also provide escape hatches for raw SQL execution, such as Django's raw(), SQLAlchemy's text(), or Eloquent's DB::statement(). If developers use these raw query methods and concatenate user input into them, SQL injection is possible even with an ORM in use. The ORM protects you as long as you use its standard query builder methods. Any raw SQL must still be parameterised manually.
3. Can NoSQL databases be injected?
   Yes. NoSQL injection is possible in databases like MongoDB, CouchDB, and others that accept structured query objects. In MongoDB, if user input is used to construct a query document without validation, an attacker may be able to pass operators like $gt, $where, or $regex as part of the input, altering the query logic. For example, a login endpoint that constructs a query object directly from request body parameters may be vulnerable to operator injection. Always validate the type and structure of input before using it in any database query, regardless of whether the database is relational or non-relational.
4. Is input sanitisation enough to prevent SQL injection?
   No. Input sanitisation, which typically means escaping special characters like quotes, is unreliable as a primary defence against SQL injection. Different database engines use different escaping rules, and it is easy to miss edge cases or apply escaping in the wrong encoding context. Parameterised queries and prepared statements are the correct solution because they structurally separate SQL code from data, making injection impossible by design rather than relying on correctly escaping every possible dangerous character in every possible context. Input validation as an additional layer is useful for rejecting obviously invalid data early, but it should never be the only line of defence against SQL injection.
5. How can I test my application for SQL injection vulnerabilities?
   Several approaches help identify SQL injection vulnerabilities before attackers find them. Manual testing involves submitting single quotes, double quotes, comment sequences like -- and #, and boolean conditions like AND 1=1 and AND 1=2 into every input field and URL parameter and observing whether the response changes in unexpected ways. Automated scanners like SQLMap, Burp Suite, and OWASP ZAP can systematically test all input points of an application and identify injection points. Code review focusing specifically on any place where string concatenation or formatting is used to construct database queries is highly effective. Running your application's dependencies and queries through a static analysis security testing tool as part of your CI pipeline can catch issues before they reach production.

Conclusion

SQL injection is entirely preventable, yet it remains one of the most commonly exploited vulnerabilities in web applications. The fix is straightforward and consistent: use parameterised queries and prepared statements for every database interaction, never concatenate user input into SQL strings, grant database accounts only the permissions they require, and never expose raw database errors to users. These four practices together eliminate SQL injection as an attack vector regardless of what data users submit. ORMs and query builders provide these protections automatically for standard operations but require care when raw queries are used. Applying the same discipline to NoSQL databases through input validation ensures injection vulnerabilities are avoided across all database types. Continue with XSS prevention, authentication vs authorization, and CSRF protection to build a comprehensive web application security posture.