Security & Performance

SQL Injection and NoSQL Injection

20 min Lesson 3 of 35

SQL Injection and NoSQL Injection

SQL Injection (SQLi) is one of the oldest and most dangerous web application vulnerabilities. Despite being well-understood, it remains prevalent, ranking #3 in the OWASP Top 10 under "Injection." NoSQL databases, while different in architecture, face similar injection vulnerabilities. This lesson covers both SQL and NoSQL injection attacks and their prevention.

Understanding SQL Injection

SQL Injection occurs when untrusted data is sent to an interpreter as part of a command or query. The attacker's hostile data tricks the interpreter into executing unintended commands or accessing unauthorized data.

Vulnerable code example:
<?php
// DANGEROUS: Direct concatenation of user input
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($conn, $query);
?>

Attack payload:
Username: admin'--
Password: anything

Resulting query:
SELECT * FROM users WHERE username = 'admin'--' AND password = 'anything'

The -- comments out the password check, allowing login without a password!

Warning: SQL Injection can lead to complete database compromise, including data theft, data modification, deletion of records, and in some cases, complete server takeover through stored procedures and OS command execution.

Types of SQL Injection

1. Classic SQL Injection

Direct manipulation of SQL queries through user input to extract or modify data:

// Example 1: Authentication bypass
Username: ' OR '1'='1
Password: ' OR '1'='1
Query: SELECT * FROM users WHERE username='' OR '1'='1' AND password='' OR '1'='1'
Result: Always true, returns all users

// Example 2: Data extraction
Product ID: 1' UNION SELECT username, password, NULL FROM users--
Query: SELECT name, price, description FROM products WHERE id='1' UNION SELECT username, password, NULL FROM users--'
Result: Extracts all usernames and passwords alongside product data

2. Blind SQL Injection

When the application doesn't display database errors or query results, attackers use boolean-based or time-based techniques:

// Boolean-based blind SQLi
// Testing if first character of admin password is 'a'
Username: admin' AND SUBSTRING(password,1,1)='a'--
If page behaves normally → first char is 'a'
If page behaves differently → first char is not 'a'

// Time-based blind SQLi
Product ID: 1' AND IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0)--
If response delays 5 seconds → first char is 'a'
If response is immediate → first char is not 'a'

// Automated extraction (pseudo-code)
password = ""
for position in 1 to 32:
    for char in 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789':
        if test_char(position, char):
            password += char
            break

3. Second-Order SQL Injection

Malicious data is stored safely but later used unsafely in a different part of the application:

Step 1: User registers with username: admin'--
// Safely stored using parameterized query
$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->execute(['admin'--', 'attacker@evil.com']);

Step 2: Profile update uses the stored username unsafely
$username = $row['username']; // Retrieves: admin'--
$query = "UPDATE users SET bio='$bio' WHERE username='$username'";
// Query becomes: UPDATE users SET bio='...' WHERE username='admin'--'
// Comments out rest of query, potentially affecting wrong users

Note: Second-order injection is harder to detect because the injection point and exploitation point are separated. Regular security scanners may miss these vulnerabilities.

SQL Injection Prevention

1. Parameterized Queries (Prepared Statements)

The most effective defense against SQL injection is using parameterized queries, where SQL code and data are kept separate:

// PHP with PDO (Recommended)
<?php
$pdo = new PDO('mysql:host=localhost;dbname=mydb', $user, $pass);

// Prepared statement with named placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->execute([
    ':username' => $_POST['username'],
    ':password' => hash('sha256', $_POST['password'])
]);
$user = $stmt->fetch();

// Prepared statement with positional placeholders
$stmt = $pdo->prepare("INSERT INTO products (name, price) VALUES (?, ?)");
$stmt->execute([$name, $price]);
?>

// Node.js with MySQL
const mysql = require('mysql2/promise');
const [rows] = await connection.execute(
    'SELECT * FROM users WHERE username = ? AND email = ?',
    [username, email]
);

Tip: Modern ORMs (Eloquent, Sequelize, Hibernate, Entity Framework) use parameterized queries by default. However, be careful with raw query methods which might bypass this protection.

2. ORM Usage and Safe APIs

Object-Relational Mappers provide an additional layer of protection when used correctly:

// Laravel Eloquent (Safe)
$users = User::where('username', $request->input('username'))
             ->where('active', true)
             ->get();

// Safe: Query builder with bindings
$users = DB::table('users')
           ->where('votes', '>', 100)
           ->get();

// DANGEROUS: Raw queries without bindings
$users = DB::select("SELECT * FROM users WHERE name = '" . $name . "'"); // Vulnerable!

