How can SQL injection be prevented in databases?

The following approaches are used to prevent SQL injection:

Using parameterized queries (Prepared Statements): This is the main and most effective method. Instead of embedding values directly in the query, placeholders (?) are used, and the values are passed separately. The database distinguishes between the query structure and data, preventing malicious code execution.

// Using a parameterized query
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
    stmt.setString(1, username); // First value for the first '?'
    stmt.setString(2, password); // Second value for the second '?'
    ResultSet rs = stmt.executeQuery();
    // Process results
} catch (SQLException e) {
    e.printStackTrace();
}

Escaping special characters: This is a less preferred method compared to parameterized queries but can be used as an additional measure or in situations where parameterized queries are not applicable (e.g., when forming dynamic table/column names, which should be avoided). Special SQL characters (e.g., ', ", \, %, _) should be escaped so they are treated as literal characters, not as part of SQL syntax.
```
// Example (not recommended for direct use with user input)
// Better to use libraries or frameworks that do this safely.
String userInput = "O'Reilly";
String escapedInput = userInput.replace("'", "''"); // Simple escaping of single quote
String sql = "SELECT * FROM books WHERE author = '" + escapedInput + "'";
```

Validation and filtering of user input: Checking the type, format, and length of user data. Cleaning or rejecting input containing special characters or incorrect format.

Example: If a number is expected, explicitly convert the input to a numeric type.

// User input validation (example)
String idString = request.getParameter("id");
try {
    int userId = Integer.parseInt(idString); // Convert to number
    // Then use userId in a parameterized query
} catch (NumberFormatException e) {
    // Handle incorrect input (e.g., send an error to the user)
    System.err.println("Invalid ID format: " + idString);
}

Using ORM frameworks (Hibernate, JPA): Most modern ORM frameworks by default use parameterized queries for standard operations (CRUD), which automatically protect against SQL injection.

// Example using JPA/Hibernate
EntityManager em = ...;
Query query = em.createQuery("SELECT u FROM User u WHERE u.username = :username", User.class);
query.setParameter("username", username);
User user = (User) query.getSingleResult();

Restrict database account privileges: Each application user account should have the minimum privileges necessary to perform its functions. For example, the account used by the web application should not have rights to delete or modify the structure of tables.

Adhering to these principles, especially using parameterized queries, is key to preventing SQL injection.