A CSV import can look like a short route: accept a file, split it on commas, and insert the values. That breaks as soon as a field contains a quoted comma or newline, and it leaves your app exposed to oversized files, malformed records, invalid values, and partial database writes.
For a small web application, treat import as a bounded pipeline: limit the upload, parse CSV with a real parser, validate a known schema, report record-level errors, and write nothing unless the file passes validation. Also plan for the moment when imported text is later exported and opened in a spreadsheet.
Define the import contract
Before accepting a file, decide what the app will accept:
- The exact column names and whether their order matters.
- The encoding and delimiter the app supports.
- Required and optional values, maximum lengths, and type-specific rules.
- The maximum file size, number of records, and field size.
- What happens when a record already exists: reject, skip with a report, or update.
For a narrow import, a fixed schema is safer and easier to explain than guessing what a file means. Map accepted columns to known application fields; never turn uploaded header names into SQL column names. If you support multiple formats, make each one an explicit import option.
RFC 4180 documents a commonly used CSV format, but it is informational rather than a universal standard. Fields can contain commas, quotes, and line breaks when quoted, and applications differ in how they produce and interpret CSV. That is why splitting each line on a comma is not a CSV parser.
Bound and store the upload safely
Apply a request-body limit at the web server or framework, and count bytes as the request is received. Do not rely on the browser's Content-Length, filename, extension, or Content-Type as proof that a file is safe or small. Require the appropriate authentication and CSRF protection, and apply per-user quotas if imports consume meaningful storage or processing time.
If you need to keep the file while validating it, stream it to a private temporary location using a server-generated name. Keep it outside the public web root, restrict access, and delete it after success, rejection, or expiration. The OWASP File Upload Cheat Sheet recommends size limits, authorization, safe storage, and defense in depth; checking a .csv extension alone is not enough.
Choose limits based on your hosting and the data you expect. For example, a small contact import might allow a 5 MB upload, 10,000 data records, and 64 KB per field. Those are illustrative limits, not universal defaults. Make the proxy, framework, parser, and application agree on the maximum, and account for concurrent uploads.
For a small bounded file, it is reasonable to keep validated records in memory until the import decision is made. For larger files, stream records into a private staging table and cap both input and staging growth.
Parse records and validate the schema
Python's standard csv module handles CSV quoting and record boundaries. Open a staged UTF-8 file with newline=""; utf-8-sig also accepts a UTF-8 byte-order mark. This example imports contacts with two required columns:
import csv
EXPECTED_HEADERS = {"email", "display_name"}
MAX_ROWS = 10_000
MAX_ERRORS = 100
# Configure once at application startup; this setting is process-wide.
csv.field_size_limit(64 * 1024)
def parse_contacts(source):
reader = csv.reader(source, strict=True)
try:
raw_headers = next(reader)
except StopIteration:
return [], ["CSV must include a header row."]
headers = [header.strip().casefold() for header in raw_headers]
if len(set(headers)) != len(headers):
return [], ["CSV contains duplicate column names."]
if set(headers) != EXPECTED_HEADERS:
return [], ["Expected exactly these columns: display_name and email."]
positions = {header: index for index, header in enumerate(headers)}
valid_rows = []
errors = []
data_records = 0
for fields in reader:
if not fields:
continue
data_records += 1
record_number = data_records + 1 # Record 1 is the header.
if data_records > MAX_ROWS:
return [], [f"CSV exceeds the {MAX_ROWS}-record limit."]
if len(fields) != len(headers):
errors.append(f"Record {record_number}: field count does not match the header.")
else:
email = fields[positions["email"]].strip()
display_name = fields[positions["display_name"]].strip()
problems = []
if not email or len(email) > 254:
problems.append("email is required and must be at most 254 characters")
if not display_name or len(display_name) > 120:
problems.append("display_name is required and must be at most 120 characters")
if problems:
errors.append(f"Record {record_number}: " + "; ".join(problems) + ".")
else:
valid_rows.append((email, display_name))
if len(errors) >= MAX_ERRORS:
errors.append(f"Stopped after {MAX_ERRORS} errors; correct these and upload again.")
break
return ([], errors) if errors else (valid_rows, [])
Use the parser with a text stream opened from the bounded temporary file:
with open(staged_path, encoding="utf-8-sig", newline="") as source:
records, errors = parse_contacts(source)
The field checks above are examples, not a complete email-address validator. Replace them with the rules your application actually enforces. Python's CSV reader returns strings; parse and validate types explicitly rather than assuming a value that looks numeric is valid. strict=True makes malformed CSV raise csv.Error, and invalid UTF-8 raises UnicodeDecodeError; handle those known failures at the request boundary with a clear file-format message. Log enough to diagnose the failure without logging the entire upload or exposing personal data.
The record number in the errors is a CSV record number, not necessarily a physical line number: quoted fields can span lines. Keep errors bounded, avoid echoing complete user-supplied values, and show a small preview before the user confirms an import. A preview is useful feedback, not a substitute for server-side validation.
Write all records or none
Do not insert records inside the parsing loop. Finish parsing and validation first; if any record is invalid, reject the import and return the bounded error list. This all-or-nothing behavior is usually easier for a small product's users to understand than silently importing only some rows.
For a small file, write valid records in one database transaction using parameterized values. With Python's SQLite driver, the connection context commits on success and rolls back if a write raises an exception:
import sqlite3
def save_contacts(records):
connection = sqlite3.connect("app.db")
try:
with connection:
connection.executemany(
"INSERT INTO contacts (email, display_name) VALUES (?, ?)",
records,
)
finally:
connection.close()
The Python sqlite3 documentation describes this transaction behavior and notes that the connection context manager does not close the connection. For another database or framework, use its transaction facility and parameter binding. Define how duplicates and database constraint failures are reported; enforce important uniqueness rules in the database, where concurrent requests cannot bypass them.
For larger imports, keep upload, validation, and application as separate states. Store a durable import ID and status, validate into a staging area, then apply the accepted data in a transaction appropriate for your database. If the task takes longer than a useful request, a worker can process it and expose progress; see the guide to background jobs and queues in small web apps. If the upload itself is too large or unreliable for one request, use the separate resumable file upload workflow.
Protect later spreadsheet exports
Importing a formula-looking value as text does not execute it in the CSV parser. The risk appears later if that value is exported and a spreadsheet program interprets it as a formula. CSV quoting protects commas, quotes, and line breaks; it does not, by itself, make a cell safe to open in a spreadsheet.
OWASP's CSV Injection guidance identifies formula-triggering prefixes including =, +, -, and @, as well as tabs, carriage returns, line feeds, and full-width variants that can matter in some locales. Delimiters and quotes also matter because an attacker may try to move text into a new cell.
There is no universal CSV sanitization that is safe for every spreadsheet and downstream importer. OWASP documents prefixing formula-starting cells with a tab inside a quoted field as an Excel-oriented mitigation, but the tab changes the underlying value and common escaping approaches may stop working after a file is saved and reopened in Excel. If you produce a spreadsheet-facing CSV, apply a documented policy to untrusted text fields, quote fields correctly with a CSV writer, and test the actual supported spreadsheet applications. Handle control characters and full-width formula characters deliberately; do not assume one prefix rule covers every client. Keep validated numeric fields typed as numbers rather than modifying every value that begins with a minus sign.
If clients need to re-import the data programmatically, provide a separate machine-readable export or use a spreadsheet format that can explicitly store untrusted values as text. Do not treat a spreadsheet-oriented CSV as an unchanged copy of the database.
A practical checklist
Before releasing an import feature, confirm that it:
- Authenticates and authorizes the importer and applies the relevant CSRF protection.
- Enforces byte, record, and field limits on the server.
- Stores temporary uploads privately and cleans them up.
- Uses a CSV parser, a defined encoding, and an explicit schema.
- Validates each field with the same rules the application uses elsewhere.
- Returns a bounded, actionable error report without exposing full row contents.
- Applies accepted records transactionally with parameterized database writes.
- Defines duplicate behavior and makes retries safe.
- Has a separate, tested policy for exporting untrusted text to spreadsheets.
Start with a fixed schema and conservative limits. Add format guessing, partial imports, or background processing only when users need those behaviors and you can explain their consequences.