Making a javascript string sql friendly


Question

Is there away to make a javascript string being passed to NodeJS friendly for MySQL? I'm trying to pass an email address to my NodeJS server and query into MySQL database. When doing regular text such as a username works fine, but the email address doesn't. Using escape clearly is not the right answer as it is not meant for SQL insertion. I'm assuming I need something on the lines of the PHP function mysql_real_escape_string().

1
45
10/12/2011 6:45:14 PM

Accepted Answer

It turns out that mysql_real_escape_string() is pretty trivial. According to the documentation:

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

Sounds pretty simple, actually. You could do something like this:

function mysql_real_escape_string (str) {
    return str.replace(/[\0\x08\x09\x1a\n\r"'\\\%]/g, function (char) {
        switch (char) {
            case "\0":
                return "\\0";
            case "\x08":
                return "\\b";
            case "\x09":
                return "\\t";
            case "\x1a":
                return "\\z";
            case "\n":
                return "\\n";
            case "\r":
                return "\\r";
            case "\"":
            case "'":
            case "\\":
            case "%":
                return "\\"+char; // prepends a backslash to backslash, percent,
                                  // and double/single quotes
        }
    });
}

NOTE: I haven't run this through any sort of unit test or security test, but it does seem to work -- and, just as an added bonus, it escapes tabs, backspaces, and '%' so it can also be used in LIKE queries, as per OWASP's recommendations (unlike the PHP original).

I do know that mysql_real_escape_string() is character-set-aware, but I'm not sure what benefit that adds.

There's a good discussion of these issues over here.

55
5/23/2017 12:17:51 PM

In case someone is looking for, the escapeString() in CUBRID RDBMS works as follows:

var _escapeString = function (val) {
  val = val.replace(/[\0\n\r\b\t\\'"\x1a]/g, function (s) {
    switch (s) {
      case "\0":
        return "\\0";
      case "\n":
        return "\\n";
      case "\r":
        return "\\r";
      case "\b":
        return "\\b";
      case "\t":
        return "\\t";
      case "\x1a":
        return "\\Z";
      case "'":
        return "''";
      case '"':
        return '""';
      default:
        return "\\" + s;
    }
  });

  return val;
};

This is an excerpt from CUBRID Node.js driver.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon