SQL Injection: Security Risks, Protective Measures & Best Practices

This post is a comprehensive guide to SQL Injection – including security risks, protective measures, prepared statements, ORM frameworks and input validation with practical examples.

In a Nutshell

SQL Injection is a critical security vulnerability where attackers can inject manipulated SQL commands. Protection through prepared statements, input validation and ORM frameworks.

Compact Technical Description

SQL Injection is an attack technique where attackers inject malicious SQL commands into input fields to gain unauthorized access to databases or manipulate data.

Attack Vectors:

• Login Forms: Circumventing authentication
• Search Fields: Extraction of sensitive data
• URL Parameters: Manipulation of database queries
• File Uploads: Injection via file names
• API Endpoints: Direct database manipulation

Protective Measures:

• Prepared Statements: Parameterized queries
• Input Validation: Whitelist-based checking
• ORM Frameworks: Abstraction layer for database access
• Least Privilege: Minimal database rights
• Error Handling: No database errors exposed externally

Exam-Relevant Key Points

• SQL Injection: Injection of SQL commands via input fields
• Attack Targets: Data extraction, manipulation, destruction
• Vulnerabilities: Login forms, search fields, URL parameters
• Protective Measures: Prepared statements, input validation, ORM
• Prepared Statements: Parameterized queries with placeholders
• ORM Frameworks: Hibernate, Entity Framework, SQLAlchemy
• Least Privilege: Minimal rights for database access
• IHK-relevant: Critical security risk in web applications

Core Components

1. Attack Vectors: Possible entry points for injection
2. Injection Techniques: Union-based, Boolean-based, Time-based
3. Protective Measures: Prepared statements, validation, ORM
4. ORM Frameworks: Abstraction layer for database access
5. Input Validation: Whitelist-based input checking
6. Error Handling: Secure error handling
7. Security Headers: Additional protection layers
8. Monitoring: Detection of attack attempts

Practical Examples

1. SQL Injection Attacks and Countermeasures

import java.sql.*;
import java.util.Scanner;
import java.util.regex.Pattern;

// Unsafe implementation (vulnerable to SQL Injection)
class UnsafeLoginService {
    private Connection connection;
    
    public UnsafeLoginService(Connection connection) {
        this.connection = connection;
    }
    
    public boolean loginUnsafe(String username, String password) {
        // VERY DANGEROUS - Direct string concatenation
        String query = "SELECT * FROM users WHERE username = '" + username + 
                      "' AND password = '" + password + "'";
        
        System.out.println("Query: " + query);
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            
            return rs.next(); // Login successful if result exists
            
        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
            return false;
        }
    }
}

// Secure implementation with Prepared Statements
class SafeLoginService {
    private Connection connection;
    
    public SafeLoginService(Connection connection) {
        this.connection = connection;
    }
    
    public boolean loginSafe(String username, String password) {
        // SECURE - Prepared statement with placeholders
        String query = "SELECT * FROM users WHERE username = ? AND password = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(query)) {
            
            // Set parameters (automatically escaped)
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next();
            }
            
        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
            return false;
        }
    }
    
    // With input validation
    public boolean loginWithValidation(String username, String password) {
        // Input validation before database query
        if (!isValidUsername(username) || !isValidPassword(password)) {
            System.err.println("Invalid input");
            return false;
        }
        
        return loginSafe(username, password);
    }
    
    private boolean isValidUsername(String username) {
        // Whitelist: Only alphanumeric characters and underscore
        Pattern pattern = Pattern.compile("^[a-zA-Z0-9_]{3,20}$");
        return pattern.matcher(username).matches();
    }
    
    private boolean isValidPassword(String password) {
        // At least 8 characters, max 100 characters
        return password != null && 
               password.length() >= 8 && 
               password.length() <= 100;
    }
}

// Advanced security measures
class AdvancedSecurityService {
    private Connection connection;
    
    public AdvancedSecurityService(Connection connection) {
        this.connection = connection;
    }
    
    // With Rate Limiting
    private Map<String, Integer> loginAttempts = new java.util.concurrent.ConcurrentHashMap<>();
    private static final int MAX_ATTEMPTS = 5;
    private static final long LOCK_TIME_MS = 30000; // 30 minutes
    
