Search code examples
javascriptmysqlsqlexpressnode-mysql

Is building a query using LIKE safe from SQL Injection?

I have an endpoint in my express app that accepts POST data (json). I'd like use the data to query a MySQL database safely. Does escaping and altering my string leave me vulnerable to exploitation?

I need to alter the string that comes in from the request because I wan't to insert a '%' before the closing single quote in the LIKE clause.

var searchTerm = mysql.escape(req.body.firstName)
var newStr = "'" + searchTerm.substring(1, searchTerm.length - 1) + "%'"

// Example:  'alex' => 'alex%'

My query is:

SELECT * FROM tbl WHERE col LIKE " + newStr + " ORDER BY key

This works, but is there a preferred or safer way to do this?

For Reference:

UPDATE/ANSWER

Based on EternalHour 's answer, the correct syntax would be:

var newStr = req.body.firstName + "%"
var sql = "SELECT cols FROM tbl WHERE col LIKE ? ORDER BY key"
sql = mysql.format(sql, newStr)
Solution

  • 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.