SQL Injection: Security Risks, Protection Measures & Best Practices

This guide covers SQL Injection in depth — including security risks, protection strategies, prepared statements, ORM frameworks, and input validation with practical code examples.

In a Nutshell

SQL Injection is a critical vulnerability where attackers inject malicious SQL commands into application input fields. Defense relies on prepared statements, input validation, and ORM frameworks.

Technical Overview

SQL Injection is an attack technique where malicious SQL commands are inserted through input fields, allowing attackers to gain unauthorized database access or manipulate data.

Attack vectors:

• Login forms: Bypassing authentication
• Search fields: Extracting sensitive data
• URL parameters: Manipulating database queries
• File uploads: Injection via filenames
• API endpoints: Direct database manipulation

Protection measures:

• Prepared Statements: Parameterized queries
• Input Validation: Whitelist-based checks
• ORM Frameworks: Abstraction layer for database access
• Least Privilege: Minimal database permissions
• Error Handling: No database errors exposed to users

Key Exam Topics

• SQL Injection: Inserting SQL commands through input fields
• Attack targets: Data extraction, manipulation, destruction
• Vulnerable points: Login forms, search fields, URL parameters
• Protection methods: Prepared statements, input validation, ORM
• Prepared Statements: Parameterized queries with placeholders
• ORM Frameworks: Hibernate, Entity Framework, SQLAlchemy
• Least Privilege: Minimal access rights for database operations
• Industry relevance: Critical security risk in web applications

Core Components

1. Attack vectors: Potential entry points for injection
2. Injection techniques: Union-based, Boolean-based, Time-based
3. Protection strategies: Prepared statements, validation, ORM
4. ORM Frameworks: Abstraction layer for database access
5. Input Validation: Whitelist-based input checking
6. Error Handling: Secure error management
7. Security Headers: Additional protection layers
8. Monitoring: Detecting 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) {
        // HIGHLY 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) {
        // Validate input 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: alphanumeric characters and underscore only
        Pattern pattern = Pattern.compile("^[a-zA-Z0-9_]{3,20}$");
        return pattern.matcher(username).matches();
    }
    
    private boolean isValidPassword(String password) {
        // Minimum 8 characters, maximum 100 characters
        return password != null && 
               password.length() >= 8 && 
               password.length() <= 100;
    }
}

// Advanced protection 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 {
            // Connect 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 protection measures
            System.out.println("\n--- Advanced Protection 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 Protections

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;
    
    // Getters and setters
    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 protections 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 + " cannot be empty");
        }
        
        if (input.length() > 100) {
            throw new IllegalArgumentException(fieldName + " is too long");
        }
        
        // Additional validation rules...
    }
    
    private void validateSearchTerm(String term) {
        if (term == null || term.trim().isEmpty()) {
            throw new IllegalArgumentException("Search term cannot 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 {
            // Set up 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 (blocked 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
// Unsafe 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 "Unsafe login: " . ($unsafeResult ? 'succeeded' : 'failed') . "\n";
    
    $safeResult = $safeService->loginSafe('admin', 'password123');
    echo "Safe login: " . ($safeResult ? 'succeeded' : '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 "Unsafe login (injection): " . 
         ($unsafeInjectionResult ? 'SUCCEEDED (Danger!)' : 'failed') . "\n";
    
    $safeInjectionResult = $safeService->loginSafe($injectionUsername, $injectionPassword);
    echo "Safe login (injection): " . 
         ($safeInjectionResult ? 'succeeded' : '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 safe
        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")
        
        # Safe 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: {'succeeded' if user else 'failed'}")
        
        # Injection attempt
        print("\n--- Injection Attempt ---")
        try:
            injected_user = service.authenticate("admin' OR '1'='1", "anything")
            print(f"Injection authentication: {'succeeded (Danger!)' if injected_user else 'failed'}")
        except Exception as e:
            print(f"Injection authentication: failed (protected)")
        
        # Safe search
        print("\n--- Safe 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 checks	Medium
Least Privilege	Minimal database rights	High
Error Handling	No database errors exposed	Medium
Rate Limiting	Request throttling	Medium

Input Validation Patterns

Whitelist Validation

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

Length Validation

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

Content-Type Validation

// Verify 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 rights
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 privileges

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

Benefits of Protection Measures

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

Drawbacks

• Performance: Slight overhead from validation
• Complexity: Additional code required
• Maintenance: Regular updates needed
• False Positives: Potential for false alarms

Common Exam Questions

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

2. What’s the best way to protect against SQL Injection? Prepared statements with parameterization are the most effective countermeasure.

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

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

Key Resources

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