44ccfacbe3a3d4b300f80d48651c77837a4b571e galt Tue Apr 26 11:12:02 2022 -0700 SQL INJECTION Prevention Version 2 - this improves our methods by making subclauses of SQL that get passed around be both easy and correct to use. The way that was achieved was by getting rid of the obscure and not well used functions sqlSafefFrag and sqlDyStringPrintfFrag and replacing them with the plain versions of those functions, since these are not needed anymore. The new version checks for NOSQLINJ in unquoted %-s which is used to include SQL clauses, and will give an error the NOSQLINJ clause is not present, and this will automatically require the correct behavior by developers. sqlDyStringPrint is a very useful function, however because it was not enforced, users could use various other dyString functions and they operated without any awareness or checking for SQL correct use. Now those dyString functions are prohibited and it will produce an error if you try to use a dyString function on a SQL string, which is simply detected by the presence of the NOSQLINJ prefix. diff --git src/hg/lib/ispyTables.c src/hg/lib/ispyTables.c index 96847a5..3116ea6 100644 --- src/hg/lib/ispyTables.c +++ src/hg/lib/ispyTables.c @@ -88,35 +88,35 @@ el = patientLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void patientSaveToDb(struct sqlConnection *conn, struct patient *el, char *tableName, int updateSize) /* Save patient as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s')", tableName, el->ispyId, el->DataExtractDt, el->Inst_ID); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct patient *patientCommaIn(char **pS, struct patient *ret) /* Create a patient out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new patient */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->DataExtractDt = sqlStringComma(&s); ret->Inst_ID = sqlStringComma(&s); @@ -277,35 +277,35 @@ el = patientInfoLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void patientInfoSaveToDb(struct sqlConnection *conn, struct patientInfo *el, char *tableName, int updateSize) /* Save patientInfo as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s',%g,'%s','%s',%d)", tableName, el->ispyId, el->DataExtractDt, el->Inst_ID, el->AgeCat, *(el->Age), el->Race_id, el->Sstat, *(el->SurvDtD)); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct patientInfo *patientInfoCommaIn(char **pS, struct patientInfo *ret) /* Create a patientInfo out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new patientInfo */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->DataExtractDt = sqlStringComma(&s); ret->Inst_ID = sqlStringComma(&s); @@ -466,35 +466,35 @@ el = chemoLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void chemoSaveToDb(struct sqlConnection *conn, struct chemo *el, char *tableName, int updateSize) /* Save chemo as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s','%s','%s','%s')", tableName, el->ispyId, el->Chemo, el->ChemoCat, el->DoseDenseAnthra, el->DoseDenseTaxane, el->Tam, el->Herceptin); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct chemo *chemoCommaIn(char **pS, struct chemo *ret) /* Create a chemo out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new chemo */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->Chemo = sqlStringComma(&s); ret->ChemoCat = sqlStringComma(&s); @@ -697,35 +697,35 @@ el = onStudyLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void onStudySaveToDb(struct sqlConnection *conn, struct onStudy *el, char *tableName, int updateSize) /* Save onStudy as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s','%s','%s',%d,%d,'%s','%s','%s','%s','%s')", tableName, el->ispyId, el->MenoStatus, el->SentinelNodeSample, el->SentinelNodeResult, el->HistTypeInvOS, el->HistologicGradeOS, *(el->ER_TS), *(el->PgR_TS), el->ERpos, el->PgRpos, el->Her2CommunityPos, el->Her2CommunityMethod, el->pCR); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct onStudy *onStudyCommaIn(char **pS, struct onStudy *ret) /* Create a onStudy out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new onStudy */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->MenoStatus = sqlStringComma(&s); ret->SentinelNodeSample = sqlStringComma(&s); @@ -968,35 +968,35 @@ el = postSurgeryLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void postSurgerySaveToDb(struct sqlConnection *conn, struct postSurgery *el, char *tableName, int updateSize) /* Save postSurgery as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s','%s','%s',%g,'%s',%d,%d,'%s','%s','%s')", tableName, el->ispyId, el->SurgeryLumpectomy, el->SurgeryMastectomy, el->InitLump_FupMast, el->Surgery, el->DCISonly, *(el->PTumor1Szcm_Micro), el->HistologicTypePS, *(el->HistologicGradePS), *(el->NumPosNodes), el->NodesExamined, el->PathologyStage, el->ReasonNoSurg); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct postSurgery *postSurgeryCommaIn(char **pS, struct postSurgery *ret) /* Create a postSurgery out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new postSurgery */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->SurgeryLumpectomy = sqlStringComma(&s); ret->SurgeryMastectomy = sqlStringComma(&s); @@ -1189,35 +1189,35 @@ el = followUpLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void followUpSaveToDb(struct sqlConnection *conn, struct followUp *el, char *tableName, int updateSize) /* Save followUp as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s','%s','%s','%s','%s','%s')", tableName, el->ispyId, el->RtTherapy, el->RtBreast, el->RtBoost, el->RtAxilla, el->RtSNode, el->RtIMamNode, el->RTChestW, el->RtOther); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct followUp *followUpCommaIn(char **pS, struct followUp *ret) /* Create a followUp out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new followUp */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->RtTherapy = sqlStringComma(&s); ret->RtBreast = sqlStringComma(&s); @@ -1410,35 +1410,35 @@ el = respEvalLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void respEvalSaveToDb(struct sqlConnection *conn, struct respEval *el, char *tableName, int updateSize) /* Save respEval as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s',%g,%g,'%s','%s','%s','%s','%s','%s','%s')", tableName, el->ispyId, *(el->TSizeClinical), *(el->NSizeClinical), el->StageTe, el->StageNe, el->StageMe, el->ClinicalStage, el->ClinRespT1_T2, el->ClinRespT1_T3, el->ClinRespT1_T4); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct respEval *respEvalCommaIn(char **pS, struct respEval *ret) /* Create a respEval out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new respEval */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->TSizeClinical = needMem(sizeof(*(ret->TSizeClinical))); *(ret->TSizeClinical) = sqlFloatComma(&s); @@ -1749,35 +1749,35 @@ el = mrLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void mrSaveToDb(struct sqlConnection *conn, struct mr *el, char *tableName, int updateSize) /* Save mr as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s','%s','%s','%s','%s',%d,%d,%d,%d,%g,%g,%g,%g,%g,%g,'%s','%s')", tableName, el->ispyId, el->ChemoCat, el->DoseDenseAnthra, el->DoseDenseTaxane, el->LES_T1, el->LES_T2, el->LES_T3, el->LES_T4, *(el->LD_T1), *(el->LD_T2), *(el->LD_T3), *(el->LD_T4), *(el->LD_T1_T2_PERCT), *(el->LD_T1_T3_PERCT), *(el->LD_T1_T4_PERCT), *(el->LD_T2_T3_PERCT), *(el->LD_T2_T4_PERCT), *(el->LD_T3_T4_PERCT), el->Mri_Pattern_Code, el->Mri_Pattern_Desc); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct mr *mrCommaIn(char **pS, struct mr *ret) /* Create a mr out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new mr */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->ChemoCat = sqlStringComma(&s); ret->DoseDenseAnthra = sqlStringComma(&s); @@ -1990,35 +1990,35 @@ el = cdnaLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void cdnaSaveToDb(struct sqlConnection *conn, struct cdna *el, char *tableName, int updateSize) /* Save cdna as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s','%s')", tableName, el->ispyId, el->Cdna_T1, el->Cdna_T2, el->Cdna_T3, el->Cdna_T4); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct cdna *cdnaCommaIn(char **pS, struct cdna *ret) /* Create a cdna out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new cdna */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->Cdna_T1 = sqlStringComma(&s); ret->Cdna_T2 = sqlStringComma(&s); @@ -2161,35 +2161,35 @@ el = agiLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void agiSaveToDb(struct sqlConnection *conn, struct agi *el, char *tableName, int updateSize) /* Save agi as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s','%s')", tableName, el->ispyId, el->Agi_T1, el->Agi_T2, el->Agi_T3, el->Agi_T4); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct agi *agiCommaIn(char **pS, struct agi *ret) /* Create a agi out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new agi */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->Agi_T1 = sqlStringComma(&s); ret->Agi_T2 = sqlStringComma(&s); @@ -2332,35 +2332,35 @@ el = ihcLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void ihcSaveToDb(struct sqlConnection *conn, struct ihc *el, char *tableName, int updateSize) /* Save ihc as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s','%s')", tableName, el->ispyId, el->Ihc_T1, el->Ihc_T2, el->Ihc_T3, el->Ihc_T4); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct ihc *ihcCommaIn(char **pS, struct ihc *ret) /* Create a ihc out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new ihc */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->Ihc_T1 = sqlStringComma(&s); ret->Ihc_T2 = sqlStringComma(&s); @@ -2503,35 +2503,35 @@ el = fishLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void fishSaveToDb(struct sqlConnection *conn, struct fish *el, char *tableName, int updateSize) /* Save fish as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s','%s')", tableName, el->ispyId, el->Fish_T1, el->Fish_T2, el->Fish_T3, el->Fish_T4); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct fish *fishCommaIn(char **pS, struct fish *ret) /* Create a fish out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new fish */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->Fish_T1 = sqlStringComma(&s); ret->Fish_T2 = sqlStringComma(&s); @@ -2674,35 +2674,35 @@ el = labTrackLoadWithNull(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void labTrackSaveToDb(struct sqlConnection *conn, struct labTrack *el, char *tableName, int updateSize) /* Save labTrack as a row to the table specified by tableName. * As blob fields may be arbitrary size updateSize specifies the approx size * of a string that would contain the entire query. Arrays of native types are * converted to comma separated strings and loaded as such, User defined types are * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s','%s','%s','%s','%s')", tableName, el->ispyId, el->trackId, el->coreType, el->timePoint, el->section); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct labTrack *labTrackCommaIn(char **pS, struct labTrack *ret) /* Create a labTrack out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new labTrack */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ispyId = sqlStringComma(&s); ret->trackId = sqlStringComma(&s); ret->coreType = sqlStringComma(&s);