    public boolean loginWithRateLimit(String username, String password) {
        String clientIp = getClientIp(); // Implementation required
        
        // Check rate limiting
        if (isBlocked(clientIp)) {
            System.err.println("IP blocked due to too many attempts");
            return false;
        }
        
        boolean loginSuccess = loginSafe(username, password);
        
        if (!loginSuccess) {
            incrementAttempts(clientIp);
        } else {
            clearAttempts(clientIp);
        }
        
        return loginSuccess;
    }
    
    private boolean isBlocked(String clientIp) {
        Integer attempts = loginAttempts.get(clientIp);
        return attempts != null && attempts >= MAX_ATTEMPTS;
    }
    
    private void incrementAttempts(String clientIp) {
        loginAttempts.merge(clientIp, 1, Integer::sum);
    }
    
    private void clearAttempts(String clientIp) {
        loginAttempts.remove(clientIp);
    }
    
    private String getClientIp() {
        // Implementation for IP extraction
        return "127.0.0.1"; // Placeholder
    }
    
    // With logging and monitoring
    public boolean loginWithMonitoring(String username, String password) {
        long startTime = System.currentTimeMillis();
        String clientIp = getClientIp();
        
        try {
            boolean success = loginSafe(username, password);
            
            // Log login attempt
            logLoginAttempt(username, clientIp, success, 
                          System.currentTimeMillis() - startTime);
            
            return success;
            
        } catch (Exception e) {
            logSecurityEvent("Login error", username, clientIp, e.getMessage());
            return false;
        }
    }
    
    private void logLoginAttempt(String username, String clientIp, 
                               boolean success, long duration) {
        String logLevel = success ? "INFO" : "WARN";
        System.out.printf("[%s] Login %s: user=%s, ip=%s, duration=%dms%n",
                         logLevel, success ? "successful" : "failed",
                         username, clientIp, duration);
    }
    
    private void logSecurityEvent(String event, String username, 
                                 String clientIp, String details) {
        System.out.printf("[SECURITY] %s: user=%s, ip=%s, details=%s%n",
                         event, username, clientIp, details);
    }
}

// SQL Injection Demo
public class SQLInjectionDemo {
    
    public static void main(String[] args) {
        System.out.println("=== SQL Injection Demo ===");
        
        try {
            // Connection to H2 in-memory database
            Connection conn = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "");
            
            // Create test database
            setupTestDatabase(conn);
            
            // Create services
            UnsafeLoginService unsafeService = new UnsafeLoginService(conn);
            SafeLoginService safeService = new SafeLoginService(conn);
            AdvancedSecurityService advancedService = new AdvancedSecurityService(conn);
            
            // Normal login attempts
            System.out.println("\n--- Normal Login Attempts ---");
            testLogin(unsafeService, "admin", "password123", "Unsafe");
            testLogin(safeService, "admin", "password123", "Safe");
            
            // SQL Injection attacks
            System.out.println("\n--- SQL Injection Attacks ---");
            
            // Classic injection attack
            String injectionUsername = "admin' OR '1'='1";
            String injectionPassword = "anything";
            
            System.out.println("Injection Username: " + injectionUsername);
            System.out.println("Injection Password: " + injectionPassword);
            
            boolean unsafeResult = unsafeService.loginUnsafe(injectionUsername, injectionPassword);
            System.out.println("Unsafe Login (Injection): " + 
                             (unsafeResult ? "SUCCESSFUL (Danger!)" : "failed"));
            
            boolean safeResult = safeService.loginSafe(injectionUsername, injectionPassword);
            System.out.println("Safe Login (Injection): " + 
                             (safeResult ? "successful" : "failed"));
            
            // Advanced protective measures
            System.out.println("\n--- Advanced Protective Measures ---");
            
            // With input validation
            boolean validationResult = safeService.loginWithValidation("admin", "password123");
            System.out.println("Login with validation: " + 
                             (validationResult ? "successful" : "failed"));
            
            boolean invalidResult = safeService.loginWithValidation("admin'; DROP TABLE users; --", "password");
            System.out.println("Login with invalid input: " + 
                             (invalidResult ? "successful" : "failed"));
            
            // With rate limiting
            boolean rateLimitedResult = advancedService.loginWithRateLimit("admin", "password123");
            System.out.println("Login with rate limiting: " + 
                             (rateLimitedResult ? "successful" : "failed"));
            
            // With monitoring
            boolean monitoredResult = advancedService.loginWithMonitoring("admin", "password123");
            System.out.println("Login with monitoring: " + 
                             (monitoredResult ? "successful" : "failed"));
            
            conn.close();
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    private static void testLogin(Object service, String username, String password, String type) {
        try {
            boolean result;
            if (service instanceof UnsafeLoginService) {
                result = ((UnsafeLoginService) service).loginUnsafe(username, password);
            } else if (service instanceof SafeLoginService) {
                result = ((SafeLoginService) service).loginSafe(username, password);
            } else {
                result = false;
            }
            
            System.out.printf("%s Login (%s, %s): %s%n", 
                             type, username, password, 
                             result ? "successful" : "failed");
        } catch (Exception e) {
            System.out.printf("%s Login: Error - %s%n", type, e.getMessage());
        }
    }
    
    private static void setupTestDatabase(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // Create users table
            stmt.execute("DROP TABLE users IF EXISTS");
            stmt.execute("""
                CREATE TABLE users (
                    id INT PRIMARY KEY AUTO_INCREMENT,
                    username VARCHAR(50) UNIQUE NOT NULL,
                    password VARCHAR(100) NOT NULL,
                    email VARCHAR(100),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """);
            
            // Insert test data
            stmt.execute("INSERT INTO users (username, password, email) VALUES " +
                         "('admin', 'password123', 'admin@example.com'), " +
                         "('user1', 'user123', 'user1@example.com'), " +
                         "('user2', 'user456', 'user2@example.com')");
            
            System.out.println("Test database created");
        }
    }
}

