SQL  

SQL Injection — what it is, how it works, and how to stop it (with examples)

Short summary: SQL Injection (SQLi) is a class of web security vulnerability where an attacker supplies input that gets interpreted as SQL code by your database. It can let attackers read, modify, or delete your data — or take over your database server. The fix is straightforward in most cases: never build SQL by concatenating untrusted input; use parameterized queries (prepared statements), least privilege, and proper input handling.

1. What is SQL Injection?

SQL Injection happens when user-controlled data is incorporated into an SQL statement in an unsafe way, so the input can change the structure of the SQL the server executes. A classic example: building a WHERE clause by concatenating a username string, which allows an attacker to inject OR '1'='1' and bypass authentication. This is a long-standing, common, and dangerous vulnerability.

2. Common attack types (quick overview)

  • In-band (Error-based / Union-based): The attacker gets results directly in the response (e.g., UNION SELECT ...).

  • Inferential (Blind) — Boolean/time-based: No direct data returned; the attacker infers data by observing responses or timing.

  • Out-of-band: Data exfiltration uses a separate channel (less common but possible).

PortSwigger (Web Security Academy) has hands-on labs showing each type in detail.

3. Simple vulnerable example (PHP + MySQL — do not use in production)

Vulnerable code that concatenates user input directly:

// vulnerable.php (do not use)
$username = $_POST['username'];
$password = $_POST['password'];

$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) > 0) {
    // login success
}

If an attacker submits username = ' OR '1'='1 and any password, the resulting SQL becomes:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = ''

'1'='1' is always true, so the query may return rows, and the attacker can bypass authentication. (This pattern and explanation are standard in many web-security guides.)

4. Safe fixes — parameterized queries / prepared statements

The most reliable protection is to separate code from data by using parameterized queries (aka prepared statements). This ensures user input is treated strictly as data.

PHP (PDO) — safe version

// safe.php (use PDO)
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :u AND password = :p');
$stmt->execute([':u' => $username, ':p' => $password_hash]);
$user = $stmt->fetch();

C# (.NET) — safe version (SqlCommand parameters)

using (var cmd = new SqlCommand("SELECT * FROM Users WHERE Username = @u AND PasswordHash = @p", connection))
{
    cmd.Parameters.AddWithValue("@u", username);
    cmd.Parameters.AddWithValue("@p", passwordHash);
    using (var reader = cmd.ExecuteReader()) { ... }
}

Prepared/parameterized queries are explicitly recommended by OWASP and PortSwigger as the primary defense. For ADO.NET, you can also call SqlCommand.Prepare() after adding typed parameters to improve performance for repeated execution.

5. Additional best practices (defense-in-depth)

  1. Use least-privilege DB accounts. The application should use a DB user with only the needed permissions. Even if an attacker runs a query, damage is limited.

  2. Stored procedures with parameterization can help, but only if they don’t themselves concatenate untrusted input into SQL.

  3. Input validation/output encoding. Validate inputs to expected types/ranges. Note: validation alone is not a substitute for parameterized queries.

  4. Use ORM safely. ORMs often parameterize queries, but you can still introduce injection through raw queries or unsafe string building—treat ORM raw queries like any SQL.

  5. Logging & monitoring. Detect unusual queries or spikes in errors; they may signal an active attack.

6. How to test for SQLi (safely)

  • Use automated scanners and security testing tools (e.g., Burp Suite, sqlmap) in authorized testing environments only.

  • Follow OWASP’s Web Security Testing Guide for safe, ethical testing steps. Never test public systems without permission.

7. Short checklist for developers (practical)

  • ✅ Always use parameterized queries / prepared statements.

  • ✅ Don’t concatenate user input into SQL.

  • ✅ Use least-privilege DB users.

  • ✅ Sanitize and validate inputs, but rely on parameterization for security.

  • ✅ Review and test code for SQLi risks (peer review + security testing).

Membership not found