4

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)
Community
  • 1
  • 1
Mike
  • 1,884
  • 2
  • 24
  • 42

3 Answers3

2

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.

EternalHour
  • 8,308
  • 6
  • 38
  • 57
  • The logic is correct, and the syntax was close. I will update my question with what has worked. As for using *, I know I shouldnt be using it ;) – Mike Feb 24 '15 at 04:48
  • @EternalHour. Can you explain why "select *" is not a good practice? Also, node-mysql's dynamic SQL construction is not fully-complete in that it does not support true prepared statements and there are other ways to avoid SQL injection which is very much specific to that package or so I think. The documentation of that package has some explanation in this area but it is not very clear. – Sunny Sep 21 '15 at 14:15
  • 1
    @Samir - `SELECT *` is bad practice unless you in fact need every column, if you need the whole table by all means do it. I think some developers just get lazy or maybe want a shorter SQL statement so they use that. I don't know anything about node-mysql to address your other concerns. – EternalHour Sep 21 '15 at 23:21
0

try directly newStr using below(don't add % explicitly) :

SELECT * FROM tbl WHERE col LIKE '" + newStr + "%' ORDER BY key
Hemant Patel
  • 184
  • 6
  • If I do this, the resulting error is: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alex'%'. This is why I had to re-build the string – Mike Feb 24 '15 at 04:06
0

You should do it via Prepared Statement:

C#:

//Creates new SQL Command with parameter @newStr
var command = new SqlCommand( "SELECT * FROM tbl WHERE col LIKE '@newStr' ORDER BY key", db);

//Set paramater @newStr to 40 characters
command.Parameters.Add("@newStr", SqlDbType.VarChar, 40);

//Prepares the statement, after all parameters are defined
command.Prepare();

//Set Parameter @newStr to "newStrValue"
command.Parameters["@newStr"] = "newStrValue";

//Executes the Command
command.Execute();

For Node.js, there is a Package wich supports prepared Statements:

https://www.npmjs.com/package/mysql2

With Prepared Statements, a SQL Injection will never happen, because the Database knows it is a user input and no SQL Command.

Christian Gollhardt
  • 16,510
  • 17
  • 74
  • 111
  • This looks like C#, but I am using javascript – Mike Feb 24 '15 at 04:03
  • @Mike, sorry about that. Didn't see. Not know about Javascript and MySql. Your're using Node.js? No knowledge here, on my side. But I would say, in every language `Prepared Statements` are the key. This package seems like support them: https://www.npmjs.com/package/mysql2 – Christian Gollhardt Feb 24 '15 at 04:07
  • I am using this package: https://github.com/felixge/node-mysql/#escaping-query-values . I did try to use prepared statements, the issue seems to be that LIKE needs to be enclosed in single quotations, and my escaped string also comes in single quotations – Mike Feb 24 '15 at 04:12
  • Something like `\'` does not work? Sorry i have no knowledge about `Node.js`. But for sure, you want to find a way to use `Prepared Statements`. They are the current way to solve problems. – Christian Gollhardt Feb 24 '15 at 04:15