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/encode/encodeExp.c src/hg/lib/encode/encodeExp.c index ac97a5a..ce2b751 100644 --- src/hg/lib/encode/encodeExp.c +++ src/hg/lib/encode/encodeExp.c @@ -1,1144 +1,1160 @@ /* encodeExp.c was originally generated by the autoSql program, which also * generated encodeExp.h and encodeExp.sql. This module links the database and * the RAM representation of objects. */ /* Copyright (C) 2014 The Regents of the University of California * See kent/LICENSE or http://genome.ucsc.edu/license/ for licensing information. */ #include "common.h" #include "linefile.h" #include "dystring.h" #include "jksql.h" #include "encode/encodeExp.h" // WARNING: autogen section has been manually updated to support fields that can be NULL void encodeExpStaticLoad(char **row, struct encodeExp *ret) /* Load a row from encodeExp table into ret. The contents of ret will * be replaced at the next call to this function. */ { ret->ix = sqlUnsigned(row[0]); ret->organism = row[1]; ret->lab = row[2]; ret->dataType = row[3]; ret->cellType = row[4]; ret->expVars = row[5]; ret->accession = row[6]; ret->updateTime = row[7]; } struct encodeExp *encodeExpLoadByQuery(struct sqlConnection *conn, char *query) /* Load all encodeExp 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 encodeExpFreeList(). */ { struct encodeExp *list = NULL, *el; struct sqlResult *sr; char **row; sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { el = encodeExpLoad(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } // Forward declaration void encodeExpSaveToDb(struct sqlConnection *conn, struct encodeExp *el, char *tableName, int updateSize); struct encodeExp *encodeExpLoad(char **row) /* Load a encodeExp from row fetched with select * from encodeExp * from database. Dispose of this with encodeExpFree(). */ { struct encodeExp *ret; AllocVar(ret); ret->ix = sqlUnsigned(row[0]); ret->organism = cloneString(row[1]); ret->lab = cloneString(row[2]); ret->dataType = cloneString(row[3]); ret->cellType = cloneString(row[4]); ret->expVars = cloneString(row[5]); ret->accession = cloneString(row[6]); ret->updateTime = cloneString(row[7]); return ret; } struct encodeExp *encodeExpLoadAll(char *fileName) /* Load all encodeExp from a whitespace-separated file. * Dispose of this with encodeExpFreeList(). */ { struct encodeExp *list = NULL, *el; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *row[8]; while (lineFileRow(lf, row)) { el = encodeExpLoad(row); slAddHead(&list, el); } lineFileClose(&lf); slReverse(&list); return list; } struct encodeExp *encodeExpLoadAllByChar(char *fileName, char chopper) /* Load all encodeExp from a chopper separated file. * Dispose of this with encodeExpFreeList(). */ { struct encodeExp *list = NULL, *el; struct lineFile *lf = lineFileOpen(fileName, TRUE); char *row[8]; while (lineFileNextCharRow(lf, chopper, row, ArraySize(row))) { el = encodeExpLoad(row); slAddHead(&list, el); } lineFileClose(&lf); slReverse(&list); return list; } struct encodeExp *encodeExpCommaIn(char **pS, struct encodeExp *ret) /* Create a encodeExp out of a comma separated string. * This will fill in ret if non-null, otherwise will * return a new encodeExp */ { char *s = *pS; if (ret == NULL) AllocVar(ret); ret->ix = sqlUnsignedComma(&s); ret->organism = sqlStringComma(&s); ret->lab = sqlStringComma(&s); ret->dataType = sqlStringComma(&s); ret->cellType = sqlStringComma(&s); ret->expVars = sqlStringComma(&s); ret->accession = sqlStringComma(&s); ret->updateTime = sqlStringComma(&s); *pS = s; return ret; } void encodeExpFree(struct encodeExp **pEl) /* Free a single dynamically allocated encodeExp such as created * with encodeExpLoad(). */ { struct encodeExp *el; if ((el = *pEl) == NULL) return; freeMem(el->organism); freeMem(el->lab); freeMem(el->dataType); freeMem(el->cellType); freeMem(el->expVars); freeMem(el->accession); freeMem(el->updateTime); freez(pEl); } void encodeExpFreeList(struct encodeExp **pList) /* Free a list of dynamically allocated encodeExp's */ { struct encodeExp *el, *next; for (el = *pList; el != NULL; el = next) { next = el->next; encodeExpFree(&el); } *pList = NULL; } void encodeExpOutput(struct encodeExp *el, FILE *f, char sep, char lastSep) /* Print out encodeExp. Separate fields with sep. Follow last field with lastSep. */ { fprintf(f, "%u", el->ix); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->organism); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->lab); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->dataType); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->cellType); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->expVars); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->accession); if (sep == ',') fputc('"',f); fputc(sep,f); if (sep == ',') fputc('"',f); fprintf(f, "%s", el->updateTime); if (sep == ',') fputc('"',f); fputc(lastSep,f); } void encodeExpJsonOutput(struct encodeExp *el, FILE *f) /* Print out encodeExp in JSON format. */ { fputc('{',f); fputc('"',f); fprintf(f,"ix"); fputc('"',f); fputc(':',f); fprintf(f, "%u", el->ix); fputc(',',f); fputc('"',f); fprintf(f,"organism"); fputc('"',f); fputc(':',f); fputc('"',f); fprintf(f, "%s", el->organism); fputc('"',f); fputc(',',f); fputc('"',f); fprintf(f,"lab"); fputc('"',f); fputc(':',f); fputc('"',f); fprintf(f, "%s", el->lab); fputc('"',f); fputc(',',f); fputc('"',f); fprintf(f,"dataType"); fputc('"',f); fputc(':',f); fputc('"',f); fprintf(f, "%s", el->dataType); fputc('"',f); fputc(',',f); fputc('"',f); fprintf(f,"cellType"); fputc('"',f); fputc(':',f); fputc('"',f); fprintf(f, "%s", el->cellType); fputc('"',f); fputc(',',f); fputc('"',f); fprintf(f,"expVars"); fputc('"',f); fputc(':',f); fputc('"',f); fprintf(f, "%s", el->expVars); fputc('"',f); fputc(',',f); fputc('"',f); fprintf(f,"accession"); fputc('"',f); fputc(':',f); fputc('"',f); fprintf(f, "%s", el->accession); fputc('"',f); fputc(',',f); fputc('"',f); fprintf(f,"updateTime"); fputc('"',f); fputc(':',f); fputc('"',f); fprintf(f, "%s", el->updateTime); fputc('"',f); fputc('}',f); } /* -------------------------------- End autoSql Generated Code -------------------------------- */ #include "hdb.h" #include "mdb.h" /* Schema in alternate format with additional properties. For each field, there is a 'get' function and an entry in the fields table. WARNING: Must parallel .sql */ /* BEGIN schema-dependent section */ void encodeExpJson(struct dyString *json, struct encodeExp *el) /* Print out encodeExp in JSON format. Manually converted from autoSql which outputs * to file pointer. */ // TODO: Extend autoSql to support in-mem version { dyStringPrintf(json, "{"); dyStringPrintf(json, "\"ix\":%u", el->ix); dyStringPrintf(json, ", "); dyStringPrintf(json, "\"organism\":\"%s\"", el->organism); dyStringPrintf(json, ", "); dyStringPrintf(json, "\"lab\":\"%s\"", el->lab); dyStringPrintf(json, ", "); dyStringPrintf(json, "\"dataType\":\"%s\"", el->dataType); dyStringPrintf(json, ", "); dyStringPrintf(json, "\"cellType\":\"%s\"", el->cellType); dyStringPrintf(json, ", "); dyStringPrintf(json, "\"expVars\":\"%s\"", el->expVars); dyStringPrintf(json, ", "); dyStringPrintf(json, "\"accession\":\"%s\"", el->accession); dyStringPrintf(json, "}"); } static char *encodeExpGetIx(struct encodeExp *exp) /* Return ix field of encodeExp */ { char buf[64]; safef(buf, sizeof(buf), "%d", exp->ix); return cloneString(buf); } static char *encodeExpGetOrganism(struct encodeExp *exp) /* Return organism field of encodeExp */ { return cloneString(exp->organism); } static char *encodeExpGetAccession(struct encodeExp *exp) /* Return accession field of encodeExp */ { return cloneString(exp->accession); } static char *encodeExpGetLab(struct encodeExp *exp) /* Return lab field of encodeExp */ { return cloneString(exp->lab); } static char *encodeExpGetDataType(struct encodeExp *exp) /* Return dataType field of encodeExp */ { return cloneString(exp->dataType); } static char *encodeExpGetCellType(struct encodeExp *exp) /* Return cellType field of encodeExp */ { return cloneString(exp->cellType); } static char *encodeExpGetExpVars(struct encodeExp *exp) /* Return expVars field of encodeExp */ { return cloneString(exp->expVars); } static char *encodeExpGetUpdateTime(struct encodeExp *exp) /* Return updateTime field of encodeExp */ { return cloneString(exp->updateTime); } typedef char * (*encodeExpGetFieldFunc)(struct encodeExp *exp); struct encodeExpField { char *name; encodeExpGetFieldFunc get; boolean required; } encodeExpField; struct encodeExpField encodeExpFields[] = { {ENCODE_EXP_FIELD_IX, &encodeExpGetIx, TRUE}, //required, set to 0 initially {ENCODE_EXP_FIELD_ORGANISM, &encodeExpGetOrganism, TRUE}, //required {ENCODE_EXP_FIELD_LAB, &encodeExpGetLab, TRUE}, //required {ENCODE_EXP_FIELD_DATA_TYPE, &encodeExpGetDataType, TRUE}, //required {ENCODE_EXP_FIELD_CELL_TYPE, &encodeExpGetCellType, TRUE}, //required {ENCODE_EXP_FIELD_FACTORS, &encodeExpGetExpVars, FALSE}, {ENCODE_EXP_FIELD_ACCESSION, &encodeExpGetAccession, FALSE}, {ENCODE_EXP_FIELD_UPDATE_TIME, &encodeExpGetUpdateTime, FALSE}, {NULL, 0, 0} }; static char *sqlCreate = "CREATE TABLE %s (\n" " ix int auto_increment, # auto-increment ID\n" " organism varchar(255) not null, # human | mouse\n" " lab varchar(255) not null, # lab name from ENCODE cv.ra\n" " dataType varchar(255) not null, # dataType from ENCODE cv.ra\n" " cellType varchar(255) not null, # cellType from ENCODE cv.ra\n" " expVars varchar(255), # var=value list of experiment-defining variables. May be NULL if none.\n" " accession varchar(255), # wgEncodeE[H|M]00000N or NULL if proposed but not yet approved\n" " updateTime timestamp default now() on update now(), # last update date-time" " #Indices\n" " PRIMARY KEY(ix)\n" ")"; /* History table approach from Peter Brawley, http://www.artfulsoftware.com */ static void encodExpAddHistoryTrigger(struct sqlConnection *conn, char *tableName, char *action) /* Create an SQL query to add a trigger to the history table for an encodeExp table */ { struct dyString *dy; char *which = NULL; if (sameString(action, "insert") || sameString(action, "update")) which = "NEW"; else if sameString(action, "delete") which = "OLD"; else errAbort("Invalid SQL trigger action: %s", action); dy = sqlDyStringCreate( "CREATE TRIGGER %s_%s AFTER %s ON %s FOR EACH ROW INSERT INTO %s%s VALUES \n" "(%s.ix, %s.organism, %s.lab, %s.dataType, %s.cellType, %s.expVars, %s.accession, NOW(), '%c', USER(), '')", tableName, action, action, tableName, tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, which, which, which, which, which, which, which, toupper(action[0])); -sqlUpdate(conn, dyStringCannibalize(&dy)); +sqlUpdate(conn, dyStringContents(dy)); +dyStringFree(&dy); } static void encodExpDropHistoryTrigger(struct sqlConnection *conn, char *tableName, char *action) /* Drop history trigger from a table */ { struct dyString *dy; if (differentString(action, "insert") && differentString(action, "update") && differentString(action, "delete")) errAbort("Invalid SQL trigger action: %s", action); dy = sqlDyStringCreate("DROP TRIGGER IF EXISTS %s_%s", tableName, action); -sqlUpdate(conn, dyStringCannibalize(&dy)); +sqlUpdate(conn, dyStringContents(dy)); +dyStringFree(&dy); } static void encodExpAddTriggers(struct sqlConnection *conn, char *tableName) { /* Add history triggers to experiment table */ encodExpAddHistoryTrigger(conn, tableName, "insert"); encodExpAddHistoryTrigger(conn, tableName, "update"); encodExpAddHistoryTrigger(conn, tableName, "delete"); } static void encodeExpDropTriggers(struct sqlConnection *conn, char *tableName) { /* Drop history triggers from experiment table */ encodExpDropHistoryTrigger(conn, tableName, "insert"); encodExpDropHistoryTrigger(conn, tableName, "update"); encodExpDropHistoryTrigger(conn, tableName, "delete"); } void encodeExpTableRename(struct sqlConnection *conn, char *tableName, char *newTableName) /* Rename table and history table, updating triggers to match */ { char oldBuf[64]; char newBuf[64]; encodeExpDropTriggers(conn, tableName); sqlRenameTable(conn, tableName, newTableName); safef(oldBuf, sizeof oldBuf, "%s%s", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX); safef(newBuf, sizeof newBuf, "%s%s", newTableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX); sqlRenameTable(conn, oldBuf, newBuf); encodExpAddTriggers(conn, newTableName); } void encodeExpTableCopy(struct sqlConnection *conn, char *tableName, char *newTableName) /* Copy table and history table, updating triggers */ { char oldBuf[64]; char newBuf[64]; sqlCopyTable(conn, tableName, newTableName); safef(oldBuf, sizeof oldBuf, "%s%s", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX); safef(newBuf, sizeof newBuf, "%s%s", newTableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX); sqlCopyTable(conn, oldBuf, newBuf); encodExpAddTriggers(conn, newTableName); } void encodeExpTableDrop(struct sqlConnection *conn, char *tableName) { /* Drop an encodeExp table */ char buf[64]; encodeExpDropTriggers(conn, tableName); sqlDropTable(conn, tableName); safef(buf, sizeof buf, "%s%s", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX); sqlDropTable(conn, buf); } #define ENCODE_EXP_HISTORY_FIELD_WHAT "action" #define ENCODE_EXP_HISTORY_FIELD_WHO "changedBy" #define ENCODE_EXP_HISTORY_FIELD_WHY "why" void encodeExpTableCreate(struct sqlConnection *conn, char *tableName) /* Create an encodeExp table */ { struct dyString *dy; dy = sqlDyStringCreate(sqlCreate, tableName); -sqlRemakeTable(conn, tableName, dyStringCannibalize(&dy)); +sqlRemakeTable(conn, tableName, dyStringContents(dy)); +dyStringFree(&dy); /* Create history table -- a clone with 3 additional columns (action, changedBy, why). * 'why' is only required for deletes * Remove auto-inc attribute on ix, and use ix and updateTime as primary key */ dy = sqlDyStringCreate("CREATE TABLE %s%s LIKE %s", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, tableName); -sqlUpdate(conn, dyStringCannibalize(&dy)); +sqlUpdate(conn, dyStringContents(dy)); +dyStringFree(&dy); dy = sqlDyStringCreate("ALTER TABLE %s%s ADD COLUMN %s CHAR(1) DEFAULT ''", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, ENCODE_EXP_HISTORY_FIELD_WHAT); -sqlUpdate(conn, dyStringCannibalize(&dy)); +sqlUpdate(conn, dyStringContents(dy)); +dyStringFree(&dy); dy = sqlDyStringCreate("ALTER TABLE %s%s ADD COLUMN %s VARCHAR(77) NOT NULL", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, ENCODE_EXP_HISTORY_FIELD_WHO); -sqlUpdate(conn, dyStringCannibalize(&dy)); +sqlUpdate(conn, dyStringContents(dy)); +dyStringFree(&dy); dy = sqlDyStringCreate("ALTER TABLE %s%s ADD COLUMN %s VARCHAR(255) NOT NULL", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, ENCODE_EXP_HISTORY_FIELD_WHY); -sqlUpdate(conn, dyStringCannibalize(&dy)); +sqlUpdate(conn, dyStringContents(dy)); +dyStringFree(&dy); + dy = sqlDyStringCreate("ALTER TABLE %s%s MODIFY COLUMN %s INT DEFAULT 0", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, ENCODE_EXP_FIELD_IX); -sqlUpdate(conn, dyStringCannibalize(&dy)); +sqlUpdate(conn, dyStringContents(dy)); +dyStringFree(&dy); dy = sqlDyStringCreate("ALTER TABLE %s%s DROP PRIMARY KEY", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX); -sqlUpdate(conn, dyStringCannibalize(&dy)); +sqlUpdate(conn, dyStringContents(dy)); +dyStringFree(&dy); //possible TODO: if we do need a primary key, will need to add a msec or autoinc column */ //alter table %s add idx int unsigned not null auto_increment, add primary key (idx); encodExpAddTriggers(conn, tableName); } int encodeExpIdMax(struct sqlConnection *conn) /* Return largest ix value */ { -return sqlQuickNum(conn, NOSQLINJ "select max(ix) from " ENCODE_EXP_TABLE); +char query[1024]; +sqlSafef(query, sizeof query, "select max(ix) from %s", ENCODE_EXP_TABLE); +return sqlQuickNum(conn, query); } /* END schema-dependent section */ struct encodeExp *encodeExpLoadAllFromTable(struct sqlConnection *conn, char *tableName) /* Load all encodeExp in table */ { -struct encodeExp *exps = NULL; - if (!sqlTableExists(conn, tableName)) return NULL; -struct dyString *dy = newDyString(0); +struct dyString *dy = dyStringNew(0); sqlDyStringPrintf(dy, "select * from %s", tableName); +struct encodeExp *exps = NULL; exps = encodeExpLoadByQuery(conn, dyStringContents(dy)); dyStringFree(&dy); return exps; } static void encodeExpAddToLatestHistory(struct sqlConnection *conn, char *table, int id, char *field, char *value) /* Add user name to history table record */ { struct dyString *dy = sqlDyStringCreate( "select max(updateTime) from %s%s where %s='%d'", table, ENCODE_EXP_HISTORY_TABLE_SUFFIX, ENCODE_EXP_FIELD_IX, id); verbose(3, "%s\n", dy->string); -char *updateTime = sqlQuickString(conn, dyStringCannibalize(&dy)); +char *updateTime = sqlQuickString(conn, dyStringContents(dy)); +dyStringFree(&dy); dy = sqlDyStringCreate( "update %s%s set %s='%s' where updateTime = '%s'", table, ENCODE_EXP_HISTORY_TABLE_SUFFIX, field, value, updateTime); verbose(3, "%s\n", dy->string); -sqlUpdate(conn, dyStringCannibalize(&dy)); +sqlUpdate(conn, dyStringContents(dy)); +dyStringFree(&dy); } static void encodeExpAddUserToLatestHistory(struct sqlConnection *conn, char *table, int id) /* Add user name to history table record */ { encodeExpAddToLatestHistory(conn, table, id, ENCODE_EXP_HISTORY_FIELD_WHO, getlogin()); } static void encodeExpAddWhyToLatestHistory(struct sqlConnection *conn, char *table, int id, char *why) /* Add comment to history table record */ { encodeExpAddToLatestHistory(conn, table, id, ENCODE_EXP_HISTORY_FIELD_WHY, why); } void encodeExpSaveToDb(struct sqlConnection *conn, struct encodeExp *el, char *tableName, int updateSize) /* Save encodeExp 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. Automatically * escapes all simple strings (not arrays of string). */ { // NOTE: Manually updated to handle NULL fields, including setting NULL (for expVars and accession) -struct dyString *update = newDyString(updateSize); +struct dyString *update = dyStringNew(updateSize); sqlDyStringPrintf(update, "insert into %s set %s=%u, %s='%s', %s='%s', %s='%s', %s='%s'", tableName, ENCODE_EXP_FIELD_IX, el->ix, ENCODE_EXP_FIELD_ORGANISM, el->organism, ENCODE_EXP_FIELD_LAB, el->lab, ENCODE_EXP_FIELD_DATA_TYPE, el->dataType, ENCODE_EXP_FIELD_CELL_TYPE, el->cellType); // Note: The sql literal NULL is not quoted in the final sql string. sqlDyStringPrintf(update, ", %s=", ENCODE_EXP_FIELD_FACTORS); if (el->expVars == NULL) - dyStringAppend(update, "NULL"); + sqlDyStringPrintf(update, "NULL"); else sqlDyStringPrintf(update, "'%s'", el->expVars); sqlDyStringPrintf(update, ", %s=", ENCODE_EXP_FIELD_ACCESSION); if (el->accession == NULL) - dyStringAppend(update, "NULL"); + sqlDyStringPrintf(update, "NULL"); else sqlDyStringPrintf(update, "'%s'", el->accession); sqlGetLock(conn, ENCODE_EXP_TABLE_LOCK); sqlUpdate(conn, update->string); int id = sqlLastAutoId(conn); encodeExpAddUserToLatestHistory(conn, tableName, id); sqlReleaseLock(conn, ENCODE_EXP_TABLE_LOCK); -freeDyString(&update); +dyStringFree(&update); } struct encodeExp *encodeExpFromMdb(struct sqlConnection *conn, char *db, struct mdbObj *mdb) /* Create an encodeExp from an ENCODE metaDb object */ { if (!mdbObjIsEncode(mdb)) errAbort("Metadata object is not from ENCODE"); struct mdbVar *edVars = mdbObjFindEncodeEdvs(conn,mdb,FALSE); // exclude vars where val=None // To use shared metaDb: //struct mdbVar *edVars = mdbObjFindEncodeEdvPairs(conn, MDB_DEFAULT_NAME, mdb, FALSE); if (edVars == NULL) { // Not willing to make these erraborts at this time. char *composite = mdbObjFindValue(mdb,MDB_VAR_COMPOSITE); if (composite == NULL) verbose(1,"MDB object '%s' does not have a composite defined in user metaDb\n",mdb->obj); else verbose(1,"Experiment Defining Variables not defined for composite '%s' in user metaDb\n",composite); return NULL; } struct encodeExp *exp = encodeExpFromMdbVars(db, edVars); mdbVarsFree(&edVars); return exp; } struct encodeExp *encodeExpFromMdbVars(char *db, struct mdbVar *vars) // Creates and returns an encodeExp struct from mdbVars, but does not touch the table // Only Experiment Defining Variables should be in the list. { struct encodeExp *exp; AllocVar(exp); exp->ix = ENCODE_EXP_IX_UNDEFINED; // This exp is not yet defined if (db == NULL) errAbort("Missing assembly"); // FIXME: centralize treatment of organism/lower-casing exp->organism = hOrganism(db); strLower(exp->organism); struct slPair *varPairs = NULL; struct mdbVar *edv = vars; for (;edv != NULL; edv = edv->next) { if (sameWord(edv->var,MDB_VAR_LAB)) { assert(exp->lab == NULL); exp->lab = cvLabNormalize((char *)(edv->val)); } else if (sameWord(edv->var,MDB_VAR_DATATYPE)) { assert(exp->dataType == NULL); exp->dataType = cloneString((char *)(edv->val)); } else if (sameWord(edv->var,MDB_VAR_CELL)) { assert(exp->cellType == NULL); exp->cellType = cloneString((char *)(edv->val)); } else { // exclude uninformative EDV's if (differentString(MDB_VAL_ENCODE_EDV_NONE, (char *)(edv->val))) slPairAdd(&varPairs, edv->var, edv->val); // No need to clone } } // Be sure we have what we need if (exp->lab == NULL || exp->dataType == NULL) { verbose(1,"Experiment Defining Variables must contain '%s' and '%s'\n", MDB_VAR_LAB,MDB_VAR_DATATYPE); // Not willing to make this an errabort at this time. return NULL; } if (exp->cellType == NULL) // Okay if no cell exp->cellType = cloneString(ENCODE_EXP_NO_CELL); if (varPairs != NULL) { slPairSortCase(&varPairs); exp->expVars = slPairListToString(varPairs,FALSE); // don't bother adding quotes since EDVs // should not have spaces slPairFreeList(&varPairs); } return exp; } struct encodeExp *encodeExpFromRa(struct hash *ra) /* Load an encodeExp from a Ra hash. */ { char *rows[ENCODEEXP_NUM_COLS]; struct encodeExp *exp; int i; AllocVar(exp); for (i = 0; i < ENCODEEXP_NUM_COLS; i++) { struct encodeExpField *fp = &encodeExpFields[i]; assert(fp->name != NULL); char *val = hashFindVal(ra, fp->name); if (val == NULL && fp->required) errAbort("Required field \'%s\' not found in .ra:\n\n%s", fp->name, hashToRaString(ra)); rows[i] = cloneString(val); } encodeExpStaticLoad(rows, exp); return exp; } struct hash *encodeExpToRaFile(struct encodeExp *exp, FILE *f) /* Create a Ra hash from an encodeExp. Print to file if non NULL */ { struct hash *ra = hashNew(0); int i; for (i = 0; i < ENCODEEXP_NUM_COLS; i++) { struct encodeExpField *fp = &encodeExpFields[i]; assert(fp->name != NULL); char *val = fp->get(exp); if (val != NULL) { hashAdd(ra, fp->name, val); if (f != NULL) fprintf(f, "%s %s\n", fp->name, val); } } if (f != NULL) fputs("\n", f); return ra; } boolean encodeExpSame(struct encodeExp *exp, struct encodeExp *exp2) /* Return TRUE if two experiments are the same */ { int i; for (i = 0; i < ENCODEEXP_NUM_COLS; i++) { struct encodeExpField *fp = &encodeExpFields[i]; assert(fp->name != NULL); if (differentStringNullOk(fp->get(exp), fp->get(exp2))) return FALSE; } return TRUE; } struct hash *encodeExpToRa(struct encodeExp *exp) /* Create a Ra hash from an encodeExp */ { return encodeExpToRaFile(exp, NULL); } struct encodeExp *encodeExpGetByIdFromTable(struct sqlConnection *conn, char *tableName, int id) /* Return experiment specified by id from named table */ { -struct dyString *query = NULL; +struct dyString *dy = sqlDyStringCreate("select * from %s where %s=\'%d\'", tableName, ENCODE_EXP_FIELD_IX, id); -query = sqlDyStringCreate("select * from %s where %s=\'%d\'", tableName, ENCODE_EXP_FIELD_IX, id); -return encodeExpLoadByQuery(conn, dyStringCannibalize(&query)); +struct encodeExp *exps = NULL; +exps = encodeExpLoadByQuery(conn, dyStringContents(dy)); +dyStringFree(&dy); +return exps; } struct encodeExp *encodeExpGetById(struct sqlConnection *conn, int id) /* Return experiment specified by id from default table */ { return encodeExpGetByIdFromTable(conn, ENCODE_EXP_TABLE, id); } struct encodeExp *encodeExpGetByAccession(struct sqlConnection *conn, char *accession) /* Return experiment specified by accession from default table */ { if (accession == NULL || strlen(accession) <= encodeExpIdOffset()) return NULL; int id = atoi(accession + encodeExpIdOffset()); return encodeExpGetById(conn, id); } static char *encodeExpMakeAccession(struct encodeExp *exp) /* Make accession string from prefix + organism + id */ { char accession[64]; char org = '\0'; if (sameString(exp->organism, ENCODE_EXP_ORGANISM_HUMAN)) org = 'H'; else if (sameString(exp->organism, ENCODE_EXP_ORGANISM_MOUSE)) org = 'M'; else errAbort("Invalid organism %s", exp->organism); safef(accession, sizeof(accession), "%s%c%06d", ENCODE_EXP_ACC_PREFIX, org, exp->ix); return cloneString(accession); } int encodeExpIdOffset() { /* Length of prefix preceding experiment ID in the accession. Prefix is defined string + 1 for org character */ return strlen(ENCODE_EXP_ACC_PREFIX) + 1; } void encodeExpAdd(struct sqlConnection *conn, char *tableName, struct encodeExp *exp) /* Add encodeExp as a new row to the table specified by tableName. */ { encodeExpSaveToDb(conn, exp, tableName, 0); } static char *encodeExpAccession(struct sqlConnection *conn, char *tableName, int id, boolean add) /* Add or remove an accession from an experiment. This is done after the experiment definition is checked for validity. */ { struct dyString *query = NULL; char *accession = NULL; struct encodeExp *exp = NULL; char queryAcc[64]; sqlGetLock(conn, ENCODE_EXP_TABLE_LOCK); exp = encodeExpGetByIdFromTable(conn, tableName, id); if (exp == NULL) errAbort("Experiment id %d not found in table %s", id, tableName); if (add) { accession = encodeExpMakeAccession(exp); safef(queryAcc, sizeof(queryAcc), "\'%s\'", accession); } else safecpy(queryAcc, sizeof(queryAcc), "NULL"); query = sqlDyStringCreate("update %s set %s=%s where %s=%d", tableName, ENCODE_EXP_FIELD_ACCESSION, queryAcc, ENCODE_EXP_FIELD_IX, exp->ix); -sqlUpdate(conn, dyStringCannibalize(&query)); +sqlUpdate(conn, query->string); encodeExpAddUserToLatestHistory(conn, tableName, id); sqlReleaseLock(conn, ENCODE_EXP_TABLE_LOCK); +dyStringFree(&query); return accession; } char *encodeExpAddAccession(struct sqlConnection *conn, char *tableName, int id) /* Add accession field to an existing "temp" experiment. This is done * after experiment is determined to be valid. * Return the accession. */ { return encodeExpAccession(conn, tableName, id, TRUE); } void encodeExpSetAccession(struct encodeExp *exp, char *tableName) /* Adds accession field to an existing experiment, updating the table. */ { struct sqlConnection *conn = sqlConnect(ENCODE_EXP_DATABASE); exp->accession = encodeExpAccession(conn, tableName, exp->ix, TRUE); sqlDisconnect(&conn); } void encodeExpRemoveAccession(struct sqlConnection *conn, char *tableName, int id) /* Revoke an experiment by removing the accession. */ { encodeExpAccession(conn, tableName, id, FALSE); } boolean encodeExpIsAccessioned(struct encodeExp *exp) /* Determine if experiment has an accession (not unaccessioned or deaccessioned) */ { return encodeExpGetAccession(exp) != NULL; } void encodeExpRemove(struct sqlConnection *conn, char *tableName, struct encodeExp *exp, char *why) /* Delete row containing experiment from encodeExp. * WARNING: This is a management function, not for regular use. Accession must * not be present. In general, experiments should be reviewed before adding to table * rather than added and removed if problematic. */ { char query[256]; /* must match entry in table in all ways */ struct encodeExp *exp2 = encodeExpGetByIdFromTable(conn, tableName, exp->ix); if (encodeExpSame(exp, exp2)) { sqlSafef(query, sizeof(query), "delete from %s where %s=%d", tableName, ENCODE_EXP_FIELD_IX, exp->ix); sqlGetLock(conn, ENCODE_EXP_TABLE_LOCK); sqlUpdate(conn, query); encodeExpAddUserToLatestHistory(conn, tableName, exp->ix); encodeExpAddWhyToLatestHistory(conn, tableName, exp->ix, why); sqlReleaseLock(conn, ENCODE_EXP_TABLE_LOCK); } } boolean encodeExpIsFieldVar(char *var) /* Return true if var is a field in schema -- one of standard set (not an expVar) */ { if (var == NULL) return FALSE; return (sameString(var, ENCODE_EXP_FIELD_LAB) || sameString(var, ENCODE_EXP_FIELD_DATA_TYPE) || sameString(var, ENCODE_EXP_FIELD_CELL_TYPE)); } char *encodeExpGetVar(struct encodeExp *exp, char *var) /* Return value of an expVar, or NULL if not found */ { struct slPair *vars = slPairListFromString(exp->expVars, FALSE); return slPairFindVal(vars, var); } char *encodeExpGetField(struct encodeExp *exp, char *var) /* Return value of a field, whether part of schema or an expVar */ { if (var == NULL) return FALSE; int i; for (i = 0; i < ENCODEEXP_NUM_COLS; i++) { struct encodeExpField *fp = &encodeExpFields[i]; assert(fp->name != NULL); if (sameString(fp->name, var)) return fp->get(exp); } // not a schema field, it may be an expVar return encodeExpGetVar(exp, var); } static boolean cvTermIsValid(char *type, char *val) /* Determine if term is valid for CV type of term * TODO: This really belongs in cv.ra, but this limited version used just by encodeExp * For now, addng special cases as needed -- e.g. allow control term for antibody type */ { if (cvOneTermHash(type, val)) return TRUE; if (sameString(type, CV_TERM_ANTIBODY)) { if (cvOneTermHash(CV_TERM_CONTROL, val)) return TRUE; } return FALSE; } void encodeExpUpdate(struct sqlConnection *conn, char *tableName, int id, char *var, char *newVal, char *oldVal) /* Update field in encodeExp or var in expVars, identified by id with value. * If oldVal is non-NULL, verify it matches experiment, as a safety check. * OldVal of ENCODE_EXP_NO_VAR allows adding expVar. * TODO: Setting newVal to ENCODE_EXP_NO_VAR will remove expVar. * Abort if experiment is accessioned (must deaccession first) */ { char *val = NULL; struct dyString *dy = NULL; char *type = (char *)cvTermNormalized(var); if (type == NULL) errAbort("Attempt to update encodeExp experiment with unknown CV type %s", var); if (cvTermIsCvDefined(type)) { verbose(1, " var %s is cv defined\n", type); /* verify new value is valid term in CV */ if (!cvTermIsValid(type, newVal)) errAbort("Attempt to update encodeExp experiment with unknown CV term %s of type %s", newVal, var); } struct encodeExp *exp = encodeExpGetByIdFromTable(conn, tableName, id); if (exp == NULL) errAbort("Id %d not found in experiment table %s", id, tableName); if (exp->accession) errAbort("Id %d in table %s has accession", id, tableName); if (encodeExpIsFieldVar(var)) { /* check if old value matches */ if (oldVal) { struct hash *expRa = encodeExpToRa(exp); val = hashFindVal(expRa, var); if (val == NULL) errAbort("Field %s not found in id %d from table %s", var, id, tableName); if (differentString(val, oldVal)) errAbort("Mismatch: id %d has %s=%s, not %s in table %s", id, var, val, oldVal, tableName); } dy = sqlDyStringCreate("update %s set %s=\'%s\' ", tableName, var, newVal); } else { /* must be an expVar -- extract all expVars for this experiment */ struct slPair *varPairs = slPairListFromString(exp->expVars,FALSE); struct slPair *pair = slPairFind(varPairs, var); if (pair != NULL) { /* change the designated var */ if (oldVal) { // TODO: remove expVar if newVal == None val = (char *)pair->val; if (differentString(val, oldVal)) errAbort("Mismatch: id %d has %s=%s, not %s in table %s", id, var, val, oldVal, tableName); } pair->val = newVal; } else { // this var not found in this experiment - add new var if (oldVal && differentString(oldVal, ENCODE_EXP_NO_VAR)) { errAbort("Attempt to change expVar %s from value %s not found in experiment %d", var, oldVal, id); } verbose(3, "Adding %s=%s to experiment %d\n", var, newVal, id); slPairAdd(&varPairs, var, newVal); slPairSortCase(&varPairs); verbose(1, "WARNING: not verifying %s is valid expVar for this experiment\n", var); } char *expVars = slPairListToString(varPairs, FALSE); dy = sqlDyStringCreate("update %s set %s=\'%s\' ", tableName, ENCODE_EXP_FIELD_FACTORS, expVars); } -dyStringPrintf(dy, " where ix=%d", id); +sqlDyStringPrintf(dy, " where ix=%d", id); sqlGetLock(conn, ENCODE_EXP_TABLE_LOCK); -sqlUpdate(conn, dyStringCannibalize(&dy)); +sqlUpdate(conn, dyStringContents(dy)); encodeExpAddUserToLatestHistory(conn, tableName, id); sqlReleaseLock(conn, ENCODE_EXP_TABLE_LOCK); +dyStringFree(&dy); } char *encodeExpKey(struct encodeExp *exp) /* Create a hash key from an encodeExp */ { -struct dyString *dy = newDyString(0); +struct dyString *dy = dyStringNew(0); dyStringPrintf(dy, "lab:%s dataType:%s cellType:%s", exp->lab, exp->dataType, exp->cellType); if (exp->expVars != NULL) dyStringPrintf(dy, " expVars:%s", exp->expVars); return dyStringCannibalize(&dy); } char *encodeExpVars(struct encodeExp *exp) // Create a string of all experiment defining vars and vals as "lab=UW dataType=ChipSeq ..." // WARNING: May be missing var=None if the var was added after composite had defined exps. { -struct dyString *dy = newDyString(0); +struct dyString *dy = dyStringNew(0); dyStringPrintf(dy, "%s=%s %s=%s", MDB_VAR_LAB, exp->lab, MDB_VAR_DATATYPE, exp->dataType ); if (exp->cellType != NULL) dyStringPrintf(dy, " %s=%s", MDB_VAR_CELL, exp->cellType); if (exp->expVars != NULL) dyStringPrintf(dy, " %s", exp->expVars); return dyStringCannibalize(&dy); } struct encodeExp *encodeExpGetFromTable(char *organism, char *lab, char *dataType, char *cell, struct slPair *varPairs, char *table) /* Return experiments matching args in named experiment table. * Organism, Lab and DataType must be non-null */ { -struct encodeExp *exps = NULL; - if (organism == NULL || lab == NULL || dataType == NULL) errAbort("Need organism, lab, and dataType to query experiment table"); if (cell == NULL) cell = ENCODE_EXP_NO_CELL; struct sqlConnection *conn = sqlConnect(ENCODE_EXP_DATABASE); struct dyString *dy = sqlDyStringCreate( "select * from %s where %s=\'%s\' and %s=\'%s\' and %s=\'%s\' and %s=\'%s\' and %s", table, ENCODE_EXP_FIELD_ORGANISM, organism, ENCODE_EXP_FIELD_LAB, lab, ENCODE_EXP_FIELD_DATA_TYPE, dataType, ENCODE_EXP_FIELD_CELL_TYPE, cell, ENCODE_EXP_FIELD_FACTORS); /* construct expVars string var=val from pairs */ if (varPairs == NULL) - dyStringAppend(dy, " is NULL"); + sqlDyStringPrintf(dy, " is NULL"); else { - dyStringAppend(dy, "="); - dyStringQuoteString(dy, '\'', slPairListToString(varPairs, FALSE)); + sqlDyStringPrintf(dy, "= '%s'", slPairListToString(varPairs, FALSE)); } verbose(4, "query: %s\n", dy->string); -exps = encodeExpLoadByQuery(conn, dyStringCannibalize(&dy)); +struct encodeExp *exps = NULL; +exps = encodeExpLoadByQuery(conn, dyStringContents(dy)); sqlDisconnect(&conn); +dyStringFree(&dy); return exps; } struct encodeExp *encodeExpGet(char *organism, char *lab, char *dataType, char *cell, struct slPair *varPairs) /* Return experiments matching args in default experiment table. * Organism, Lab and DataType must be non-null */ { return encodeExpGetFromTable(organism, lab, dataType, cell, varPairs, ENCODE_EXP_TABLE); } struct encodeExp *encodeExpGetByMdbVarsFromTable(char *db, struct mdbVar *vars, char *table) /* Return experiments by looking up mdb var list from the named experiment table */ { struct encodeExp *exp = encodeExpFromMdbVars(db,vars); // don't expect quoted EDVs which should always be simple tokens. struct slPair *edvVars = slPairListFromString(exp->expVars,FALSE); struct encodeExp *expFound = encodeExpGetFromTable(exp->organism,exp->lab,exp->dataType,exp->cellType,edvVars,table); // No longer needed encodeExpFree(&exp); if (edvVars != NULL) slPairFreeValsAndList(&edvVars); return expFound; } struct encodeExp *encodeExpGetByMdbVars(char *db, struct mdbVar *vars) /* Return experiments by looking up mdb var list from the default experiment table */ { return encodeExpGetByMdbVarsFromTable(db, vars, ENCODE_EXP_TABLE); } struct encodeExp *encodeExpGetOrCreateByMdbVarsFromTable(char *db, struct mdbVar *vars, char *table) // Return experiment looked up or created from the mdb var list from the named experiment table. { struct encodeExp *exp = encodeExpFromMdbVars(db,vars); // don't expect quoted EDVs which should always be simple tokens. struct slPair *edvVars = slPairListFromString(exp->expVars,FALSE); struct encodeExp *expFound = encodeExpGetFromTable(exp->organism,exp->lab,exp->dataType,exp->cellType,edvVars,table); if (expFound == NULL) { struct sqlConnection *conn = sqlConnect(ENCODE_EXP_DATABASE); encodeExpAdd(conn, table, exp); sqlDisconnect(&conn); expFound = encodeExpGetFromTable(exp->organism,exp->lab,exp->dataType,exp->cellType, edvVars,table); } encodeExpFree(&exp); slPairFreeValsAndList(&edvVars); return expFound; } int encodeExpExists(char *db, struct mdbVar *vars) /* Return TRUE if at least one experiment exists for these vars */ { struct encodeExp *exp = encodeExpGetByMdbVars(db, vars); int found = (exp != NULL); freez(&exp); return found; } char *encodeExpGetAccessionByMdbVars(char *db, struct mdbVar *vars) /* Return accession of (first) experiment matching vars, or NULL if not found */ { struct encodeExp *exp = encodeExpGetByMdbVars(db, vars); char *acc = encodeExpGetAccession(exp); freez(&exp); return acc; }