Preventing SQL Injection Using PreparedStatement in Java

Course: Advanced Java Training

Topic: Preventing SQL Injection Using PreparedStatement

Course Link: Advanced Java Training Course

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to SQL Injection

SQL injection is a security vulnerability that occurs when an application inserts untrusted input directly into an SQL query. An attacker may manipulate the query to access unauthorized records, bypass application checks, modify data, or interfere with database operations.

Java applications commonly connect to relational databases using Java Database Connectivity (JDBC). When developers build SQL statements by concatenating user input, the database may interpret part of that input as SQL code instead of ordinary data.

PreparedStatement is a JDBC interface that supports parameterized SQL statements. It allows developers to define the SQL structure separately from the values supplied by users, helping prevent user input from changing the intended meaning of the query.

2. Learning Objectives

• Understand SQL injection and its security risks.
• Identify unsafe SQL query construction using string concatenation.
• Understand the role of JDBC PreparedStatement.
• Use placeholders and setter methods to bind parameters.
• Implement secure SELECT, INSERT, UPDATE, and DELETE operations.
• Handle SQL exceptions and database resources correctly.
• Apply input validation, least-privilege access, and other complementary security measures.
• Recognize common mistakes and test database code for injection vulnerabilities.

3. What Is SQL Injection?

SQL injection happens when user-controlled input is incorporated into SQL syntax without safe parameterization. This can allow the input to alter the logic of a database query.

Common Causes

• Concatenating user input directly into SQL statements.
• Using ordinary Statement objects with dynamically constructed queries.
• Assuming that removing a few special characters is sufficient protection.
• Building SQL statements from request parameters without separating code from data.
• Giving the application database account more privileges than necessary.

Potential Consequences

• Unauthorized access to confidential records.
• Authentication bypass in applications with vulnerable login queries.
• Unauthorized changes or deletion of database records.
• Exposure of personal or business information.
• Database errors that reveal implementation details.
• Further compromise when database permissions are overly broad.

4. Understanding Unsafe SQL Query Construction

Consider a login application that accepts a username and password. The following example illustrates an unsafe approach because user input is concatenated directly into the SQL string.

String username = request.getParameter("username");
String password = request.getParameter("password");

String sql = "SELECT * FROM users WHERE username = '"
        + username + "' AND password = '" + password + "'";

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);

Why is this unsafe? The application treats user input as part of the SQL statement itself. Specially crafted input may change the query's structure or conditions. The exact result depends on the database, query, and surrounding application logic.

Do not use this approach for queries containing untrusted input. Also, do not attempt to make it safe merely by deleting quotation marks or manually escaping selected characters.

5. What Is PreparedStatement?

PreparedStatement is part of the java.sql package. It represents a parameterized SQL statement whose values are supplied separately through methods such as setString(), setInt(), and setLong().

Instead of inserting a username directly into SQL text, the developer uses a question mark (?) as a parameter placeholder and binds the username separately.

Basic Syntax

String sql = "SELECT * FROM users WHERE username = ?";

PreparedStatement preparedStatement =
        connection.prepareStatement(sql);

preparedStatement.setString(1, username);

ResultSet resultSet = preparedStatement.executeQuery();

The database driver handles the parameter as a value rather than treating its contents as additional SQL syntax. This separation is the primary security benefit of parameterized queries.

6. How PreparedStatement Prevents SQL Injection

PreparedStatement separates the SQL statement structure from the values provided by the application. When a user enters text containing SQL-like characters, those characters remain part of the parameter value rather than becoming executable query logic.

Step-by-Step Process

1. Define the SQL query: Write the SQL statement using placeholders for values.
2. Create the prepared statement: Call connection.prepareStatement().
3. Bind parameters: Supply values through the appropriate setter methods.
4. Execute the statement: Call executeQuery(), executeUpdate(), or another suitable execution method.
5. Process the result: Read returned rows or inspect the affected-row count.
6. Close resources: Use try-with-resources to close JDBC objects reliably.

Unsafe vs. Safe Approach

7. Important PreparedStatement Methods

Parameter indexes start at 1, not 0. Bind values using types compatible with the corresponding database columns.

8. Creating a Secure Database Connection

JDBC uses a Connection object to communicate with a database. The connection should be created using an appropriate JDBC driver and secure configuration.