2. ORM Framework Protection Measures

import javax.persistence.*;
import java.util.List;
import java.util.regex.Pattern;

// JPA Entity
@Entity
@Table(name = "users")
class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(unique = true, nullable = false)
    private String username;
    
    @Column(nullable = false)
    private String password;
    
    private String email;
    
    // Getter and Setter
    public Long getId() { return id; }
    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }
    public String getPassword() { return password; }
    public void setPassword(String password) { this.password = password; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
}

// Secure service class with JPA
class SecureUserService {
    private EntityManager em;
    
    public SecureUserService(EntityManager em) {
        this.em = em;
    }
    
    // Secure login check with JPA
    public User authenticate(String username, String password) {
        // Input validation
        if (!isValidInput(username) || !isValidInput(password)) {
            throw new IllegalArgumentException("Invalid input");
        }
        
        try {
            // JPA query with named parameters (automatically safe)
            TypedQuery<User> query = em.createQuery(
                "SELECT u FROM User u WHERE u.username = :username AND u.password = :password", 
                User.class);
            
            query.setParameter("username", username);
            query.setParameter("password", password);
            
            List<User> results = query.getResultList();
            
            return results.isEmpty() ? null : results.get(0);
            
        } catch (Exception e) {
            // Secure error handling
            throw new RuntimeException("Authentication failed", e);
        }
    }
    
    // Secure search with JPA Criteria API
    public List<User> searchUsers(String searchTerm) {
        if (!isValidSearchTerm(searchTerm)) {
            throw new IllegalArgumentException("Invalid search term");
        }
        
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<User> cq = cb.createQuery(User.class);
        Root<User> user = cq.from(User.class);
        
        // Secure search with Criteria API (no string concatenation)
        Predicate searchPredicate = cb.or(
            cb.like(user.get("username"), "%" + searchTerm + "%"),
            cb.like(user.get("email"), "%" + searchTerm + "%")
        );
        
        cq.where(searchPredicate);
        
        return em.createQuery(cq).getResultList();
    }
    
    // Secure pagination
    public List<User> getUsersPaginated(int page, int size) {
        if (page < 0 || size <= 0 || size > 100) {
            throw new IllegalArgumentException("Invalid pagination parameters");
        }
        
        TypedQuery<User> query = em.createQuery("SELECT u FROM User u ORDER BY u.username", User.class);
        query.setFirstResult(page * size);
        query.setMaxResults(size);
        
        return query.getResultList();
    }
    
    private boolean isValidInput(String input) {
        // Whitelist validation
        Pattern pattern = Pattern.compile("^[a-zA-Z0-9_@.-]{3,100}$");
        return input != null && pattern.matcher(input).matches();
    }
    
    private boolean isValidSearchTerm(String term) {
        // Search terms allow more characters
        Pattern pattern = Pattern.compile("^[a-zA-Z0-9_@.-\\s]{1,50}$");
        return term != null && pattern.matcher(term).matches();
    }
}

// Advanced protection measures with Spring Data JPA
@Repository
interface UserRepository extends JpaRepository<User, Long> {
    
    // Secure queries with method names
    Optional<User> findByUsernameAndPassword(String username, String password);
    