// Safe: Raw queries WITH bindings
$users = DB::select("SELECT * FROM users WHERE name = ?", [$name]);

// Safe: Named bindings
$users = DB::select("SELECT * FROM users WHERE name = :name", ['name' => $name]);

Warning: ORMs can still be vulnerable if you use raw SQL methods improperly. Always use parameter binding even when using raw query methods.

3. Input Validation and Sanitization

While not sufficient alone, input validation adds defense-in-depth:

<?php
// Whitelist validation for expected types
function validateProductId($id) {
    if (!is_numeric($id) || $id < 1) {
        throw new InvalidArgumentException("Invalid product ID");
    }
    return (int) $id;
}

// Whitelist for column names (can't use parameters for these)
$allowedSortColumns = ['name', 'price', 'created_at'];
$sortColumn = $_GET['sort'] ?? 'name';
if (!in_array($sortColumn, $allowedSortColumns)) {
    $sortColumn = 'name';
}
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY $sortColumn DESC");

// Limit input length
$username = substr($_POST['username'], 0, 50);

// Reject dangerous patterns (last resort, easily bypassed)
$blacklist = ['union', 'select', 'drop', 'insert', 'update', 'delete', '--', '/*'];
foreach ($blacklist as $keyword) {
    if (stripos($input, $keyword) !== false) {
        die("Suspicious input detected");
    }
}
?>

4. Least Privilege Database Access

Limit the damage of successful injection by restricting database user permissions:

-- Create application-specific database user
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE ON myapp.products TO 'webapp_user'@'localhost';
GRANT SELECT, INSERT ON myapp.orders TO 'webapp_user'@'localhost';
GRANT SELECT ON myapp.users TO 'webapp_user'@'localhost';

-- DO NOT grant:
-- DROP, CREATE, ALTER (structure modification)
-- FILE (read/write server files)
-- SUPER, PROCESS (server administration)
-- Access to mysql.user or other system tables

-- Separate users for different functions
CREATE USER 'webapp_readonly'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON myapp.* TO 'webapp_readonly'@'localhost';

NoSQL Injection

NoSQL databases like MongoDB, CouchDB, and Cassandra use different query languages, but they're not immune to injection attacks:

MongoDB Injection Examples

// Vulnerable Node.js code with MongoDB
app.post('/login', async (req, res) => {
    const user = await db.collection('users').findOne({
        username: req.body.username,
        password: req.body.password
    });
});

// Attack payload (sent as JSON)
{
    "username": {"$ne": null},
    "password": {"$ne": null}
}

// Resulting query
db.users.findOne({
    username: {$ne: null},  // Not equal to null (always true)
    password: {$ne: null}   // Not equal to null (always true)
})
// Returns first user in database, bypassing authentication!

// Another attack: $gt (greater than)
{
    "username": "admin",
    "password": {"$gt": ""}
}
// Matches any password, since all strings are greater than empty string

NoSQL Injection Prevention

// 1. Type validation and sanitization
app.post('/login', async (req, res) => {
    // Ensure inputs are strings, not objects
    const username = String(req.body.username);
    const password = String(req.body.password);
    
    const user = await db.collection('users').findOne({
        username: username,
        password: password
    });
});

// 2. Input validation with schema
const Joi = require('joi');

const loginSchema = Joi.object({
    username: Joi.string().alphanum().min(3).max(30).required(),
    password: Joi.string().min(8).required()
});

const { error, value } = loginSchema.validate(req.body);
if (error) {
    return res.status(400).json({ error: error.details[0].message });
}

// 3. Use ORM/ODM with built-in protection
// Mongoose for MongoDB
const UserSchema = new mongoose.Schema({
    username: { type: String, required: true },
    password: { type: String, required: true }
});

const User = mongoose.model('User', UserSchema);

// Safe query using Mongoose
const user = await User.findOne({ username: username });

// 4. Disable operator query parsing in Express
// Using express-mongo-sanitize
const mongoSanitize = require('express-mongo-sanitize');
app.use(mongoSanitize());

// This automatically removes any keys starting with $ or containing .

Note: JavaScript's type coercion makes NoSQL injection particularly dangerous. Always validate that user inputs are the expected primitive types (string, number, boolean), not objects or arrays.

Other NoSQL Injection Types

// CouchDB injection via view parameters
// Vulnerable:
db.view('users', 'by_username', {
    key: req.query.username  // Could be object with startkey/endkey
});

