SQL Injection: Attacks, Protection Measures & Prevention

This article is a definition of SQL Injection Attacks – including protection measures and best practices.

In a Nutshell

SQL Injection is a vulnerability in web applications where attackers inject manipulated SQL commands into input fields to gain unauthorized access to databases or modify data.

Compact Technical Description

SQL Injection is one of the most dangerous and common security vulnerabilities in web applications. Attackers exploit insecure processing of user input to execute their own SQL commands.

Attack Vectors:

• Login Forms: Bypassing authentication
• Search Fields: Extracting data from other tables
• URL Parameters: Manipulating database queries
• API Endpoints: Direct SQL command execution

Dangers:

• Data Theft: Extraction of sensitive information
• Data Manipulation: Changing or deleting data
• System Takeover: Execution of system commands
• Denial of Service: Destroying database structures

Exam-Relevant Key Points

• Goal: Access to, modification or destruction of databases
• Cause: Insecure passing of user input to database
• Typical Vulnerabilities: Login forms, search fields, URL parameters
• Protection Measures: Prepared Statements, Input Validation, ORM Frameworks
• Example Attack: admin' OR '1'='1 bypasses password check
• OWASP A03: Injection is top 3 security risk
• Principle of Rights: No root access for web users
• Chamber of Commerce Relevant: Important for software architecture and development

Core Components

1. Vulnerable Queries: Direct string concatenation of SQL
2. Prepared Statements: Parameterized queries
3. Input Validation: Whitelisting and escaping
4. ORM Frameworks: Hibernate, Entity Framework
5. Least Privilege: Minimal database rights
6. Error Handling: No database errors exposed
7. Security Testing: Automated scanners
8. Code Reviews: Manual inspection

Practical Examples

Vulnerable Code

// VERY DANGEROUS - SQL Injection possible
public User login(String username, String password) {
    String query = "SELECT * FROM users WHERE username = '" + 
                   username + "' AND password = '" + password + "'";
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    
    if (rs.next()) {
        return new User(rs.getString("username"), rs.getString("role"));
    }
    return null;
}

// Attack: username = "admin' OR '1'='1" -- "
// Result: SELECT * FROM users WHERE username = 'admin' OR '1'='1' -- ' AND password = ''
// All users are returned!

Secure Alternative with Prepared Statements

// SECURE - SQL Injection prevented
public User login(String username, String password) {
    String query = "SELECT * FROM users WHERE username = ? AND password = ?";
    PreparedStatement stmt = connection.prepareStatement(query);
    stmt.setString(1, username);
    stmt.setString(2, password);
    ResultSet rs = stmt.executeQuery();
    
    if (rs.next()) {
        return new User(rs.getString("username"), rs.getString("role"));
    }
    return null;
}

Further Attack Examples

-- Union-Based Injection
' UNION SELECT username, password FROM admins --

-- Blind Injection
' AND (SELECT COUNT(*) FROM users WHERE username='admin' AND password LIKE 'a%') > 0 --

-- Time-Based Injection
' AND (SELECT SLEEP(5)) --

-- StoredProcedure Injection
'; DROP TABLE users; --

Protection Measures

1. Prepared Statements (Parameterized Queries)

// Java
String sql = "SELECT * FROM products WHERE name = ? AND price < ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, productName);
stmt.setDouble(2, maxPrice);

// PHP with PDO
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);

// Python
cursor.execute("SELECT * FROM users WHERE id = %s", [user_id])

2. Input Validation & Escaping

// Whitelist Validation
public boolean isValidUsername(String username) {
    return username.matches("^[a-zA-Z0-9_]{3,20}$");
}

// HTML Escaping for output
String safeOutput = StringEscapeUtils.escapeHtml4(userInput);

3. ORM Frameworks

// JPA/Hibernate
@Entity
@Table(name="users")
public class User {
    @Id
    private String username;
    private String password;
    
    // Automatic SQL creation, safe from injection
}

public User findByUsername(String username) {
    return entityManager.createQuery(
        "SELECT u FROM User u WHERE u.username = :username", User.class)
        .setParameter("username", username)
        .getSingleResult();
}

4. Least Privilege Principle

-- Web application receives only necessary rights
GRANT SELECT, INSERT, UPDATE ON app_db.users TO 'webapp'@'localhost';
-- NO DROP, DELETE, ALTER rights!

Advantages and Disadvantages of Protection Measures

Advantages

• Security: Prevents database compromise
• Compliance: Meets security standards (GDPR, ISO 27001)
• Trust: Protects user data and company reputation
• Cost Efficiency: Prevents expensive security incidents

Disadvantages

• Performance: Prepared Statements can be slower
• Complexity: Requires additional programming effort
• Learning Curve: Developers must learn secure coding
• Testing: Additional security testing required

Common Exam Questions

1. What is SQL Injection and how does it work? Injecting SQL code via user input through insecure string concatenation.

2. How do you most effectively prevent SQL Injection? By using Prepared Statements/Parameterized Queries.

3. Why is Input Validation alone not sufficient? Validation can be bypassed, Prepared Statements are more secure.

4. What role does the Least Privilege Principle play? Web applications should have only minimal database rights to limit damage.

Most Important Sources

1. https://owasp.org/www-community/attacks/SQL_Injection
2. https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
3. https://portswigger.net/web-security/sql-injection