Preventing SQL Injection in Web Forms

TL;DR

Database injections happen when attackers sneak malicious code into your web forms to control your database. This guide shows you how to stop them using prepared statements and input validation.

1. Understand the Risk

Imagine a simple login form with username and password fields. An attacker could type something like this into the username field:

' OR '1'='1

If your code doesn’t handle this properly, it might let them bypass security checks and log in without a password! This is because the database interprets this as a valid SQL command.

2. Prepared Statements: Your First Line of Defence

Prepared statements separate the SQL code from the data you’re inserting into it. This prevents attackers from injecting malicious commands.

• How they work: You send the SQL structure to the database first, then provide the data separately. The database knows what’s code and what’s data.
• Example (PHP with PDO):

prepare($sql);
$stmt->bindParam(':username', $_POST['username']);
$stmt->bindParam(':password', $_POST['password']);
$stmt->execute();
?>

Notice how :username and :password are placeholders. The data from the form is passed separately using bindParam().

3. Input Validation: Check What Users Enter

Even with prepared statements, it’s good practice to validate user input. This means checking that the data they enter meets your expectations.

• Whitelisting: Only allow specific characters or formats. For example, if you expect a username to be letters and numbers only, reject anything else.
• Data Type Validation: Make sure numbers are actually numbers, dates are valid dates, etc.
• Length Limits: Set maximum lengths for input fields to prevent overly long submissions that could cause problems.

Example (PHP):

This example uses a regular expression (preg_match()) to check if the username contains only letters and numbers.

4. Escaping: A Last Resort (Use with Caution!)

Escaping converts special characters into safe equivalents. However, it’s not a replacement for prepared statements. It’s best used as an extra layer of defence or when you absolutely can’t use prepared statements.

• Example (PHP):

This example uses htmlspecialchars() to convert special characters like <, > and " into their HTML entities, preventing them from being interpreted as code.

5. Store Passwords Securely

Never store passwords in plain text! Use a strong hashing algorithm (like bcrypt or Argon2) to securely hash passwords before storing them in the database.

• Example (PHP):

This example uses the built-in password_hash() function to hash the password.

6. Principle of Least Privilege

Give your database user only the permissions it needs. Don’t grant full access if it’s not required. This limits the damage an attacker can do even if they manage to inject code.