// Redis injection via Lua scripts
// Vulnerable:
const script = `return redis.call('GET', '${req.body.key}')`;
redis.eval(script);
// Attack: key = ") redis.call('FLUSHALL') --"

// Cassandra CQL injection
// Vulnerable:
const query = `SELECT * FROM users WHERE username='${username}'`;
// Safe: Use parameterized queries
const query = 'SELECT * FROM users WHERE username=?';
client.execute(query, [username]);

Advanced Protection Techniques

1. Stored Procedures (SQL)

-- Create stored procedure
DELIMITER //
CREATE PROCEDURE GetUserByUsername(IN p_username VARCHAR(50))
BEGIN
    SELECT user_id, username, email FROM users WHERE username = p_username;
END //
DELIMITER ;

// PHP calling stored procedure
<?php
$stmt = $pdo->prepare("CALL GetUserByUsername(?)");
$stmt->execute([$username]);
$user = $stmt->fetch();
?>

Tip: Stored procedures protect against injection only if they internally use parameterized queries. If they concatenate strings, they're still vulnerable.

2. Web Application Firewalls (WAF)

WAFs can detect and block SQL injection attempts, but should not be the only defense:

WAF detection patterns:
- Multiple SQL keywords (SELECT, UNION, FROM)
- SQL comments (--, /* */)
- Boolean operators in unexpected contexts
- UNION attacks
- Time-based attack patterns (SLEEP, BENCHMARK)

Limitations:
- Can be bypassed with encoding (URL encoding, Unicode, hex)
- False positives may block legitimate traffic
- Zero-day bypass techniques
- Performance overhead

3. Database Activity Monitoring

Monitoring for injection attempts:
- Unusual query patterns (UNION, excessive JOINs)
- Queries to system tables (information_schema, mysql.user)
- Multiple failed login attempts
- Queries with sleep/delay functions
- Access to administrative procedures
- Data exfiltration (large result sets)

// Example: Logging suspicious queries
<?php
$logFile = '/var/log/app/suspicious_queries.log';

if (preg_match('/union|information_schema|benchmark|sleep/i', $query)) {
    error_log(date('Y-m-d H:i:s') . " - Suspicious query: " . $query . "\n", 3, $logFile);
    // Also alert security team
}
?>

Testing for Injection Vulnerabilities

// Manual testing payloads
1. Single quote: ' (Should cause error if vulnerable)
2. Boolean test: ' OR '1'='1 and ' OR '1'='2
3. Time-based: ' AND SLEEP(5)--
4. UNION test: ' UNION SELECT NULL--
5. Comment injection: --+, #, /*

// Automated tools
- SQLMap (comprehensive SQL injection tool)
- Burp Suite (with SQLi scanner)
- NoSQLMap (for NoSQL databases)
- Havij, jSQL Injection

// SQLMap example
sqlmap -u "http://example.com/product?id=1" --batch --dbs
sqlmap -u "http://example.com/product?id=1" -D mydb --tables
sqlmap -u "http://example.com/product?id=1" -D mydb -T users --dump

Exercise: Create a vulnerable login system and then secure it:
1. Build a basic PHP/MySQL login page with deliberate SQL injection vulnerability
2. Test it with at least 5 different injection payloads
3. Rewrite using prepared statements
4. Add input validation layer
5. Implement rate limiting to prevent brute force
6. Create a MongoDB version and secure it against NoSQL injection
7. Document the differences between SQL and NoSQL injection prevention

Bonus: Set up SQLMap and run it against your vulnerable version, then verify it fails against the secured version.

Real-World Impact

2020 - Freepik SQL Injection: Exposed 8.3 million user records including emails and hashed passwords
2019 - GoDaddy SQL Injection: Compromised 28,000 customer accounts through hosting control panel vulnerability
2017 - Equifax Breach: SQL injection in web application framework led to theft of 147 million records
2014 - Yahoo Voices: SQL injection exposed 450,000 plaintext passwords

Warning: SQL injection regularly appears in data breach reports despite being preventable with known techniques. Many breaches occur because legacy code or poorly maintained applications aren't updated with proper protections.

Summary

SQL and NoSQL injection remain critical threats despite being well-understood vulnerabilities. The primary defense is always using parameterized queries or prepared statements that separate code from data. ORMs provide good protection when used properly. Input validation, least privilege database access, and monitoring add additional security layers. NoSQL databases require type validation and operator sanitization. Regular security testing and code review help ensure injection vulnerabilities are caught before production deployment.

Next Steps: In the next lesson, we'll explore Cross-Site Request Forgery (CSRF), learning how attackers can trick users into performing unwanted actions and how to protect against these attacks.