JDBC & the DAO Pattern

Avoiding SQL Injection & Best Practices

18 min Lesson 9 of 13

Avoiding SQL Injection & Best Practices

SQL injection has sat at or near the top of the OWASP Top 10 for over two decades — not because it is hard to prevent, but because developers keep mixing user input with SQL strings. This lesson consolidates the defensive layers that a professional JDBC application must have: parameterization as the primary control, input validation as a secondary defence, least-privilege database accounts, and a handful of coding disciplines that eliminate entire classes of vulnerabilities.

What SQL Injection Actually Is

An injection attack occurs when user-supplied text is concatenated directly into a SQL string and the database executes the attacker-controlled fragment as part of the query. The classic example — a login form:

// NEVER DO THIS
String sql = "SELECT * FROM users WHERE username = '" + username
           + "' AND password = '" + password + "'";

If an attacker enters admin' -- as the username, the rendered SQL becomes:

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

The double-dash comments out the password check. Every row with username = 'admin' is returned, bypassing authentication entirely. More destructive payloads can DROP TABLE, exfiltrate every row, or call stored procedures that modify OS-level files.

Primary Defence: Parameterization with PreparedStatement

PreparedStatement is the non-negotiable baseline. When you supply a parameter through a typed setter, the JDBC driver sends the SQL template and the value as two separate network messages. The database never parses the value as SQL — it treats it as a literal string, number, or binary blob.

// Correct — parameterised; injection is structurally impossible
String sql = "SELECT * FROM users WHERE username = ? AND password_hash = ?";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, username);
    ps.setString(2, hashPassword(rawPassword));   // always hash passwords

    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            return Optional.of(mapRow(rs));
        }
    }
}
return Optional.empty();

Parameterization works because of protocol-level separation. The SQL template is compiled once; bind values arrive in a subsequent step. There is no string that an attacker can construct as an input value that will ever be interpreted as SQL syntax — the database simply does not parse the value slot.

Dynamic Clauses: IN Lists and Sort Columns

Parameterization covers scalar values trivially. Two patterns trip up developers who already understand the basics:

IN lists with variable length. You cannot bind a whole list to a single ?. Build the placeholder string programmatically, then bind each element individually:

public List<Product> findByIds(Connection conn, List<Integer> ids)
        throws SQLException {

    if (ids.isEmpty()) return List.of();

    // Build "?,?,?" with exactly ids.size() placeholders
    String placeholders = String.join(",",
        Collections.nCopies(ids.size(), "?"));

    String sql = "SELECT id, name, price FROM products WHERE id IN (" + placeholders + ")";

    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        for (int i = 0; i < ids.size(); i++) {
            ps.setInt(i + 1, ids.get(i));          // still parameterised — safe
        }
        try (ResultSet rs = ps.executeQuery()) {
            List<Product> result = new ArrayList<>();
            while (rs.next()) result.add(mapRow(rs));
            return result;
        }
    }
}

Sort columns. Column names cannot be passed as bind parameters — the database needs them at parse time. Use an allowlist:

private static final Set<String> ALLOWED_SORT_COLUMNS =
    Set.of("name", "price", "stock", "created_at");

public List<Product> findAllSorted(Connection conn, String sortColumn)
        throws SQLException {

    if (!ALLOWED_SORT_COLUMNS.contains(sortColumn)) {
        throw new IllegalArgumentException("Invalid sort column: " + sortColumn);
    }

    // sortColumn is safe — it came from our allowlist, not from raw user input
    String sql = "SELECT id, name, price, stock FROM products ORDER BY " + sortColumn;

    try (PreparedStatement ps = conn.prepareStatement(sql);
         ResultSet rs = ps.executeQuery()) {
        List<Product> list = new ArrayList<>();
        while (rs.next()) list.add(mapRow(rs));
        return list;
    }
}

Never trust the allowlist check alone without a strict definition. The set must list exact column identifiers, not patterns. If the set is built from user-facing enum values, validate that each enum value maps to a real column name in a one-time startup check, not at query time.

Secondary Defence: Input Validation

Parameterization stops injection; validation stops bad data from reaching the database at all. These are complementary, not alternatives — apply both.