Example Database Connection Utility

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {
    private static final String URL =
            System.getenv("DB_URL");
    private static final String USER =
            System.getenv("DB_USER");
    private static final String PASSWORD =
            System.getenv("DB_PASSWORD");

    public static Connection getConnection()
            throws SQLException {

        if (URL == null || USER == null || PASSWORD == null) {
            throw new IllegalStateException(
                    "Database configuration is missing.");
        }

        return DriverManager.getConnection(
                URL, USER, PASSWORD);
    }
}

Configure the database URL, username, and password through environment variables or a suitable secrets-management system. Do not hardcode production credentials in source code or commit them to version control. In larger applications, use a managed DataSource and connection pool.

9. Secure SELECT Query Using PreparedStatement

A SELECT statement retrieves data from a database. When a query uses values from a user, bind those values through PreparedStatement.

Example: Searching for a User by Username

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserRepository {
    public boolean userExists(String username)
            throws SQLException {

        String sql =
                "SELECT id FROM users WHERE username = ?";

        try (Connection connection =
                     DatabaseConnection.getConnection();
             PreparedStatement statement =
                     connection.prepareStatement(sql)) {

            statement.setString(1, username);

            try (ResultSet resultSet =
                         statement.executeQuery()) {
                return resultSet.next();
            }
        }
    }
}

Explanation

• The query contains a single placeholder for the username.
• setString(1, username) binds the supplied value to the first placeholder.
• executeQuery() executes the SELECT statement.
• resultSet.next() checks whether a matching row exists.
• Try-with-resources closes the connection, statement, and result set automatically.

For a production application, consider using a connection pool instead of opening a new physical database connection for each operation.

10. Secure INSERT Query Using PreparedStatement

INSERT statements add new records. Each user-supplied value should be bound to its own placeholder.

String sql =
        "INSERT INTO users (username, email) VALUES (?, ?)";

try (Connection connection =
             DatabaseConnection.getConnection();
     PreparedStatement statement =
             connection.prepareStatement(sql)) {

    statement.setString(1, username);
    statement.setString(2, email);

    int rowsInserted = statement.executeUpdate();

    if (rowsInserted == 1) {
        System.out.println("User created successfully.");
    }
}

The placeholders prevent the username and email values from changing the structure of the INSERT statement. The application should also validate required fields, enforce database constraints, and handle duplicate values appropriately.

11. Secure UPDATE Query Using PreparedStatement

UPDATE statements modify existing records. Use parameters for both the values being changed and the conditions identifying the target rows.

String sql =
        "UPDATE users SET email = ? WHERE id = ?";

try (Connection connection =
             DatabaseConnection.getConnection();
     PreparedStatement statement =
             connection.prepareStatement(sql)) {

    statement.setString(1, newEmail);
    statement.setLong(2, userId);

    int rowsUpdated = statement.executeUpdate();

    if (rowsUpdated == 1) {
        System.out.println("User updated successfully.");
    }
}

Binding the user ID prevents it from being interpreted as SQL syntax. However, parameterization alone does not determine whether the current user is allowed to update that record. Check authorization and record ownership before performing sensitive updates.

12. Secure DELETE Query Using PreparedStatement

DELETE statements remove records, so applications should apply parameterization and carefully validate authorization and business rules.

String sql = "DELETE FROM users WHERE id = ?";

try (Connection connection =
             DatabaseConnection.getConnection();
     PreparedStatement statement =
             connection.prepareStatement(sql)) {

    statement.setLong(1, userId);

    int rowsDeleted = statement.executeUpdate();

    if (rowsDeleted == 1) {
        System.out.println("User deleted successfully.");
    } else {
        System.out.println("No matching user was deleted.");
    }
}

Before deleting important data, confirm the user's permissions and consider whether the application requires a confirmation step, a soft-delete strategy, or an audit record.

13. Using Multiple Parameters in a Query

A PreparedStatement can contain multiple placeholders. Each placeholder must be assigned a value using its corresponding one-based index.

String sql =
        "SELECT id, username FROM users "
      + "WHERE status = ? AND created_at >= ?";

