Re: [NEWBIE] Escaping (') from strings

From: Daniel A. Koepke (
Date: 09/03/01

On Sun, 2 Sep 2001, Artovil wrote:

> ... so I am just sticking with your simple string_to_store which I
> believe will be faster anyway, since all I need to escape is (').

Unlikely.  Or, at least, if it's true, it's a trivial metric to compare
by, since there's no noticeable difference to you or to the end-users.  I
was under the impression that there was more to quoting for MySQL than
simply escaping apostrophes.  IIRC, the quotation mark, backslash,
newline, carriage return, NUL, and CTRL+Z characters all need to be
escaped as well.  In addition, the string_to_store() function and its kin
use the strcat() function repeatedly, which may not be efficiently
implemented depending upon your architecture and is undoubtedly not as
efficient as pointer explicit pointer arithmetic and handling insertion
yourself.  The mysql_real_escape_string() funciton may, therefore, be
actually faster/more efficient.  It's still a meaningless metric.  The
string_to_store() function and its kin also do not check for buffer
overflows, which can be dangerous/disasterous when you're sending things
to the MySQL server.

I would strongly recommend using the provided function in the library for
this.  It's guaranteed to do things right for you.

Having said all of that:

> How would I do that without messing the old string up?

You can return a static buffer from a function.  An example wrapper for

  char *quote_mysql(MYSQL *sql, const char *txt)
    static char buffer[MAX_STRING_LENGTH*2+1];
    mysql_real_escape_string(sql, buffer, txt, strlen(txt));
    return (buffer);

which would then be used like

  sprintf(request, "INSERT INTO foobar values('%s', 'Binary data: %s')",
          quote_mysql(mysql, "It's Working"),
          quote_mysql(mysql, "\r\n\0\r\n"));

We can get a little more fancy, though, with something similar to

  ssize_t qquery_mysql(MYSQL *sql, const char *fmt, ...)
    char buf[MAX_STRING_LENGTH*2+1];
    char *top = fmt + strlen(fmt);
    register char *ptr = buf;
    const char *str;
    va_list ap;

    va_start(ap, fmt);

    for ( ; fmt < top; fmt++) {
      if (*fmt == '$' && *(fmt+1)) {
        switch (*(++fmt)) {
        case 'q':               /* Escaped string. */
          str = va_arg(ap, const char *);
          ptr += mysql_real_escape_string(sql, ptr, str, strlen(str));
        case 's':               /* Unescaped string. */
          str = va_arg(ap, const char *);
          while (*str) *(ptr++) = *(str++);
        case '$':               /* $$ = $ */
          *(ptr++) = '$';
        default:                /* Invalid taken verbatim. */
          *(ptr++) = '$';
          *(ptr++) = *fmt;
      } else
        *(ptr++) = *fmt;

    *ptr = '\0';

    if (mysql_real_query(sql, buf, ptr - buf)) {
      log("SYSERR:MySQL: Failed on query: %s", buf);
      return (-1);

    return (ptr - buf);

which would replace something like:

  sprintf(buf, "INSERT INTO %s values('%s', '%s')",
          GET_NAME(foo), quote_mysql(GET_NAME(foo)),

  if (mysql_query(sql, buf) {
    log("SYSERR:MySQL: Failed on query: %s", buf);
    ... handle the error condition ...;


  qquery_mysql(sql, "INSERT INTO $s values('$q', '$q')",
               GET_NAME(foo), GET_NAME(foo),

There are undoubtedly better ways to do these things.  This is Mailer
Code(tm), so it's not guaranteed to work.  It's written late and with no
recent MySQL experience.  I didn't bother consulting actual documentation,
since I'm too lazy.  Use it at your own risk.


   | FAQ: |
   | Archives: |

This archive was generated by hypermail 2b30 : 12/06/01 PST