How Can I Safely Insert JavaScript Strings into SQL Queries in Node.js to Prevent SQL Injection?

Securing JavaScript Strings for SQL Queries in NodeJS

When passing user-provided strings to NodeJS for SQL database insertion, it's crucial to prevent SQL injection vulnerabilities. Regular text handling may suffice for simple data like usernames, but email addresses require special care. This article explores a solution to enhance the SQL friendliness of JavaScript strings.

Emulating MySQL's mysql_real_escape_string()

The PHP function mysql_real_escape_string() sanitizes strings for safe SQL insertion. However, NodeJS does not offer a native equivalent natively. To remedy this, a custom function can be implemented to mimic its functionality by escaping characters that are problematic in SQL queries.

The following code provides a custom mysql_real_escape_string() function for JavaScript:

function mysql_real_escape_string(str) {
    return str.replace(/[\x08\x09\x1a\n\r"'\\%]/g, function(char) {
        switch (char) {
            case "":
                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;
            default:
                return char;
        }
    });
}

This function replaces specified characters with their escaped counterparts, rendering the string safe for SQL insertion. It even extends the scope of escaped characters to include tabs, backspaces, and '%', ensuring compatibility with LIKE queries.

Note on Character Set Awareness

MySQL's mysql_real_escape_string() is character-set-aware, but the custom function provided here does not consider character sets. However, its broad character escaping ensures that it works reliably across most scenarios.

Further Reading

For more information and discussions on SQL injection prevention, refer to the OWASP website.

The above is the detailed content of How Can I Safely Insert JavaScript Strings into SQL Queries in Node.js to Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!