T09 · Insecure Skill Coding Practices
Error
- Location
references/labs.md:252- Finding
SQL Injection in Generated Python Database Templates
- Content
View full analysis
10 THEN NULL" ) elif lab_name == 'creatinine': case_clauses.append( f"WHEN l.labname = '{lab_name}' AND l.labresult > 150 THEN NULL" ) query += f""" l.labmeasurenamesystem, l.labmeasurenameinterface FROM lab l WHERE l.labresultoffset >= 0 AND l.labresultoffset < 1440 AND l.labresult IS NOT NULL AND l.labname IN ('{lab_names_str}') AND l.labresult > 0 ORDER BY l.patientunitstayid, l.labname, l.labresultoffset """ if patient_ids is not None: if len(patient_ids) == 1: query = query.replace( "WHERE l.labresultoffset", f"WHERE l.patientunitstayid = {patient_ids[0]} AND l.labresultoffset" ) else: ids_s ...[truncated 4060 chars]- Remediation
View remediation
= %(min_offset)s AND l.labresultoffset < %(max_offset)s ORDER BY l.patientunitstayid, l.labname, l.labresultoffset """ params = { "patient_ids": validated_patient_ids, "lab_names": validated_lab_names, "min_offset": 0, "max_offset": 1440, } df = pd.read_sql_query(query, conn, params=params) ``` 2. Validate patient identifiers before database execution: ```python def validate_patient_ids(patient_ids): if patient_ids is None: return None if not isinstance(patient_ids, (list, tuple)): raise TypeError("patient_ids must be a list or tuple") if not patient_ids: raise ValueError("patient_ids cannot be empty") if any(type(value) is not int or value <= 0 for value in patient_ids): raise ValueError("Every patient ID must be a positive integer") return list(patient_ids) ``` 3. Restrict string filters to documented allowlists where appropriate. Validation is defense in depth and must not replace parameter binding. 4. Do not build SQL value lists using: ```python "','".join(values) ",".join(map(str, values)) ``` 5. If dynamic identifiers are ever required, use `psycopg2.sql.Identifier` rather than ordinary value parameters. Values must continue to use placeholders. 6. Update every affected reference template, not only the representative laboratory example, because agents may reproduce any documented implementation. 7. Recommend a read-only PostgreSQL role with access li ...[truncated 95 chars]