Validate early, at the boundary where input enters your system (a Servlet, a REST endpoint, a service method). For each field, ask three questions:

Type: Is this actually a number / date / UUID? Parse and reject on failure before touching the DAO.
Range / length: Is a VARCHAR(150) column being fed a 10 000-character string? Reject it; do not rely on the DB to truncate silently.
Format: Email addresses, phone numbers, and URLs have structural rules. Enforce them with regex or a dedicated library — never with a hand-rolled SQL LIKE clause.

// Validate before touching the DAO
public void createProduct(String name, String rawPrice, String rawStock)
        throws ValidationException, SQLException {

    if (name == null || name.isBlank()) {
        throw new ValidationException("Product name is required.");
    }
    if (name.length() > 150) {
        throw new ValidationException("Product name must be 150 characters or fewer.");
    }

    double price;
    try {
        price = Double.parseDouble(rawPrice);
    } catch (NumberFormatException e) {
        throw new ValidationException("Price must be a valid number.");
    }
    if (price < 0) {
        throw new ValidationException("Price cannot be negative.");
    }

    int stock;
    try {
        stock = Integer.parseInt(rawStock);
    } catch (NumberFormatException e) {
        throw new ValidationException("Stock must be a whole number.");
    }

    try (Connection conn = dataSource.getConnection()) {
        productDao.create(conn, name.strip(), price, stock);
    }
}

Least-Privilege Database Accounts

Even perfectly parameterised code cannot prevent damage if the application database account has unrestricted rights. Follow the principle of least privilege: grant the application user only the permissions it actually needs.

-- MySQL: create a restricted application user
CREATE USER 'shop_app'@'%' IDENTIFIED BY 'strong_random_password';

-- Grant only the operations the application performs
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.products TO 'shop_app'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.orders   TO 'shop_app'@'%';

-- No GRANT OPTION, no DDL rights, no FILE, no SUPER
FLUSH PRIVILEGES;

With this setup, even if an attacker exploits a logic bug and somehow executes arbitrary SQL through the application account, they cannot DROP TABLE, read /etc/passwd via LOAD DATA INFILE, or access tables outside the allowlisted set.

Use a separate read-only account for SELECT-only code paths. Reporting queries, dashboards, and admin read views do not need INSERT or UPDATE rights. A read-only account that leaks cannot modify data.

Additional Best Practices

Never store plain-text passwords. Store only a password hash produced by a slow, salted algorithm. BCrypt from Spring Security or the java.security.MessageDigest with a proper KDF (PBKDF2) are the standard choices. A raw SHA-256 hash without a salt is not acceptable.

Avoid SELECT *. Always name the columns you need. Wildcard selects pull data you may not intend to expose, break if column order changes, and make the mapping code harder to audit.

Log queries without parameters. If you need to log SQL for debugging, log the template only — never the bound values. A log line containing WHERE password_hash = 'bcrypt$...' leaks sensitive data to anyone with log access.

Use schema-level constraints as a last line. NOT NULL, UNIQUE, CHECK constraints, and foreign keys catch data integrity problems that slip past application validation. They are not a replacement for application-level validation but a reliable backstop.

-- Schema constraints complement application validation
CREATE TABLE users (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(80)  NOT NULL UNIQUE,
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_email_format CHECK (email LIKE '%@%.%')
);

Checklist: Secure Data Access

Every query that touches user input uses PreparedStatement with typed setters.
Dynamic column/table names are validated against an explicit allowlist before concatenation.
All IN-list queries build ? placeholders programmatically and bind each value individually.
Input validation (type, length, range, format) runs at the service boundary before any DAO call.
The application database account holds only the minimum necessary privileges.
Passwords are stored as hashes, never plain text.
Log output contains SQL templates, not bound parameter values.
Schema constraints (NOT NULL, UNIQUE, CHECK) back up application validation.

Summary

SQL injection is prevented at the architectural level by using PreparedStatement for every query that incorporates external data. Parameterization is not a style choice — it is the structural guarantee that user input can never alter query semantics. Layer on top: validate inputs early at the service boundary, enforce least-privilege database accounts, name your columns explicitly, and let schema constraints act as a backstop. Each layer is individually weak; together they make a robust, defence-in-depth data access strategy that holds up under real attack conditions.