69

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().

Improve this question
7
  • You need something along the lines of sanitizing input and then something along the lines of a proper mysql library that allows you to pass in key/value pairs to an insert function Commented Oct 12, 2011 at 18:59
  • I am using the NodeJS MySQL library which works fine. How would I sanitize it properly? Commented Oct 12, 2011 at 19:02
  • Run an email regexp on it, if it fails send an error message back to the client. Commented Oct 12, 2011 at 19:07
  • @Raynos this would be appropriate for e-mail, since RFC2822 doesn't allow a lot of weird characters, but it'd be nice to have a validator for all strings. Commented Oct 13, 2011 at 20:10
  • 2
    @Bobby by any chance, is your last name Tables? Commented Apr 6, 2016 at 9:21

9 Answers 9

Reset to default
75

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

@Bobby Well I'm as surprised as you! I just hope it's secure in all situations :-)
@Bobby Do not forget emoji that can trigger error. something like var m=m.replace(/([\uE000-\uF8FF]|\uD83C[\uDF00-\uDFFF]|\uD83D[\uDC00-\uDDFF])/g, ''); could be added
@zipp do you have any more details on this issue? Feel free to edit my answer if you have that privilege.
@Pauld'Aoust Well, I had some issue parsing emoji.This require editing the config.However, I couldn't do it. I ended up just calling these two functions which I am sure could be combined in one: posts[i].message = mysql_real_escape_string(posts[i].message); posts[i].message = posts[i].message.replace(/([\uE000-\uF8FF]|\uD83C[\uDF00-\uDFFF]|\uD83D[\uDC00-\uDDFF])/g, '').trim(); you can read more about emoji issue here. Basically you need utf8mb4 as @Soyoes said.
@Pauld'Aoust, that was beyond awesome. Thank you. Saved me a tremendous amount of time. A minor glitch that was utterly impeding my main task.
|
26

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
        }
    });
}
Improve this answer

1 Comment

This is a really good addition to the accepted answer. Thanks.
17

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 );
Improve this answer

Comments

12

Solution that works also for Frontend projects

Install sqlstring (a library maintained by mysqljs):

npm install sqlstring

if you use TypeScript you can also install the typings:

npm install @types/sqlstring

Then use it:

import { escape } from 'sqlstring';

const escapedString = escape(`it's going to be escaped!`);
Improve this answer

4 Comments

it should be the accepted answer.Too easy using sqlString package
I agree this is the best answer, although the --save-dev is going to make it not work once compiled
worked to me, thx for store json as php in JS I was need escape(JSON.stringify(JSON.stringify()))
with typescript I had to do it like this import SqlString from 'sqlstring' and then const escapedString = SqlString.escape(email)
7

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.

Improve this answer

Comments

7

Using arrays instead of a case statement:

var regex = new RegExp(/[\0\x08\x09\x1a\n\r"'\\\%]/g)
var escaper = function escaper(char){
    var m = ['\\0', '\\x08', '\\x09', '\\x1a', '\\n', '\\r', "'", '"', "\\", '\\\\', "%"];
    var r = ['\\\\0', '\\\\b', '\\\\t', '\\\\z', '\\\\n', '\\\\r', "''", '""', '\\\\', '\\\\\\\\', '\\%'];
    return r[m.indexOf(char)];
};

//Implementation
"Some Crazy String that Needs Escaping".replace(regex, escaper);
Improve this answer

2 Comments

I figured there were some more straightforward ways of doing this than the switch statement in my answer. I suspect it's more efficient than mine, too, but of course I haven't done a comparison to see. Thanks for contributing an alternate approach!
It is easier to swap the two in the event of having to go the other way instead of having to write another set of case statements. In my opinion it is also more functional and more in the spirit of Javascript. Most likely could be greatly improved upon.
0

If you are playing with CJK characters http://en.wikipedia.org/wiki/CJK_characters or some special emotional icons of iOS/Android/Other mobiles ... such as "�‡‰™©󾭠" or decodeURIComponent("\xF3\xBE\xAD\xA0").

You will need to set your my.cnf like this

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
skip-character-set-client-handshake
Improve this answer

Comments

0

Why not simply use the built function escape Like this:

var escaped_str = escape(your_unescaped_string);

This works for me using MySQL on the back-end.

Improve this answer

1 Comment

Because that function is deprecated.
-3

This is simple way to write SQL query in you JavaScript code

const QUERY = INSERT INTO users (firstName, lastName) VALUES ( "${firstName}", "${lastName}")

Improve this answer

1 Comment

This answer leaves wide open possibility of SQL injection

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.