SQL Injection: Preventing Query Substitution

TL;DR

Attackers might try to change your database queries (inject their own) if you don’t handle user input carefully. This guide shows how to protect against this using prepared statements and input validation.

What is Query Substitution?

Query substitution, often called SQL injection, happens when an attacker inserts malicious SQL code into your application through user inputs (like forms or search boxes). If your application doesn’t properly sanitise these inputs, the injected code can be executed against your database.

How to Prevent Query Substitution

1. Use Prepared Statements (Parameterized Queries)
• Prepared statements separate the SQL code from the data. This means user input is treated as *data* and not part of the SQL command itself, preventing injection.
• Most database libraries support prepared statements. Here’s an example in Python using sqlite3:

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Bad - vulnerable to SQL injection
# user_input = input("Enter username:")
# query = "SELECT * FROM users WHERE username = '" + user_input + "'"
# cursor.execute(query)

# Good - using a prepared statement
user_input = input("Enter username:")
query = "SELECT * FROM users WHERE username = ?"
cursor.execute(query, (user_input,)) # Pass user input as a tuple

results = cursor.fetchall()
for row in results:
  print(row)
conn.close()

• The ? is a placeholder for the user input. The database library handles escaping and quoting correctly.
2. Input Validation
• Even with prepared statements, it’s good practice to validate user inputs. This means checking that the input meets your expected format and length.
• For example, if you expect a number, check that the input is actually a number before using it in a query.

def validate_number(input_string):
  try:
    int(input_string)
    return True
  except ValueError:
    return False

user_age = input("Enter your age:")
if validate_number(user_age):
  # Use the validated age in a prepared statement
  query = "SELECT * FROM users WHERE age = ?"
  cursor.execute(query, (int(user_age),))
else:
  print("Invalid input. Please enter a number.")

3. Least Privilege Principle
• Grant database users only the minimum necessary permissions. If an attacker does manage to inject code, they’ll be limited by the user’s privileges.
4. Web Application Firewalls (WAFs)
• A WAF can help detect and block common SQL injection attacks before they reach your application.
5. Regular Security Audits & Penetration Testing
• Regularly review your code for vulnerabilities and conduct penetration testing to identify potential weaknesses.

Important Considerations

• Never trust user input directly in SQL queries. Always use prepared statements or carefully sanitise the input.
• Understand the specific escaping rules for your database system. Different databases may have different requirements.
• Keep your database library up to date. Updates often include security fixes.