080a160c7b9595d516c9c70e83689a09b60839d0 galt Mon Jun 3 12:16:53 2013 -0700 fix SQL Injection diff --git src/hg/autoSql/autoSql.c src/hg/autoSql/autoSql.c index 577a805..d996f9e 100644 --- src/hg/autoSql/autoSql.c +++ src/hg/autoSql/autoSql.c @@ -5,30 +5,31 @@ * limitations (described in the autoSql.doc). Rather than do major * changes from here I'd rewrite it though. The tokenizer is * pretty sound, the parser isn't bad (though the language it * parses is quirky), but the code generator is u-g-l-y. * * This file is copyright 2002-2005 Jim Kent, but license is hereby * granted for all use - public, private or commercial. */ #include "common.h" #include "errabort.h" #include "linefile.h" #include "obscure.h" #include "dystring.h" #include "asParse.h" #include "options.h" +#include "jksql.h" boolean withNull = FALSE; boolean addBin = FALSE; boolean makeJson = FALSE; boolean makeDjango = FALSE; boolean defaultZeros = FALSE; void usage() /* Explain usage and exit. */ { errAbort("autoSql - create SQL and C code for permanently storing\n" "a structure in database and loading it back into memory\n" "based on a specification file\n" "usage:\n" @@ -963,33 +964,31 @@ fprintf(f, "return list;\n"); fprintf(f, "}\n\n"); } void dynamicSaveToDbPrintPrototype(char *tableName, FILE *f, boolean addSemi) /* Print out function prototype and opening comment. */ { fprintf(f, "void %sSaveToDb(struct sqlConnection *conn, struct %s *el, char *tableName, int updateSize)%s\n", tableName, tableName, (addSemi ? ";" : "")); fprintf(f, "/* Save %s as a row to the table specified by tableName. \n", tableName); fprintf(f, " * As blob fields may be arbitrary size updateSize specifies the approx size\n"); fprintf(f, " * of a string that would contain the entire query. Arrays of native types are\n"); fprintf(f, " * converted to comma separated strings and loaded as such, User defined types are\n"); -fprintf(f, " * inserted as NULL. Note that strings must be escaped to allow insertion into the database.\n"); -fprintf(f, " * For example \"autosql's features include\" --> \"autosql\\'s features include\" \n"); -fprintf(f, " * If worried about this use %sSaveToDbEscaped() */\n", tableName); +fprintf(f, " * inserted as NULL. This function automatically escapes quoted strings for mysql. */\n"); } boolean lastArrayType(struct asColumn *colList) /* if there are any more string types returns TRUE else returns false */ { struct asColumn *col; for(col = colList; col != NULL; col = col->next) { struct asTypeInfo *lt = col->lowType; enum asTypes type = lt->type; if((col->isArray || col->isList) && type != t_object && type != t_simple) return FALSE; } return TRUE; } @@ -1009,31 +1008,31 @@ /* create C code that will save a table structure to the database */ { char *tableName = table->name; struct asColumn *col; struct dyString *colInsert = newDyString(1024); /* code to associate columns with printf format characters the insert statement */ struct dyString *stringDeclarations = newDyString(1024); /* code to declare necessary strings */ struct dyString *stringFrees = newDyString(1024); /* code to free necessary strings */ struct dyString *update = newDyString(1024); /* code to do the update statement itself */ struct dyString *stringArrays = newDyString(1024); /* code to convert arrays to strings */ boolean hasArray = FALSE; dynamicSaveToDbPrintPrototype(tableName, hFile, TRUE); fprintf(hFile, "\n"); dynamicSaveToDbPrintPrototype(tableName, f, FALSE); fprintf(f, "{\n"); fprintf(f, "struct dyString *update = newDyString(updateSize);\n"); -dyStringPrintf(update, "dyStringPrintf(update, \"insert into %%s values ( "); +dyStringPrintf(update, "sqlDyStringPrintf(update, \"insert into %%s values ( "); dyStringPrintf(stringDeclarations, "char "); for (col = table->columnList; col != NULL; col = col->next) { char *colName = col->name; char *outString = NULL; /* printf formater for column, i.e. %d for int, '%s' for string */ struct asTypeInfo *lt = col->lowType; enum asTypes type = lt->type; char colInsertBuff[256]; /* what variable name matches up with the printf format character in outString */ boolean colInsertFlag = TRUE; /* if column is not a native type insert NULL with no associated variable */ switch(type) { case t_char: outString = (col->fixedSize > 0) ? "'%s'" : "'%c'"; break; case t_string: @@ -1117,185 +1116,44 @@ fprintf(f, ")\", \n\ttableName, "); fprintf(f, "%s);\n", colInsert->string); fprintf(f, "sqlUpdate(conn, update->string);\n"); fprintf(f, "freeDyString(&update);\n"); if(hasArray) { fprintf(f, "%s", stringFrees->string); } fprintf(f, "}\n\n"); dyStringFree(&colInsert); dyStringFree(&stringDeclarations); dyStringFree(&stringFrees); dyStringFree(&update); } -void dynamicSaveToDbEscapedPrintPrototype(char *tableName, FILE *f, boolean addSemi) -/* Print out function prototype and opening comment. */ -{ -fprintf(f, - "void %sSaveToDbEscaped(struct sqlConnection *conn, struct %s *el, char *tableName, int updateSize)%s\n", - tableName, tableName, (addSemi ? ";" : "")); -fprintf(f, - "/* Save %s as a row to the table specified by tableName. \n", tableName); -fprintf(f, " * As blob fields may be arbitrary size updateSize specifies the approx size.\n"); -fprintf(f, " * of a string that would contain the entire query. Automatically \n"); -fprintf(f, " * escapes all simple strings (not arrays of string) but may be slower than %sSaveToDb().\n", tableName); -fprintf(f, " * For example automatically copies and converts: \n"); -fprintf(f, " * \"autosql's features include\" --> \"autosql\\'s features include\" \n"); -fprintf(f, " * before inserting into database. */ \n"); -} - boolean lastStringType(struct asColumn *colList) /* if there are any more string types returns TRUE else returns false */ { struct asColumn *col; for(col = colList; col != NULL; col = col->next) { struct asTypeInfo *lt = col->lowType; enum asTypes type = lt->type; if(type == t_char || type == t_string || type == t_lstring || ((col->isArray || col->isList) && type != t_object && type != t_simple)) return FALSE; } return TRUE; } -void dynamicSaveToDbEscaped(struct asObject *table, FILE *f, FILE *hFile) -/* create C code that will save a table structure to the database with - * all strings escaped. */ -{ -char *tableName = table->name; -struct asColumn *col; -/* We need to do a lot of things with the string datatypes use - * these buffers to only cycle through columns once */ -struct dyString *colInsert = newDyString(1024); /* code to associate columns with printf format characters the insert statement */ -struct dyString *stringDeclarations = newDyString(1024); /* code to declare necessary strings */ -struct dyString *stringFrees = newDyString(1024); /* code to free necessary strings */ -struct dyString *update = newDyString(1024); /* code to do the update statement itself */ -struct dyString *stringEscapes = newDyString(1024); /* code to escape strings */ -struct dyString *stringArrays = newDyString(1024); /* code to convert arrays to strings */ -boolean hasString = FALSE; -boolean hasArray = FALSE; -dynamicSaveToDbEscapedPrintPrototype(tableName, hFile, TRUE); -fprintf(hFile, "\n"); -dynamicSaveToDbEscapedPrintPrototype(tableName, f, FALSE); -fprintf(f, "{\n"); -fprintf(f, "struct dyString *update = newDyString(updateSize);\n"); -dyStringPrintf(update, "dyStringPrintf(update, \"insert into %%s values ( "); -dyStringPrintf(stringDeclarations, "char "); - -/* loop through each of the columns and add things appropriately */ -for (col = table->columnList; col != NULL; col = col->next) - { - char *colName = col->name; - char *outString = NULL; /* printf formater for column, i.e. %d for int, '%s' for string */ - struct asTypeInfo *lt = col->lowType; - enum asTypes type = lt->type; - char colInsertBuff[256]; /* what variable name matches up with the printf format character in outString */ - boolean colInsertFlag = TRUE; /* if column is not a native type insert NULL with no associated variable */ - switch(type) - { - case t_char: - case t_string: - case t_lstring: - /* if of string type have to do all the work of declaring, escaping, - * and freeing */ - if(!col->isArray && !col->isList) - { - hasString = TRUE; - outString = "'%s'"; - /* code to escape strings */ - dyStringPrintf(stringEscapes, "%s = sqlEscapeString(el->%s);\n", colName, colName); - /* code to free strings */ - dyStringPrintf(stringFrees, "freez(&%s);\n", colName); - sprintf(colInsertBuff, " %s", colName); - if(lastStringType(col->next)) - dyStringPrintf(stringDeclarations, " *%s;", colName); - else - dyStringPrintf(stringDeclarations, " *%s,", colName); - } - break; - default: - outString = lt->outFormat; - if (withNull) - sprintf(colInsertBuff, " *(el->%s)", colName); - else - sprintf(colInsertBuff, " el->%s", colName); - break; - } - if(col->isArray || col->isList || type == t_object || type == t_simple) - { - /* if we have a basic array type convert it to a string representation and insert into db */ - if(type != t_object && type != t_simple ) - { - hasArray = TRUE; - outString = "'%s'"; - if(lastStringType(col->next) && lastArrayType(col->next)) - dyStringPrintf(stringDeclarations, " *%sArray;", colName); - else - dyStringPrintf(stringDeclarations, " *%sArray,", colName); - if(col->fixedSize) - dyStringPrintf(stringArrays, "%sArray = sql%sArrayToString(el->%s, %d);\n", colName, lt->listyName, colName, col->fixedSize); - else - dyStringPrintf(stringArrays, "%sArray = sql%sArrayToString(el->%s, el->%s);\n", colName, lt->listyName, colName, col->linkedSizeName); - dyStringPrintf(stringFrees, "freez(&%sArray);\n", colName); - sprintf(colInsertBuff, " %sArray ", colName); - } - /* if we have an object, or simple data type just insert NULL, don't wrap the whole thing up into one string.*/ - else - { - warn("The user defined type \"%s\" in table \"%s\" will be saved to the database as NULL.", col->obType->name, tableName); - outString = " NULL "; - colInsertFlag = FALSE; - } - } - /* can't have comma at the end of the insert */ - if(col->next == NULL) - dyStringPrintf(update, "%s", outString); - else - dyStringPrintf(update, "%s,",outString); - - /* if we still have more columns to insert add a comma */ - if(!noMoreColumnsToInsert(col->next)) - strcat(colInsertBuff, ", "); - /* if we have a column to append do so */ - if(colInsertFlag) - dyStringPrintf(colInsert, "%s", colInsertBuff); - } -if(hasString || hasArray) - { - fprintf(f, "%s\n", stringDeclarations->string); - fprintf(f, "%s\n", stringEscapes->string); - } -fprintf(f, "%s", stringArrays->string); -fprintf(f, "%s", update->string); -fprintf(f, ")\", \n\ttableName, "); -fprintf(f, "%s);\n", colInsert->string); -fprintf(f, "sqlUpdate(conn, update->string);\n"); -fprintf(f, "freeDyString(&update);\n"); -if(hasString) - { - fprintf(f, "%s", stringFrees->string); - } -fprintf(f, "}\n\n"); -dyStringFree(&colInsert); -dyStringFree(&stringDeclarations); -dyStringFree(&stringEscapes); -dyStringFree(&stringFrees); -dyStringFree(&update); -} - boolean internalsNeedFree(struct asObject *table) /* Return TRUE if the fields of a table contain strings, * dynamically sized arrays, objects, or anything else that * needs freeing. */ { struct asColumn *col; for (col = table->columnList; col != NULL; col = col->next) { struct asTypeInfo *lt = col->lowType; enum asTypes type = lt->type; struct asObject *obj; if ((obj = col->obType) != NULL) { if (type == t_object) @@ -1893,31 +1751,30 @@ { cTable(obj, hFile); cSymColumnDefs(obj, cFile); if (obj->isTable) { sqlTable(obj, sqlFile); if (makeDjango) djangoModel(obj, djangoFile); if (!objectHasVariableLists(obj) && !objectHasSubObjects(obj)) staticLoadRow(obj, cFile, hFile); if(doDbLoadAndSave) { dynamicLoadByQuery(obj, cFile, hFile); dynamicSaveToDb(obj, cFile, hFile); - dynamicSaveToDbEscaped(obj, cFile, hFile); } } dynamicLoadRow(obj, cFile, hFile); dynamicLoadAll(obj, cFile, hFile); dynamicLoadAllByChar(obj, cFile, hFile); makeCommaIn(obj, cFile, hFile); /*if (makeJson) makeJsonInput(obj, cFile, hFile);*/ if (obj->isSimple) { if (internalsNeedFree(obj)) makeFreeInternals(obj, cFile, hFile); } else