080a160c7b9595d516c9c70e83689a09b60839d0 galt Mon Jun 3 12:16:53 2013 -0700 fix SQL Injection diff --git src/hg/lib/hgFind.c src/hg/lib/hgFind.c index 8af4b17..eb79c9e 100644 --- src/hg/lib/hgFind.c +++ src/hg/lib/hgFind.c @@ -362,32 +362,32 @@ } return writeIx; } static struct slName *doGrepQuery(char *indexFile, char *table, char *key, char *extraOptions) /* grep -i key indexFile, return a list of ids (first word of each line). */ { struct pipeline *pl = NULL; struct slName *idList = NULL; struct lineFile *lf = NULL; char *id, *rest, *line; char *keyWords[HGFIND_MAX_KEYWORDS]; char **cmds[HGFIND_MAX_KEYWORDS+1]; -/* No need to escape special chars here, it's already been done: */ -char *escapedKey = cloneString(key); +/* escape special chars here */ +char *escapedKey = sqlEscapeString(key); /* presumably this is the right way escape it? -Galt*/ int keyCount; touppers(escapedKey); keyCount = chopLine(escapedKey, keyWords); keyCount = removeTooCommon(table, keyWords, keyCount); if (keyCount > 0) { if (extraOptions == NULL) extraOptions = ""; makeCmds(cmds, keyWords, keyCount, extraOptions); pl = pipelineOpen(cmds, pipelineRead | pipelineNoAbort, indexFile, NULL); lf = pipelineLineFile(pl); verbose(3, "\n***Running this fgrep command with pipeline from %s:\n*** %s\n\n", indexFile, pipelineDesc(pl)); @@ -415,61 +415,61 @@ } static char *MrnaIDforGeneName(char *db, char *geneName) /* return mRNA ID for a gene name */ { struct sqlConnection *conn; struct sqlResult *sr = NULL; char query[256]; char **row; char *result = NULL; conn = hAllocConn(db); if (sqlTableExists(conn, "refLink")) { - safef(query, sizeof(query), "SELECT mrnaAcc FROM refLink WHERE name='%s'", + sqlSafef(query, sizeof(query), "SELECT mrnaAcc FROM refLink WHERE name='%s'", geneName); sr = sqlGetResult(conn, query); if ((row = sqlNextRow(sr)) != NULL) { result = cloneString(row[0]); } else { result = NULL; } sqlFreeResult(&sr); } hFreeConn(&conn); return result; } char *MrnaIDforProtein(char *db, char *proteinID) /* return mRNA ID for a protein */ { struct sqlConnection *conn; struct sqlResult *sr = NULL; struct dyString *query; char **row; char * result; conn = hAllocConn(db); query = newDyString(256); -dyStringPrintf(query, "SELECT mrnaID FROM spMrna WHERE spID='%s'", proteinID); +sqlDyStringPrintf(query, "SELECT mrnaID FROM spMrna WHERE spID='%s'", proteinID); sr = sqlGetResult(conn, query->string); if ((row = sqlNextRow(sr)) != NULL) { result = cloneString(row[0]); } else { result = NULL; } freeDyString(&query); sqlFreeResult(&sr); hFreeConn(&conn); return result; } @@ -482,42 +482,40 @@ struct sqlResult *sr = NULL; struct dyString *query; char **row; boolean ok = FALSE; struct hgPosTable *table = NULL; struct hgPos *pos = NULL; int rowOffset; char *localName; localName = spec; if (!hTableExists(db, tableName)) return FALSE; rowOffset = hOffsetPastBin(db, NULL, tableName); conn = hAllocConn(db); query = newDyString(256); -dyStringPrintf(query, +sqlDyStringPrintf(query, "SELECT chrom, txStart, txEnd, name FROM %s WHERE name='%s'", tableName, localName); sr = sqlGetResult(conn, query->string); while ((row = sqlNextRow(sr)) != NULL) { if (ok == FALSE) { ok = TRUE; AllocVar(table); - dyStringClear(query); - dyStringPrintf(query, "%s Gene Predictions", tableName); if (hTableExists(db, "kgProtMap2")) table->description = cloneString("UCSC Genes"); else table->description = cloneString("Known Genes"); table->name = cloneString("knownGene"); slAddHead(&hgp->tableList, table); } AllocVar(pos); pos->chrom = hgOfficialChromName(db, row[0]); pos->chromStart = atoi(row[1]); pos->chromEnd = atoi(row[2]); pos->name = cloneString(geneSymbol); /* pos->browserName = cloneString(geneSymbol); highlight change */ pos->browserName = cloneString(row[3]); slAddHead(&table->posList, pos); @@ -595,64 +593,60 @@ tsr = slElementFromIx(tsrList, maxToReturn-1); tsr->next = NULL; } /* Make hash of all search results - one for each known gene ID. */ for (tsr = tsrList; tsr != NULL; tsr = tsr->next) { lmAllocVar(hash->lm, tp); tp->tsr = tsr; slAddHead(&tpList, tp); hashAdd(hash, tsr->itemId, tp); } /* Stream through knownGenes table and make up a pos * for each mapping of each gene matching search. */ -dyStringAppend(dy, +sqlDyStringAppend(dy, "select name,chrom,txStart,txEnd from knownGene where name in ("); for (tsr = tsrList; tsr != NULL; tsr = tsr->next) { - dyStringAppendC(dy, '"'); - dyStringAppend(dy, tsr->itemId); - dyStringAppendC(dy, '"'); + sqlDyStringPrintf(dy, "'%s'", tsr->itemId); if (tsr->next != NULL) dyStringAppendC(dy, ','); } dyStringAppend(dy, ")"); sr = sqlGetResult(conn, dy->string); while ((row = sqlNextRow(sr)) != NULL) { tp = hashFindVal(hash, row[0]); if (tp == NULL) internalErr(); AllocVar(pos); pos->chrom = cloneString(row[1]); pos->chromStart = sqlUnsigned(row[2]); pos->chromEnd = sqlUnsigned(row[3]); slAddHead(&tp->posList, pos); } sqlFreeResult(&sr); /* Stream through kgXref table adding description and geneSymbol */ dyStringClear(dy); -dyStringAppend(dy, +sqlDyStringAppend(dy, "select kgID,geneSymbol,description from kgXref where kgID in ("); for (tsr = tsrList; tsr != NULL; tsr = tsr->next) { - dyStringAppendC(dy, '"'); - dyStringAppend(dy, tsr->itemId); - dyStringAppendC(dy, '"'); + sqlDyStringPrintf(dy, "'%s'", tsr->itemId); if (tsr->next != NULL) dyStringAppendC(dy, ','); } dyStringAppend(dy, ")"); sr = sqlGetResult(conn, dy->string); while ((row = sqlNextRow(sr)) != NULL) { tp = hashFindVal(hash, row[0]); if (tp == NULL) internalErr(); for (pos = tp->posList; pos != NULL; pos = pos->next) { char nameBuf[256]; safef(nameBuf, sizeof(nameBuf), "%s (%s)", row[1], row[0]); pos->name = cloneString(nameBuf); @@ -715,31 +709,31 @@ struct sqlResult *sr = NULL; struct dyString *query; char **row; boolean ok = FALSE; struct hgPosTable *table = NULL; struct hgPos *pos = NULL; int rowOffset; char *localName; localName = spec; if (!hTableExists(db, tableName)) return FALSE; rowOffset = hOffsetPastBin(db, NULL, tableName); conn = hAllocConn(db); query = newDyString(256); -dyStringPrintf(query, "SELECT chrom, txStart, txEnd, name, description FROM %s, kgXref " +sqlDyStringPrintf(query, "SELECT chrom, txStart, txEnd, name, description FROM %s, kgXref " "WHERE description LIKE '%%%s%%' and kgId=name", tableName, localName); sr = sqlGetResult(conn, query->string); while ((row = sqlNextRow(sr)) != NULL) { if (ok == FALSE) { ok = TRUE; table = addKnownGeneTable(db, hgp); } AllocVar(pos); pos->chrom = hgOfficialChromName(db, row[0]); pos->chromStart = atoi(row[1]); pos->chromEnd = atoi(row[2]); pos->name = cloneString(row[3]); @@ -762,31 +756,31 @@ struct sqlResult *sr = NULL; struct dyString *query; char **row; boolean ok = FALSE; struct hgPosTable *table = NULL; struct hgPos *pos = NULL; int rowOffset; char *localName; localName = spec; if (!hTableExists(db, tableName)) return FALSE; rowOffset = hOffsetPastBin(db, NULL, tableName); conn = hAllocConn(db); query = newDyString(256); -dyStringPrintf(query, "SELECT chrom, txStart, txEnd, name FROM %s " +sqlDyStringPrintf(query, "SELECT chrom, txStart, txEnd, name FROM %s " "WHERE name LIKE '%s%%'", tableName, localName); sr = sqlGetResult(conn, query->string); while ((row = sqlNextRow(sr)) != NULL) { if (ok == FALSE) { ok = TRUE; table = addKnownGeneTable(db, hgp); slAddHead(&hgp->tableList, table); } AllocVar(pos); pos->chrom = hgOfficialChromName(db, row[0]); pos->chromStart = atoi(row[1]); @@ -907,31 +901,31 @@ num = atoi(chrom); if (num < 1 || num > 22) return NULL; safef(buf, sizeof(buf), "chr%d", num); return hgOfficialChromName(db, buf); } static struct cytoBand *loadAllBands(char *db) /* Load up all bands from database. */ { struct cytoBand *list = NULL, *el; struct sqlConnection *conn = hAllocConn(db); struct sqlResult *sr = NULL; char **row; -sr = sqlGetResult(conn, "select * from cytoBand"); +sr = sqlGetResult(conn, "NOSQLINJ select * from cytoBand"); while ((row = sqlNextRow(sr)) != NULL) { el = cytoBandLoad(row); slAddHead(&list, el); } sqlFreeResult(&sr); slReverse(&list); hFreeConn(&conn); return list; } static struct cytoBand *bandList = NULL; void hgFindChromBand(char *db, char *chromosome, char *band, int *retStart, int *retEnd) /* Return start/end of band in chromosome. */ @@ -1090,56 +1084,56 @@ boolean findChromContigPos(char *db, char *name, char **retChromName, int *retWinStart, int *retWinEnd) /* Find position in genome of contig. Look in all chroms. * Don't alter return variables unless found. */ /* NOTE: could probably speed this up by using the chromInfo hashtable */ { struct sqlConnection *conn = hAllocConn(db); struct sqlResult *sr = NULL; char **row; char query[256]; boolean foundIt = FALSE; /* In case this is a scaffold-based assembly, check for unsplit table first: */ if (sqlTableExists(conn, "gold")) { - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select chrom,chromStart,chromEnd from gold where frag = '%s'", name); sr = sqlMustGetResult(conn, query); row = sqlNextRow(sr); if (row != NULL) { *retChromName = cloneString(row[0]); *retWinStart = atoi(row[1]); *retWinEnd = atoi(row[2]); foundIt = TRUE; } sqlFreeResult(&sr); } else { struct slName *allChroms = hAllChromNames(db); struct slName *chromPtr; for (chromPtr=allChroms; chromPtr != NULL; chromPtr=chromPtr->next) { char tableName[256]; safef(tableName, sizeof(tableName), "%s_gold", chromPtr->name); if (! sqlTableExists(conn, tableName)) continue; - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select chromStart,chromEnd from %s where frag = '%s'", tableName, name); sr = sqlMustGetResult(conn, query); row = sqlNextRow(sr); if (row != NULL) { *retChromName = cloneString(chromPtr->name); *retWinStart = atoi(row[0]); *retWinEnd = atoi(row[1]); foundIt = TRUE; } sqlFreeResult(&sr); if (foundIt) break; } @@ -1164,31 +1158,31 @@ } #endif static boolean mrnaInfo(char *acc, struct sqlConnection *conn, char **mrnaType) /* Sets *mrnaType to mrna/est type for the accession */ /* Ignores returned values if parameters are NULL */ /* Return TRUE if search succeeded, else FALSE */ /* NOTE: caller must free mrnaType */ { char query[256]; struct sqlResult *sr; char **row; int ret; -safef(query, sizeof(query), +sqlSafef(query, sizeof(query), "select type from gbCdnaInfo where acc = '%s'", acc); sr = sqlGetResult(conn, query); if ((row = sqlNextRow(sr)) != NULL) { if (mrnaType != NULL) *mrnaType = cloneString(row[0]); ret = TRUE; } else ret = FALSE; sqlFreeResult(&sr); return ret; } boolean isRefSeqAcc(char *acc) @@ -1265,31 +1259,31 @@ char *vis = cart ? cartOptionalString(cart, trackName) : NULL; if (vis && sameString(vis, "full")) return "full"; else return hTrackOpenVis(db, trackName); } static struct psl *getPslFromTable(struct sqlConnection *conn, char *db, char *table, char *acc) /* If table exists, return PSL for each row with qName = acc. */ { struct psl *pslList = NULL; if (sqlTableExists(conn, table)) { int rowOffset = hOffsetPastBin(db, NULL, table); char query[256]; - safef(query, sizeof(query), "select * from %s where qName = '%s'", table, acc); + sqlSafef(query, sizeof(query), "select * from %s where qName = '%s'", table, acc); struct sqlResult *sr = sqlGetResult(conn, query); char **row; while ((row = sqlNextRow(sr)) != NULL) { struct psl *psl = pslLoad(row+rowOffset); slAddHead(&pslList, psl); } slReverse(&pslList); sqlFreeResult(&sr); } return pslList; } static void addPslResultToHgp(struct hgPositions *hgp, char *db, char *tableName, char *shortLabel, char *acc, struct psl *pslList) @@ -1426,48 +1420,50 @@ if (grepIndexRoot != NULL && hfsSetting != NULL) { char buf[1024]; safef(buf, sizeof(buf), "%s/%s/%s.%s", grepIndexRoot, db, table, suffix); if (fileExists(buf)) return cloneString(buf); } return NULL; } static struct slName *genbankGrepQuery(char *indexFile, char *table, char *key) /* grep -i key indexFile, return a list of ids (first word of each line). */ { +//verbose(1,"genbankGrepQuery table=[%s] key=[%s]\n", table, key); // DEBUG REMOVE char *extraOptions = ""; if (sameString(table, "author")) extraOptions = "-w"; return doGrepQuery(indexFile, table, key, extraOptions); } static struct slName *genbankSqlFuzzyQuery(struct sqlConnection *conn, char *table, char *key) /* Perform a fuzzy sql search for %key% in table.name; return list of * corresponding table.id's. */ { +//verbose(1,"genbankSqlFuzzyQuery table=[%s] key=[%s]\n", table, key); // DEBUG REMOVE struct slName *idList = NULL, *idEl = NULL; if (!isTooCommon(table, key)) { struct sqlResult *sr; char **row; char query[256]; - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select id,name from %s where name like '%%%s%%'", table, key); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { touppers(row[1]); if (keyIsPrefix(key, row[1])) { idEl = newSlName(row[0]); slAddHead(&idList, idEl); } } sqlFreeResult(&sr); } return idList; } @@ -1476,58 +1472,60 @@ char *tables[], int tableCount) /* Return TRUE if all tables have a readable genbank index file. */ { int i; for (i=0; i < tableCount; i++) if (! getGenbankGrepIndex(db, hfs, tables[i], "idName")) return FALSE; return TRUE;; } static void findHitsToTables(char *db, struct hgFindSpec *hfs, char *key, char *tables[], int tableCount, struct hash **retHash, struct slName **retList) /* Return all unique accessions that match any table. */ { +//verbose(1,"findHitsToTables db=[%s] key=[%s]\n", db, key); // DEBUG REMOVE struct slName *list = NULL, *el; struct hash *hash = newHash(0); struct sqlConnection *conn = hAllocConn(db); struct sqlResult *sr; char **row; char query[256]; char *field; int i; for (i = 0; i<tableCount; ++i) { struct slName *idList = NULL, *idEl; char *grepIndexFile = NULL; /* I'm doing this query in two steps in C rather than * in one step in SQL just because it somehow is much * faster this way (like 100x faster) when using mySQL. */ field = tables[i]; if (!hTableExists(db, field)) continue; if ((grepIndexFile = getGenbankGrepIndex(db, hfs, field, "idName")) != NULL) idList = genbankGrepQuery(grepIndexFile, field, key); else idList = genbankSqlFuzzyQuery(conn, field, key); for (idEl = idList; idEl != NULL; idEl = idEl->next) { /* don't check srcDb to exclude refseq for compat with older tables */ - safef(query, sizeof(query), + //verbose(1,"findHitsToTables field=[%s] idEl->name=[%s]\n", field, idEl->name); // DEBUG REMOVE + sqlSafef(query, sizeof(query), "select acc, organism from gbCdnaInfo where %s = %s " " and type = 'mRNA'", field, idEl->name); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { char *acc = row[0]; /* will use this later to distinguish xeno mrna */ int organismID = sqlUnsigned(row[1]); if (!isRefSeqAcc(acc) && !hashLookup(hash, acc)) { el = newSlName(acc); slAddHead(&list, el); hashAddInt(hash, acc, organismID); } @@ -1564,31 +1562,31 @@ *retHash = hash; *retList = list; } static void mrnaKeysHtmlOnePos(struct hgPosTable *table, struct hgPos *pos, FILE *f) { fprintf(f, "%s", pos->description); } static boolean mrnaAligns(struct sqlConnection *conn, char *table, char *acc) /* Return TRUE if accession is in the designated alignment table (for speed, * this assumes that we've already checked that the table exists) */ { char query[256]; -safef(query, sizeof(query), +sqlSafef(query, sizeof(query), "select count(*) from %s where qName = '%s'", table, acc); return (sqlQuickNum(conn, query) > 0); } static int addMrnaPositionTable(char *db, struct hgPositions *hgp, struct slName **pAccList, struct hash *accOrgHash, struct cart *cart, struct sqlConnection *conn, char *hgAppName, boolean aligns, boolean isXeno) /* Generate table of positions that match criteria. * Add to hgp if any found. Return number found */ { struct hgPosTable *table = NULL; struct hgPos *pos = NULL; struct slName *el = NULL; @@ -1607,31 +1605,31 @@ boolean mrnaTableExists = hTableExists(hgp->database, mrnaTable); AllocVar(table); /* Examine all accessions to see if they fit criteria for * this table. Add all matching to the position list, and * remove from the accession list */ for (el = *pAccList; el != NULL; el = el->next) { freez(&elToFree); acc = el->name; /* check if item matches xeno criterion */ if (hTableExists(db, "gbStatus")) { - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select (orgCat = 'native' && srcDb != 'RefSeq') from gbStatus where acc = '%s'", acc); /* redmine #3301 */ if (isXeno == sqlQuickNum(conn, query)) continue; } else { int itemOrganismID = hashIntVal(accOrgHash, acc); if (isXeno == (itemOrganismID == organismID)) continue; } /* check if item matches alignment criterion */ if (aligns != (mrnaTableExists && mrnaAligns(conn, mrnaTable, acc))) continue; @@ -1649,46 +1647,46 @@ } else { /* display mRNA details page -- need to add dummy CGI variables*/ dyStringPrintf(dy, "<A HREF=\"%s%cg=%s&i=%s&c=0&o=0&l=0&r=0", hgcName(), hgAppCombiner, mrnaTable, acc); } if (ui != NULL) dyStringPrintf(dy, "&%s", ui); dyStringPrintf(dy, "%s\">", hgp->extraCgi); dyStringPrintf(dy, "%s</A>", acc); /* print description for item, or lacking that, the product name */ safef(description, sizeof(description), "%s", "n/a"); - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select description.name from gbCdnaInfo,description" " where gbCdnaInfo.acc = '%s' and gbCdnaInfo.description = description.id", acc); sqlQuickQuery(conn, query, description, sizeof(description)); if (sameString(description, "n/a")) { /* look for product name */ - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select productName.name from gbCdnaInfo,productName" " where gbCdnaInfo.acc = '%s' and gbCdnaInfo.productName = productName.id", acc); sqlQuickQuery(conn, query, product, sizeof(product)); if (!sameString(product, "n/a")) { /* get organism name */ - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select organism.name from gbCdnaInfo,organism" " where gbCdnaInfo.acc = '%s' and gbCdnaInfo.organism = organism.id", acc); *organism = 0; sqlQuickQuery(conn, query, organism, sizeof(organism)); safef(description, sizeof(description), "%s%s%s", *organism ? organism : "", *organism ? ", " : "", product); } } if (!sameString(description, "n/a")) /* print description if it has been loaded */ dyStringPrintf(dy, " - %s", description); dyStringPrintf(dy, "\n"); pos->description = cloneString(dy->string); @@ -1855,31 +1853,31 @@ slAddHead(&hgp->tableList, table); table->description = cloneString("Known Genes"); table->name = cloneString("knownGene"); for (kl = kaList; kl != NULL; kl = kl->next) { /* Don't return duplicate mrna accessions */ if (hashFindVal(hash, kl->kgID)) { hashAdd(hash, kl->kgID, kl); continue; } hashAdd(hash, kl->kgID, kl); dyStringClear(ds); - dyStringPrintf(ds, "select * from knownGene where name = '%s'", + sqlDyStringPrintf(ds, "select * from knownGene where name = '%s'", kl->kgID); sr = sqlGetResult(conn, ds->string); while ((row = sqlNextRow(sr)) != NULL) { gp = genePredLoad(row); AllocVar(pos); slAddHead(&table->posList, pos); pos->name = cloneString(kl->alias); #if UNUSED pos->browserName = cloneString(kl->alias); // highlight change #endif pos->browserName = cloneString(kl->kgID); safef(cond_str, sizeof(cond_str), "kgID = '%s'", kl->kgID); answer = sqlGetField(db, "kgXref", "description", cond_str); @@ -1943,31 +1941,31 @@ slAddHead(&hgp->tableList, table); table->description = cloneString("Known Genes"); table->name = cloneString("knownGene"); for (kl = kpaList; kl != NULL; kl = kl->next) { /* Don't return duplicate mrna accessions */ if (hashFindVal(hash, kl->kgID)) { hashAdd(hash, kl->kgID, kl); continue; } hashAdd(hash, kl->kgID, kl); dyStringClear(ds); - dyStringPrintf(ds, "select * from knownGene where name = '%s'", + sqlDyStringPrintf(ds, "select * from knownGene where name = '%s'", kl->kgID); sr = sqlGetResult(conn, ds->string); while ((row = sqlNextRow(sr)) != NULL) { gp = genePredLoad(row); AllocVar(pos); slAddHead(&table->posList, pos); pos->name = cloneString(kl->alias); /* pos->browserName = cloneString(kl->alias); highlight change */ pos->browserName = cloneString(kl->kgID); safef(cond_str, sizeof(cond_str), "kgID = '%s'", kl->kgID); answer = sqlGetField(db, "kgXref", "description", cond_str); if (answer != NULL) { @@ -2010,115 +2008,116 @@ } sqlFreeResult(&sr); } static void addRefLinkAccs(struct sqlConnection *conn, struct slName *accList, struct refLink **pList) /* Query database and add returned refLinks to head of list. */ { struct slName *accEl = NULL; struct sqlResult *sr = NULL; char **row = NULL; char query[256]; for (accEl = accList; accEl != NULL; accEl = accEl->next) { - safef(query, sizeof(query), "select * from refLink where mrnaAcc = '%s'", + sqlSafef(query, sizeof(query), "select * from refLink where mrnaAcc = '%s'", accEl->name); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { struct refLink *rl = refLinkLoad(row); slAddHead(pList, rl); } sqlFreeResult(&sr); } } static boolean findRefGenes(char *db, struct hgFindSpec *hfs, char *spec, struct hgPositions *hgp) /* Look up refSeq genes in table. */ { +//verbose(1,"findRefGenes db=[%s] spec=[%s]\n", db, spec); // DEBUG REMOVE struct sqlConnection *conn = hAllocConn(db); struct dyString *ds = newDyString(256); struct refLink *rlList = NULL, *rl; boolean gotRefLink = hTableExists(db, "refLink"); boolean found = FALSE; char *specNoVersion = cloneString(spec); (void) chopPrefix(specNoVersion); - +//verbose(1,"findRefGenes specNoVersion=[%s]\n", specNoVersion); // DEBUG REMOVE if (gotRefLink) { if (startsWith("NM_", specNoVersion) || startsWith("NR_", specNoVersion) || startsWith("XM_", specNoVersion)) { - dyStringPrintf(ds, "select * from refLink where mrnaAcc = '%s'", specNoVersion); + sqlDyStringPrintf(ds, "select * from refLink where mrnaAcc = '%s'", specNoVersion); addRefLinks(conn, ds, &rlList); } else if (startsWith("NP_", specNoVersion) || startsWith("XP_", specNoVersion)) { - dyStringPrintf(ds, "select * from refLink where protAcc = '%s'", specNoVersion); + sqlDyStringPrintf(ds, "select * from refLink where protAcc = '%s'", specNoVersion); addRefLinks(conn, ds, &rlList); } else if (isUnsignedInt(specNoVersion)) { - dyStringPrintf(ds, "select * from refLink where locusLinkId = %s", + sqlDyStringPrintf(ds, "select * from refLink where locusLinkId = %s", specNoVersion); addRefLinks(conn, ds, &rlList); dyStringClear(ds); - dyStringPrintf(ds, "select * from refLink where omimId = %s", specNoVersion); + sqlDyStringPrintf(ds, "select * from refLink where omimId = %s", specNoVersion); addRefLinks(conn, ds, &rlList); } else { char *indexFile = getGenbankGrepIndex(db, hfs, "refLink", "mrnaAccProduct"); - dyStringPrintf(ds, "select * from refLink where name like '%s%%'", + sqlDyStringPrintf(ds, "select * from refLink where name like '%s%%'", specNoVersion); addRefLinks(conn, ds, &rlList); if (indexFile != NULL) { struct slName *accList = doGrepQuery(indexFile, "refLink", specNoVersion, NULL); addRefLinkAccs(conn, accList, &rlList); } else { dyStringClear(ds); - dyStringPrintf(ds, "select * from refLink where product like '%%%s%%'", + sqlDyStringPrintf(ds, "select * from refLink where product like '%%%s%%'", specNoVersion); addRefLinks(conn, ds, &rlList); } } } if (rlList != NULL) { struct hgPosTable *table = NULL; struct hash *hash = newHash(8); for (rl = rlList; rl != NULL; rl = rl->next) { char where[64]; struct genePredReader *gpr; struct genePred *gp; /* Don't return duplicate mrna accessions */ if (hashFindVal(hash, rl->mrnaAcc)) { hashAdd(hash, rl->mrnaAcc, rl); continue; } hashAdd(hash, rl->mrnaAcc, rl); - safef(where, sizeof where, "name = '%s'", rl->mrnaAcc); + sqlSafefFrag(where, sizeof where, "name = '%s'", rl->mrnaAcc); gpr = genePredReaderQuery(conn, hfs->searchTable, where); while ((gp = genePredReaderNext(gpr)) != NULL) { struct hgPos *pos = NULL; AllocVar(pos); if (table == NULL) { char desc[256]; AllocVar(table); table->name = cloneString(hfs->searchTable); if (startsWith("xeno", hfs->searchTable)) safef(desc, sizeof(desc), "Non-%s RefSeq Genes", hOrganism(db)); else safef(desc, sizeof(desc), "RefSeq Genes"); table->description = cloneString(desc); @@ -2166,58 +2165,58 @@ /* Look up TIGR and Genbank genes from keyword */ { struct sqlConnection *conn = hAllocConn(db); struct sqlResult *sr = NULL; struct dyString *ds = newDyString(256); char **row; struct hgPosTable *table = NULL; struct hgPos *pos; struct bed *bed; struct tigrCmrGene *tigrList = NULL, *tigr; /* struct minGeneInfo *gbList = NULL, *gb; */ boolean gotTIGRkeys = sqlTableExists(conn, "tigrCmrORFsInfo"); if (gotTIGRkeys) { - dyStringPrintf(ds, "select * from tigrCmrORFsInfo where tigrCommon like '%%%s%%'", spec); + sqlDyStringPrintf(ds, "select * from tigrCmrORFsInfo where tigrCommon like '%%%s%%'", spec); addTigrCmrGenes(conn, ds, &tigrList); dyStringClear(ds); - dyStringPrintf(ds, "select * from tigrCmrORFsInfo where tigrMainRole like '%%%s%%'", spec); + sqlDyStringPrintf(ds, "select * from tigrCmrORFsInfo where tigrMainRole like '%%%s%%'", spec); addTigrCmrGenes(conn, ds, &tigrList); dyStringClear(ds); - dyStringPrintf(ds, "select * from tigrCmrORFsInfo where tigrSubRole like '%%%s%%'", spec); + sqlDyStringPrintf(ds, "select * from tigrCmrORFsInfo where tigrSubRole like '%%%s%%'", spec); addTigrCmrGenes(conn, ds, &tigrList); dyStringClear(ds); } if (tigrList != NULL) { struct hash *hash = newHash(8); AllocVar(table); slAddHead(&hgp->tableList, table); table->description = cloneString("TIGR CMR Genes"); table->name = cloneString("tigrORFsCmr"); for (tigr = tigrList; tigr != NULL; tigr = tigr->next) { /* Don't return duplicate TIGR CMR accessions */ if (hashFindVal(hash, tigr->name)) { hashAdd(hash, tigr->name, tigr); continue; } hashAdd(hash, tigr->name, tigr); dyStringClear(ds); - dyStringPrintf(ds, "select * from tigrCmrORFs where name = '%s'", tigr->name); + sqlDyStringPrintf(ds, "select * from tigrCmrORFs where name = '%s'", tigr->name); sr = sqlGetResult(conn, ds->string); while ((row = sqlNextRow(sr)) != NULL) { bed = bedLoadN(row+1,6); AllocVar(pos); slAddHead(&table->posList, pos); pos->name = cloneString(tigr->name); pos->browserName = cloneString(tigr->name); dyStringClear(ds); dyStringPrintf(ds, "%s; %s; %s", tigr->tigrCommon, tigr->tigrMainRole, tigr->tigrSubRole); pos->description = cloneString(ds->string); pos->chrom = hgOfficialChromName(db, bed->chrom); pos->chromStart = bed->chromStart; pos->chromEnd = bed->chromEnd; bedFree(&bed); @@ -2240,31 +2239,31 @@ struct sqlConnection *conn; struct sqlResult *sr = NULL; struct dyString *query; char **row; boolean ok = FALSE; struct hgPos *pos = NULL; int rowOffset; char *localName; localName = pattern; if (!hTableExists(db, tableName)) return FALSE; rowOffset = hOffsetPastBin(db, NULL, tableName); conn = hAllocConn(db); query = newDyString(256); -dyStringPrintf(query, +sqlDyStringPrintf(query, "SELECT chrom, txStart, txEnd, name FROM %s WHERE name LIKE '%s'", tableName, pattern); sr = sqlGetResult(conn, query->string); while ((row = sqlNextRow(sr)) != NULL) { if (ok == FALSE) { ok = TRUE; if (table == NULL) { AllocVar(table); dyStringClear(query); dyStringPrintf(query, "%s Gene Predictions", tableName); table->description = cloneString(query->string); table->name = cloneString(tableName); @@ -2318,69 +2317,69 @@ } static boolean findYeastGenes(char *db, char *pattern, struct hgPositions *hgp) /* Scan yeast-specific tables. */ { struct sqlConnection *conn = hAllocConn(db); struct sqlResult *sr; char **row, query[256]; struct hgPosTable *table = NULL; boolean found = FALSE; if (hTableExists(db, "sgdGene")) { struct hash *uniqHash = newHash(0); boolean gotNames = FALSE, gotDescriptions = FALSE; - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select name from sgdGene where name = '%s'", pattern); addUniqYeastGene(db, uniqHash, conn, query, hgp, "sgdGene", &table); if (hTableExists(db, "sgdToName")) { gotNames = TRUE; - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select name from sgdToName where value like '%s%%'", pattern); addUniqYeastGene(db, uniqHash, conn, query, hgp, "sgdGene", &table); } if (hTableExists(db, "sgdDescription")) { gotDescriptions = TRUE; - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select name from sgdDescription where description like '%%%s%%'", pattern); addUniqYeastGene(db, uniqHash, conn, query, hgp, "sgdGene", &table); } hashFree(&uniqHash); /* Add descriptions to table. */ if (table != NULL) { struct hgPos *pos; for (pos = table->posList; pos != NULL; pos = pos->next) { struct dyString *dy = newDyString(1024); if (gotNames) { - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select value from sgdToName where name = '%s'", pos->name); sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) dyStringPrintf(dy, "(%s) ", row[0]); sqlFreeResult(&sr); } if (gotDescriptions) { - safef(query, sizeof(query), + sqlSafef(query, sizeof(query), "select description from sgdDescription where name = '%s'", pos->name); sr = sqlGetResult(conn, query); if ((row = sqlNextRow(sr)) != NULL) dyStringPrintf(dy, "%s", row[0]); sqlFreeResult(&sr); } if (dy->stringSize > 0) pos->description = cloneString(dy->string); dyStringFree(&dy); } found = TRUE; } } hFreeConn(&conn); @@ -2582,34 +2581,36 @@ char *table) { if (relativeFlag) hUserAbort("Sorry, range spec (\":%d-%d\") is not supported for %s.", relStart+1, relEnd, table); } #endif static boolean searchSpecial(char *db, struct hgFindSpec *hfs, char *term, struct hgPositions *hgp, boolean relativeFlag, int relStart, int relEnd, boolean *retFound) /* Handle searchTypes for which we have special code. Return true if * we have special code. Set retFind according to whether we find term. */ { +//verbose(1,"searchSpecial db=[%s] term=[%s]\n", db, term); // DEBUG REMOVE boolean isSpecial = TRUE; boolean found = FALSE; char *upcTerm = cloneString(term); touppers(upcTerm); +//verbose(1,"searchSpecial hfs->searchType=[%s]\n", hfs->searchType); // DEBUG REMOVE if (sameString(hfs->searchType, "knownGene")) { if (gotFullText(db)) found = findKnownGeneFullText(db, term, hgp); else /* NOTE, in a few months (say by April 1 2006) get rid of else -JK */ { if (!found && hTableExists(db, "kgAlias")) found = findKgGenesByAlias(db, term, hgp); if (!found && hTableExists(db, "kgProtAlias")) found = findKgGenesByProtAlias(db, term, hgp); if (!found) found = findKnownGene(db, term, hgp, hfs->searchTable); } } else if (sameString(hfs->searchType, "refGene")) @@ -2647,46 +2648,61 @@ } else if (sameString(hfs->searchType, "mrnaKeyword")) { found = findMrnaKeys(db, hfs, upcTerm, hgp); } else if (sameString(hfs->searchType, "sgdGene")) { found = findYeastGenes(db, term, hgp); } else { isSpecial = FALSE; } *retFound = found; freeMem(upcTerm); +//verbose(1,"searchSpecial done isSpecial=%d\n", isSpecial); // DEBUG REMOVE return(isSpecial); } static struct slPair *getXrefTerms(char *db, struct hgFindSpec *hfs, char *term) /* Search xrefTable for xrefQuery with term. Return all matching names. */ { +//verbose(1,"getXrefTerms db=[%s] term=[%s]\n", db, term); // DEBUG REMOVE struct slPair *xrefList = NULL, *xrefPtr = NULL; struct sqlConnection *conn = hAllocConn(db); struct sqlResult *sr = NULL; char **row; -char buf[512]; boolean isFuzzy = sameWord(hfs->searchMethod, "fuzzy"); -safef(buf, sizeof(buf), hfs->xrefQuery, hfs->xrefTable, term); -sr = sqlGetResult(conn, buf); +// TODO wonder if we could re-work this better to get to upstream sql creation and +// then be able to avoid this complexity:? +// hfs->refTable sometimes contains a comma-separated table list +// but we do not have control over the original sql since it is in trackDb.ra + +// example from human/hg19/trackDb.ra +// xrefTable kgXref, ucscRetroInfo5 +// xrefQuery select ucscRetroInfo5.name, spDisplayID from %s where spDisplayID like '%s%%' and kgName = kgID + +struct dyString *dy = dyStringNew(256); +dyStringAppend(dy, "NOSQLINJ "); +// in particular, if we could get to the upstream and change the first %s to %-s for the param corresponding to xrefTable, +// that would be nice. +dyStringPrintf(dy, hfs->xrefQuery, sqlCkIl(hfs->xrefTable), sqlEscapeString(term)); // keep this sqlEscape +sr = sqlGetResult(conn, dy->string); +dyStringFree(&dy); while ((row = sqlNextRow(sr)) != NULL) { if (!isFuzzy || keyIsPrefixIgnoreCase(term, row[1])) { xrefPtr = slPairNew(cloneString(row[1]), cloneString(row[0])); slAddHead(&xrefList, xrefPtr); } } sqlFreeResult(&sr); hFreeConn(&conn); slReverse(&xrefList); if (xrefList == NULL && hgFindSpecSetting(hfs, "searchBoth") != NULL) xrefList = slPairNew(cloneString(""), cloneString(term)); return(xrefList); } @@ -2711,30 +2727,31 @@ { int sz; va_list args; va_start(args, format); sz = vatruncatef(buf, size, format, args); va_end(args); } static boolean doQuery(char *db, struct hgFindSpec *hfs, char *xrefTerm, char *term, struct hgPositions *hgp, boolean relativeFlag, int relStart, int relEnd, boolean multiTerm) /* Perform a query as specified in hfs, assuming table existence has been * checked and xref'ing has been taken care of. */ { +//verbose(1,"doQuery term=[%s]\n", term); // DEBUG REMOVE struct slName *tableList = hSplitTableNames(db, hfs->searchTable); struct slName *tPtr = NULL; struct hgPosTable *table = NULL; struct hgPos *pos = NULL; struct sqlConnection *conn = hAllocConn(db); struct sqlResult *sr = NULL; char **row = NULL; char *termPrefix = hgFindSpecSetting(hfs, "termPrefix"); char *paddingStr = hgFindSpecSetting(hfs, "padding"); int padding = isEmpty(paddingStr) ? 0 : atoi(paddingStr); boolean found = FALSE; char *description = NULL; char buf[2048]; if (isNotEmpty(termPrefix) && startsWith(termPrefix, term)) @@ -2743,32 +2760,34 @@ return(FALSE); if (isNotEmpty(hfs->searchDescription)) truncatef(buf, sizeof(buf), "%s", hfs->searchDescription); else safef(buf, sizeof(buf), "%s", hfs->searchTable); description = cloneString(buf); if (hgp->tableList != NULL && sameString(hgp->tableList->name, hfs->searchTable) && sameString(hgp->tableList->description, description)) table = hgp->tableList; for (tPtr = tableList; tPtr != NULL; tPtr = tPtr->next) { - safef(buf, sizeof(buf), hfs->query, tPtr->name, term); - sr = sqlGetResult(conn, buf); + // we do not have control over the original sql since it comes from trackDb.ra or elsewhere? + char query[2048]; + sqlSafef(query, sizeof(query), hfs->query, tPtr->name, term); + sr = sqlGetResult(conn, query); while ((row = sqlNextRow(sr)) != NULL) { if(table == NULL) { AllocVar(table); table->description = description; table->name = cloneString(hfs->searchTable); slAddHead(&hgp->tableList, table); } found = TRUE; AllocVar(pos); pos->chrom = cloneString(row[0]); pos->chromStart = atoi(row[1]); pos->chromEnd = atoi(row[2]); if (isNotEmpty(xrefTerm)) @@ -2805,58 +2824,65 @@ } if (table != NULL) slReverse(&table->posList); sqlFreeResult(&sr); hFreeConn(&conn); slFreeList(&tableList); return(found); } boolean hgFindUsingSpec(char *db, struct hgFindSpec *hfs, char *term, struct hgPositions *hgp, boolean relativeFlag, int relStart, int relEnd, boolean multiTerm) /* Perform the search described by hfs on term. If successful, put results * in hgp and return TRUE. (If not, don't modify hgp.) */ { +//verbose(1,"hgFindUsingSpec db=[%s] term=[%s]\n", db, term); // DEBUG REMOVE struct slPair *xrefList = NULL, *xrefPtr = NULL; boolean found = FALSE; if (hfs == NULL || term == NULL || hgp == NULL) errAbort("NULL passed to hgFindUsingSpec.\n"); if (strlen(term)<2 && ! (sameString(hfs->searchName, "knownGene") || sameString(hfs->searchName, "flyBaseGeneSymbolOneLetter"))) return FALSE; if (isNotEmpty(hfs->termRegex) && ! regexMatchNoCase(term, hfs->termRegex)) return(FALSE); if (! hTableOrSplitExists(db, hfs->searchTable)) return(FALSE); if (isNotEmpty(hfs->searchType) && searchSpecial(db, hfs, term, hgp, relativeFlag, relStart, relEnd, &found)) return(found); +//verbose(1,"hgFindUsingSpec hfs->xrefTable=[%s]\n", hfs->xrefTable); // DEBUG REMOVE if (isNotEmpty(hfs->xrefTable)) { struct sqlConnection *conn = hAllocConn(db); - boolean exists = sqlTableExists(conn, hfs->xrefTable); + // NOTE hfs->xrefTable can sometimes contain a comma-separated table list, + // rather than just a single table. + char *tables = replaceChars(hfs->xrefTable, ",", " "); + boolean exists = sqlTablesExist(conn, tables); hFreeConn(&conn); + freeMem(tables); if (! exists) return(FALSE); + xrefList = getXrefTerms(db, hfs, term); } else xrefList = slPairNew(cloneString(""), cloneString(term)); for (xrefPtr = xrefList; xrefPtr != NULL; xrefPtr = xrefPtr->next) { found |= doQuery(db, hfs, xrefPtr->name, (char *)xrefPtr->val, hgp, relativeFlag, relStart, relEnd, multiTerm); } slPairFreeValsAndList(&xrefList); return(found); } @@ -2936,31 +2962,31 @@ hgPosTableFreeList(&(hgp->tableList->next)); hgPosFreeList(&(hgp->tableList->posList->next)); } } static boolean searchKnownCanonical(char *db, char *term, struct hgPositions *hgp) /* Look for term in kgXref.geneSymbol, and if found, put knownCanonical coords and * knownGene.name in hgp. */ { boolean foundIt = FALSE; struct sqlConnection *conn = hAllocConn(db); if (sqlTableExists(conn, "knownGene") && sqlTableExists(conn, "knownCanonical") && sqlTableExists(conn, "kgXref")) { char query[512]; - safef(query, sizeof(query), "select chrom,chromStart,chromEnd,kgID from knownCanonical,kgXref " + sqlSafef(query, sizeof(query), "select chrom,chromStart,chromEnd,kgID from knownCanonical,kgXref " "where kgXref.geneSymbol = '%s' and kgXref.kgId = knownCanonical.transcript;", term); struct sqlResult *sr = sqlGetResult(conn, query); char **row; if ((row = sqlNextRow(sr)) != NULL) { singlePos(hgp, "UCSC Genes", term, "knownGene", row[3], row[3], cloneString(row[0]), atoi(row[1]), atoi(row[2])); foundIt = TRUE; } sqlFreeResult(&sr); } hFreeConn(&conn); return foundIt; } @@ -3069,63 +3095,65 @@ } else relEnd = atoi(stripCommas(term+substrs[3].rm_so)); if (canonicalSpec || gbrowserSpec || lengthSpec) relStart--; if (lengthSpec) relEnd += relStart; if (relStart > relEnd) { int tmp = relStart; relStart = relEnd; relEnd = tmp; } relativeFlag = TRUE; } -term = sqlEscapeString(term); +//term = sqlEscapeString(term); // term is pre-escaped ! // DEBUG REMOVE +term = cloneString(term); // DEBUG KEEP? -if (hgOfficialChromName(db, term) != NULL) +if (hgOfficialChromName(db, term) != NULL) // this mangles the term { char *chrom; int start, end; hgParseChromRange(db, term, &chrom, &start, &end); if (relativeFlag) { int chromSize = end; end = start + relEnd; start = start + relStart; if (end > chromSize) end = chromSize; if (start < 0) start = 0; } singlePos(hgp, "Chromosome Range", NULL, "chromInfo", originalTerm, "", chrom, start, end); } else { struct hgFindSpec *shortList = NULL, *longList = NULL; struct hgFindSpec *hfs; boolean done = FALSE; // Disable singleBaseSpec for any term that is not hgOfficialChromName // because that mangles legitimate IDs that are [A-Z]:[0-9]+. if (singleBaseSpec) { singleBaseSpec = relativeFlag = FALSE; - term = sqlEscapeString(originalTerm); + //term = sqlEscapeString(originalTerm); // DEBUG REMOVE + term = cloneString(originalTerm); // DEBUG KEEP relStart = relEnd = 0; } if (!trackHubDatabase(db)) hgFindSpecGetAllSpecs(db, &shortList, &longList); for (hfs = shortList; hfs != NULL; hfs = hfs->next) { if (hgFindUsingSpec(db, hfs, term, hgp, relativeFlag, relStart, relEnd, multiTerm)) { done = TRUE; if (! hgFindSpecSetting(hfs, "semiShortCircuit")) break; } }