/* MySQL based Bans Written by Justin Rocha and Jason Anderson for the Age of Legacy, kyndig.com port 3000 and for Just Another MUD Engine (JaMUDe) - http://jamude.sf.net/ Please send bug reports and feedback to legacycoders@kyndig.com We're putting the code we wrote for Legacy up for public use, because we know how difficult it is to find good examples of MySQL usage in mud coding. While we don't claim this to be the best or most efficient, it works, and hopefully can be used to make the jobs of other developers a little easier. MySQL is a fast and powerful database, and with a little creativity, can be used for virtually all information storage and retrieval in many applications. Rather than just go over the code, we'll give step by step instructions to setting up the bans table and implementing the code. We assume that the reader has at least basic experience with MySQL (we didn't when we wrote this, but the documentation found in the MySQL website is most informative regarding setting up tables, using the database, etc). Before you start, you need to make sure that your mud can resolve IPs and use their names. I'm afraid I don't know how stock ROMs handle this, so in the rest of this snippet I'll assume that you are able to use the site name in nanny(). Once you have a database set up, you'll need to create a table to hold the ban information. Do this with this query from the command line: CREATE TABLE bans (site VARCHAR(30), name VARCHAR(20), flags INTEGER, reason VARCHAR(100)); This creates your bans table with four fields, the name of the site, name of the person who banned the site, a bit packed integer for extra info, and the reason given for banning. (note: syntax for a query is case insensitive, but in this example and code, we capitalize MySQL keywords for clarity of reading) Now that you have your table, you're ready to use the code. The following three immortal commands can go in act_wiz.c (or wherever you prefer): void do_ban (CHAR_DATA *ch, char *argument); void do_allow (CHAR_DATA *ch, char *argument); void do_permit (CHAR_DATA *ch, char *argument); The last function, check_ban, can go anywhere, we keep ours in comm.c. Files using MySQL C APIs need to have this include: #include This includes the standard header file for the APIs, assuming that it is with the other standard header files (ours is in /usr/include/). Also, you'll need to add this LAST to your link flags in Makefile: -L/usr/include/mysql -lmysqlclient This, again, assumes that the MySQL headers are with the other header files on your system. Some editing may be necessary depending on the exact location of these files. This code uses constant definitions for the values used to connect to the MySQL database, I can't vouch for them working on every system, but these work here at Kyndig.com. #define DB_HOST "localhost" #define DB_PORT 0 #define DB_SOCKET NULL #define DB_CLIENT_FLAG 0 #define DB_USER "yourusername" #define DB_DB "yourdatabasename" #define DB_PASS "yourdatabasepassword" You can put these in merc.h, although if you plan to use MySQL extensively you may want to create a header file for your MySQL stuff. Now, on with the code! */ /* This function bans a site. Without an argument, it will list all currently banned sites, an argument requires the site name, type of ban, and reason for banning. */ void do_ban(CHAR_DATA *ch, char *argument) { char arg1[MAX_INPUT_LENGTH], arg2[MAX_INPUT_LENGTH], query[MAX_STRING_LENGTH], site[MAX_INPUT_LENGTH]; char *p; int flags = 0; MYSQL *db; MYSQL_RES *result; /* No argument, list the sites */ if (argument[0] == '\0') { MYSQL_ROW row; BUFFER *output; bool found = FALSE; /* Initialize the database */ if ((db = mysql_init(NULL)) == NULL) { bug("do_ban: error on initialize", 0); return; } /* Connect to the database */ if (!mysql_real_connect(db, DB_HOST, DB_USER, DB_PASS, DB_DB, DB_PORT, DB_SOCKET, DB_CLIENT_FLAG)) { bug("do_ban: error on connect", 0); /* Calling mysql_error() will return a text representation of what went wrong. A similar function is mysql_errno(), which will return the error number so you can handle it as you wish. */ bugf("Error: %s", mysql_error(db)); return; } /* Send a query to the database, SELECT all fields FROM the ban table. The 18 is how many characters long the query is, constant here, although strlen() could be called for varying length querys. mysql_real_query() will return 0 if the query is correct, otherwise if the syntax is wrong */ if (mysql_real_query(db, "SELECT * FROM bans", 18)) { bug("do_ban: error on query", 0); bugf("Error: %s", mysql_error(db)); /* At this point, we have an open connection, we need to close the connection before we return */ mysql_close(db); return; } /* Store the results of the query for processing */ result = mysql_store_result(db); output = new_buf(); add_buf(output, "Banned Sites {T|{xBanned by {T|{xType {T|{xReason\n\r"); add_buf(output, "{T------------------------------+---------------+-------+-----------------------------{x\n\r"); /* A while loop to fetch all rows of the results, one at a time. Stops when there are no more rows */ while ((row = mysql_fetch_row(result))) { char buf[MSL]; found = TRUE; /* Process each row into usable data. mysql_fetch_row() always returns the result in text format, so we call atoi() to convert row[2] (the flags field) into a number for parsing. */ flags = atoi(row[2]); /* This is explained more later, but wildcards are not stored in the actual site, because of slower processing in other functions. Reproduce the original banned site by reinserting the wildcards */ sprintf(site, "%s%s%s", IS_SET(flags, BAN_PREFIX) ? "{P*{x" : "", row[0], IS_SET(flags, BAN_SUFFIX) ? "{P*{x" : ""); sprintf(buf, "%-30s{T|{x%-15s{T|{x%s{T|{x%s\n\r", site, row[1], IS_SET(flags, BAN_PERMIT) ? "PERMIT " : IS_SET(flags, BAN_ALL) ? " ALL " : IS_SET(flags, BAN_NEWBIES) ? "NEWBIES" : " ", row[3]); add_buf(output, buf); } if (found) page_to_char(buf_string(output), ch); else send_to_char("There are no banned hosts.\n\r", ch); free_buf(output); /* Here we free the memory used by the results */ mysql_free_result(result); /* And, close the connection, we're done */ mysql_close(db); return; } /* An argument was given, process into a new ban */ /* MySQL has problems with some characters in a query, such as apostrophes. The API mysql_real_escape_string() handles this, but I've had problems with it, probably due to incorrect usage. This is actually more of a quick fix, I highly recommend looking into mysql_real_escape_string() and using it rather than the smash_apostrophe() function, which I've included with this snippet. Basically, smash_apostrophe() parses a string and turns apostrophes into escape stringed apostrophes, so the query will work. */ argument = one_argument(smash_apostrophe(argument), arg1); argument = one_argument(argument, arg2); /* To prevent problems below, set a minimum of 5 characters, and maximum of 30 characters to prevent overrunning the size of the field, banning too specifically, and screwing up our nice listing of bans. */ if (strlen(arg1) < 5 || strlen(arg1) > 30) { send_to_char("The site must be between 5 and 30 characters long.\n\r", ch); return; } if (arg2[0] == '\0') { send_to_char("You must specify a ban type.\n\r", ch); return; } if (argument[0] == '\0') { send_to_char("You must provide a reason.\n\r", ch); return; } /* Start setting bits on our flags variable. Here, the user can specify to ban all connections, or only newbies. */ if (!str_cmp(arg2, "all")) SET_BIT(flags, BAN_ALL); else if (!str_cmp(arg2, "newbies")) SET_BIT(flags, BAN_NEWBIES); else { send_to_char("Type must be ALL or NEWBIES.\n\r", ch); return; } /* Parse the string for wildcards. We change them into bits in the flags variable, because do_ban() is called so much less frequently than check_ban(). */ p = arg1; if (*p == '*') { sprintf(site, p+1); SET_BIT(flags, BAN_PREFIX); } else sprintf(site, p); for (p = site; *(p+1) != '\0'; p++); if (*p == '*') { *p = '\0'; SET_BIT(flags, BAN_SUFFIX); } /* Initialize */ if ((db = mysql_init(NULL)) == NULL) { bug("do_ban: error on initialize", 0); return; } /* Connect */ if (!mysql_real_connect(db, DB_HOST, DB_USER, DB_PASS, DB_DB, DB_PORT, DB_SOCKET, DB_CLIENT_FLAG)) { bug("do_ban: error on connect", 0); bugf("Error: %s", mysql_error(db)); return; } /* Here we look for a banned site by the same name. The bit math on this might look complicated, but in essence, it checks the last four bits of the flags on the results vs the bits already set on our temporary flags variable, for prefix, suffix, and type matching. Currently the only flag that could be cut off with this is BAN_PERMIT, but this allows for future options */ sprintf(query, "SELECT * FROM bans WHERE site='%s' AND (flags-((flags>>4)<<4))=%d", site, flags); /* Send the query. Since the query is variable length, call strlen() to send the correct character count */ if (mysql_real_query(db, query, strlen(query))) { bug("do_ban: error on query", 0); bugf("Error: %s", mysql_error(db)); mysql_close(db); return; } /* Store the results of the query */ result = mysql_store_result(db); /* If there were any results, there is another ban of the same type with the same name. Otherwise... */ if (mysql_num_rows(result)) printf_to_char(ch, "%s is already banned.\n\r", arg1); else { /* Insert a new ban into the list */ sprintf(query, "INSERT INTO bans VALUES('%s','%s',%d,'%s')", site, ch->name, flags, argument); /* Send the query, if successful, report to the user. Don't close on failure, let it fall through to the result freeing and close below */ if (mysql_real_query(db, query, strlen(query))) { bug("do_ban: error on query", 0); bugf("Error: %s", mysql_error(db)); } else printf_to_char(ch, "%s has been banned.\n\r", arg1); } /* Free the result and close the connection */ mysql_free_result(result); mysql_close(db); } /* This function removes a banned site from the list. While it *could* be part of do_ban(), on Legacy ban and allow are in different command groups */ void do_allow(CHAR_DATA *ch, char *argument) { char arg[MAX_INPUT_LENGTH], query[MAX_STRING_LENGTH], site[MAX_INPUT_LENGTH]; char *p; MYSQL *db; int wildflags = 0; one_argument(argument, arg); if (arg[0] == '\0') { send_to_char("Remove which site from the ban list?\n\r", ch); return; } /* Only check for minimum length to prevent messing up our string hacking below */ if (strlen(arg) < 5) { send_to_char("Banned sites are no shorter than five characters.\n\r", ch); return; } /* Parse the string for wildcards, so we can find the correct ban in the list */ p = arg; if (*p == '*') { sprintf(site, p+1); SET_BIT(wildflags, BAN_PREFIX); } else sprintf(site, p); for (p = site; *(p+1) != '\0'; p++); if (*p == '*') { *p = '\0'; SET_BIT(wildflags, BAN_SUFFIX); } /* Initialize */ if ((db = mysql_init(NULL)) == NULL) { bug("do_allow: error on initialize", 0); return; } /* Connect */ if (!mysql_real_connect(db, DB_HOST, DB_USER, DB_PASS, DB_DB, DB_PORT, DB_SOCKET, DB_CLIENT_FLAG)) { bug("do_allow: error on connect", 0); bugf("Error: %s", mysql_error(db)); return; } /* Attempt to delete the row with matching ban information. Only compare the last two bits to our temporary wildflags, we don't care what type of ban it is */ sprintf(query, "DELETE FROM bans WHERE site='%s' AND (flags-((flags>>2)<<2))=%d", smash_apostrophe(site), wildflags); /* Send the query */ if (mysql_real_query(db, query, strlen(query))) { bug("do_allow: error on query", 0); bugf("Error: %s", mysql_error(db)); mysql_close(db); return; } /* If any rows were affected by the last query, it means that a matching ban was found and deleted */ if (mysql_affected_rows(db)) printf_to_char(ch, "Ban on %s lifted.\n\r", arg); else send_to_char("That site is not banned.\n\r", ch); /* Close the connection */ mysql_close(db); } /* Add a BAN_PERMIT flag to a ban, for allowing temporary access to the mud. Useful especially for bans of type BAN_NEWBIES, to allow a person to create a character and then reban the site. We do this to prevent having to remove the ban completely and then reset it */ void do_permit(CHAR_DATA *ch, char *argument) { char arg[MAX_INPUT_LENGTH], query[MAX_STRING_LENGTH], site[MAX_INPUT_LENGTH]; char *p; MYSQL *db; MYSQL_RES *result; MYSQL_ROW row; int wildflags = 0; bool found = FALSE; one_argument(argument, arg); if (arg[0] == '\0') { send_to_char("Permit which site to play?\n\r", ch); return; } /* Prevent screwing up our string mangling */ if (strlen(arg) < 5) { send_to_char("Banned sites are no shorter than five characters.\n\r", ch); return; } p = arg; if (*p == '*') { sprintf(site, p+1); SET_BIT(wildflags, BAN_PREFIX); } else sprintf(site, p); for (p = site; *(p+1) != '\0'; p++); if (*p == '*') { *p = '\0'; SET_BIT(wildflags, BAN_SUFFIX); } /* Initialize */ if ((db = mysql_init(NULL)) == NULL) { bug("do_permit: error on initialize", 0); return; } /* Connect */ if (!mysql_real_connect(db, DB_HOST, DB_USER, DB_PASS, DB_DB, DB_PORT, DB_SOCKET, DB_CLIENT_FLAG)) { bug("do_permit: error on connect", 0); bugf("Error: %s", mysql_error(db)); return; } /* Find our list of matching sites */ sprintf(query, "SELECT * FROM bans WHERE site='%s' AND (flags-((flags>>2)<<2))=%d", smash_apostrophe(site), wildflags); /* Send the query */ if (mysql_real_query(db, query, strlen(query))) { bug("do_permit: error on query", 0); bugf("Error: %s", mysql_error(db)); mysql_close(db); return; } /* Store the results */ result = mysql_store_result(db); /* Find the *first* result of the query and modify it */ while (!found && (row = mysql_fetch_row(result))) { /* Set up a temporary variable to set bits on */ int rowflags = atoi(row[2]); /* Modify our temporary bit packed variable */ if (IS_SET(rowflags, BAN_PERMIT)) { printf_to_char(ch, "Permit flag removed on %s.\n\r", arg); REMOVE_BIT(rowflags, BAN_PERMIT); } else { printf_to_char(ch, "Permit flag set on %s.\n\r", arg); SET_BIT(rowflags, BAN_PERMIT); } /* Update table, make sure we get the correct one by comparing the flags to the results of our query */ sprintf(query, "UPDATE bans SET flags=%d WHERE site='%s' AND flags=%d", rowflags, smash_apostrophe(row[0]), atoi(row[2])); /* Send the query. If it fails, make sure to free the results and close before we return */ if (mysql_real_query(db, query, strlen(query))) { bug("do_permit: error on query", 0); bugf("Error: %s", mysql_error(db)); mysql_free_result(result); mysql_close(db); return; } /* We found it, exit the loop on the next iteration */ found = TRUE; } /* If we didn't find it, inform the user */ if (!found) send_to_char("That site is not banned.\n\r", ch); /* Free the results and close */ mysql_free_result(result); mysql_close(db); } /* This function is called from nanny(), in two places; once to check if all connections from said host are banned, and once in new character creation for newbies. Simply find an appropriate place for checking and insert something like: if (check_ban(host, BAN_ALL)) { write_to_descriptor(descriptor, "Your site is banned from play.", 0); close_socket(descriptor); continue; } The other instance, in newbie creation, would use BAN_NEWBIES and a more appropriate message. In this case, don't forget to free the character if it has already been allocated. */ bool check_ban(char *site, int type) { char query[MAX_STRING_LENGTH]; MYSQL *db; MYSQL_RES *result; MYSQL_ROW row; bool ban = FALSE; /* Initialize */ if ((db = mysql_init(NULL)) == NULL) { bug("check_ban: error on initialize", 0); return FALSE; } /* Connect */ if (!mysql_real_connect(db, DB_HOST, DB_USER, DB_PASS, DB_DB, DB_PORT, DB_SOCKET, DB_CLIENT_FLAG)) { bug("check_ban: error on connect", 0); bugf("Error: %s", mysql_error(db)); return FALSE; } /* Query the database for all sites matching the passed type, regardless of their prefix/suffix bits. Note that this will not return results with the BAN_PERMIT bit set, since those sites are permitted to log on anyway. */ sprintf(query, "SELECT * FROM bans WHERE (flags-((flags>>2)<<2))=%d", type); /* Send the query */ if (mysql_real_query(db, query, strlen(query))) { bug("check_ban: error on query", 0); bugf("Error: %s", mysql_error(db)); mysql_close(db); return FALSE; } /* Store the results for prefix/suffix checking */ result = mysql_store_result(db); /* Fetch each row for checking */ while (!ban && (row = mysql_fetch_row(result))) { int flags = atoi(row[2]); /* Make a couple of booleans to make the below if statement more readable */ bool prefix = IS_SET(flags, BAN_PREFIX); bool suffix = IS_SET(flags, BAN_SUFFIX); /* Logic of this statement is as follows: if both prefix and suffix wildcards are set, and the banned site is any part of the passed host, TRUE if prefix and not suffix wildcards are set, and the banned site is a suffix of the passed host, TRUE if suffix and not prefix wildcards are set, and the banned site is a prefix of the passed host, TRUE if neither prefix nor suffix wildcards are set, and the banned site matches the passed host exactly, TRUE otherwise, continue checking the query results */ if ((prefix && suffix && !str_infix(row[0], site)) || (prefix && !suffix && !str_suffix(row[0], site)) || (!prefix && suffix && !str_prefix(row[0], site)) || (!prefix && !suffix && !str_cmp(row[0], site))) ban = TRUE; } /* Free the result and close */ mysql_free_result(result); mysql_close(db); /* Return FALSE if the ban was not found in the database, or TRUE if it was */ return ban; } /*** MISCELLANEOUS FUNCTIONS ***/ /* These are included in this file in case you do not have them, because they are used in the above code. */ /* Insert a backslash before apostrophes in a string. Used to make a string legal for a MySQL query. */ char *smash_apostrophe(char *str) { static char string[MAX_STRING_LENGTH]; char *p = string; while (*str != '\0') { if (*str == 39) /* an apostrophe */ { *p = 92; /* a backslash */ p++; } *p = *str; p++; str++; } *p = '\0'; return string; } /* Compare strings, case insensitive. Return TRUE if different (compatibility with historical functions). */ bool str_cmp( const char *astr, const char *bstr ) { for ( ; *astr || *bstr; astr++, bstr++) if (LOWER(*astr) != LOWER(*bstr)) return TRUE; return FALSE; } /* Compare strings, case insensitive, for prefix matching. Return TRUE if astr is not a prefix of bstr (compatibility with historical functions). */ bool str_prefix( const char *astr, const char *bstr ) { for ( ; *astr; astr++, bstr++) if (LOWER(*astr) != LOWER(*bstr)) return TRUE; return FALSE; } /* Compare strings, case insensitive, for match anywhere. Returns TRUE if astr is not part of bstr. (compatibility with historical functions). */ bool str_infix( const char *astr, const char *bstr ) { int sstr1, sstr2, ichar; char c0; if ((c0 = LOWER(astr[0])) == '\0') return FALSE; sstr1 = strlen(astr); sstr2 = strlen(bstr); for (ichar = 0; ichar <= sstr2 - sstr1; ichar++) if (c0 == LOWER(bstr[ichar]) && !str_prefix(astr, bstr + ichar)) return FALSE; return TRUE; } /* Compare strings, case insensitive, for suffix matching. Return TRUE if astr is not a suffix of bstr (compatibility with historical functions). */ bool str_suffix( const char *astr, const char *bstr ) { int sstr1, sstr2; sstr1 = strlen(astr); sstr2 = strlen(bstr); if (sstr1 <= sstr2 && !str_cmp(astr, bstr + sstr2 - sstr1)) return FALSE; return TRUE; } /*** ADDITIONAL ***/ /* This code performs an initialization and connection every time a query is to be sent to the MySQL database. This can be slow, so if you plan to use MySQL in your mud to any great extent, I recommend talking to your system administrator about maintaining a constant connection to the database through a global connection handler used by all your functions that interact with the database. The details of this shouldn't be hard to work out for an experienced coder, so I won't go into them here. The savings in processing time and system resources drain by maintaining a connection could be worthwhile, though. We hope you can make use of the information contained here, and would do the same as we did by sharing your progress with the mud community. Implementation of databasing in muds is still rather uncommon, especially for ROMs, so documentation is almost nonexistant. This and a few other examples are really all that are out there, it would be great if more people released code or other help to the less experienced users. Thanks for reading ;) */