[CODE] A Better mySQL Query Wrapper

From: Greg Buxton (gbuxton@maple.he.net)
Date: 03/09/03

It's been a bit quiet on the list, so here's a bit of code which
some of you might find useful.

This is an "improved" wrapper function for mysql_real_query.  It's
a va_arg function, but doesn't use vsprintf.  It only has support
for %s, %c, %d, and %ld format chars, but the advantage of it is
that it automatically passes strings through mysql_real_escape_string
to make them safe for mysql_real_query.

The function returns an int with the returned error from
mysql_real_query (if any.) For simplicity it's also passed a pointer
to a MYSQL_RES variable to store the result in, but that could easily
be removed and have code calling sqlQuery get the result normally via

This was developed on a mud where the pfile sql table has 116 columns,
and works fine there.  Parts of the code could be a bit cleaner, but
this saves the need of having to have existing variables to store the
escaped strings in before you do your queries.

- Greg Buxton
Shadows of Amber / A Moment in Tyme
shadows.dune.net 5200
tyme.envy.com 6969


int sqlQuery(MYSQL_RES *resource, char *format, ...) {
  char query[100000], reformat[100000];
  va_list args;
  int holder_int;
  char *holder_string, holder_char;
  long holder_long;
  int sqlerror=0;

  *query = 0;
  va_start(args, format);
  while (*format && (strlen(query) < 9000))
    if (*format == '%')
      switch (*format)
        case 's':
        case 'S':                       // String (%s)  The whole reason
we do it this way.
          holder_string = va_arg(args, char *);
          mysql_real_escape_string(db, reformat, holder_string,
MIN(4999,holder_string != NULL ? strlen(holder_string) : 0));
          strcat(query, reformat);
        case 'c':
        case 'C':                       // Char (%c)
          holder_char = (char) va_arg(args, int);
          sprintf(reformat, "%c", holder_char);
          strncat(query, reformat, 1);
        case 'd':
        case 'D':                       // Int (%d)
          holder_int = va_arg(args, int);
          sprintf(reformat, "%d", holder_int);
          strcat(query, reformat);
        case 'l':
        case 'L':                       // Long (%ld)
          format++;                     // To keep the printf() chars,
drop the d in %ld.
          holder_long = va_arg(args, long);
          sprintf(reformat, "%ld", holder_long);
          strcat(query, reformat);
         strncat(query, format, 1); // Works fine for 0 as well.
      strncat(query, format, 1); // Not a % sequence, just copy

  slog(":: sqlQueryTwo : %s", query);

  if (mysql_real_query(db, query, strlen(query))) {
    sqlerror = mysql_errno(db);
    slog("SQL: Error With Query at (%s)%s:%d: %s (Query: %s)", __FILE__,
__FUNCTION__, __LINE__, mysql_error(db), query);
    return (sqlerror);

  resource = mysql_store_result(db);

  return (sqlerror);

   | FAQ: http://qsilver.queensu.ca/~fletchra/Circle/list-faq.html |
   | Archives: http://post.queensu.ca/listserv/wwwarch/circle.html |
   | Newbie List:  http://groups.yahoo.com/group/circle-newbies/   |

This archive was generated by hypermail 2b30 : 06/26/03 PDT