Making a JavaScript String SQL Friendly

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.

Is there a standard function for making a string into an sql friendly string in Java?

The following advice is good for any language or framework: do not write your queries by concatenating strings, that's unsafe. Use parameterized queries instead.

In Java, parameterized queries are written with PreparedStatement. For instance:

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM whatever WHERE id = ? AND name= ?");
stmt.setInt(1, 1000); // parameters starts at 1
stmt.setString(2, "'$%&");
ResultSet rs = stmt.executeQuery();
// ...

More details there:
http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html

See also the documentation for PreparedStatement:
http://download.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html

Is building a query using LIKE safe from SQL Injection?

As Christian recommended (in different programming language), using prepared statements is the safest option. Looks like you will need to reformat your string though to remove the single quotes (because prepared statements are typically escaped automatically). It seems this library is no exception.

Unfamiliar with this library, but seems it will be like this:

var query = "SELECT * FROM tbl WHERE col LIKE ? ORDER BY key";
var newStr = searchTerm.substring(1, searchTerm.length - 1) + "%";
sql = mysql.format(query, newStr);

On a side note, try not to use * in your SQL select statements.

Handling single quotes in string sql query using variables

Your best resource here is going to be OWASP (check out the SQL Injection Prevention cheat sheet). I feel compelled to reiterate that your best bet would be to attempt solutions in the following order:

1. Prepared Statements
2. Stored Procedures
3. Escaping User Supplied Input

If you're absolutely set on escaping queries for SQL Server you can refer to This MSDN Article, specifically the Escaping Input section. If you can further validate user input by implementing a White List, all the better!

Node and SQL Injection by using ${variable} on query string

According to https://tediousjs.github.io/node-mssql/ , "All values are automatically sanitized against sql injection." applies only when you use the ES6 Tagged template literals. You should add the tag sql.query before the template string.

let sqlString = sql.query`select * from mytable where id = ${value}`

For more information on tagged template literals: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals#tagged_templates

How do I convert a string into safe SQL String?

Don't sanitize your strings. Use parameterized queries instead, as they handle all sanitization.

You don't specify which database you are using, so I assume it is MS SQL Server. Microsoft has an article on the official ASP.net website about this. Also see MSDN for SqlCommand.Parameters and the AddWithValue method.

Javascript string syntax to write SQL

You're mixing with VB syntax. In JavaScript you must concatenate string with +

SQLdetail += ' LEFT JOIN Ordre ON Avis.[Ordre SAP] = Ordre.[Ordre SAP] WHERE Avis.[Date Appel] BETWEEN #' + DateOne + '# AND #' + DateTwo + '#;' 

---

Related Topics