Skip to main content
StartPart 1BasicsPart 2Data StructuresPart 3Advanced PythonPart 4AI & ML

Inserting Records & Preventing SQL Injection

Once your table is created, you can add records using SQL INSERT INTO queries.

However, how you insert variables into your SQL query determines whether your application is secure and production-ready, or vulnerable to devastating hacker attacks.

1. Single Record Insertion (cursor.execute())​

❌ The Dangerous Anti-Pattern (SQL Injection Bug)​

Never use Python f-strings or string concatenation (+) to insert user input into SQL queries:

# 🚨 NEVER DO THIS IN PRODUCTION:
# cursor.execute(f"INSERT INTO users (name) VALUES ('{user_input}')")
# If user enters: "Sai'); DROP TABLE users; --" your entire database gets deleted!

✅ The Secure Way: Parameterized Queries (? Placeholders)​

Always pass values as a tuple of parameters using question mark ? placeholders. SQLite handles safe escaping automatically:

import sqlite3

conn = sqlite3.connect("university.db")
cursor = conn.cursor()

# Safe parameterized insertion
insert_query = """
INSERT INTO students (name, email, gpa)
VALUES (?, ?, ?)
"""

student_data = ("Rahul Sharma", "rahul@gmail.com", 8.75)
cursor.execute(insert_query, student_data)

# ⚠️ CRITICAL: Must commit, otherwise changes disappear when program closes!
conn.commit()

print(f"✅ Record inserted with ID: {cursor.lastrowid}")
conn.close()

2. Bulk Insertion with cursor.executemany()​

If you need to insert 500 records from a CSV file or list, calling cursor.execute() inside a for loop is slow because it creates 500 separate disk write operations.

Use cursor.executemany() to insert all 500 records in a single high-speed database transaction:

import sqlite3

students_list = [
    ("Priya Rao", "priya@gmail.com", 9.1),
    ("Sai Kumar", "sai@thinkittelugu.in", 9.4),
    ("Ananya Sen", "ananya@gmail.com", 8.2)
]

with sqlite3.connect("university.db") as conn:
    cursor = conn.cursor()
    
    insert_sql = "INSERT INTO students (name, email, gpa) VALUES (?, ?, ?)"
    
    # Executes all inserts in one batch
    cursor.executemany(insert_sql, students_list)
    print(f"✅ Batch insert complete! Rows added: {cursor.rowcount}")

3. Why conn.commit() is Mandatory​

SQLite uses ACID Transactions. When you insert data, SQLite keeps changes in a temporary staging area in RAM.

Calling conn.commit() writes those staged changes permanently to the .db file on your hard disk.

Quick Summary​

  • Parameterized Queries (?): Always pass query variables as tuples to prevent malicious SQL Injection vulnerabilities.
  • cursor.executemany(sql, list_of_tuples): Batch inserts thousands of records in a single high-performance operation.
  • ACID Transactions (conn.commit()): Saves in-memory staging updates permanently to disk.

What's Next?​

Let's explore how to query rows, filter by conditions, sort, and iterate through result sets in Fetching Data with SELECT!

Powered by Think IT Telugu