Database Security & SQL Injection Prevention

SQL Injection

SQL Injection (SQLi) is a security vulnerability in which an attacker inserts or “injects” malicious SQL statements into a query to manipulate or access a database unlawfully.

It targets applications that dynamically build SQL queries using unvalidated user inputs.

Example

Vulnerable Code:

// User input: username=admin' --  and password=anything
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";

Here, the injected input admin' -- comments out the rest of the query, allowing unauthorized login.

Resulting Query:

SELECT * FROM users WHERE username = 'admin' -- ' AND password = 'anything';

The attacker logs in without knowing the real password.

---

Types of SQL Injection

Type	Description
Classic SQL Injection	Inject malicious queries to manipulate database logic.
Blind SQL Injection	Attacker infers data from the application’s responses (true/false).
Union-based SQL Injection	Uses the UNION keyword to extract data from other tables.
Error-based SQL Injection	Exploits error messages to reveal database structure.
Time-based Blind SQL Injection	Uses delays (like SLEEP()) to detect vulnerabilities indirectly.

---

Preventive Measures

1. Input Validation: Always validate and sanitize user inputs.

2. Parameterized Queries (Prepared Statements):

   $stmt = $conn->prepare("SELECT * FROM users WHERE username=? AND password=?");
   $stmt->bind_param("ss", $username, $password);
   $stmt->execute();

   Prevents query manipulation.

3. Stored Procedures: Encapsulate SQL logic within the database to avoid dynamic queries.

4. Least Privilege Principle: Give minimal permissions to application-level database users.

5. Use ORM Frameworks: Frameworks like Sequelize, Hibernate, or Prisma automatically handle query sanitization.

6. Error Handling: Avoid exposing SQL error messages to users.

---

Consequences of SQL Injection

• Unauthorized data access or modification.
• Data theft or deletion.
• Compromise of entire database server.
• Loss of business integrity and trust.

---

Summary Table

Concept	Definition	Key Focus	Tools/Methods
Data Warehousing	Centralized data storage for analytics	Data integration and OLAP	ETL, Star Schema
Data Mining	Discovering patterns and insights from data	Knowledge discovery	Clustering, Classification
SQL Injection	Attacking via malicious SQL input	Database security	Input validation, Prepared statements