SQL injection occurs when attacker-controlled input changes a database query’s intended structure or behavior. String interpolation such as embedding a filename directly into SQL creates this risk; parameterized queries keep values separate from query structure. section 5.6
Apply these practices when inserting uploaded-file metadata:
- Avoid interpolating file.filename or other input into SQL strings. section 5.6
- Pass SQL values separately as query parameters, as with the filename and computed MD5 hash. section 5.6
- Exercise the endpoint with a unit test and verify that the database contains the expected record. section 5.6
ACH file upload
An upload endpoint reads an ACH file, computes its MD5 hash, and inserts the filename and hash into ach_files. The safe form supplies both values separately instead of interpolating them into the SQL statement. section 5.6
Listing 5.24 Updated endpoint to upload ACH files
@router.post("", status_code=status.HTTP_201_CREATED)
async def create_file(file: UploadFile = File(...)):
contents = await file.read() #1
md5_hash = hashlib.md5(contents).hexdigest() #2
with DbUtils.get_db_connection() as conn: #3
conn.execute( #4
f""" #4
INSERT INTO ach_files (file_name, file_hash) #4
VALUES (%s, %s) #4
""", (file.filename, md5_hash) #4
) #4
return None #4
Inspect the upload endpoint’s INSERT statement and compare its parameterized values with the unsafe interpolation pattern. section 5.6
