Understanding SQL Injection with SQLite: Real‑World Demo and Prevention Techniques

SQL injection is a common database attack where malicious users embed SQL keywords in input fields, causing the database to execute unintended commands. The article begins with a humorous cartoon illustration and notes that many students have experienced poor network environments and insecure database handling.

What Is SQL Injection?

SQL injection occurs when an attacker supplies data containing SQL statements (e.g., SELECT, DROP) that the database interprets as executable code, effectively allowing the attacker to manipulate or destroy data.

Demo: Creating a Student Table with SQLite

The article walks through a complete Python/SQLite example:

import sqlite3
conn = sqlite3.connect('test.db')
conn.executescript('''DROP TABLE IF EXISTS students;
       CREATE TABLE students
       (id INTEGER PRIMARY KEY AUTOINCREMENT,
       name TEXT NOT NULL);''')
students = ['Paul','Tom','Tracy','Lily']
for name in students:
    query = "INSERT INTO students (name) VALUES ('%s')" % (name)
    conn.executescript(query)
cursor = conn.execute("SELECT id, name from students")
print('IDName')
for row in cursor:
    print('{0}{1}'.format(row[0], row[1]))
conn.close()

This script creates a students table, inserts four records, and prints them.

Injecting Malicious Data

To illustrate the vulnerability, the article inserts a crafted string "Robert');DROP TABLE students;--" using the same executescript method:

name = "Robert');DROP TABLE students;--"
query = "INSERT INTO students (name) VALUES ('%s')" % (name)
conn.executescript(query)

When executed, the database interprets the input as two statements: an INSERT followed by DROP TABLE students, resulting in the table being deleted and subsequent queries failing.

Why Simple Keyword Filtering Fails

Blocking specific keywords is insufficient because attackers can use variations, different languages, or obfuscation. Moreover, legitimate data may contain those words.

Defensive Strategies

Avoid predictable table and column names to make automated attacks harder.

Validate input with regular expressions to restrict characters such as quotes and semicolons.

Use parameterized queries (prepared statements) so that data and code are separated. Example with SQLite:

name = "Robert');DROP TABLE students;--"
query = "INSERT INTO students (name) VALUES (?)"
conn.execute(query, [name])

In other languages (e.g., PHP) , employ functions like mysqli_real_escape_string or prepared statements to escape input.

Regular backups and encryption of sensitive data to mitigate damage if an injection succeeds.

By combining these measures—parameterized statements, input validation, and secure database design—developers can effectively prevent SQL injection attacks.