4

I am working to fetch data of listed users and I have an array of users like

var result_user_id = [
  {
    "id": 19
  },
  {
    "id": 20
  }
];

and I tried something like

connection.query( 
   "select * from `contents` where `user_is` IN "+ result_user_id, 
   function( err_user, result_user ) {

   }
);

but unable to fetch related data. how to do that in node js.

2
  • Is the array you've posted the value of result_user_id ? Commented Nov 27, 2015 at 12:22
  • yes, in this format [ { "id": 19 }, { "id": 20 } ] . Commented Nov 27, 2015 at 12:24

5 Answers 5

13
var o =[
  {
    "id": 19
  },
  {
    "id": 20
  }
];
var arr = o.map( function(el) { return el.id; });
connection.query( "select * from `contents` where `user_is` IN ("+ connection.escape(arr)+")", function( err_user, result_user ) { });

input can be sanitized using connection.escape(), mysql.escape() or pool.escape(). please refer this to sanitize input.

Sign up to request clarification or add additional context in comments.

3 Comments

$ is not defined in node js. :(
dont concatenate strings to a query without string sanitation
@wasserholz I have changed code to fix the issue. If any modifications needed let me know or please edit it.
10

I know this question is a little bit older, but I found it via googled and stumbled across the same problem. This is the easiest solution I found:

    var result_user_id = [
  {
    "id": 19
  },
  {
    "id": 20
  }
];

var result_array = [];

  for (var i = 0; i < result_user_id; i++) {
      result_array[i] = result_user_id[i].id;
    }

connection.query( "select * from contents where user_is IN (?)", [result_array], function( err_user, result_user ) { });

Comments

3

If you wish to take advantage of safe escaping (?) you can do the following:

 connection.query('SELECT FROM `contents` WHERE `user_is` IN (' + 
   Array(result_user_id.length + 1).join('?').split('').join(',') + ')', result_user_id.map(p => p.id), function(err, result_user){});

Comments

2

You can't just take the variable result_user_id as is and put it into the sql. Try something like:

for (var i=0, len=result_user_id.length; i<len; i++){
   userIds += result_user_id[i].id + ",";
}

and then user the userIds instead of result_user_id in the sql you're trying to execute.

Comments

1

Here's how to do it with es6 syntax:

const inClause = ids.map(id=>"'"+id+"'").join();

const query =  `SELECT * FROM docs d WHERE d.id IN (${inClause})`;

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.