Converting CSV to SQL INSERT Statements the Safe Way
Generate INSERT statements from a CSV without breaking on O'Neil or running a DROP TABLE hidden in a value. Batches of 1,000 loaded 193,708 rows in 1.6 s.
A file of INSERT statements is the most portable way to ship a small table: any database client can run it, you can read it in a code review and a diff shows exactly which row changed. It is also the output most often produced by code that should not have produced it. A script that builds SQL by gluing strings together fails on the first apostrophe, and in the worst case executes whatever someone typed into a CSV field. This article generates INSERT statements correctly, tests the quoting rules of two engines, measures how much slower statements are than COPY, and finds a quiet difference between the two in how they treat empty values.
The format-string version
The version everyone writes first:
import csv
SRC = "/batches/csv-0001-0200/lab/data/conv/people.csv"
rows = list(csv.DictReader(open(SRC, newline="", encoding="utf-8")))
print("1. A format string builds the statement")
r = rows[1]
sql = f"INSERT INTO people (id, name, note) VALUES ({r['id']}, '{r['name']}', '{r['note']}');"
print(sql)
print("\n2. The same row with the quote doubled")
q = lambda s: "'" + s.replace("'", "''") + "'"
print(f"INSERT INTO people (id, name, note) VALUES ({r['id']}, {q(r['name'])}, {q(r['note'])});")
print("\n3. A value that is not data")
evil = {"id": "99", "name": "x', 'n'); DROP TABLE victim; --", "note": "n"}
print(f"INSERT INTO people (id, name, note) VALUES ({evil['id']}, '{evil['name']}', '{evil['note']}');")
print(f"INSERT INTO people (id, name, note) VALUES ({evil['id']}, {q(evil['name'])}, {q(evil['note'])});")python lab/conv/t0152.py1. A format string builds the statement
INSERT INTO people (id, name, note) VALUES (2, 'O'Neil, Pat', 'has, a comma');
2. The same row with the quote doubled
INSERT INTO people (id, name, note) VALUES (2, 'O''Neil, Pat', 'has, a comma');
3. A value that is not data
INSERT INTO people (id, name, note) VALUES (99, 'x', 'n'); DROP TABLE victim; --', 'n');
INSERT INTO people (id, name, note) VALUES (99, 'x'', ''n''); DROP TABLE victim; --', 'n');The first statement is VALUES (2, 'O'Neil, Pat', 'has, a comma'), which is not valid SQL. Running it:
psql -q <<'SQL' 2>&1 | grep -v NOTICE
DROP TABLE IF EXISTS people;
CREATE TABLE people (id int, name text, note text);
INSERT INTO people (id, name, note) VALUES (2, 'O'Neil, Pat', 'has, a comma');
SELECT count(*) AS rows_loaded FROM people;
SQLERROR: syntax error at or near "Neil"
LINE 1: INSERT INTO people (id, name, note) VALUES (2, 'O'Neil, Pat'...
^PostgreSQL stopped with syntax error at or near "Neil". That is the lucky failure, because it is loud. The unlucky one is a value that is valid SQL. The third block of the Python output builds a statement from a name that was typed as x', 'n'); DROP TABLE victim; --:
psql -q <<'SQL' 2>&1 | grep -v NOTICE
DROP TABLE IF EXISTS people, victim;
CREATE TABLE people (id int, name text, note text);
CREATE TABLE victim (x int);
INSERT INTO people (id, name, note) VALUES (99, 'x', 'n'); DROP TABLE victim; --', 'n');
SELECT id, name FROM people;
SELECT to_regclass('victim') AS victim_table_after;
SQL id | name
----+------
99 | x
(1 row)
victim_table_after
--------------------
(1 row)The insert worked (id 99, name x), and then the statement the value had smuggled in ran as well: victim_table_after is empty, the table is gone. This is SQL injection, and a CSV from a partner, a form export or a scrape is as good a source as a web form. The generator is the attack surface.
With the single quote doubled, the same values are plain data:
psql -q <<'SQL' 2>&1 | grep -v NOTICE
DROP TABLE IF EXISTS people, victim;
CREATE TABLE people (id int, name text, note text);
CREATE TABLE victim (x int);
INSERT INTO people (id, name, note) VALUES (2, 'O''Neil, Pat', 'has, a comma');
INSERT INTO people (id, name, note) VALUES (99, 'x'', ''n''); DROP TABLE victim; --', 'n');
SELECT id, name FROM people ORDER BY id;
SELECT to_regclass('victim') AS victim_table_after;
SQL id | name
----+---------------------------------
2 | O'Neil, Pat
99 | x', 'n'); DROP TABLE victim; --
(2 rows)
victim_table_after
--------------------
victim
(1 row)Both rows were inserted, the second with the name x', 'n'); DROP TABLE victim; -- stored as text, and victim_table_after shows the table still exists. Doubling the quote is the rule in standard SQL, and INSERT statements built this way are safe for PostgreSQL's default settings.
Quoting rules belong to the engine
"Double the single quote" is not the whole rule, because engines disagree about the backslash. In PostgreSQL with standard_conforming_strings on, which is the default, a backslash in a string is just a backslash. In MySQL by default, a backslash escapes the next character:
M="mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4"
$M 2>&1 <<'SQL' | grep -v Warning
DROP TABLE IF EXISTS bs;
CREATE TABLE bs (v varchar(40));
INSERT INTO bs VALUES ('C:\');
SQL
echo "--- the backslash doubled, which is MySQL's own rule"
$M 2>&1 <<'SQL' | grep -v Warning
INSERT INTO bs VALUES ('C:\\');
SELECT v, length(v) AS chars FROM bs;
SQL
echo "--- with NO_BACKSLASH_ESCAPES, the PostgreSQL-style literal works"
$M 2>&1 <<'SQL' | grep -v Warning
SET SESSION sql_mode = CONCAT(@@sql_mode, ',NO_BACKSLASH_ESCAPES');
INSERT INTO bs VALUES ('D:\');
SELECT v, length(v) AS chars FROM bs ORDER BY v;
SQLERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''C:\')' at line 1
--- the backslash doubled, which is MySQL's own rule
v chars
C:\\ 3
--- with NO_BACKSLASH_ESCAPES, the PostgreSQL-style literal works
v chars
C:\\ 3
D:\\ 3The value C:\ written PostgreSQL-style as 'C:\' failed in MySQL with ERROR 1064: the backslash escaped the closing quote, so the string never ended. Written MySQL-style as 'C:\\', it stored C:\, three characters (the client prints a backslash as \\ in batch output). With NO_BACKSLASH_ESCAPES added to the session's sql_mode, the PostgreSQL-style literal worked, which shows the problem is the engine's mode and not the value. A generator has to target an engine and, for MySQL, a mode. The MySQL manual on string literals lists the escape sequences, and the PostgreSQL manual describes its string constants. If you can, do not generate literals at all: use the database driver's parameter binding, which quotes for the connection it is talking to. I did not benchmark that route here.
A generator
The version I use for seed files and small loads:
"""CSV to SQL INSERT statements: quoting by rule, NULL for empty numerics, optional multi-row batches."""
import csv, os, sys
TABLE = "cust_ins"
NUMERIC = {"id", "balance", "units"}
def literal(col, v):
if v == "":
return "NULL" if (col in NUMERIC or os.environ.get("EMPTY_AS_NULL")) else "''"
if col in NUMERIC:
float(v) # raises on 12x instead of writing broken SQL
return v
return "'" + v.replace("'", "''") + "'"
def statements(path, batch):
with open(path, newline="", encoding="utf-8") as f:
r = csv.reader(f)
head = next(r)
cols = ", ".join(head)
buf = []
for row in r:
buf.append("(" + ", ".join(literal(c, v) for c, v in zip(head, row)) + ")")
if len(buf) == batch:
yield f"INSERT INTO {TABLE} ({cols}) VALUES\n" + ",\n".join(buf) + ";\n"
buf = []
if buf:
yield f"INSERT INTO {TABLE} ({cols}) VALUES\n" + ",\n".join(buf) + ";\n"
if __name__ == "__main__":
src, out, batch = sys.argv[1], sys.argv[2], int(sys.argv[3])
limit = int(sys.argv[4]) if len(sys.argv) > 4 else None
with open(out, "w", encoding="utf-8") as o:
o.write("BEGIN;\n" if not (len(sys.argv) > 5 and sys.argv[5] == "autocommit") else "")
for n, s in enumerate(statements(src, batch)):
if limit is not None and n >= limit:
break
o.write(s)
o.write("COMMIT;\n" if not (len(sys.argv) > 5 and sys.argv[5] == "autocommit") else "")It quotes every text value by doubling quotes. It writes numeric columns unquoted, but only after float(v) succeeds, so a stray 12x raises an error and does not produce a broken statement. It writes an empty numeric as NULL (an empty string is not a number). It can batch many rows into one INSERT ... VALUES (...), (...), and it wraps the file in a transaction.
The decision in it that matters most is what an empty text field becomes. By default, the script writes '', the empty string.
Empty is not NULL, and COPY disagrees
I loaded the same file two ways and counted:
Q="SELECT count(*) FILTER (WHERE phone_unparsed IS NULL) AS null_phone_unparsed, count(*) FILTER (WHERE phone_unparsed = '') AS empty_phone_unparsed, count(*) FILTER (WHERE signup_date IS NULL) AS null_signup, count(*) FILTER (WHERE signup_date = '') AS empty_signup FROM cust_ins"
echo "loaded by INSERT (empty text written as ''):"
psql -qc "DROP TABLE IF EXISTS cust_ins; CREATE TABLE cust_ins (id int, first_name text, last_name text, email text, phone text, phone_unparsed text, signup_date text, balance numeric, balance_currency text, units int, country text, status text);"; psql -q -v ON_ERROR_STOP=1 -f work/conv/ins_1000.sql; psql -x -At -c "$Q" | tr '\n' ' '; echo
echo "loaded by COPY:"
psql -qc "DROP TABLE IF EXISTS cust_ins; CREATE TABLE cust_ins (id int, first_name text, last_name text, email text, phone text, phone_unparsed text, signup_date text, balance numeric, balance_currency text, units int, country text, status text);"; psql -q -c "\copy cust_ins FROM 'work/val/customers.csv' WITH (FORMAT csv, HEADER)"; psql -x -At -c "$Q" | tr '\n' ' '; echo
echo "loaded by INSERT with EMPTY_AS_NULL=1:"
EMPTY_AS_NULL=1 python lab/conv/gen_sql.py work/val/customers.csv work/conv/ins_1000_null.sql 1000
psql -qc "DROP TABLE IF EXISTS cust_ins; CREATE TABLE cust_ins (id int, first_name text, last_name text, email text, phone text, phone_unparsed text, signup_date text, balance numeric, balance_currency text, units int, country text, status text);"; psql -q -v ON_ERROR_STOP=1 -f work/conv/ins_1000_null.sql; psql -At -c "SELECT md5(string_agg(t::text, '|' ORDER BY id)) FROM cust_ins t"
echo "COPY again, for the checksum:"
psql -qc "DROP TABLE IF EXISTS cust_ins; CREATE TABLE cust_ins (id int, first_name text, last_name text, email text, phone text, phone_unparsed text, signup_date text, balance numeric, balance_currency text, units int, country text, status text);"; psql -q -c "\copy cust_ins FROM 'work/val/customers.csv' WITH (FORMAT csv, HEADER)"; psql -At -c "SELECT md5(string_agg(t::text, '|' ORDER BY id)) FROM cust_ins t"loaded by INSERT (empty text written as ''):
null_phone_unparsed|0 empty_phone_unparsed|193708 null_signup|0 empty_signup|902
loaded by COPY:
null_phone_unparsed|193708 empty_phone_unparsed|0 null_signup|902 empty_signup|0
loaded by INSERT with EMPTY_AS_NULL=1:
b2d3bafa7e9685d054e2aed3d4956a0b
COPY again, for the checksum:
b2d3bafa7e9685d054e2aed3d4956a0bLoaded by INSERT with empty text written as '', the column phone_unparsed held 193,708 empty strings and no NULLs. Loaded by COPY, it held 193,708 NULLs and no empty strings, because COPY in CSV mode reads an unquoted empty field as NULL. The same split showed up in signup_date: 902 empty strings from INSERT, 902 NULLs from COPY. The two loads of one file gave different tables, and the difference changes answers: WHERE signup_date IS NULL finds 902 rows in one and none in the other. Setting EMPTY_AS_NULL=1 made the generator write NULL for every empty field, and then the two loads were identical: the checksum of every row's text, b2d3bafa7e9685d054e2aed3d4956a0b, matched. Decide what empty means once (the missing-values article covers the options), and make INSERT and COPY agree.
How much slower are statements?
Four ways to load the 193,708 rows into PostgreSQL:
ls -l work/val/customers.csv work/conv/ins_1.sql work/conv/ins_1000.sql | awk '{printf "%-24s %d bytes\n", $9, $5}' | sed 's#work/conv/##;s#work/val/##'
head -n 3 work/conv/ins_1.sql | cut -c1-200ins_1.sql 52645279 bytes
ins_1000.sql 23618179 bytes
customers.csv 17297141 bytes
BEGIN;
INSERT INTO cust_ins (id, first_name, last_name, email, phone, phone_unparsed, signup_date, balance, balance_currency, units, country, status) VALUES
(1, 'Cy', 'Riga', '[email protected]', '+14192381408', '', '2025-04-03', 10485.58, 'USD', 4156, 'IT', 'active');One INSERT per row produced 52,645,279 bytes of SQL, three times the CSV. Batches of 1,000 rows produced 23,618,179 bytes, since the column list is written once per batch.
psql -qc "DROP TABLE IF EXISTS cust_ins; CREATE TABLE cust_ins (id int, first_name text, last_name text, email text, phone text, phone_unparsed text, signup_date text, balance numeric, balance_currency text, units int, country text, status text);"
time psql -q -v ON_ERROR_STOP=1 -f work/conv/ins_1.sql
psql -Atc "SELECT count(*) FROM cust_ins"NOTICE: table "cust_ins" does not exist, skipping
real 0m16.947s
user 0m1.767s
sys 0m3.212s
193708psql -qc "DROP TABLE IF EXISTS cust_ins; CREATE TABLE cust_ins (id int, first_name text, last_name text, email text, phone text, phone_unparsed text, signup_date text, balance numeric, balance_currency text, units int, country text, status text);"
time psql -q -v ON_ERROR_STOP=1 -f work/conv/ins_1000.sql
psql -Atc "SELECT count(*) FROM cust_ins"
real 0m1.609s
user 0m0.275s
sys 0m0.070s
193708psql -qc "DROP TABLE IF EXISTS cust_ins; CREATE TABLE cust_ins (id int, first_name text, last_name text, email text, phone text, phone_unparsed text, signup_date text, balance numeric, balance_currency text, units int, country text, status text);"
time psql -q -c "\copy cust_ins FROM 'work/val/customers.csv' WITH (FORMAT csv, HEADER)"
psql -Atc "SELECT count(*) FROM cust_ins"
real 0m0.295s
user 0m0.036s
sys 0m0.031s
193708| Method | Time | Rows |
|---|---|---|
| One INSERT per row, one transaction | 16.95 s | 193,708 |
| 1,000 rows per INSERT, one transaction | 1.61 s | 193,708 |
\copy | 0.295 s | 193,708 |
Batching is a factor of ten, and COPY a factor of five more. And the worst case is one transaction per row, which is what you get from a script that does not say BEGIN:
psql -qc "DROP TABLE IF EXISTS cust_ins; CREATE TABLE cust_ins (id int, first_name text, last_name text, email text, phone text, phone_unparsed text, signup_date text, balance numeric, balance_currency text, units int, country text, status text);"
time psql -q -v ON_ERROR_STOP=1 -f work/conv/ins_1_5000_auto.sql
psql -Atc "SELECT count(*) FROM cust_ins"
real 0m1.164s
user 0m0.069s
sys 0m0.103s
50005,000 single-row inserts in autocommit mode took 1.16 s here, which would be around 45 s for 193,708 rows if the rate held; that is an extrapolation, and on a database that waits for the disk on every commit it can be far worse. These figures are from a local container with a fast disk and no network, so the ratios matter more than the seconds.
When to use INSERT scripts
Use them when the table is small (reference data, test fixtures, seeds), when the target is any database and you want one file that runs everywhere, when the file is reviewed by people and when you have no bulk loader, as with some managed services and restricted accounts. Use COPY or the engine's loader for everything big: the PostgreSQL loading article and the MySQL one cover them. Between the two, generate batches of a few hundred to a thousand rows inside a transaction.
How to tell it worked
Count the rows in the table and compare with the CSV's record count. Compute a checksum over the rows' text in both the loaded table and a COPY-loaded copy, as above, and compare them; any difference points to a quoting or NULL decision. Put awkward rows through the generator and load them: an apostrophe, a backslash, a double quote, a comma, a newline, a non-ASCII character and the SQL-looking value above. And keep the generated file out of anywhere untrusted people can edit.
When COPY is available
If the loader exists, it wins on speed, on memory and on not needing any quoting code at all, because the CSV parser deals with quotes and the database never sees them as SQL. The remaining reason for INSERT statements is portability and reviewability, and for those, keep the file small. A generated SQL file is an artifact, not the source of truth; the source is the CSV and the script, which is why validating the CSV comes before generating anything.
