How SQL Injection Works and How to Prevent It with Safe SQLite Code

What Is SQL Injection?

SQL injection is a common database attack where an attacker inserts malicious SQL keywords into input fields, causing the database engine to execute unintended commands. Because SQL statements combine code and data, untrusted input that contains keywords like SELECT or DROP TABLE can be executed as part of the query.

SQLite Example – Vulnerable Code

import sqlite3
# Connect to database
conn = sqlite3.connect('test.db')
# Create a simple table
conn.executescript('''
DROP TABLE IF EXISTS students;
CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);
''')
# Insert normal records
students = ['Paul', 'Tom', 'Tracy', 'Lily']
for name in students:
    query = "INSERT INTO students (name) VALUES ('%s')" % (name)
    conn.executescript(query)
# Show current rows
cursor = conn.execute("SELECT id, name FROM students")
print('IDName')
for row in cursor:
    print('{0}{1}'.format(row[0], row[1]))
conn.close()

Demonstrating the Attack

conn = sqlite3.connect('test.db')
# Insert a malicious string
name = "Robert');DROP TABLE students;--"
query = "INSERT INTO students (name) VALUES ('%s')" % (name)
conn.executescript(query)
# Try to read the table
cursor = conn.execute("SELECT id, name FROM students")
print('IDName')
for row in cursor:
    print('{0}{1}'.format(row[0], row[1]))
conn.close()

Why the Attack Succeeds

The injected value contains the keyword DROP TABLE. The preceding '); terminates the original INSERT statement, so the database interprets the remainder as a separate command that drops the students table. After the table is removed, subsequent queries fail because the table no longer exists.

How to Prevent SQL Injection

Avoid predictable table and column names; obscure schemas make it harder for attackers to guess structure.

Validate input with regular expressions, limiting characters such as quotes, semicolons, and other special symbols.

Use parameterized (prepared) statements so that data and SQL code are sent separately, e.g., INSERT INTO students (name) VALUES (?).

In languages like PHP, escape special characters with functions such as mysql_real_escape_string when parameterized queries are not available.

Maintain regular backups and encrypt sensitive data; backups allow recovery if an injection succeeds.

Safe SQLite Example Using Parameters

conn = sqlite3.connect('test.db')
name = "Robert');DROP TABLE students;--"
query = "INSERT INTO students (name) VALUES (?)"
conn.execute(query, [name])
# Verify the table still exists
cursor = conn.execute("SELECT id, name FROM students")
print('IDName')
for row in cursor:
    print('{0}{1}'.format(row[0], row[1]))
conn.close()

For PHP developers, functions like mysql_real_escape_string can also be used to escape dangerous characters, but parameterized queries remain the most reliable defense.

Source: https://jizhi.im/blog/post/sql_injection_intro