/* 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 <mysql/mysql.h>
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 ;)
*/