Reason for error pset7

Question

There seems to be a problem with the sql line but idk whats wrong.

from cs50 import SQL, get_string
from sys import argv
import csv
db = SQL("sqlite:///students.db")

if (len(argv) != 2):
    exit()
with open(argv[1],"r") as inp:
    reader = csv.DictReader(inp)
    for row in reader:
        lis = row["name"].split()
        if (len(lis) == 2):
            print(lis[0],lis[1])
            db.execute("")
            #TODO
        else:
            db.execute("INSERT INTO students (first, middle, last) VALUES (lis[0], lis[1], lis[2])")

Error

Traceback (most recent call last):
  File "import.py", line 18, in <module>
    db.execute("INSERT INTO students (first, middle, last) VALUES (lis[0], lis[1], lis[2])")
  File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 21, in decorator
    return f(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 372, in execute
    raise e
RuntimeError: near "[0]": syntax error

Answer 1

Use the template syntax, somewhat similar to printf format strings.

db.execute("INSERT INTO students (first, middle, last) VALUES (?, ?, ?)", lis[0], lis[1], lis[2])

The template syntax makes sure the values are passed in a safe way, encoded according to their type, so that none of the values end up being treated as SQL commands. Other solutions like format strings (a general-purpose built-in Python feature for inserting values into strings) are probably unsafe, especially if they use user input. There's a point at the end of https://cs50.harvard.edu/x/2020/notes/7/#problems explaining that, and I think the last few minutes of the lecture.