What is an SQL injection and how to prevent it?

SQL injection is a security vulnerability that allows an attacker to inject malicious SQL code into a database query.

This occurs when an application constructs SQL queries using user-provided data directly, without proper sanitization or parameterization. An attacker can manipulate this data to alter the logic of the SQL query, access confidential data, modify or delete it, or even execute operating system commands.

Example of vulnerable code:

// Vulnerable code: string concatenation
String username = request.getParameter("username");
String password = request.getParameter("password");
String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);

If an attacker enters ' OR '1'='1 in the username field and anything in the password field, the query becomes:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '...'

The condition '1'='1' is always true, which can lead to bypassing authentication and gaining access to all records in the users table.

Preventive measures:

1. Using prepared statements: This is the main and most effective method. With prepared statements, query parameters are passed separately from the SQL code itself. The database treats parameters as data, not as part of the SQL command.

   // Safe code: using Prepared Statements
   String username = request.getParameter("username");
   String password = request.getParameter("password");
   String query = "SELECT * FROM users WHERE username = ? AND password = ?";
   PreparedStatement pstmt = connection.prepareStatement(query);
   pstmt.setString(1, username); // Set username parameter
   pstmt.setString(2, password); // Set password parameter
   ResultSet rs = pstmt.executeQuery();
   

2. Restrict database user permissions: Grant the application account only the minimum necessary rights to work with data. For example, do not grant rights to delete tables or modify the database schema unless required.

3. Input validation and sanitization: While using prepared statements is the primary protection, additional input validation (e.g., data type checks, allowed characters, length) can provide an extra layer of security and protect against other types of attacks.

4. Using ORM frameworks: Frameworks like Hibernate, JPA, MyBatis often provide built-in protection against SQL injection by using parameterized queries under the hood. However, it is important to understand how to use them correctly to avoid bypassing this protection.

5. Avoid string concatenation: Never construct SQL queries by directly concatenating input data with parts of the SQL command.

Comparison of concatenation and prepared statements:

Method	Description	SQL Injection Safety	Notes
String concatenation	Builds SQL query by concatenating strings with user data.	Low	Dangerous, prone to injection.
Prepared statements	Separates SQL code from parameters. The database treats parameters as data.	High	Recommended and safe approach.

Implementing secure database practices, primarily using prepared statements, is critically important for protecting applications from this widespread and dangerous vulnerability.