CODEWITHSUNDEEP
javascriptsqlstringnode.jsescaping
Bobby
Bobby

Reputation: 4502

Making a javascript string sql friendly

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

Upvotes: 67

Views: 69951

Answers (9)

Francesco Borzi
Francesco Borzi

Reputation: 61874

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!`);

Upvotes: 11

Priyansh sharma
Priyansh sharma

Reputation: 85

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

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

Upvotes: -3

Paul d'Aoust
Paul d'Aoust

Reputation: 3209

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.

Upvotes: 74

wbartussek
wbartussek

Reputation: 2008

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.

Upvotes: 0

sien
sien

Reputation: 169

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 );

Upvotes: 16

Simon H
Simon H

Reputation: 21005

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
        }
    });
}

Upvotes: 25

Justin
Justin

Reputation: 885

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);

Upvotes: 7

esengineer
esengineer

Reputation: 9928

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.

Upvotes: 7

Soyoes
Soyoes

Reputation: 940

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

Upvotes: 0

Related Questions