Can you explain what SQL Injection is and how it can be used or prevented?

SQL Injection is a vulnerability where an attacker can inject arbitrary SQL code into a database query through user input. This occurs if the application constructs SQL queries by concatenating strings without proper filtering.

Example of SQL Injection:

-- Normal query:
SELECT * FROM users WHERE username = 'user' AND password = 'pass';

-- If the user enters in the username field: ' OR '1'='1
-- The query becomes:
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'pass';
-- This condition is always true, and the attacker can gain access without a password.

How to prevent SQL Injection:

• Use prepared statements with parameterization, where values are supplied separately from the query code.
• Use ORM libraries that automatically escape data.
• Validate and filter user input.
• Restrict database access rights.

In Node.js, using, for example, the mysql2 library, prepared statements look like:

const [rows] = await connection.execute('SELECT * FROM users WHERE username = ? AND password = ?', [username, password]);

This ensures that the input data is not interpreted as part of the SQL code.