    @Query("SELECT u FROM User u WHERE u.username LIKE %:search% OR u.email LIKE %:search%")
    List<User> findByUsernameOrEmailContaining(@Param("search") String search);
    
    // Native query with parameter binding
    @Query(value = "SELECT * FROM users WHERE email = :email", nativeQuery = true)
    Optional<User> findByEmailNative(@Param("email") String email);
}

// Service with Spring Data JPA
@Service
@Transactional
class UserServiceWithSpring {
    private UserRepository userRepository;
    
    public UserServiceWithSpring(UserRepository userRepository) {
        this.userRepository = userRepository;
    }
    
    public Optional<User> authenticate(String username, String password) {
        // Input validation
        validateInput(username, "username");
        validateInput(password, "password");
        
        // Spring Data JPA - automatically safe
        return userRepository.findByUsernameAndPassword(username, password);
    }
    
    public List<User> searchUsers(String searchTerm) {
        validateSearchTerm(searchTerm);
        return userRepository.findByUsernameOrEmailContaining(searchTerm);
    }
    
    private void validateInput(String input, String fieldName) {
        if (input == null || input.trim().isEmpty()) {
            throw new IllegalArgumentException(fieldName + " must not be empty");
        }
        
        if (input.length() > 100) {
            throw new IllegalArgumentException(fieldName + " is too long");
        }
        
        // Further validation rules...
    }
    
    private void validateSearchTerm(String term) {
        if (term == null || term.trim().isEmpty()) {
            throw new IllegalArgumentException("Search term must not be empty");
        }
        
        if (term.length() > 50) {
            throw new IllegalArgumentException("Search term is too long");
        }
    }
}

// ORM Demo
public class ORMSecurityDemo {
    
    public static void main(String[] args) {
        System.out.println("=== ORM Security Demo ===");
        
        // In a real application this would be managed by Spring/DI
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("testdb");
        EntityManager em = emf.createEntityManager();
        
        try {
            // Create test data
            setupTestData(em);
            
            // Create service
            SecureUserService service = new SecureUserService(em);
            
            // Normal authentication
            System.out.println("\n--- Normal Authentication ---");
            User user = service.authenticate("admin", "password123");
            System.out.println("Authentication: " + (user != null ? "successful" : "failed"));
            
            // Injection attempt (prevented by ORM)
            System.out.println("\n--- Injection Attempt ---");
            try {
                User injectedUser = service.authenticate("admin' OR '1'='1", "anything");
                System.out.println("Injection authentication: " + 
                                 (injectedUser != null ? "successful (danger!)" : "failed"));
            } catch (Exception e) {
                System.out.println("Injection authentication: failed (protected)");
            }
            
            // Secure search
            System.out.println("\n--- Secure Search ---");
            List<User> searchResults = service.searchUsers("admin");
            System.out.println("Search results: " + searchResults.size() + " users");
            
            // Pagination
            System.out.println("\n--- Pagination ---");
            List<User> paginatedUsers = service.getUsersPaginated(0, 2);
            System.out.println("Paginated results: " + paginatedUsers.size() + " users");
            
        } finally {
            em.close();
            emf.close();
        }
    }
    
    private static void setupTestData(EntityManager em) {
        EntityTransaction tx = em.getTransaction();
        try {
            tx.begin();
            
            // Create test users
            User admin = new User();
            admin.setUsername("admin");
            admin.setPassword("password123");
            admin.setEmail("admin@example.com");
            em.persist(admin);
            
            User user1 = new User();
            user1.setUsername("user1");
            user1.setPassword("user123");
            user1.setEmail("user1@example.com");
            em.persist(user1);
            
            tx.commit();
            System.out.println("Test data created");
            
        } catch (Exception e) {
            if (tx.isActive()) {
                tx.rollback();
            }
            e.printStackTrace();
        }
    }
}

3. PHP and Python Examples

<?php
// Insecure PHP implementation (vulnerable)
class UnsafeLoginPHP {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function loginUnsafe($username, $password) {
        // DANGEROUS - Direct string concatenation
        $query = "SELECT * FROM users WHERE username = '" . $username . 
                "' AND password = '" . $password . "'";
        
        echo "Query: " . $query . "\n";
        
        $stmt = $this->pdo->query($query);
        return $stmt->rowCount() > 0;
    }
}

// Secure PHP implementation
class SafeLoginPHP {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function loginSafe($username, $password) {
        // SECURE - Prepared Statement
        $query = "SELECT * FROM users WHERE username = ? AND password = ?";
        
