Query Compilation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

# ANTI-PATTERN: Ad-hoc queries with string interpolation
# Every execution triggers full parse → analyze → optimize → execute
 
def authenticate_user_BAD(username: str, password_hash: str) -> bool:
    """
    This approach has TWO critical problems:
    1. SQL injection vulnerability
    2. Repeated query compilation overhead
    """
    # String interpolation - NEVER DO THIS
    query = f"""
        SELECT id, role FROM users 
        WHERE username = '{username}' 
        AND password_hash = '{password_hash}'
    """
    
    # Each call: parse (0.1ms) + analyze (0.2ms) + optimize (5ms) + execute (0.5ms)
    # Total: ~6ms per login, only 0.5ms is actual work
    result = connection.execute(query)
    return result.fetchone() is not None
 
 
# CORRECT: Prepared statement - compile once, execute many
def authenticate_user_GOOD(username: str, password_hash: str) -> bool:
    """
    Prepared statement approach:
    1. SQL injection proof (parameters are not parsed)
    2. Zero compilation overhead after first preparation
    """
    # SQL template with parameter placeholders
    query = """
        SELECT id, role FROM users 
        WHERE username = %s 
        AND password_hash = %s
    """
    
    # Database recognizes this as a prepared template
    # Execution: 0.5ms (just the actual work)
    result = connection.execute(query, (username, password_hash))
    return result.fetchone() is not None

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- PostgreSQL Prepared Statement Syntax
 
-- Step 1: PREPARE - Parse, analyze, and optimize once
PREPARE user_lookup (text, text) AS
    SELECT id, email, role, created_at
    FROM users
    WHERE username = $1
    AND password_hash = $2;
 
-- Step 2: EXECUTE - Run with specific parameter values
EXECUTE user_lookup('alice', 'sha256:abc123...');
EXECUTE user_lookup('bob', 'sha256:def456...');
EXECUTE user_lookup('charlie', 'sha256:ghi789...');
 
-- Each EXECUTE skips parsing and optimization!
 
-- Query the prepared statement cache
SELECT name, statement, prepare_time
FROM pg_prepared_statements;
 
-- Step 3: DEALLOCATE - Release resources when done
DEALLOCATE user_lookup;
 
-- Or deallocate all prepared statements
DEALLOCATE ALL;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

// Java JDBC Parameter Binding Examples
 
import java.sql.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;
 
public class ParameterBindingDemo {
    
