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/interact.c src/hg/lib/interact.c index 51aa19a..a1e5dba 100644 --- src/hg/lib/interact.c +++ src/hg/lib/interact.c @@ -1,550 +1,550 @@ /* interact.c was originally generated by the autoSql program, which also * generated interact.h and interact.sql. This module links the database and * the RAM representation of objects. */ #include "common.h" #include "linefile.h" #include "dystring.h" #include "jksql.h" #include "basicBed.h" #include "interact.h" char *interactCommaSepFieldNames = "chrom,chromStart,chromEnd,name,score,value,exp,color,sourceChrom,sourceStart,sourceEnd,sourceName,sourceStrand,targetChrom,targetStart,targetEnd,targetName,targetStrand"; void interactStaticLoad(char **row, struct interact *ret) /* Load a row from interact table into ret. The contents of ret will * be replaced at the next call to this function. */ { ret->chrom = row[0]; ret->chromStart = sqlUnsigned(row[1]); ret->chromEnd = sqlUnsigned(row[2]); ret->name = row[3]; ret->score = sqlUnsigned(row[4]); ret->value = sqlDouble(row[5]); ret->exp = row[6]; ret->color = sqlUnsigned(row[7]); ret->sourceChrom = row[8]; ret->sourceStart = sqlUnsigned(row[9]); ret->sourceEnd = sqlUnsigned(row[10]); ret->sourceName = row[11]; ret->sourceStrand = row[12]; ret->targetChrom = row[13]; ret->targetStart = sqlUnsigned(row[14]); ret->targetEnd = sqlUnsigned(row[15]); ret->targetName = row[16]; ret->targetStrand = row[17]; } struct interact *interactLoadByQuery(struct sqlConnection *conn, char *query) /* Load all interact from table that satisfy the query given. * Where query is of the form 'select * from example where something=something' * or 'select example.* from example, anotherTable where example.something = * anotherTable.something'. * Dispose of this with interactFreeList(). */ { struct interact *list = NULL, *el; struct sqlResult *sr; char **row; sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { el = interactLoad(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void interactSaveToDb(struct sqlConnection *conn, struct interact *el, char *tableName, int updateSize) /* Save interact 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. This function automatically escapes quoted strings for mysql. */ { -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s values ( '%s',%u,%u,'%s',%u,%g,'%s',%u,'%s',%u,%u,'%s','%s','%s',%u,%u,'%s','%s')", tableName, el->chrom, el->chromStart, el->chromEnd, el->name, el->score, el->value, el->exp, el->color, el->sourceChrom, el->sourceStart, el->sourceEnd, el->sourceName, el->sourceStrand, el->targetChrom, el->targetStart, el->targetEnd, el->targetName, el->targetStrand); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); } struct interact *interactLoad(char **row) /* Load a interact from row fetched with select * from interact * from database. Dispose of this with interactFree(). */ { struct interact *ret; AllocVar(ret); ret->chrom = cloneString(row[0]); ret->chromStart = sqlUnsigned(row[1]); ret->chromEnd = sqlUnsigned(row[2]); ret->name = cloneString(row[3]); ret->score = sqlUnsigned(row[4]); ret->value = sqlDouble(row[5]); ret->exp = cloneString(row[6]); ret->color = sqlUnsigned(row[7]); ret->sourceChrom = cloneString(row[8]); ret->sourceStart = sqlUnsigned(row[9]); ret->sourceEnd = sqlUnsigned(row[10]); ret->sourceName = cloneString(row[11]); ret->sourceStrand = cloneString(row[12]); ret->targetChrom = cloneString(row[13]); ret->targetStart = sqlUnsigned(row[14]); ret->targetEnd = sqlUnsigned(row[15]); ret->targetName = cloneString(row[16]); ret->targetStrand = cloneString(row[17]); return ret; } struct interact *interactLoadAll(char *fileName) /* Load all interact from a whitespace-separated file. * Dispose of this with interactFreeList(). */ { struct interact *list = NULL, *el; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *row[18]; while (lineFileRow(lf, row)) { el = interactLoad(row); slAddHead(&list, el); } lineFileClose(&lf); slReverse(&list); return list; } struct interact *interactLoadAllByChar(char *fileName, char chopper) /* Load all interact from a chopper separated file. * Dispose of this with interactFreeList(). */ { struct interact *list = NULL, *el; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *row[18]; while (lineFileNextCharRow(lf, chopper, row, ArraySize(row))) { el = interactLoad(row); slAddHead(&list, el); } lineFileClose(&lf); slReverse(&list); return list; } struct interact *interactCommaIn(char **pS, struct interact *ret) /* Create a interact out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new interact */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->chrom = sqlStringComma(&s); ret->chromStart = sqlUnsignedComma(&s); ret->chromEnd = sqlUnsignedComma(&s); ret->name = sqlStringComma(&s); ret->score = sqlUnsignedComma(&s); ret->value = sqlDoubleComma(&s); ret->exp = sqlStringComma(&s); ret->color = sqlUnsignedComma(&s); ret->sourceChrom = sqlStringComma(&s); ret->sourceStart = sqlUnsignedComma(&s); ret->sourceEnd = sqlUnsignedComma(&s); ret->sourceName = sqlStringComma(&s); ret->sourceStrand = sqlStringComma(&s); ret->targetChrom = sqlStringComma(&s); ret->targetStart = sqlUnsignedComma(&s); ret->targetEnd = sqlUnsignedComma(&s); ret->targetName = sqlStringComma(&s); ret->targetStrand = sqlStringComma(&s); *pS = s; return ret; } void interactFree(struct interact **pEl) /* Free a single dynamically allocated interact such as created * with interactLoad(). */ { struct interact *el; if ((el = *pEl) == NULL) return; freeMem(el->chrom); freeMem(el->name); freeMem(el->exp); freeMem(el->sourceChrom); freeMem(el->sourceName); freeMem(el->sourceStrand); freeMem(el->targetChrom); freeMem(el->targetName); freeMem(el->targetStrand); freez(pEl); } void interactFreeList(struct interact **pList) /* Free a list of dynamically allocated interact's */ { struct interact *el, *next; for (el = *pList; el != NULL; el = next) { next = el->next; interactFree(&el); } *pList = NULL; } void interactOutput(struct interact *el, FILE *f, char sep, char lastSep) /* Print out interact. Separate fields with sep. Follow last field with lastSep. */ { if (sep == ',') fputc('"',f); fprintf(f, "%s", el->chrom); if (sep == ',') fputc('"',f); fputc(sep,f); fprintf(f, "%u", el->chromStart); fputc(sep,f); fprintf(f, "%u", el->chromEnd); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->name); if (sep == ',') fputc('"',f); fputc(sep,f); fprintf(f, "%u", el->score); fputc(sep,f); fprintf(f, "%g", el->value); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->exp); if (sep == ',') fputc('"',f); fputc(sep,f); fprintf(f, "%u", el->color); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->sourceChrom); if (sep == ',') fputc('"',f); fputc(sep,f); fprintf(f, "%u", el->sourceStart); fputc(sep,f); fprintf(f, "%u", el->sourceEnd); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->sourceName); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->sourceStrand); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->targetChrom); if (sep == ',') fputc('"',f); fputc(sep,f); fprintf(f, "%u", el->targetStart); fputc(sep,f); fprintf(f, "%u", el->targetEnd); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->targetName); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->targetStrand); if (sep == ',') fputc('"',f); fputc(lastSep,f); } /* -------------------------------- End autoSql Generated Code -------------------------------- */ static char *interactAutoSqlString = "table interact" "\"BED5+13 interaction between two regions\"" " (" " string chrom; \"Chromosome (or contig, scaffold, etc.). For interchromosomal, use 2 records\"" " uint chromStart; \"Start position of lower region. For chromosomal, set to chromStart of this region\"" " uint chromEnd; \"End position of upper region. For interchromsomal, set to chromEnd of this region\"" " string name; \"Name or ID of item. Usually name1/name2 or name1/name2/exp or empty\"" " uint score; \"Score from 0-1000, typically derived from value\"" " double value; \"Strength of interaction or other data value\"" " string exp; \"Experiment name for filtering. Use . if not applicable\"" " uint color; \"Item color, as itemRgb in bed9. Typically based on value or exp\"" " string sourceChrom; \"Chromosome of source region (directional) or lower region. For non-directional interchromosomal, chrom of this region\"" " uint sourceStart; \"Start position of source/lower/this region\"" " uint sourceEnd; \"End position of source/lower/this region\"" " string sourceName; \"Identifier of source/lower/this region. Can be used as link to related table.\"" " string sourceStrand;\"Orientation of source/lower/this region: + or -. Use . if not applicable.\"" " string targetChrom; \"Chromosome of target region (directional) or upper region. For non-directional interchromosomal, chrom of other region\"" " uint targetStart; \"Start position of target/upper/this region\"" " uint targetEnd; \"End position of target/upper/this region\"" " string targetName; \"Identifier of target/upper/this region. Can be used as link to related table.\"" " string targetStrand;\"Orientation of target/upper/this region: + or -. Use . if not applicable.\"" " )" ; #include "asParse.h" struct asObject *interactAsObj() /* Return asObject describing fields of interact object */ { return asParseText(interactAutoSqlString); } char *interactOtherChrom(struct interact *inter) /* Get other chromosome from an interaaction. Return NULL if same chromosome */ { if (sameString(inter->sourceChrom, inter->targetChrom)) return NULL; if (inter->chromStart == inter->sourceStart) return cloneString(inter->targetChrom); return cloneString(inter->sourceChrom); } int interactRegionCenter(int start, int end) /* Return genomic location of center of region */ { return ((double)(end - start + .5) / 2) + start; } boolean interactEndsOverlap(struct interact *inter) /* Determine if there is any overlap of interact endpoints */ { if (differentString(inter->sourceChrom, inter->targetChrom)) return FALSE; if (inter->sourceStart < inter->targetStart && inter->sourceEnd < inter->targetEnd) return FALSE; if (inter->targetStart < inter->sourceStart && inter->targetEnd < inter->sourceEnd) return FALSE; return TRUE; } int interactRegionDistance(struct interact *inter) /* Return distance between region midpoints. Return -1 for other chromosome */ { if (interactOtherChrom(inter)) return -1; return abs(interactRegionCenter(inter->sourceStart, inter->sourceEnd) - interactRegionCenter(inter->targetStart, inter->targetEnd)); } int interactDistanceCmp(const void *va, const void *vb) /* Compare based on distance between region midpoints */ { struct interact *a = *((struct interact **)va); struct interact *b = *((struct interact **)vb); int aDist = interactRegionDistance(a); int bDist = interactRegionDistance(b); // cross chromosome; always larger than same chrom if (aDist < 0) { if (bDist < 0) return 0; return 1; } if (bDist < 0) return -1; // same chromosome return aDist - bDist; } struct interact *interactLoadAndValidate(char **row) /* Load a interact from row fetched with select * from interact * from database, validating fields. Dispose of this with interactFree(). * This currently differs from auto-gened only by it's handling of color field */ // TODO: more validating { struct interact *ret; AllocVar(ret); ret->chrom = cloneString(row[0]); ret->chromStart = sqlUnsigned(row[1]); ret->chromEnd = sqlUnsigned(row[2]); ret->name = cloneString(row[3]); ret->score = sqlUnsigned(row[4]); ret->value = sqlDouble(row[5]); ret->exp = cloneString(row[6]); ret->color = bedParseColor(row[7]); // handle #NNNNNN, and HTML color (as well as rgb) ret->sourceChrom = cloneString(row[8]); ret->sourceStart = sqlUnsigned(row[9]); ret->sourceEnd = sqlUnsigned(row[10]); ret->sourceName = cloneString(row[11]); ret->sourceStrand = cloneString(row[12]); ret->targetChrom = cloneString(row[13]); ret->targetStart = sqlUnsigned(row[14]); ret->targetEnd = sqlUnsigned(row[15]); ret->targetName = cloneString(row[16]); ret->targetStrand = cloneString(row[17]); return ret; } void interactRegionCenters(struct interact *inter, int *sourceCenter, int *targetCenter) /* Return genomic position of endpoint centers */ { assert(sourceCenter); assert(targetCenter); *sourceCenter = interactRegionCenter(inter->sourceStart, inter->sourceEnd); *targetCenter = interactRegionCenter(inter->targetStart, inter->targetEnd); } struct bed *interactToBed(struct interact *inter) /* Convert an interact to a BED12 (actually, BED15+label) */ { struct bed *bed = NULL; AllocVar(bed); bed->chrom = inter->chrom; bed->name = cloneString(inter->name); bed->score = inter->score; bed->itemRgb = inter->color; AllocArray(bed->blockSizes, 2); AllocArray(bed->chromStarts, 2); char *strand = "+"; if (differentString(inter->sourceChrom, inter->targetChrom)) { // inter-chromosomal bed->blockCount = 1; bed->chromStart = inter->chromStart; bed->chromEnd = inter->chromEnd; bed->blockSizes[0] = inter->chromEnd - inter->chromStart; bed->chromStarts[0] = 0; if sameString(bed->chrom, inter->targetChrom) strand = "-"; } else { // same chromosome bed->blockCount = 2; // expand extents to edges of endpoints // NOTE: this should be changed in schema defn bed->chromStart = min(inter->sourceStart, inter->targetStart); bed->chromEnd = max(inter->sourceEnd, inter->targetEnd); bed->chromStarts[0] = 0; int sourceCenter, targetCenter; interactRegionCenters(inter, &sourceCenter, &targetCenter); if (targetCenter < sourceCenter) strand = "-"; if (inter->sourceStart < inter->targetStart) { bed->blockSizes[0] = inter->sourceEnd - inter->sourceStart; bed->blockSizes[1] = inter->targetEnd - inter->targetStart; bed->chromStarts[1] = inter->targetStart - bed->chromStart; } else { bed->blockSizes[0] = inter->targetEnd - inter->targetStart; bed->blockSizes[1] = inter->sourceEnd - inter->sourceStart; bed->chromStarts[1] = inter->sourceStart - bed->chromStart; } } bed->thickStart = bed->chromStart; bed->thickEnd = bed->chromEnd; strcpy(bed->strand, strand); bed->label = bed->name; return bed; } struct interact *interactLoadAllAndValidate(char *fileName) /* Load all interact from a whitespace-separated file. * Dispose of this with interactFreeList(). */ { struct interact *list = NULL, *el; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *row[18]; while (lineFileRow(lf, row)) { el = interactLoadAndValidate(row); slAddHead(&list, el); } lineFileClose(&lf); slReverse(&list); return list; } void interactOutputCustom(struct interact *el, FILE *f, char sep, char lastSep) /* Print out interact. Separate fields with sep. Follow last field with lastSep. * Differs from auto-gen'ed by printing rgb color */ { if (sep == ',') fputc('"',f); fprintf(f, "%s", el->chrom); if (sep == ',') fputc('"',f); fputc(sep,f); fprintf(f, "%u", el->chromStart); fputc(sep,f); fprintf(f, "%u", el->chromEnd); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->name); if (sep == ',') fputc('"',f); fputc(sep,f); fprintf(f, "%u", el->score); fputc(sep,f); fprintf(f, "%g", el->value); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->exp); if (sep == ',') fputc('"',f); fputc(sep,f); // print rgb color bedOutputRgb(f, el->color); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->sourceChrom); if (sep == ',') fputc('"',f); fputc(sep,f); fprintf(f, "%u", el->sourceStart); fputc(sep,f); fprintf(f, "%u", el->sourceEnd); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->sourceName); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->sourceStrand); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->targetChrom); if (sep == ',') fputc('"',f); fputc(sep,f); fprintf(f, "%u", el->targetStart); fputc(sep,f); fprintf(f, "%u", el->targetEnd); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->targetName); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->targetStrand); if (sep == ',') fputc('"',f); fputc(lastSep,f); } void interactFixRange(struct interact *inter) /* Set values for chromStart/chromEnd based on source and target start/ends */ { int chromStart = min(inter->sourceStart, inter->targetStart); int chromEnd = max(inter->sourceEnd, inter->targetEnd); if (inter->chromStart != chromStart) { warn("Fixed chromStart: %d to %d. ", inter->chromStart, chromStart); inter->chromStart = chromStart; } if (inter->chromEnd != chromEnd) { warn("Fixed chromEnd: %d to %d. ", inter->chromEnd, chromEnd); inter->chromEnd = chromEnd; } }