        $stmt = $this->pdo->prepare($query);
        $stmt->execute([$username, $password]);
        
        return $stmt->rowCount() > 0;
    }
    
    public function loginWithValidation($username, $password) {
        // Input validation
        if (!$this->isValidUsername($username) || !$this->isValidPassword($password)) {
            throw new InvalidArgumentException("Invalid input");
        }
        
        return $this->loginSafe($username, $password);
    }
    
    private function isValidUsername($username) {
        // Whitelist validation
        return preg_match('/^[a-zA-Z0-9_]{3,20}$/', $username);
    }
    
    private function isValidPassword($password) {
        return strlen($password) >= 8 && strlen($password) <= 100;
    }
}

// PHP Demo
echo "=== PHP SQL Injection Demo ===\n";

try {
    $pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
    
    // Create services
    $unsafeService = new UnsafeLoginPHP($pdo);
    $safeService = new SafeLoginPHP($pdo);
    
    // Normal login attempts
    echo "\n--- Normal Login Attempts ---\n";
    $unsafeResult = $unsafeService->loginUnsafe('admin', 'password123');
    echo "Insecure Login: " . ($unsafeResult ? 'successful' : 'failed') . "\n";
    
    $safeResult = $safeService->loginSafe('admin', 'password123');
    echo "Secure Login: " . ($safeResult ? 'successful' : 'failed') . "\n";
    
    // Injection attack
    echo "\n--- SQL Injection Attack ---\n";
    $injectionUsername = "admin' OR '1'='1";
    $injectionPassword = "anything";
    
    echo "Injection Username: $injectionUsername\n";
    echo "Injection Password: $injectionPassword\n";
    
    $unsafeInjectionResult = $unsafeService->loginUnsafe($injectionUsername, $injectionPassword);
    echo "Insecure Login (Injection): " . 
         ($unsafeInjectionResult ? 'SUCCESSFUL (Danger!)' : 'failed') . "\n";
    
    $safeInjectionResult = $safeService->loginSafe($injectionUsername, $injectionPassword);
    echo "Secure Login (Injection): " . 
         ($safeInjectionResult ? 'successful' : 'failed') . "\n";
    
} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage() . "\n";
}
?>

# Python SQLAlchemy Implementation
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import re

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    password = Column(String(100), nullable=False)
    email = Column(String(100))
    
    def __repr__(self):
        return f"<User(username='{self.username}')>"

class SecureUserService:
    def __init__(self, session):
        self.session = session
    
    def authenticate(self, username, password):
        # Input validation
        if not self.is_valid_input(username) or not self.is_valid_input(password):
            raise ValueError("Invalid input")
        
        # SQLAlchemy Query - automatically secure
        user = self.session.query(User).filter(
            User.username == username,
            User.password == password
        ).first()
        
        return user
    
    def search_users(self, search_term):
        if not self.is_valid_search_term(search_term):
            raise ValueError("Invalid search term")
        
        # Secure search with SQLAlchemy
        users = self.session.query(User).filter(
            (User.username.like(f"%{search_term}%")) |
            (User.email.like(f"%{search_term}%"))
        ).all()
        
        return users
    
    def is_valid_input(self, input_str):
        # Whitelist validation
        pattern = r'^[a-zA-Z0-9_@.-]{3,100}$'
        return input_str is not None and re.match(pattern, input_str) is not None
    
    def is_valid_search_term(self, term):
        # Search terms allow more characters
        pattern = r'^[a-zA-Z0-9_@.-\s]{1,50}$'
        return term is not None and re.match(pattern, term) is not None

# Python Demo
def main():
    print("=== Python SQLAlchemy Security Demo ===")
    
    # Create database engine
    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    try:
        # Create test data
        admin = User(username='admin', password='password123', email='admin@example.com')
        user1 = User(username='user1', password='user123', email='user1@example.com')
        
        session.add(admin)
        session.add(user1)
        session.commit()
        
        # Create service
        service = SecureUserService(session)
        
        # Normal authentication
        print("\n--- Normal Authentication ---")
        user = service.authenticate('admin', 'password123')
        print(f"Authentication: {'successful' if user else 'failed'}")
        
        # Injection attempt
        print("\n--- Injection Attempt ---")
        try:
            injected_user = service.authenticate("admin' OR '1'='1", "anything")
            print(f"Injection Authentication: {'successful (Danger!)' if injected_user else 'failed'}")
        except Exception as e:
            print(f"Injection Authentication: failed (protected)")
        
        # Secure search
        print("\n--- Secure Search ---")
        search_results = service.search_users('admin')
        print(f"Search Results: {len(search_results)} users")
        
    finally:
        session.close()

