// Note that this file has bits that are specific to the // CircleMUD based system it ran in, and will likely not // compile standalone. // Not also that the jelos98@yahoo.com address is also mine, // and I while I do still check it, it currently receives 99.9% // spam. /******************* SQLMUD SOURCE FILE ******************* *** Originally by Jelos (jelos98@yahoo.com) *** *********************************************************/ #include "conf.h" #include "sysdep.h" #include "structs.h" #include "utils.h" #include "mysql.h" #include "sql_lib.h" #include/**************************************************************************** * Defines: Why are they here? Nowhere else needs them, and if we want to * tighten security, we can NOT make this file available, merely the .o, * thus making the mud's SQL login/pass secret (NOTE: to do this, you also * need to NOT specify them both as static strings, which would be easily * readable if you have time to pore through the .o file) ***************************************************************************/ // Information changed to protect the innocent. #define SQL_SERVER "mysql.server.com" #define SQL_PORT 3306 #define SQL_USER "USER" #define SQL_PASS "PASS" #define SQL_DATABASE "DB" MYSQL* mysql; bool initialize_mysql() { CREATE(mysql, MYSQL, 1); mysql_init(mysql); if (!(mysql_real_connect(mysql, SQL_SERVER, SQL_USER, SQL_PASS, SQL_DATABASE, SQL_PORT, NULL, 0))) { return FALSE; }; return TRUE; }; bool cleanup_mysql() { mysql_close(mysql); free(mysql); return TRUE; }; void free_mysql_result(struct mysql_result* freeme) { int i; /* Free up the fields */ for (i = 0; i < freeme->num_fields; i++) { free(freeme->values[i]); }; /* Free the pointer to the fields */ free(freeme->values); freeme->num_fields = 0; /* Free the pointer itself */ free(freeme); }; void free_mysql_result_set(struct mysql_result_set* freeme) { int i; /* Free each of the results in the set */ for (i = 0; i < freeme->num_rows; i++) { free_mysql_result(freeme->results[i]); }; /* Free the main pointer to those results */ free(freeme->results); freeme->num_rows = 0; /* Free the pointer to the set */ free(freeme); }; /* This function changes ' and " to characters that won't be used (ascii code < 0), so that they don't get escaped. It's a bit of a trick allowing us to excape all the data passed in in the sprintf, without escaping the format string */ void escape_sql(char* in) { int i; for (i = 0; i < strlen(in); i++) { switch(in[i]) { case '\'': in[i] = -123; break; /* Use The square root symbol. */ case '"': in[i] = -113; break; /* Use +/- symbol. */ }; }; }; /* This function undoes the damage of escape_sql, allowing us to bring * back ' and " characters in the format. */ void unescape_sql(char* in) { int i; for (i = 0; i < strlen(in); i++) { switch(in[i]) { case -123: in[i] = '\''; break; /* Use The square root symbol. */ case -113: in[i] = '"'; break; /* Use +/- symbol. */ }; }; }; struct mysql_result* select_first(char* format, ...) { int number_of_fields; int nRows; MYSQL_ROW row; MYSQL_RES* res; /* Bleh. But we need buffers */ char formbuf[8192]; char sqlbuf[8192]; char escapedsqlbuf[16385]; struct mysql_result* output; va_list args; int field; /* We have to copy format because we are going to modify it, which will * seg fault if you pass in a static string. */ strncpy(formbuf, format, 8192); escape_sql(formbuf); /* Now use vsnprintf to do the magic of parsing it all together */ va_start(args, format); vsnprintf(sqlbuf, 8192, formbuf, args); va_end(args); /* Now, we escape the string. This will ignore ' and " in the format, * since they are now funky symbols */ mysql_real_escape_string(mysql, escapedsqlbuf, sqlbuf, strlen(sqlbuf)); /* Finally, convert those characters back. */ unescape_sql(escapedsqlbuf); /* Perform the actual query */ if (mysql_query(mysql, escapedsqlbuf)) { log("SYSERR: MySQL query failed: %s\r\n", escapedsqlbuf); return NULL; }; /* Fetch all of the results */ res = mysql_store_result(mysql); /* Iffen you don't get a result, something failed. */ if (res) { /* count the fields, and count the rows */ number_of_fields = mysql_num_fields(res); nRows = mysql_num_rows(res); if (number_of_fields < 1) { log("SYSERR: Query returned no fields: %s\r\n", escapedsqlbuf); return NULL; }; CREATE(output, struct mysql_result, 1); output->num_fields = number_of_fields; /* CREATE kept giving compile erros for this */ output->values = calloc(number_of_fields, sizeof(char*)); row = mysql_fetch_row(res); /* Originally gave a warning/error here, but it's not really an error. You need to be able to make queries for non-existant data.*/ if (!row) { return NULL; }; /* Copy over all of the fields */ for (field = 0; field < number_of_fields; field++) { output->values[field] = row[field] ? (strdup(row[field])) : (strdup("")); }; /* Clean up the original */ mysql_free_result(res); return output; } else { log("SYSERR: Mysql query could not store results: %s\r\n",escapedsqlbuf); return NULL; }; }; /* See select_first for more comments on this. This one just doesn't return rows, so is faster and better for UPDATE/DELETE/INSERT calls. Returns TRUE on success */ bool modify(char* format, ...) { char formbuf[8192]; char sqlbuf[8192]; char escapedsqlbuf[16385]; va_list args; strcpy(formbuf, format); escape_sql(formbuf); va_start(args, format); vsnprintf(sqlbuf, 8192, formbuf, args); va_end(args); mysql_real_escape_string(mysql, escapedsqlbuf, sqlbuf, strlen(sqlbuf)); unescape_sql(escapedsqlbuf); return !mysql_query(mysql, escapedsqlbuf); }; /* This version retrieves all matching rows, instead of just the first */ struct mysql_result_set* selectf(char* format, ...) { int number_of_fields; int nRows; MYSQL_ROW row; MYSQL_RES* res; char formbuf[8192]; char sqlbuf[8192]; char escapedsqlbuf[16385]; struct mysql_result_set* output; va_list args; int field; int row_n; strncpy(formbuf, format, 8192); escape_sql(formbuf); va_start(args, format); vsnprintf(sqlbuf, 8192, formbuf, args); va_end(args); mysql_real_escape_string(mysql, escapedsqlbuf, sqlbuf, strlen(sqlbuf)); unescape_sql(escapedsqlbuf); mysql_query(mysql, escapedsqlbuf); res = mysql_store_result(mysql); nRows = mysql_num_rows(res); number_of_fields = mysql_num_fields(res); if (!nRows) { return NULL; }; /* Create our main object */ CREATE(output, struct mysql_result_set, 1); output->num_rows = nRows; /* Allocate enough room for all of the results */ output->results = calloc(nRows, sizeof(struct mysql_result*)); row_n = 0; /* Iterate over each result and copy it */ while( (row = mysql_fetch_row(res))) { CREATE(output->results[row_n], struct mysql_result, 1); /* Allocate room for the fields, and store the count */ output->results[row_n]->values = calloc(number_of_fields, sizeof(char*)); output->results[row_n]->num_fields = number_of_fields; /* Copy over each of the fields */ for (field = 0; field < number_of_fields; field++) { if (row[field]) { output->results[row_n]->values[field] = strdup(row[field]); } else { output->results[row_n]->values[field] = strdup(""); }; }; row_n++; }; /* Free the original result, and return */ mysql_free_result(res); return output; }; /************************************************************************* * $Log: $ ************************************************************************/