SQLi Fundamentals

Web applications frequently interact with databases using SQL, making them susceptible to SQL Injection (SQLi) attacks when input validation and query construction are improperly handled. This section explores fundamental SQL syntax, injection vectors, and database interaction patterns to help identify and mitigate SQLi vulnerabilities.

---

SQL Syntax Fundamentals

SQL (Structured Query Language) is used to manage relational databases. Common statements include:
- SELECT: Retrieve data (e.g., SELECT * FROM users).
- INSERT: Add new records (e.g., INSERT INTO users (username) VALUES ('john')).
- UPDATE: Modify existing records (e.g., UPDATE users SET password = '123' WHERE id = 1).
- DELETE: Remove records (e.g., DELETE FROM users WHERE id = 1).

Attackers exploit SQL syntax to manipulate queries. For example, appending a semicolon (;) allows execution of multiple statements:

SELECT * FROM users; DROP TABLE users;

---

SQL Injection Vectors

SQLi occurs when untrusted input is directly concatenated into SQL queries. Common vectors include:
1. Input fields: Text boxes, search bars, or form fields.

GET /search?query=1' OR '1'='1 HTTP/1.1

This could trigger:

SELECT * FROM products WHERE name = '1' OR '1'='1';

Resulting in unintended data exposure.

1. API parameters: Malformed JSON or URL-encoded inputs.
   

   POST /api/users HTTP/1.1
   Content-Type: application/json
   
   {"username": "admin'--", "password": "anything"}
   

   The -- comment terminator could bypass authentication checks.

2. Cookie values: Malicious data in HTTP headers.
   

   Cookie: session=abc; admin=1
   

   If the backend uses session without validation, this could escalate privileges.

---

Parameterized Queries vs. String Concatenation

Parameterized queries (also called prepared statements) separate SQL logic from data, preventing injection:

# Secure: Using parameterized queries (Python/SQLite)
cursor.execute("SELECT * FROM users WHERE username = ?", (username,))

String concatenation is inherently risky:

# Vulnerable: Directly appending user input
query = f"SELECT * FROM users WHERE username = '{username}'"
cursor.execute(query)

Always use parameterized queries or ORM tools with proper escaping mechanisms.

---

Common Database Interaction Patterns

1. ORM frameworks (e.g., SQLAlchemy, Hibernate):
2. Risk: Improper use of raw SQL or string formatting.

3. Mitigation: Leverage ORM's built-in query builders.

4. Stored procedures:

5. Can reduce exposure if inputs are validated and sanitized.

6. Example:
   

   CREATE PROCEDURE GetUser (@username NVARCHAR(50))  
   AS  
   BEGIN  
       SELECT * FROM users WHERE username = @username;  
   END
   

7. Blind SQLi:

8. Used when direct output is filtered. Attackers infer results via time delays or boolean responses:
   

   GET /login?username=admin' AND SLEEP(5) -- HTTP/1.1
   

9. Tools like sqlmap automate detection of such patterns.

---

Key takeaways

• Understand SQL syntax to recognize injection opportunities.
• Always use parameterized queries or ORM tools to separate logic from data.
• Identify injection vectors in input fields, API parameters, and headers.
• Leverage tools like sqlmap to detect blind SQLi and test mitigation strategies.
• Validate and sanitize all user inputs, even when using ORM frameworks.