9f7e8d3b93fc9d1ed0e1e3e9f8c164f90496e1a8 galt Tue May 24 14:35:15 2022 -0700 Fix NOSQLINJv2 bug in genomeTracks.c. Needed to use sql safe functions. diff --git src/hg/hgTracks/gencodeTracks.c src/hg/hgTracks/gencodeTracks.c index 47fe47e..56cc100 100644 --- src/hg/hgTracks/gencodeTracks.c +++ src/hg/hgTracks/gencodeTracks.c @@ -72,38 +72,38 @@ struct gencodeQuery *gencodeQuery = *gencodeQueryPtr; if (gencodeQuery != NULL) { dyStringFree(&gencodeQuery->fields); dyStringFree(&gencodeQuery->from); dyStringFree(&gencodeQuery->where); freeMem(gencodeQuery); *gencodeQueryPtr = NULL; } } static void gencodeQueryBeginSubWhere(struct gencodeQuery *gencodeQuery) /* begin adding new where sub-clause */ { if (dyStringLen(gencodeQuery->where) > 0) - dyStringAppend(gencodeQuery->where, " and "); -dyStringAppend(gencodeQuery->where, "("); + sqlDyStringPrintf(gencodeQuery->where, " and "); +sqlDyStringPrintf(gencodeQuery->where, "("); } static void gencodeQueryEndSubWhere(struct gencodeQuery *gencodeQuery) /* finish adding new where sub-clause */ { -dyStringAppend(gencodeQuery->where, ")"); +sqlDyStringPrintf(gencodeQuery->where, ")"); } static struct genePred *gencodeQueryGenePred(struct gencodeQuery *gencodeQuery, char **row) /* get genePred from a query results */ { return genePredExtLoad(row, gencodeQuery->genePredNumColumns); } static struct wgEncodeGencodeAttrs *gencodeQueryAttrs(struct gencodeQuery *gencodeQuery, char **row) /* get attributes from a query results, or NULL if not requested */ { if (gencodeQuery->attrsNumColumns > 0) return wgEncodeGencodeAttrsLoad(row + gencodeQuery->genePredNumColumns, gencodeQuery->attrsNumColumns); @@ -136,106 +136,106 @@ return tslSym+3; } static void filterByMethodChoiceQuery(char *choice, struct gencodeQuery *gencodeQuery) /* add SQL expression for GENCODE transcript method choice. */ { /* * example sources and categories: * havana_ig_gene manual, manual only * ensembl_havana_transcript manual, automatic & manual and automatic * havana manual, manual only * ensembl automatic, automatic only * mt_genbank_import automatic, automatic only */ if (sameString(choice, "manual")) - dyStringAppend(gencodeQuery->where, "(transSrc.source like \"%havana%\")"); + sqlDyStringPrintf(gencodeQuery->where, "(transSrc.source like \"%%havana%%\")"); else if (sameString(choice, "automatic")) - dyStringAppend(gencodeQuery->where, "((transSrc.source like \"%ensembl%\") or (transSrc.source not like \"%havana%\"))"); + sqlDyStringPrintf(gencodeQuery->where, "((transSrc.source like \"%%ensembl%%\") or (transSrc.source not like \"%%havana%%\"))"); else if (sameString(choice, "manual_only")) - dyStringAppend(gencodeQuery->where, "((transSrc.source like \"%havana%\") and (transSrc.source not like \"%ensembl%\"))"); + sqlDyStringPrintf(gencodeQuery->where, "((transSrc.source like \"%%havana%%\") and (transSrc.source not like \"%%ensembl%%\"))"); else if (sameString(choice, "automatic_only")) - dyStringAppend(gencodeQuery->where, "(transSrc.source not like \"%havana%\")"); + sqlDyStringPrintf(gencodeQuery->where, "(transSrc.source not like \"%%havana%%\")"); else if (sameString(choice, "manual_and_automatic")) - dyStringAppend(gencodeQuery->where, "((transSrc.source like \"%havana%\") and (transSrc.source like \"%ensembl%\"))"); + sqlDyStringPrintf(gencodeQuery->where, "((transSrc.source like \"%%havana%%\") and (transSrc.source like \"%%ensembl%%\"))"); else errAbort("BUG: filterByMethodChoiceQuery missing choice: \"%s\"", choice); } static void filterByMethodChoicesQuery(filterBy_t *filterBy, struct gencodeQuery *gencodeQuery) /* add transcript source compare clauses */ { struct slName *choice = NULL; for (choice = filterBy->slChoices; choice != NULL; choice = choice->next) { if (choice != filterBy->slChoices) - dyStringAppend(gencodeQuery->where, " or "); + sqlDyStringPrintf(gencodeQuery->where, " or "); filterByMethodChoiceQuery(choice->name, gencodeQuery); } } static void filterByMethodQuery(struct track *tg, filterBy_t *filterBy, struct gencodeQuery *gencodeQuery) /* generate SQL where clause for annotation method filtering */ { gencodeQueryBeginSubWhere(gencodeQuery); filterByMethodChoicesQuery(filterBy, gencodeQuery); gencodeQuery->joinTranscriptSource = TRUE; gencodeQueryEndSubWhere(gencodeQuery); } static void filterBySupportLevelChoiceQuery(char *choice, struct gencodeQuery *gencodeQuery) /* add SQL expression GENCODE support choice. */ { /* table is numeric (silly me), and string is tsl1..tsl5 or tslNA */ -dyStringPrintf(gencodeQuery->where, "(supLevel.level = %s)", tslSymToNumStr(choice)); +sqlDyStringPrintf(gencodeQuery->where, "(supLevel.level = %d)", atoi(tslSymToNumStr(choice))); } static void filterBySupportLevelChoicesQuery(filterBy_t *filterBy, struct gencodeQuery *gencodeQuery) /* add support level compare clauses */ { struct slName *choice = NULL; for (choice = filterBy->slChoices; choice != NULL; choice = choice->next) { if (choice != filterBy->slChoices) - dyStringAppend(gencodeQuery->where, " or "); + sqlDyStringPrintf(gencodeQuery->where, " or "); filterBySupportLevelChoiceQuery(choice->name, gencodeQuery); } } static void filterBySupportLevelQuery(struct track *tg, filterBy_t *filterBy, struct gencodeQuery *gencodeQuery) /* generate SQL where clause for annotation support level filtering */ { gencodeQueryBeginSubWhere(gencodeQuery); filterBySupportLevelChoicesQuery(filterBy, gencodeQuery); gencodeQuery->joinSupportLevel = TRUE; gencodeQueryEndSubWhere(gencodeQuery); } static void filterByTagChoiceQuery(char *choice, struct gencodeQuery *gencodeQuery) /* add SQL expression GENCODE tag choice. */ { -dyStringPrintf(gencodeQuery->where, "(tag.tag = \"%s\")", choice); +sqlDyStringPrintf(gencodeQuery->where, "(tag.tag = \"%s\")", choice); } static void filterByTagChoicesQuery(filterBy_t *filterBy, struct gencodeQuery *gencodeQuery) /* add tag compare clauses */ { struct slName *choice = NULL; for (choice = filterBy->slChoices; choice != NULL; choice = choice->next) { if (choice != filterBy->slChoices) - dyStringAppend(gencodeQuery->where, " or "); + sqlDyStringPrintf(gencodeQuery->where, " or "); filterByTagChoiceQuery(choice->name, gencodeQuery); } } static void filterByTagQuery(struct track *tg, filterBy_t *filterBy, struct gencodeQuery *gencodeQuery) /* generate SQL where clause for annotation tag filtering */ { gencodeQueryBeginSubWhere(gencodeQuery); filterByTagChoicesQuery(filterBy, gencodeQuery); gencodeQuery->joinTag = TRUE; gencodeQueryEndSubWhere(gencodeQuery); } static void filterByAttrsQuery(struct track *tg, filterBy_t *filterBy, struct gencodeQuery *gencodeQuery) /* handle adding on filterBy clause for attributes table */ @@ -309,33 +309,32 @@ static void addQueryCommon(struct track *tg, filterBySetGetFuncType filterBySetGetFunc, struct gencodeQuery *gencodeQuery) /* Add tables and joins for both gene and highlight queries */ { // bin range overlap part hAddBinToQuery(winStart, winEnd, gencodeQuery->where); sqlDyStringPrintf(gencodeQuery->where, "(g.chrom = \"%s\") and (g.txStart < %u) and (g.txEnd > %u)", chromName, winEnd, winStart); gencodeFilterBySetQuery(tg, filterBySetGetFunc, gencodeQuery); addQueryTables(tg, gencodeQuery); } static struct sqlResult *executeQuery(struct sqlConnection *conn, struct gencodeQuery *gencodeQuery) /* execute the actual SQL query */ { struct dyString *query = dyStringNew(0); -sqlCkIl(fieldsSafe,dyStringContents(gencodeQuery->fields)) sqlDyStringPrintf(query, "select %-s from %-s where %-s", - fieldsSafe, dyStringContents(gencodeQuery->from), dyStringContents(gencodeQuery->where)); + dyStringContents(gencodeQuery->fields), dyStringContents(gencodeQuery->from), dyStringContents(gencodeQuery->where)); struct sqlResult *sr = sqlGetResult(conn, dyStringContents(query)); dyStringFree(&query); return sr; } static boolean annotIsGenePredExt(struct track *tg) /* determine if a table has genePred extended fields. two-way consensus * pseudo doesn't have them. */ { struct sqlConnection *conn = hAllocConn(database); struct slName *fields = sqlFieldNames(conn, tg->table); hFreeConn(&conn); boolean isGenePredX = slNameInList(fields, "score"); slFreeList(&fields); return isGenePredX; @@ -344,80 +343,76 @@ static boolean attrsHasProteinId(struct track *tg) /* determine if the attributes table has the proteinId field.. */ { struct sqlConnection *conn = hAllocConn(database); struct slName *fields = sqlFieldNames(conn, trackDbRequiredSetting(tg->tdb, "wgEncodeGencodeAttrs")); hFreeConn(&conn); boolean hasProteinId = slNameInList(fields, "proteinId"); slFreeList(&fields); return hasProteinId; } static void geneQueryAddGenePredCols(struct track *tg, struct gencodeQuery *gencodeQuery) /* add genePred columns to query */ { -static char *genePredFields = "g.name, g.chrom, g.strand, g.txStart, g.txEnd, g.cdsStart, g.cdsEnd, g.exonCount, g.exonStarts, g.exonEnds"; -static char *genePredXFields = ", g.score, g.name2, g.cdsStartStat, g.cdsEndStat, g.exonFrames"; gencodeQuery->isGenePredX = annotIsGenePredExt(tg); -dyStringAppend(gencodeQuery->fields, genePredFields); +sqlDyStringPrintf(gencodeQuery->fields, "g.name, g.chrom, g.strand, g.txStart, g.txEnd, g.cdsStart, g.cdsEnd, g.exonCount, g.exonStarts, g.exonEnds"); gencodeQuery->genePredNumColumns = GENEPRED_NUM_COLS; if (gencodeQuery->isGenePredX) { - dyStringAppend(gencodeQuery->fields, genePredXFields); + sqlDyStringPrintf(gencodeQuery->fields, ", g.score, g.name2, g.cdsStartStat, g.cdsEndStat, g.exonFrames"); gencodeQuery->genePredNumColumns = GENEPREDX_NUM_COLS; } } static void geneQueryAddAttrsCols(struct track *tg, struct gencodeQuery *gencodeQuery) /* add attributes columns to query */ { -char *attrsBaseFields = "attrs.geneId, attrs.geneName, attrs.geneType, attrs.geneStatus, attrs.transcriptId, attrs.transcriptName, attrs.transcriptType, attrs.transcriptStatus, attrs.havanaGeneId, attrs.havanaTranscriptId, attrs.ccdsId, attrs.level, attrs.transcriptClass"; -char *attrsExtraFields = ", attrs.proteinId"; -dyStringAppend(gencodeQuery->fields, ", "); -dyStringAppend(gencodeQuery->fields, attrsBaseFields); +sqlDyStringPrintf(gencodeQuery->fields, ", "); +sqlDyStringPrintf(gencodeQuery->fields, "attrs.geneId, attrs.geneName, attrs.geneType, attrs.geneStatus, attrs.transcriptId, attrs.transcriptName, attrs.transcriptType, attrs.transcriptStatus, attrs.havanaGeneId, attrs.havanaTranscriptId, attrs.ccdsId, attrs.level, attrs.transcriptClass"); gencodeQuery->attrsNumColumns = WGENCODEGENCODEATTRS_NO_PROTEIN_ID_NUM_COLS; if (attrsHasProteinId(tg)) { - dyStringAppend(gencodeQuery->fields, attrsExtraFields); + sqlDyStringPrintf(gencodeQuery->fields, ", attrs.proteinId"); gencodeQuery->attrsNumColumns = WGENCODEGENCODEATTRS_NUM_COLS; } gencodeQuery->joinAttrs = TRUE; } static struct gencodeQuery *geneQueryConstruct(struct track *tg, boolean includeAttrs) /* construct the query for a GENCODE records, which includes filters. */ { struct gencodeQuery *gencodeQuery = gencodeQueryNew(); geneQueryAddGenePredCols(tg, gencodeQuery); if (includeAttrs) geneQueryAddAttrsCols(tg, gencodeQuery); addQueryCommon(tg, filterBySetGet, gencodeQuery); return gencodeQuery; } static struct gencodeQuery *highlightQueryConstruct(struct track *tg) /* construct the query for GENCODE ids which should be highlighted. * this essentially redoes the genePred query, only using the filter functions * and only getting ids */ { struct gencodeQuery *gencodeQuery = gencodeQueryNew(); -dyStringAppend(gencodeQuery->fields, "g.name"); +sqlDyStringPrintf(gencodeQuery->fields, "g.name"); addQueryCommon(tg, highlightBySetGet, gencodeQuery); return gencodeQuery; } static unsigned getHighlightColor(struct track *tg) /* get the highlightColor from trackDb, or a default if not found */ { unsigned char red = 255, green = 165, blue = 0; // Orange default char *colorStr = trackDbSetting(tg->tdb, "highlightColor"); if (colorStr != NULL) parseColor(colorStr, &red, &green, &blue); return MAKECOLOR_32(red, green, blue); }