080a160c7b9595d516c9c70e83689a09b60839d0 galt Mon Jun 3 12:16:53 2013 -0700 fix SQL Injection diff --git src/hg/lib/mdb.c src/hg/lib/mdb.c index e1f9ec2..0e810f2 100644 --- src/hg/lib/mdb.c +++ src/hg/lib/mdb.c @@ -38,64 +38,39 @@ while ((row = sqlNextRow(sr)) != NULL) { el = mdbLoad(row); slAddHead(&list, el); } slReverse(&list); sqlFreeResult(&sr); return list; } void mdbSaveToDb(struct sqlConnection *conn, struct mdb *el, char *tableName, int updateSize) /* Save mdb 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. Note that strings must be escaped to allow insertion into the database. - * For example "autosql's features include" --> "autosql\'s features include" - * If worried about this use mdbSaveToDbEscaped() */ + * inserted as NULL. Strings are automatically escaped to allow insertion into the database. */ { struct dyString *update = newDyString(updateSize); -dyStringPrintf(update, "insert into %s set obj='%s', var='%s', val='%s'", +sqlDyStringPrintf(update, "insert into %s set obj='%s', var='%s', val='%s'", tableName, el->obj, el->var, el->val); sqlUpdate(conn, update->string); freeDyString(&update); } -void mdbSaveToDbEscaped(struct sqlConnection *conn, struct mdb *el, char *tableName, int updateSize) -/* Save mdb 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) but may be slower than mdbSaveToDb(). - * For example automatically copies and converts: - * "autosql's features include" --> "autosql\'s features include" - * before inserting into database. */ -{ -struct dyString *update = newDyString(updateSize); -char *obj, *var, *val; -obj = sqlEscapeString(el->obj); -var = sqlEscapeString(el->var); -val = sqlEscapeString(el->val); - -dyStringPrintf(update, "insert into %s set obj='%s', var='%s', val='%s'", - tableName, obj, var, val); -sqlUpdate(conn, update->string); -freeDyString(&update); -freez(&obj); -freez(&var); -freez(&val); -} struct mdb *mdbLoad(char **row) /* Load a mdb from row fetched with select * from mdb * from database. Dispose of this with mdbFree(). */ { struct mdb *ret; AllocVar(ret); ret->obj = cloneString(row[0]); ret->var = cloneString(row[1]); ret->val = cloneString(row[2]); return ret; } struct mdb *mdbLoadAll(char *fileName) @@ -938,31 +913,31 @@ "# Contains metadata for a table, file or other objects.\n" "CREATE TABLE %s (\n" " obj varchar(255) not null, # Object name or ID.\n" " var varchar(255) not null, # Metadata variable name.\n" " val varchar(2048) not null, # Metadata value.\n" " #Indices\n" " PRIMARY KEY(obj,var),\n" " INDEX varKey (var),\n" " INDEX valKey (val(64))\n" ")"; if (sqlTableExists(conn,tblName)) verbose(2, "Table '%s' already exists. It will be recreated.\n",tblName); struct dyString *dy = newDyString(512); -dyStringPrintf(dy, sqlCreate, tblName); +sqlDyStringPrintf(dy, sqlCreate, tblName); verbose(2, "Requesting table creation:\n%s;\n", dyStringContents(dy)); if (!testOnly) sqlRemakeTable(conn, tblName, dyStringContents(dy)); dyStringFree(&dy); } #define HG_CONF_SANDBOX_MDB "db.metaDb" #define HG_CONF_SANDBOX_TDB "db.trackDb" #define SANDBOX_TDB_ROOT "trackDb" static char*mdbTableNamePreferSandbox() // returns the mdb table name or NULL if conn supplied but the table doesn't exist { char *table = cfgOption(HG_CONF_SANDBOX_MDB); if (table != NULL) @@ -1056,109 +1031,109 @@ else if (!sqlTableExists(conn,tableName)) errAbort("mdbObjsSetToDb attempting to update non-existent table named '%s'.\n",tableName); // Table specific lock (over-cautious, since most work is done on sandbox tables) char lock[64]; safef(lock,sizeof lock,"lock_%s",tableName); sqlGetLock(conn, lock); for (mdbObj = mdbObjs;mdbObj != NULL; mdbObj = mdbObj->next) { // Handle delete requests first if (mdbObj->deleteThis) { if (mdbObj->vars == NULL) // deletes all { - safef(query, sizeof(query),"%s where obj = '%s'",tableName,mdbObj->obj); + sqlSafef(query, sizeof(query),"%s where obj = '%s'",sqlCheckTableName(tableName),sqlCheckQuotedLiteral(mdbObj->obj)); // NOSQLINJ int delCnt = sqlRowCount(conn,query); if (delCnt>0) { - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "delete from %s where obj = '%s'",tableName,mdbObj->obj); verbose(2, "Requesting delete of %d rows:\n\t%s;\n",delCnt, query); if (!testOnly) sqlUpdate(conn, query); count += delCnt; } } else // deletes selected vars { for (mdbVar = mdbObj->vars;mdbVar != NULL; mdbVar = mdbVar->next) { - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select obj from %s where obj = '%s' and var = '%s'", tableName,mdbObj->obj,mdbVar->var); if (sqlExists(conn,query)) { - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "delete from %s where obj = '%s' and var = '%s'", tableName,mdbObj->obj,mdbVar->var); verbose(2, "Requesting delete of 1 row:\n\t%s;\n",query); if (!testOnly) sqlUpdate(conn, query); count++; } } } continue; // Done with this mdbObj } else if (replace) // If replace then clear out deadwood before inserting new vars { - safef(query, sizeof(query),"%s where obj = '%s'",tableName,mdbObj->obj); + sqlSafef(query, sizeof(query),"%s where obj = '%s'",sqlCheckTableName(tableName),sqlCheckQuotedLiteral(mdbObj->obj)); // NOSQLINJ int delCnt = sqlRowCount(conn,query); if (delCnt>0) { - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "delete from %s where obj = '%s'",tableName,mdbObj->obj); verbose(2, "Requesting replacement of %d rows:\n\t%s;\n",delCnt, query); if (!testOnly) sqlUpdate(conn, query); count += delCnt; } } // Now it is time for update or add! for (mdbVar = mdbObj->vars;mdbVar != NULL; mdbVar = mdbVar->next) { stripEnclosingDoubleQuotes(mdbVar->val); // Ensures values are stripped of enclosing quotes // Be sure to check for var existence first, then update if (!replace) { struct mdbObj *objExists = mdbObjQueryByObj(conn,tableName,mdbObj->obj,mdbVar->var); if (objExists) { if (differentString(mdbVar->val,objExists->vars->val)) { - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "update %s set val = '%s' where obj = '%s' and var = '%s'", - tableName, sqlEscapeString(mdbVar->val), mdbObj->obj, mdbVar->var); + tableName, mdbVar->val, mdbObj->obj, mdbVar->var); verbose(2, "Requesting update of 1 row:\n\t%s;\n",query); if (!testOnly) sqlUpdate(conn, query); count++; } mdbObjsFree(&objExists); continue; // The object was found/updated so done with it } } // Finally ready to insert new vars - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "insert into %s set obj='%s', var='%s', val='%s'", - tableName,mdbObj->obj,mdbVar->var,sqlEscapeString(mdbVar->val)); + tableName,mdbObj->obj,mdbVar->var,mdbVar->val); verbose(2, "Requesting insert of one row:\n\t%s;\n",query); if (!testOnly) sqlUpdate(conn, query); count++; } } sqlReleaseLock(conn, lock); return count; } int mdbObjsLoadToDb(struct sqlConnection *conn,char *tableName,struct mdbObj *mdbObjs, boolean testOnly) // Adds mdb Objs with minimal error checking { int count = 0; @@ -1167,91 +1142,91 @@ if (tableName == NULL) tableName = mdbTableName(conn,TRUE); // defaults to sandbox, if exists, else MDB_DEFAULT_NAME else if (!sqlTableExists(conn,tableName)) errAbort("mdbObjsLoadToDb attempting to load non-existent table named '%s'.\n",tableName); assert(mdbObjs != NULL); // If this is the case, then be vocal #define MDB_TEMPORARY_TAB_FILE "temporaryMdb.tab" long lastTime = 0; count = mdbObjPrintToTabFile(mdbObjs,MDB_TEMPORARY_TAB_FILE); verboseTime(2, "past mdbObjPrintToTabFile()"); // Disable keys in hopes of speeding things up. No danger since it only disables non-unique keys char query[8192]; -safef(query, sizeof(query),"alter table %s disable keys",tableName); +sqlSafef(query, sizeof(query),"alter table %s disable keys",tableName); sqlUpdate(conn, query); // Quick? load sqlLoadTabFile(conn, MDB_TEMPORARY_TAB_FILE, tableName, SQL_TAB_FILE_WARN_ON_ERROR|SQL_TAB_FILE_WARN_ON_WARN); verboseTime(2, "past sqlLoadTabFile()"); // Enabling the keys again -safef(query, sizeof(query),"alter table %s enable keys",tableName); +sqlSafef(query, sizeof(query),"alter table %s enable keys",tableName); sqlUpdate(conn, query); verboseTime(2, "Past alter table"); //unlink(MDB_TEMPORARY_TAB_FILE); verbose(0,"%04ldms - Done loading mdb with 'LOAD DATA INFILE' mysql command.\n", (clock1000() - lastTime)); return count; } // ------------------ Querys ------------------- struct mdbObj *mdbObjQuery(struct sqlConnection *conn,char *table,struct mdbObj *mdbObj) // Query the metadata table by obj and optional vars and vals in metaObj struct. // If mdbObj is NULL query all. Returns new mdbObj struct fully populated & sorted in obj,var order { // select obj,var,val where (var= [and val=]) or ([var= and] val=) order by obj,var boolean buildHash = TRUE; if (table == NULL) table = mdbTableName(conn,TRUE); // defaults to sandbox, if exists, else MDB_DEFAULT_NAME else if (!sqlTableExists(conn,table)) return NULL; struct dyString *dy = newDyString(4096); -dyStringPrintf(dy, "select obj,var,val from %s", table); +sqlDyStringPrintf(dy, "select obj,var,val from %s", table); if (mdbObj != NULL && mdbObj->obj != NULL) { - dyStringPrintf(dy, " where obj %s '%s'", + sqlDyStringPrintf(dy, " where obj %-s '%s'", (strchr(mdbObj->obj,'%') ? "like" : "="),mdbObj->obj); struct mdbVar *mdbVar; for (mdbVar=mdbObj->vars;mdbVar!=NULL;mdbVar=mdbVar->next) { if (mdbVar==mdbObj->vars) dyStringPrintf(dy, " and ("); else dyStringPrintf(dy, " or "); if (mdbVar->var != NULL) { if (mdbVar->val != NULL) dyStringPrintf(dy, "("); - dyStringPrintf(dy, "var %s '%s'", + sqlDyStringPrintf(dy, "var %-s '%s'", (strchr(mdbVar->var,'%') ? "like" : "="),mdbVar->var); } if (mdbVar->val != NULL) { if (mdbVar->var != NULL) dyStringPrintf(dy, " and "); - dyStringPrintf(dy, "val %s '%s'", - (strchr(mdbVar->val,'%') ? "like" : "="), sqlEscapeString(mdbVar->val)); + sqlDyStringPrintf(dy, "val %-s '%s'", + (strchr(mdbVar->val,'%') ? "like" : "="), mdbVar->val); if (mdbVar->var != NULL) dyStringPrintf(dy, ")"); } if (mdbVar->var == NULL && mdbVar->val) errAbort("mdbObjQuery has empty mdbVar struct.\n"); buildHash = FALSE; // too few variables } if (mdbObj->vars != NULL) dyStringPrintf(dy, ")"); } verbose(2, "Requesting mdbObjQuery query:\n\t%s;\n",dyStringContents(dy)); struct mdb *mdb = mdbLoadByQuery(conn, dyStringCannibalize(&dy)); slSort(&mdb,mdbCmp); // Use internal sort instead of ORDER BY because of mysql inefficiency struct mdbObj *mdbObjs = mdbObjsLoadFromMemory(&mdb,buildHash); @@ -1274,77 +1249,77 @@ struct mdbByVar *mdbByVarsQuery(struct sqlConnection *conn,char *table,struct mdbByVar *mdbByVars) // Query the metadata table by one or more var=val pairs to find the distinct set of objs // that satisfy ANY conditions. // Returns new mdbByVar struct fully populated and sorted in var,val,obj order. { // select obj,var,val where (var= [and val in (val1,val2)]) // or (var= [and val in (val1,val2)]) order by var,val,obj if (table == NULL) table = mdbTableName(conn,TRUE); // defaults to sandbox, if exists, else MDB_DEFAULT_NAME else if (!sqlTableExists(conn,table)) return NULL; struct dyString *dy = newDyString(4096); -dyStringPrintf(dy, "select obj,var,val from %s", table); +sqlDyStringPrintf(dy, "select obj,var,val from %s", table); struct mdbByVar *rootVar; for (rootVar=mdbByVars;rootVar!=NULL;rootVar=rootVar->next) { if (rootVar==mdbByVars) dyStringPrintf(dy, " where (var "); else dyStringPrintf(dy, " OR (var "); if (rootVar->notEqual && rootVar->vals == NULL) dyStringPrintf(dy, "%s",strchr(rootVar->var,'%')?"NOT ":"!"); // one of: "NOT LIKE". "!=" or "NOT EXISTS" if (rootVar->vals != NULL && rootVar->vals->val != NULL && strlen(rootVar->vals->val) > 0) { - dyStringPrintf(dy, "%s '%s'", + sqlDyStringPrintf(dy, "%-s '%s'", (strchr(rootVar->var,'%') ? "like" : "="), rootVar->var); } else dyStringPrintf(dy, "EXISTS"); struct mdbLimbVal *limbVal; boolean multiVals = FALSE; for (limbVal=rootVar->vals;limbVal!=NULL;limbVal=limbVal->next) { if (limbVal->val == NULL || strlen(limbVal->val) < 1) continue; if (!multiVals) { dyStringPrintf(dy, " and val "); if (rootVar->notEqual) dyStringPrintf(dy, "%s",strchr(limbVal->val,'%')?"NOT ":"!"); if (limbVal->next == NULL) // only one val { - dyStringPrintf(dy, "%s '%s'", - (strchr(limbVal->val,'%')?"like":"="), sqlEscapeString(limbVal->val)); + sqlDyStringPrintf(dy, "%-s '%s'", + (strchr(limbVal->val,'%')?"like":"="), limbVal->val); break; } else dyStringPrintf(dy, "in ("); multiVals=TRUE; } else dyStringPrintf(dy, ","); - dyStringPrintf(dy, "'%s'", sqlEscapeString(limbVal->val)); + sqlDyStringPrintf(dy, "'%s'", limbVal->val); } if (multiVals) dyStringPrintf(dy, ")"); dyStringPrintf(dy, ")"); } verbose(2, "Requesting mdbByVarsQuery query:\n\t%s;\n",dyStringContents(dy)); struct mdb *mdb = mdbLoadByQuery(conn, dyStringCannibalize(&dy)); verbose(3, "rows (vars) returned: %d\n",slCount(mdb)); slSort(&mdb,mdbVarValCmp); // Use internal sort instead of ORDER BY because of mysql inefficiency struct mdbByVar *mdbByVarsFromMem = mdbByVarsLoadFromMemory(&mdb,TRUE); verbose(3, "Returned %d vars(s) with %d val(s) with %d object(s).\n", mdbByVarCount(mdbByVarsFromMem,TRUE,FALSE), mdbByVarCount(mdbByVarsFromMem,FALSE,TRUE ), mdbByVarCount(mdbByVarsFromMem,FALSE,FALSE)); @@ -1399,50 +1374,50 @@ // JOIN metaDb T2 WHERE T1.obj = T2.obj // AND T2.var = 'cell' AND T2.val NOT IN ('GM12878','K562')' // ORDER BY T1.obj, T1.var; // "cell=GM% cell!=GM12878" (very powerful) // SELECT T1.obj,T1.var,T1.val FROM metaDb T1 // JOIN metaDb T2 WHERE T1.obj = T2.obj AND T2.var = 'cell' AND T2.val LIKE 'GM%' // JOIN metaDb T3 WHERE T1.obj = T3.obj AND T3.var = 'cell' AND T3.val != 'GM12878' // ORDER BY T1.obj, T1.var; if (table == NULL) table = mdbTableName(conn,TRUE); // defaults to sandbox, if exists, else MDB_DEFAULT_NAME else if (!sqlTableExists(conn,table)) return NULL; struct dyString *dy = newDyString(4096); -dyStringPrintf(dy, "SELECT T1.obj,T1.var,T1.val FROM %s T1", table); +sqlDyStringPrintf(dy, "SELECT T1.obj,T1.var,T1.val FROM %s T1", table); struct mdbByVar *rootVar; int tix; for (rootVar=mdbByVars,tix=2;rootVar!=NULL;rootVar=rootVar->next,tix++) { boolean hasVal = (rootVar->vals != NULL); boolean varWild = (strchr(rootVar->var,'%') != NULL); // If you want objects where var='cell' does not exist, then we need the tricky and inscrutable // LEFT JOIN metaDb T2 ON T2.obj = T1.obj AND T2.var = 'cell' WHERE T2.obj is NULL if (!hasVal && rootVar->notEqual) dyStringAppend(dy, " LEFT"); - dyStringPrintf(dy, " JOIN %s T%d ON T%d.obj = T1.obj AND T%d.var ",table,tix,tix,tix); + sqlDyStringPrintf(dy, " JOIN %s T%d ON T%d.obj = T1.obj AND T%d.var ",table,tix,tix,tix); // var = 'x' || var != 'x' || var LIKE 'x%' || var NOT LIKE 'x%' if (hasVal && rootVar->notEqual && rootVar->vals == NULL) dyStringAppend(dy, (varWild ? "NOT " : "!")); - dyStringPrintf(dy, "%s '%s'",(varWild ? "LIKE" : "="), rootVar->var); + sqlDyStringPrintf(dy, "%-s '%s'",(varWild ? "LIKE" : "="), rootVar->var); // Finish the tricky and inscrutable LEFT JOIN / WHERE NULL if (!hasVal && rootVar->notEqual) dyStringPrintf(dy, " WHERE T%d.obj IS NULL",tix); // Now 1 or more vals. First some booleans struct mdbLimbVal *limbVal; boolean multiVals = (rootVar->vals != NULL && rootVar->vals->next != NULL); boolean wilds = FALSE; for (limbVal=rootVar->vals;wilds == FALSE && limbVal!=NULL; limbVal=limbVal->next) wilds = (limbVal->val != NULL && strchr(limbVal->val,'%') != NULL); // breaks when true // Now walk through vals creating: // AND (val = 'x' or val = 'y') or val IN ('a','b','c') or val NOT LIKE 'd%' for (limbVal=rootVar->vals;limbVal!=NULL;limbVal=limbVal->next) @@ -1462,31 +1437,31 @@ { if (wilds && multiVals) dyStringPrintf(dy, " or T%d.val ",tix); // continues LIKE 'd%' else dyStringAppend(dy, ","); // continues IN ('a' } if (limbVal==rootVar->vals // First val || (wilds && multiVals)) // and successive if wildcards { boolean valWild = (strchr(limbVal->val,'%') != NULL); if (rootVar->notEqual) dyStringAppend(dy, (valWild || limbVal->next)?"NOT ":"!"); dyStringAppend(dy, (valWild ? "LIKE " : (!multiVals || wilds ? "= " : "IN ("))); } - dyStringPrintf(dy, "'%s'", sqlEscapeString(limbVal->val)); + sqlDyStringPrintf(dy, "'%s'", limbVal->val); } if (multiVals) dyStringPrintf(dy, ")"); // closes IN ('a','b','c') || AND (val LIKE 'd%' or val LIKE 'e%') } verbose(2, "Requesting mdbObjsQueryByVars query:\n\t%s;\n",dyStringContents(dy)); struct mdb *mdb = mdbLoadByQuery(conn, dyStringCannibalize(&dy)); verbose(3, "rows (vars) returned: %d\n",slCount(mdb)); slSort(&mdb,mdbCmp); // Use internal sort instead of ORDER BY because of mysql inefficiency struct mdbObj *mdbObjs = mdbObjsLoadFromMemory(&mdb,TRUE); verbose(3, "Returned %d object(s) with %d var(s).\n", mdbObjCount(mdbObjs,TRUE),mdbObjCount(mdbObjs,FALSE)); return mdbObjs; } @@ -3315,51 +3290,51 @@ } struct mdbObj *mdbObjSearch(struct sqlConnection *conn, char *var, char *val, char *op, int limit) // Search the metaDb table for objs by var and val. // Can restrict by op "is", "like", "in" and accept (non-zero) limited string size // Search is via mysql, so it's case-insensitive. Return is sorted on obj. { if (var == NULL && val == NULL) errAbort("mdbObjSearch requests objects but provides no criteria.\n"); char *tableName = mdbTableName(conn,TRUE); // Look for sandBox name first // Build a query string struct dyString *dyQuery = dyStringNew(512); -dyStringPrintf(dyQuery,"select l1.obj, l1.var, l1.val from %s l1",tableName); +sqlDyStringPrintf(dyQuery,"select l1.obj, l1.var, l1.val from %s l1",tableName); if (var != NULL || val != NULL) - dyStringPrintf(dyQuery," JOIN %s l2 ON l2.obj = l1.obj and ", + sqlDyStringPrintf(dyQuery," JOIN %s l2 ON l2.obj = l1.obj and ", tableName); if (var != NULL) - dyStringPrintf(dyQuery,"l2.var = '%s'", var); + sqlDyStringPrintf(dyQuery,"l2.var = '%s'", var); if (var != NULL && val != NULL) dyStringAppend(dyQuery," and "); if (val != NULL) { dyStringAppend(dyQuery,"l2.val "); if (sameString(op, "in")) dyStringPrintf(dyQuery,"in (%s)", val); // Note, must be a formatted string already: 'a','b','c' or 1,2,3 else if (sameString(op, "contains") || sameString(op, "like")) - dyStringPrintf(dyQuery,"like '%%%s%%'", val); + sqlDyStringPrintf(dyQuery,"like '%%%s%%'", val); else if (limit > 0 && strlen(val) != limit) - dyStringPrintf(dyQuery,"like '%.*s%%'", limit, val); + sqlDyStringPrintf(dyQuery,"like '%.*s%%'", limit, val); else - dyStringPrintf(dyQuery,"= '%s'", val); + sqlDyStringPrintf(dyQuery,"= '%s'", val); } verbose(2, "Requesting mdbObjSearch query:\n\t%s;\n",dyStringContents(dyQuery)); struct mdb *mdb = mdbLoadByQuery(conn, dyStringCannibalize(&dyQuery)); verbose(3, "rows (vars) returned: %d\n",slCount(mdb)); slSort(&mdb,mdbCmp); // Use internal sort instead of ORDER BY because of mysql inefficiency struct mdbObj *mdbObjs = mdbObjsLoadFromMemory(&mdb,TRUE); return mdbObjs; } struct mdbObj *mdbObjRepeatedSearch(struct sqlConnection *conn,struct slPair *varValPairs, boolean tables,boolean files) // Search the metaDb table for objs by var,val pairs. Uses mdbCvSearchMethod() if available. // This method will use mdbObjsQueryByVars() @@ -3432,105 +3407,106 @@ // "select A.val from metaDb A where A.val = 'fred'". { // A note about tables and files: objType=table may have fileNames associated, // but objType=file will not have tableNames // While objType=table should have a var=tableName, this is redundant because the obj=tableName // So the lopsided 'exists' queries below are meant to be the most flexible/efficent assert(isalpha(letter) && isalpha(letter + 2)); // will need one or two sub-queries. char nextLtr = letter + 1; // We are only searching for objects that are of objType table or file. // objType=composite are not search targets! if (hasTableName && !hasFileName) { // objType=table may have fileNames associated, but objType=file will not have tableNames - dyStringPrintf(dyQuery," and exists (select %c.obj from %s %c where %c.obj = %c.obj and " + sqlDyStringPrintf(dyQuery," and exists (select %c.obj from %s %c where %c.obj = %c.obj and " "%c.var='objType' and %c.val = '%s')",nextLtr,tableName,nextLtr,nextLtr, letter,nextLtr,nextLtr,MDB_OBJ_TYPE_TABLE); } else // tables OR files (but not objType=composite) { - dyStringPrintf(dyQuery," and exists (select %c.obj from %s %c where %c.obj = %c.obj and " + sqlDyStringPrintf(dyQuery," and exists (select %c.obj from %s %c where %c.obj = %c.obj and " "%c.var='objType' and %c.val in ('%s','%s'))",nextLtr,tableName,nextLtr, nextLtr,letter,nextLtr,nextLtr,MDB_OBJ_TYPE_TABLE,MDB_OBJ_TYPE_FILE); } nextLtr++; // last of 3 possibilites objType either table or file but must have fileName var if (!hasTableName && hasFileName) - dyStringPrintf(dyQuery," and exists (select %c.obj from %s %c where %c.obj = %c.obj and " + sqlDyStringPrintf(dyQuery," and exists (select %c.obj from %s %c where %c.obj = %c.obj and " "%c.var in ('%s','%s'))",nextLtr,tableName,nextLtr,nextLtr,letter, nextLtr,MDB_VAR_FILENAME,MDB_VAR_FILEINDEX); } struct slName *mdbValSearch(struct sqlConnection *conn, char *var, int limit, boolean hasTableName, boolean hasFileName) // Search the metaDb table for vals by var. // Can impose (non-zero) limit on returned string size of val // Search is via mysql, so it's case-insensitive. Return is sorted on val. // Searchable vars are only for table or file objects. // Further restrict to vars associated with tableName, fileName or both. { // TODO: Change this to use normal mdb struct routines? struct slName *retVal; if (!hasTableName && !hasFileName) errAbort("mdbValSearch requests vals associated with neither table nor files.\n"); char *tableName = mdbTableName(conn,TRUE); // Look for sandBox name first char letter = 'A'; struct dyString *dyQuery = dyStringNew(512); if (limit > 0) - dyStringPrintf(dyQuery,"select distinct LEFT(%c.val,%d)",letter,limit); + sqlDyStringPrintf(dyQuery,"select distinct LEFT(%c.val,%d)",letter,limit); else - dyStringPrintf(dyQuery,"select distinct %c.val",letter); + sqlDyStringPrintf(dyQuery,"select distinct %c.val",letter); -dyStringPrintf(dyQuery," from %s %c where %c.var='%s'",tableName,letter,letter,var); +sqlDyStringPrintf(dyQuery," from %s %c where %c.var='%s'",tableName,letter,letter,var); mdbSearchableQueryRestictForTablesOrFiles(dyQuery,tableName, letter, hasTableName, hasFileName); dyStringPrintf(dyQuery," order by %c.val",letter); retVal = sqlQuickList(conn, dyStringCannibalize(&dyQuery)); slNameSortCase(&retVal); return retVal; } struct slPair *mdbValLabelSearch(struct sqlConnection *conn, char *var, int limit, boolean tags, boolean hasTableName, boolean hasFileName) // Search the metaDb table for vals by var and returns val (as pair->name) // and controlled vocabulary (cv) label (if it exists) (as pair->val). // Can impose (non-zero) limit on returned string size of name. // Searchable vars are only for table or file objects. // Further restrict to vars associated with tableName, fileName or both. // Return is case insensitive sorted on label (cv label or else val). // If requested, return cv tag instead of mdb val. { // TODO: Change this to use normal mdb struct routines? + if (!hasTableName && !hasFileName) errAbort("mdbValLabelSearch requests vals associated with neither table nor files.\n"); char *tableName = mdbTableName(conn,TRUE); // Look for sandBox name first char letter = 'A'; struct dyString *dyQuery = dyStringNew(512); if (limit > 0) - dyStringPrintf(dyQuery,"select distinct LEFT(%c.val,%d)",letter,limit); + sqlDyStringPrintf(dyQuery,"select distinct LEFT(%c.val,%d)",letter,limit); else - dyStringPrintf(dyQuery,"select distinct %c.val",letter); + sqlDyStringPrintf(dyQuery,"select distinct %c.val",letter); -dyStringPrintf(dyQuery," from %s %c where %c.var='%s'",tableName,letter,letter,var); +sqlDyStringPrintf(dyQuery," from %s %c where %c.var='%s'",tableName,letter,letter,var); mdbSearchableQueryRestictForTablesOrFiles(dyQuery,tableName,letter, hasTableName, hasFileName); //warn("%s",dyStringContents(dyQuery)); struct hash *varHash = (struct hash *)cvTermHash(var); struct slPair *pairs = NULL; struct sqlResult *sr = sqlGetResult(conn, dyStringContents(dyQuery)); dyStringFree(&dyQuery); char **row; while ((row = sqlNextRow(sr)) != NULL) { char *val = row[0]; char *label = NULL; if (varHash != NULL) @@ -3572,31 +3548,31 @@ // Further restrict to vars associated with tableName, fileName or both. { if (!hasTableName && !hasFileName) errAbort("mdbVarsSearchable requests vals associated with neither table nor files.\n"); char *tableName = mdbTableName(conn,TRUE); // Look for sandBox name first char letter = 'A'; struct slPair *cvApproved = cvWhiteList(TRUE,FALSE); struct slPair *relevant = NULL; struct dyString *dyQuery = dyStringNew(256); while (cvApproved != NULL) { struct slPair *oneVar = slPopHead(&cvApproved); dyStringClear(dyQuery); - dyStringPrintf(dyQuery, "select count(DISTINCT %c.val) from %s %c where %c.var = '%s'", + sqlDyStringPrintf(dyQuery, "select count(DISTINCT %c.val) from %s %c where %c.var = '%s'", letter,tableName,letter,letter,oneVar->name); mdbSearchableQueryRestictForTablesOrFiles(dyQuery,tableName,letter, hasTableName, hasFileName); int count = sqlQuickNum(conn,dyStringContents(dyQuery)); //warn("%d %s",count,dyStringContents(dyQuery)); if (count > 1) // If there is only one value then searching on that variable is useless! slAddHead(&relevant, oneVar); else slPairFree(&oneVar); } dyStringFree(&dyQuery); slReverse(&relevant); return relevant; }