Escaping without NO_BACKSLASH_ESCAPE in Mysql

While working with Node.JS and dbslayer today, I was facing a weird issue with escaping strings.

Best thing would have been, if I could have forced my server to use NOBACKSLASHESCAPES as sql-mode and then just escape the ' to ''.

But since this server is also used by other programs and NOBACKSLASHESCAPES option cannot be set by option for dbslayer I had to turn it off again.

I ended up with this tiny escape string method:

var db_escape_string = function(string) {
    return string.replace(/([\\\n\r])/g, "\\$&").replace("'", "''", 'g');

It replaces \ (backslash), newline and carriage return with \, \n and \r. Since also single quote needs to be escaped, I finally replace all ' with ''.

You may wonder why I do not escape ". The issue is, that a " appearing within a string in between ' does not need to be escaped.

'this is a test"2' => 'this is a test\"2'

That's why I sticked to the rule to put strings within single quote and do not escape those question marks at all.

In javascript, mysql, node.js by
@ 21 Mar 2010, Comments at Reddit & Hackernews

Give something back

Were my blog posts useful to you? If you want to give back, support one of these charities, too!

Report hate in social media Campact e.V. With our technology and your help, we protect the oceans from plastic waste. Gesellschaft fur Freiheitsrechte e. V. The civil eye in the mediterranean

Recent Dev-Articles

Read recently

Recent Files