What is SQL injection and how to protect against it?

SQL Injection is a method of attacking a database where an attacker inserts malicious SQL code through input fields, allowing unauthorized SQL queries to be executed.

Consequences of SQL Injection:

• Data theft.
• Deletion or modification of data.
• Gaining administrative access.

Methods of protection against SQL injection

1. Parameterized queries (Prepared Statements):

• Use parameters instead of string concatenation.
• The DBMS automatically escapes special characters.

Example (in Java using JDBC):

MySQL 8.1
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

2. Using ORM (Object-Relational Mapping):

• ORM libraries often include built-in protection mechanisms against SQL injection.

3. Input Validation and Filtering:

• Validate input data to match the expected format.
• Use validation both on the server and client side.

4. Restricting access rights:

• Grant only the minimum necessary privileges to database users.
• Restrict access to system tables and operations.

5. Using stored procedures:

• Encapsulates data logic within a procedure.
• Users have access only to the procedures, not directly to the tables.