node-mysql Using array in query

Question

I'm trying do a query using an array but having a parse error.

a - contains an array

Ex:[ 7, 26, 87, 65, 86, 23, 63, 69, 44, 61, 8, 79, 47, 88, 15, 17 ]

conexao_bd.escape(a) - escaped array

Ex: 7, 26, 87, 65, 86, 23, 63, 69, 44, 61, 8, 79, 47, 88, 15, 17

It needs to be in this format (7, 26, 87, 65, 86, 23, 63, 69, 44, 61, 8, 79, 47, 88, 15, 17), so I can use it in the query. Any help how to change format?

Code

conexao_bd.query("SELECT question, answer FROM ", conexao_bd.escape(tipo) + " WHERE id IN " + conexao_bd.escape(a) ,function(err, rows){

    if(err) console.log("Erro na query questions: "+err);
    else{
        perguntas.questions.push(rows);
        console.log(JSON.stringify(perguntas));
    }
});

Chris · Accepted Answer · 2013-12-29 18:12:06Z

2

Look into Array.join() to return it as a string. This is a basic javascript question, not node related.

answered Dec 29, 2013 at 18:12

Chris

1,61112 silver badges11 bronze badges

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

7 Comments

ampc Over a year ago

But I don't want a string... It needs to be something like this (12,2,14) so I can use it in a query: SELECT question,answer FROM Game where ID in (12,2,14);

Chris Over a year ago

You want a string, what do you think the query string is? It's a string. WHERE id IN (" + conexao_bd.escape(a).join(", ") + ") ...."

ampc Over a year ago

You can't use join after escape. After you use escape on array it's no longer an array.

Chris Over a year ago

Docs say "Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'", so maybe all you are missing are the parentheses.

ampc Over a year ago

Array.prototype.join() Joins all elements of an array into a string. Source

|

Joniras · Accepted Answer · 2017-07-26 09:36:22Z

I Actually found the correct Answer including Escaping (to prevent SQL Injection):

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

so the correct way:

conexao_bd.query("SELECT question, answer FROM ? WHERE id IN ?",[tipo,[a]],function(err, rows){

  if(err) 
     console.log("Erro na query questions: "+err);
  else{
    perguntas.questions.push(rows);
    console.log(JSON.stringify(perguntas));
 }
});

ampc · Accepted Answer · 2013-12-29 20:27:47Z

1

There is an error on query

conexao_bd.query("SELECT question, answer FROM ",

should be

conexao_bd.query("SELECT question, answer FROM "+

answered Dec 29, 2013 at 20:27

ampc

3121 gold badge7 silver badges17 bronze badges

Collectives™ on Stack Overflow

node-mysql Using array in query

3 Answers 3

7 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Linked

Related