    public void demonstrateBindingTypes(Connection conn) throws SQLException {
        
        // Prepare with multiple parameter types
        String sql = """
            INSERT INTO orders (
                customer_id,        -- INTEGER
                product_name,       -- VARCHAR
                quantity,           -- INTEGER
                unit_price,         -- DECIMAL
                order_date,         -- TIMESTAMP
                is_priority         -- BOOLEAN
            ) VALUES (?, ?, ?, ?, ?, ?)
        """;
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            // Each set*() method binds a typed value to a position
            pstmt.setInt(1, 42);                                    // customer_id
            pstmt.setString(2, "Widget Pro");                       // product_name
            pstmt.setInt(3, 10);                                    // quantity
            pstmt.setBigDecimal(4, new BigDecimal("29.99"));        // unit_price
            pstmt.setTimestamp(5, Timestamp.valueOf(                // order_date
                LocalDateTime.now()));
            pstmt.setBoolean(6, true);                              // is_priority
            
            pstmt.executeUpdate();
            
            // Reuse with different values - no recompilation
            pstmt.setInt(1, 43);
            pstmt.setString(2, "Gadget Deluxe");
            pstmt.setInt(3, 5);
            pstmt.setBigDecimal(4, new BigDecimal("149.99"));
            pstmt.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now()));
            pstmt.setBoolean(6, false);
            
            pstmt.executeUpdate();
        }
    }
    
    // NULL handling requires explicit setNull()
    public void handleNullValues(Connection conn) throws SQLException {
        String sql = "UPDATE users SET middle_name = ? WHERE id = ?";
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setNull(1, Types.VARCHAR);  // Explicit NULL with type
            pstmt.setInt(2, 123);
            pstmt.executeUpdate();
        }
    }
    
    // Batch operations leverage the same prepared statement
    public void batchInsert(Connection conn, List<Order> orders) 
            throws SQLException {
        String sql = "INSERT INTO orders (customer_id, total) VALUES (?, ?)";
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (Order order : orders) {
                pstmt.setInt(1, order.getCustomerId());
                pstmt.setBigDecimal(2, order.getTotal());
                pstmt.addBatch();  // Accumulate for batch execution
            }
            
            int[] results = pstmt.executeBatch();  // Execute all at once
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

# SQL Injection: Vulnerable vs. Secure Patterns
 
import psycopg2
 
# Attacker input designed to exploit string interpolation
malicious_input = "'; DROP TABLE users; --"
 
# ============================================================
# VULNERABLE: String formatting/interpolation
# ============================================================
def get_user_VULNERABLE(username: str) -> dict:
    """
    NEVER do this! User input becomes SQL code.
    """
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
    
    # String formatting - CATASTROPHIC VULNERABILITY
    query = f"SELECT * FROM users WHERE username = '{username}'"
    # With malicious_input:
    # "SELECT * FROM users WHERE username = ''; DROP TABLE users; --'"
    #                                          ^^^^^^^^^^^^^^^^^^^
    #                                          Injected destructive command!
    
    cursor.execute(query)  # If multi-statement enabled: table deleted!
    return cursor.fetchone()
 
 
# ============================================================
# SECURE: Parameterized query (prepared statement)
# ============================================================
def get_user_SECURE(username: str) -> dict:
    """
    Correct approach. User input is data, not code.
    """
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
    
    # Parameterized query - completely safe
    query = "SELECT * FROM users WHERE username = %s"
    
    # Parameter passed separately from query
    cursor.execute(query, (username,))
    # With malicious_input:
    # Searches for user whose name is literally: "'; DROP TABLE users; --"
    # Finds no matches (unless someone really has that username)
    # NO SQL is executed, just a harmless string comparison
    
    return cursor.fetchone()
 
 
# The security guarantee:
# - Query structure is parsed BEFORE parameters are known
# - Parameters are bound AFTER parsing, as data values
# - No amount of special characters in parameters can alter query logic

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

// HikariCP + PostgreSQL: Prepared Statement Caching
 
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
 
public class PreparedStatementPooling {
    
    public static HikariDataSource createOptimizedPool() {
        HikariConfig config = new HikariConfig();
        
        // Basic connection settings
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
        config.setUsername("app_user");
        config.setPassword("secure_password");
        
        // Connection pool sizing
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        
        // CRITICAL: Enable prepared statement caching
        // This is the server-side prepared statement cache size
        config.addDataSourceProperty("preparedStatementCacheQueries", "256");
        
        // PostgreSQL driver-level statement caching
        config.addDataSourceProperty("prepareThreshold", "5");
        // After 5 uses of the same query, driver prepares it server-side
        
        // Optional: Enable prepared statement cache globally
        config.addDataSourceProperty("preparedStatementCacheSizeMiB", "5");
        
        return new HikariDataSource(config);
    }
    
    // Usage pattern: statement cached per-connection
    public void executeWithCachedStatement(HikariDataSource ds) 
            throws SQLException {
        
        // Get connection from pool
        try (Connection conn = ds.getConnection();
             // First use: driver may interpret (uses more CPU)
             // After prepareThreshold uses: server-side preparation
             PreparedStatement pstmt = conn.prepareStatement(
                 "SELECT * FROM orders WHERE customer_id = ?")) {
            
            pstmt.setInt(1, 42);
            ResultSet rs = pstmt.executeQuery();
            // process results
        }
        // Connection returned to pool; cached statements persist
        
        // Next request gets same (or different) connection
        try (Connection conn = ds.getConnection();
             // If same connection: immediate server-side execution
             // If different connection: may need re-preparation
             PreparedStatement pstmt = conn.prepareStatement(
                 "SELECT * FROM orders WHERE customer_id = ?")) {
            
            pstmt.setInt(1, 43);
            ResultSet rs = pstmt.executeQuery();
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- PostgreSQL: Monitoring Prepared Statement Usage
 
-- View all prepared statements in current session
SELECT name, statement, prepare_time, parameter_types
FROM pg_prepared_statements;
 
-- Check statement statistics (requires pg_stat_statements extension)
-- Shows which queries are called most frequently
SELECT 
    substring(query, 1, 80) as query_preview,
    calls,
    mean_exec_time,
    total_exec_time,
    rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;
 
-- Identify queries that would benefit from preparation
-- High calls with low mean_exec_time = preparation candidates
SELECT 
    query,
    calls,
    mean_exec_time as avg_ms,
    (calls * mean_exec_time) as total_time_ms
FROM pg_stat_statements
WHERE calls > 100
AND mean_exec_time < 10  -- Quick queries executed often
ORDER BY calls DESC;
 
-- Check if JIT compilation is being used (PG 11+)
SHOW jit;
SHOW jit_above_cost;  -- JIT kicks in for queries above this cost