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/barChartBed.c src/hg/lib/barChartBed.c index 380400c..7751746 100644 --- src/hg/lib/barChartBed.c +++ src/hg/lib/barChartBed.c @@ -1,362 +1,362 @@ /* barChartBed.c was originally generated by the autoSql program, which also * generated barChartBed.h and barChartBed.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 "barChartBed.h" char *barChartBedCommaSepFieldNames = "chrom,chromStart,chromEnd,name,score,strand,name2,expCount,expScores,_dataOffset,_dataLen"; #ifdef NOT_SAFE struct barChartBed *barChartBedLoadByQuery(struct sqlConnection *conn, char *query) /* Load all barChartBed 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 barChartBedFreeList(). */ { struct barChartBed *list = NULL, *el; struct sqlResult *sr; char **row; sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { el = barChartBedLoad(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } #endif void barChartBedSaveToDb(struct sqlConnection *conn, struct barChartBed *el, char *tableName, int updateSize) /* Save barChartBed 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); char *expScoresArray; expScoresArray = sqlFloatArrayToString(el->expScores, el->expCount); sqlDyStringPrintf(update, "insert into %s values ( '%s',%u,%u,'%s',%u,'%s','%s',%u,'%s',%lld,%d)", tableName, el->chrom, el->chromStart, el->chromEnd, el->name, el->score, el->strand, el->name2, el->expCount, expScoresArray , el->_dataOffset, el->_dataLen); sqlUpdate(conn, update->string); -freeDyString(&update); +dyStringFree(&update); freez(&expScoresArray); } #ifdef NOT_SAFE struct barChartBed *barChartBedLoad(char **row) /* Load a barChartBed from row fetched with select * from barChartBed * from database. Dispose of this with barChartBedFree(). */ { struct barChartBed *ret; AllocVar(ret); ret->expCount = sqlUnsigned(row[7]); 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]); safecpy(ret->strand, sizeof(ret->strand), row[5]); ret->name2 = cloneString(row[6]); { int sizeOne; sqlFloatDynamicArray(row[8], &ret->expScores, &sizeOne); assert(sizeOne == ret->expCount); } ret->_dataOffset = sqlLongLong(row[9]); ret->_dataLen = sqlSigned(row[10]); return ret; } struct barChartBed *barChartBedLoadAll(char *fileName) /* Load all barChartBed from a whitespace-separated file. * Dispose of this with barChartBedFreeList(). */ { struct barChartBed *list = NULL, *el; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *row[11]; while (lineFileRow(lf, row)) { el = barChartBedLoad(row); slAddHead(&list, el); } lineFileClose(&lf); slReverse(&list); return list; } struct barChartBed *barChartBedLoadAllByChar(char *fileName, char chopper) /* Load all barChartBed from a chopper separated file. * Dispose of this with barChartBedFreeList(). */ { struct barChartBed *list = NULL, *el; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *row[11]; while (lineFileNextCharRow(lf, chopper, row, ArraySize(row))) { el = barChartBedLoad(row); slAddHead(&list, el); } lineFileClose(&lf); slReverse(&list); return list; } #endif struct barChartBed *barChartBedCommaIn(char **pS, struct barChartBed *ret) /* Create a barChartBed out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new barChartBed */ { 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); sqlFixedStringComma(&s, ret->strand, sizeof(ret->strand)); ret->name2 = sqlStringComma(&s); ret->expCount = sqlUnsignedComma(&s); { int i; s = sqlEatChar(s, '{'); AllocArray(ret->expScores, ret->expCount); for (i=0; i<ret->expCount; ++i) { ret->expScores[i] = sqlFloatComma(&s); } s = sqlEatChar(s, '}'); s = sqlEatChar(s, ','); } ret->_dataOffset = sqlLongLongComma(&s); ret->_dataLen = sqlSignedComma(&s); *pS = s; return ret; } void barChartBedFree(struct barChartBed **pEl) /* Free a single dynamically allocated barChartBed such as created * with barChartBedLoad(). */ { struct barChartBed *el; if ((el = *pEl) == NULL) return; freeMem(el->chrom); freeMem(el->name); freeMem(el->name2); freeMem(el->expScores); freez(pEl); } void barChartBedFreeList(struct barChartBed **pList) /* Free a list of dynamically allocated barChartBed's */ { struct barChartBed *el, *next; for (el = *pList; el != NULL; el = next) { next = el->next; barChartBedFree(&el); } *pList = NULL; } void barChartBedOutput(struct barChartBed *el, FILE *f, char sep, char lastSep) /* Print out barChartBed. 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); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->strand); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->name2); if (sep == ',') fputc('"',f); fputc(sep,f); fprintf(f, "%u", el->expCount); fputc(sep,f); { int i; if (sep == ',') fputc('{',f); for (i=0; i<el->expCount; ++i) { fprintf(f, "%g", el->expScores[i]); fputc(',', f); } if (sep == ',') fputc('}',f); } fputc(sep,f); fprintf(f, "%lld", el->_dataOffset); fputc(sep,f); fprintf(f, "%d", el->_dataLen); fputc(lastSep,f); } /* -------------------------------- End autoSql Generated Code -------------------------------- */ #include "basicBed.h" void barChartBedCreateTable(struct sqlConnection *conn, char *table) /* Create barChart format table of given name. */ { char query[1024]; sqlSafef(query, sizeof(query), "CREATE TABLE %s (\n" " chrom varchar(255) not null, # Reference sequence chromosome or scaffold\n" " chromStart int unsigned not null, # Start position in chromosome\n" " chromEnd int unsigned not null, # End position in chromosome\n" " name varchar(255) not null, # Identifier\n" " score int unsigned not null, # Score from 0-1000\n" " strand char(1) not null, # + or - for strand\n" " name2 varchar(255) not null, # Alternative name for item\n" " expCount int unsigned not null, # Number of experiment values\n" " expScores longblob not null, # Comma separated list of experiment scores\n" " _dataOffset bigint not null, # Offset of sample data in data matrix file, for boxplot on details page\n" " _dataLen int not null, # Length of sample data row in data matrix file\n" " INDEX(name),\n" " INDEX(chrom(20), chromStart)\n" ")\n", table); sqlRemakeTable(conn, table, query); } static char *barChartAutoSqlString = "table barChartBed" "\"BED6+5 with additional fields for category count and median values, and sample matrix fields\"" " (" " string chrom; \"Reference sequence chromosome or scaffold\"" " uint chromStart; \"Start position in chromosome\"" " uint chromEnd; \"End position in chromosome\"" " string name; \"Name or ID of item, ideally both human readable and unique\"" " uint score; \"Score from 0-1000, typically derived from total of median value from all categories\"" " char[1] strand; \"+ or - for strand. Use . if not applicable\"" " string name2; \"Alternative name for item\"" " uint expCount; \"Number of categories\"" " float[expCount] expScores; \"Comma separated list of category values\"" " bigint _dataOffset; \"Offset of sample data in data matrix file, for boxplot on details page\"" " int _dataLen; \"Length of sample data row in data matrix file\"" " )" ; struct asObject *barChartAsObj() /* Return asObject describing fields of barChart database table (includes bin) */ { return asParseText(barChartAutoSqlString); } struct bed *barChartSimpleBedLoad(char **row) /* Load a bed from row containing barChart bed fields. * This reuses autoSql barChartBedLoad, but with a full-size bed. Dispose of this with bedFree() */ { struct bed *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]); safecpy(ret->strand, sizeof(ret->strand), row[5]); // name2 is in row[6] ret->expCount = sqlUnsigned(row[7]); int sizeOne; sqlFloatDynamicArray(row[8], &ret->expScores, &sizeOne); if (sizeOne != ret->expCount) { warn("expScores mismatch"); return NULL; } return ret; } struct barChartBed *barChartBedLoadOptionalOffsets(char **row, boolean hasOffsets) /* Load a barChartBed from row fetched with select * from barChartBed * from database or file. Also supports schema lacking file offet and length for details. . Dispose of this with barChartBedFree(). */ { struct barChartBed *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]); safecpy(ret->strand, sizeof(ret->strand), row[5]); ret->name2 = cloneString(row[6]); ret->expCount = sqlUnsigned(row[7]); int sizeOne; sqlFloatDynamicArray(row[8], &ret->expScores, &sizeOne); if (sizeOne != ret->expCount) { warn("expScores mismatch"); return NULL; } if (hasOffsets) { ret->_dataOffset = sqlLongLong(row[9]); ret->_dataLen = sqlSigned(row[10]); } return ret; } float barChartTotalValue(struct barChartBed *bed) /* Return total of all category values */ { int i; float sum = 0.0; for (i=0; i<bed->expCount; i++) sum += bed->expScores[i]; return sum; } float barChartMaxValue(struct barChartBed *bed, int *categIdRet) /* Return value and id of category with highest value for this item */ { int i; float maxScore = 0.0; if (!categIdRet) return maxScore; for (i=0; i<bed->expCount; i++) { float score = bed->expScores[i]; if (score > maxScore) { maxScore = score; *categIdRet = i; } } return maxScore; }