Making a javascript string sql friendly

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            default:                return char;        }    });}

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.

Learnt the hard way that passing numbers to this function causes the whole process it is used in to die quietly. So I add a little test:

function mysql_real_escape_string (str) {    if (typeof str != 'string')        return 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        }    });}

For anyone who is coming to this answer from 2018 onwards it is also worth noting that a number of javascript database frameworks now contain a connection.escape method.

For instance:

var mysql = require('mysql')var connection = mysql.createConnection( // your connection string here var query = "SELECT THING FROM THING WHERE FRED= " + connection.escape( your_string_here );