try (Connection connection =
             DatabaseConnection.getConnection();
     PreparedStatement statement =
             connection.prepareStatement(sql)) {

    statement.setString(1, status);
    statement.setTimestamp(2, startTimestamp);

    try (ResultSet resultSet =
                 statement.executeQuery()) {

        while (resultSet.next()) {
            long id = resultSet.getLong("id");
            String username =
                    resultSet.getString("username");

            System.out.println(id + ": " + username);
        }
    }
}

Always bind parameters in the order that matches the placeholders in the SQL statement. Using the wrong index or an incompatible data type can cause errors or incorrect results.

14. Handling User Input Safely

PreparedStatement is the primary defense against SQL injection for values in SQL queries. Input validation is an additional layer that helps ensure submitted data matches the application's business requirements.

Recommended Input-Handling Practices

• Validate required fields and maximum lengths.
• Parse numeric values into the appropriate Java types.
• Validate email addresses and other structured fields according to application requirements.
• Use allow-lists for values that must belong to a known set, such as status codes or sort options.
• Do not assume that input validation or filtering alone prevents SQL injection.
• Do not reject all apostrophes or SQL-like characters simply because they appear suspicious; legitimate user data can contain them.
• Bind user-supplied values through PreparedStatement.

Example: Validating a Numeric ID

String rawId = request.getParameter("id");

long userId;

try {
    userId = Long.parseLong(rawId);
} catch (NumberFormatException exception) {
    response.sendError(
            HttpServletResponse.SC_BAD_REQUEST,
            "Invalid user ID");
    return;
}

Parsing the ID ensures that the application receives a valid long integer. The database query should still use a placeholder for the ID, and the application must check whether the requester is permitted to access the corresponding record.

15. Dynamic SQL and Identifiers

PreparedStatement placeholders are designed for parameter values. They generally cannot be used to represent table names, column names, SQL keywords, or sort directions.

For example, this does not safely select a column dynamically:

String sql = "SELECT ? FROM users";

The placeholder is treated as a value expression, not as a column identifier. If the application needs dynamic columns or sort directions, choose them from a strict allow-list of known, trusted SQL fragments.

Example: Safe Allow-Listed Sorting

String requestedSort =
        request.getParameter("sort");

String sortColumn;

if ("username".equals(requestedSort)) {
    sortColumn = "username";
} else if ("created".equals(requestedSort)) {
    sortColumn = "created_at";
} else {
    sortColumn = "id";
}

String sql =
        "SELECT id, username FROM users ORDER BY "
      + sortColumn;

try (Connection connection =
             DatabaseConnection.getConnection();
     PreparedStatement statement =
             connection.prepareStatement(sql);
     ResultSet resultSet = statement.executeQuery()) {

    while (resultSet.next()) {
        System.out.println(
                resultSet.getString("username"));
    }
}

Only predefined column names are concatenated into the SQL. The user cannot supply an arbitrary SQL fragment. If sort direction is also dynamic, allow-list it to the exact supported values, such as ASC or DESC.

16. Common Mistakes When Using PreparedStatement

17. Handling SQL Exceptions

JDBC operations may throw SQLException when a connection fails, a query is invalid, a parameter is incorrect, or another database problem occurs. Handle exceptions carefully without exposing credentials, SQL details, or database internals to end users.

import java.sql.SQLException;

try {
    // Perform a parameterized database operation.
} catch (SQLException exception) {
    // Log details using a secure application logger.
    // Avoid sending raw database details to the browser.
    throw new RuntimeException(
            "The database operation could not be completed.");
}

This is a simplified illustration. In production, use an application's established exception-handling strategy, preserve useful diagnostic information in protected logs, and avoid logging passwords, access tokens, or other secrets.

18. Additional Security Best Practices

• Use parameterized queries consistently: Apply them to every SQL operation involving untrusted values.
• Follow least privilege: Use a database account with only the permissions required by the application.
• Hash passwords properly: Use a dedicated password-hashing algorithm such as Argon2id, bcrypt, or PBKDF2 rather than storing plaintext passwords.
• Separate authentication from query construction: A safe query does not automatically make a login process secure.
• Apply authorization checks: Verify that the user is permitted to access or modify the requested records.
• Validate inputs: Enforce types, lengths, ranges, and business rules.
• Protect secrets: Store database credentials securely and rotate them when necessary.
• Use database constraints: Enforce uniqueness, foreign keys, and other integrity rules in the database.
• Review stored procedures: Stored procedures can still be vulnerable if they construct unsafe dynamic SQL internally.
• Test security: Include negative tests for malicious-looking input and unexpected parameter values.
• Keep dependencies updated: Use supported JDBC drivers and database versions.

19. Practical Exercise

Project: Create a secure Java user-search application using JDBC and PreparedStatement.

Requirements

1. Create a database named training_db.
2. Create a users table with columns for ID, username, email, and status.
3. Connect the Java application to the database using JDBC.
4. Create a search form that accepts a username.
5. Retrieve matching records using a parameterized SELECT query.
6. Implement parameterized INSERT, UPDATE, and DELETE operations.
7. Validate user input and handle invalid values.
8. Use try-with-resources for JDBC objects.
9. Return safe error messages without exposing SQL internals.
10. Test the application with ordinary text, apostrophes, empty input, long input, and SQL-like strings.

Sample Table

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL
);

The exact SQL syntax may vary by database system. Ensure that the selected database supports the data types and constraints used in the example.

20. Interview Questions and Answers

Q1. What is SQL injection?

SQL injection is a vulnerability in which untrusted input alters the structure or meaning of an SQL query, potentially allowing unauthorized database operations.

Q2. What is PreparedStatement in Java?

PreparedStatement is a JDBC interface used to execute parameterized SQL statements. It allows values to be supplied separately from the SQL structure.

Q3. How does PreparedStatement help prevent SQL injection?

It binds user input as parameter values instead of concatenating that input into SQL syntax, helping prevent the input from changing the intended query logic.

Q4. What is the difference between Statement and PreparedStatement?

Statement is commonly used for executing a fixed SQL string, while PreparedStatement supports parameterized SQL and setter methods for safely binding values.

Q5. What does the question mark (?) represent?

It is a parameter placeholder for a value in the SQL statement. Its value is supplied through a setter method such as setString() or setInt().

Q6. Why do parameter indexes start at 1?

JDBC defines the first parameter position as index 1, the second as index 2, and so on.

Q7. Can a PreparedStatement placeholder represent a table name?

No. Placeholders represent values, not table names, column names, or SQL keywords. Dynamic identifiers should be selected from a strict allow-list.

Q8. Is input validation still necessary when using PreparedStatement?

Yes. Parameterization helps prevent SQL injection, while validation ensures that input satisfies application requirements such as valid formats, ranges, and lengths.

Q9. Which method is used for SELECT queries?

executeQuery() is used for queries that return a ResultSet.

Q10. Which method is used for INSERT, UPDATE, and DELETE queries?

executeUpdate() is commonly used for these operations and returns the number of affected rows.

Q11. Does PreparedStatement guarantee complete application security?

No. It is a primary defense against SQL injection in parameterized queries, but applications also need authorization, secure configuration, appropriate error handling, and other security controls.

Q12. Why should database permissions be restricted?

Least-privilege database accounts reduce the potential damage if another vulnerability is exploited.

21. Quick Revision Checklist

• Understand how SQL injection occurs.
• Avoid concatenating untrusted input into SQL strings.
• Use PreparedStatement for parameterized queries.
• Use question-mark placeholders for SQL values.
• Bind every parameter using the correct one-based index and compatible type.
• Use executeQuery() for result-returning queries and executeUpdate() for common data-modification queries.
• Use try-with-resources to close JDBC resources.
• Allow-list dynamic table names, column names, and sort directions.
• Validate inputs and enforce business rules.
• Apply least privilege, secure error handling, and server-side authorization.

22. Conclusion

Preventing SQL injection is a critical responsibility when developing Java applications that communicate with relational databases. By using JDBC PreparedStatement, developers separate SQL structure from user-supplied values and significantly reduce the risk of malicious input changing query logic.

Secure database development also requires proper input validation, careful handling of dynamic SQL identifiers, least-privilege database permissions, resource management, safe error handling, and authorization checks. Combining these practices helps create more reliable and secure Java web applications.

23. Learn More with JustAcademy

Explore the Advanced Java Training course to learn more about JDBC, database connectivity, and secure Java web application development.

Register for a course demo to explore the training, or visit the JustAcademy website for more learning resources.

24. References

• Oracle Java Tutorials: Using Prepared Statements
• Oracle Java API: PreparedStatement
• OWASP SQL Injection Prevention Cheat Sheet