if __name__ == "__main__":
    main()

SQL Injection Techniques

Union-based Injection

-- Original Query
SELECT * FROM users WHERE username = 'admin' AND password = 'password'

-- Injection
SELECT * FROM users WHERE username = 'admin' UNION SELECT table_name, null, null, null FROM information_schema.tables--' AND password = 'anything'

Boolean-based Injection

-- Original Query
SELECT * FROM products WHERE category = 'electronics'

-- Injection
SELECT * FROM products WHERE category = 'electronics' AND 1=1--'  -- Always true
SELECT * FROM products WHERE category = 'electronics' AND 1=0--'  -- Always false

Time-based Injection

-- Original Query
SELECT * FROM users WHERE id = 1

-- Injection with time delay
SELECT * FROM users WHERE id = 1 AND (SELECT SLEEP(5))--'  -- Delays response if true

Protection Measures Overview

Measure	Description	Effectiveness
Prepared Statements	Parameterized queries	Very high
ORM Frameworks	Abstraction layer	High
Input Validation	Whitelist-based checking	Medium
Least Privilege	Minimal DB permissions	High
Error Handling	No DB errors exposed	Medium
Rate Limiting	Limit attempts	Medium

Input Validation Patterns

Whitelist Validation

// Only allowed characters
Pattern pattern = Pattern.compile("^[a-zA-Z0-9_]{3,20}$");
boolean isValid = pattern.matcher(input).matches();

Length Validation

// Length limitation
if (input == null || input.length() < 3 || input.length() > 100) {
    throw new IllegalArgumentException("Invalid length");
}

Content-Type Validation

// Check expected data types
try {
    int number = Integer.parseInt(input);
} catch (NumberFormatException e) {
    throw new IllegalArgumentException("Number expected");
}

Database Security Best Practices

Least Privilege Principle

-- Web application user with minimal permissions
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE ON app_db.users TO 'webapp';
GRANT SELECT ON app_db.products TO 'webapp';
-- NO DROP, ALTER, CREATE permissions

Stored Procedures

-- Secure stored procedure
CREATE PROCEDURE authenticate_user(IN p_username VARCHAR(50), IN p_password VARCHAR(100))
BEGIN
    SELECT id FROM users WHERE username = p_username AND password = p_password;
END;

-- Call from application
CALL authenticate_user(?, ?);

Monitoring and Detection

Anomaly Detection

// Detect unusual query patterns
public class QueryMonitor {
    private Map<String, Integer> queryPatterns = new ConcurrentHashMap<>();
    
    public void monitorQuery(String query, String ip) {
        // Analyze query pattern
        String pattern = extractPattern(query);
        
        // Check frequency
        int count = queryPatterns.merge(pattern, 1, Integer::sum);
        
        if (count > 100) { // Threshold
            alertSecurityTeam("Suspicious query pattern", ip, pattern);
        }
    }
    
    private String extractPattern(String query) {
        // Normalize query for pattern recognition
        return query.replaceAll("\\d+", "N")
                  .replaceAll("'[^']*'", "'S'")
                  .toLowerCase();
    }
    
    private void alertSecurityTeam(String message, String ip, String details) {
        System.out.printf("[ALERT] %s from %s: %s%n", message, ip, details);
        // Send to SIEM system
    }
}

Advantages and Disadvantages

Advantages of Protection Measures

• Security: Prevents database compromise
• Compliance: Meets security standards
• Trust: Protects user data
• Stability: Prevents data corruption

Disadvantages

• Performance: Slight overhead from validation
• Complexity: Additional code required
• Maintenance: Regular updates necessary
• Error Rate: False positives possible

Common Exam Questions

1. What is SQL Injection and how does it work? Injection of SQL commands through input fields via string concatenation in queries.

2. What is the best way to protect against SQL Injection? Prepared statements with parameterization are the most effective protection measure.

3. Why are ORM Frameworks more secure? They abstract SQL queries and automatically use prepared statements.

4. What is the difference between whitelist and blacklist validation? Whitelist allows only defined characters, blacklist blocks known dangerous patterns.

Key Sources

1. https://owasp.org/www-community/attacks/SQL_Injection
2. https://www.w3schools.com/sql/sql_injection.asp
3. https://portswigger.net/web-security/sql-injection