SQL Processing

SQL Injection exploits applications that directly embed user-supplied data into SQL statements without proper validation. Attackers craft malicious input to alter the structure of the SQL query, allowing unintended database operations.

Example:

If a user submits the username admin'-- and leaves the password empty, the resulting SQL query becomes:

Here, -- is a comment delimiter in SQL. The query ignores the password check, logging the attacker in as "admin" without needing the password. This bypasses authentication because the attacker’s input breaks out of the data context and modifies the query logic.

1. Parameterized Queries (Prepared Statements):

• How it works: Separate the SQL command structure from user input. The query is predefined with placeholders (e.g., ?), and user input is treated as data, not executable code.

• Example:

This ensures user input (e.g., oid) cannot alter the query’s structure.

2. Defense in Depth:

• Least Privilege: Use low-privilege database accounts for the application (e.g., avoid DBA permissions).

• Disable Unnecessary Functions: Remove default features (e.g., unused stored procedures) to reduce attack surfaces.

• Apply Security Patches: Regularly update the database software to fix known vulnerabilities.

3. Partially Effective Measures:

• Input Sanitization: Escape special characters (e.g., doubling single quotes: ' → '').

• Stored Procedures: Use them cautiously; they prevent injection only if parameters are properly validated or parameterized. It uses predefined procedures (method) stored on the database site.

• Whitelisting: Reject inputs not matching a predefined list of safe values (used when parameterization isn’t feasible).

Key Takeaway: Parameterized queries are the most robust defense, while other measures provide supplementary layers of security.