A parameterized query keeps SQL structure separate from the values supplied to it. Instead of inserting a filename or hash into an SQL string, write placeholders in the statement and pass the values separately. This is a secure-coding practice because string interpolation in SQL is identified as ripe for SQL injection. section 5.6
In an ACH-file upload workflow, the database insert has a fixed shape: add a file name and file hash to `ach_files`. The changing values are the uploaded file’s name and the MD5 hash calculated from its contents. The SQL uses `%s` placeholders, while `(file.filename, md5_hash)` supplies the corresponding values. section 5.6
Store uploaded-file metadata safely
After reading the uploaded file and computing its hash, open a database connection and execute an insert such as `INSERT INTO ach_files (file_name, file_hash) VALUES (%s, %s)` with the filename and hash passed as a value tuple. The f-string is used for the multiline SQL literal here, not to interpolate those values into SQL. section 5.6
Use the technique consistently in database helpers and tests:
- Keep table-name construction separate from parameter passing; f-strings may construct SQL where necessary, but values should still use parameterized queries. section 5.4.1
- Use parameterized queries even for internal tooling, rather than assuming internal inputs cannot be malicious. section 5.4.1
- Pair database tests with setup and teardown that clear tables, so repeated runs do not leave records that invalidate assertions. section 5.4.1
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
The upload endpoint shows the complete path from file contents to hash calculation, database connection, placeholder-based insert, and successful response. section 5.6
