SQL Injection: Common Checks & Why They’re Often Useless

TL;DR

Many simple SQL injection checks – like looking for single quotes or using blacklists of keywords – are easily bypassed. They give a false sense of security. Focus on parameterised queries (prepared statements) and input validation instead.

Why Basic Checks Fail

SQL injection happens when attackers insert malicious SQL code into your application’s database queries, often through user inputs like forms or URL parameters. Simple checks try to spot this code before it reaches the database. However, these are usually ineffective because:

• Encoding: Attackers can use different encoding methods (URL encoding, Unicode) to hide their malicious SQL.
• Obfuscation: They can split up keywords or use alternative syntax. For example, instead of ' OR '1'='1 they might try 'OR 1=1--.
• Context-dependent injection: The vulnerability isn’t always in a simple string; it could be within more complex logic.

What Checks Are We Talking About?

These are the types of checks that often give a false sense of security:

• Single Quote Check: Looking for single quotes (') in user input.
• Keyword Blacklist: Blocking keywords like SELECT, UNION, DROP, INSERT etc.
• Regular Expressions: Trying to match patterns of malicious SQL code.

These are easily defeated with even basic techniques.

A Better Approach: Parameterised Queries

Parameterised queries (also known as prepared statements) treat user input as data, not as part of the SQL command itself. This is the most effective way to prevent SQL injection.

1. How they work: You define a query with placeholders for the data, and then separately provide the data values. The database driver handles escaping and quoting correctly.
2. Example (PHP using PDO):

   prepare("SELECT * FROM users WHERE username = ?");
   $stmt->execute([$username]);
   $results = $stmt->fetchAll();
   ?>

3. Example (Python using psycopg2):

   import psycopg2
   conn = psycopg2.connect("host=localhost dbname=mydb user=user password=password")
   cur = conn.cursor()
   username = request.form['username']
   cur.execute("SELECT * FROM users WHERE username = %s", (username,))
   results = cur.fetchall()
   conn.close()

Input Validation

While parameterised queries are the primary defence, input validation adds an extra layer of security.

1. Whitelist: Define what valid input looks like. For example, if you expect a number, only allow digits.
2. Data Type Checks: Ensure inputs match expected types (integer, string, email address etc.).
3. Length Restrictions: Limit the maximum length of inputs to prevent excessively long queries.
4. Example (Python):

   def validate_username(username):
     if not username.isalnum():
       return False
     if len(username) > 50:
       return False
     return True

Don’t Rely on Checks Alone

Simple SQL injection checks are a distraction. They can be bypassed easily and create a false sense of security. Invest your time in parameterised queries, input validation, and regular cyber security assessments.