Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

javascript array as a list of strings (preserving quotes)

I've got an array of strings. When I use .toString() to output it the quotes are not preserved. This makes it hard to build the mysql query using an "in". Consider the following:

SELECT * FROM Table WHERE column IN ('item1','item2','item3','item4')  toString is returning: IN (item1,item2,item3,item4) 

There must be a simple fix I am overlooking here.

like image 518
Anthony Webb Avatar asked Dec 13 '11 01:12

Anthony Webb


2 Answers

The quotes aren't preserved because they're not actually part of the string value, they're just necessary to indicate string literals in your code.

So, don't use toString(). Instead, one way to do it is as follows:

var arr = ['item1','item2','item3','item4'];  var quotedAndCommaSeparated = "'" + arr.join("','") + "'";  // quotedAndCommaSeparated === "'item1','item2','item3','item4'" 

The Array.join() method returns a string that is all of the array elements concatenated into a single string with an (optional) separator between each item. So if you specify a separator that includes the quotation marks and commas you just have to manually append a starting and ending quote for the first and last item (respectively).

(And please tell me you're not using client-side JavaScript to form your SQL.)

EDIT: to allow for an empty array, include a default value for the resulting string, otherwise (as pointed out by missingno) the string would be "''":

var newString = arr.length === 0 ? "" : "'" + arr.join("','") + "'"; // default for empty array here ---^^ 

(Might be more appropriate to have an if (arr.length===0) to take some other action rather than running the SELECT statement.)

like image 185
nnnnnn Avatar answered Oct 09 '22 23:10

nnnnnn


Use Array.map to wrap each element with quotes:

items.map(function(item) { return "'" + item + "'" }).join(','); 

The code gets simpler with ES6 features - arrow functions and template strings (implemented in node.js 4.0 and higher):

items.map(i => `'${i}'`).join(','); 

You may also use whitelisting to prevent SQL injections:

const validItems = new Set(['item1', 'item2', 'item3', 'item4']);  items    .filter(i => validItems.has(i))    .map(i => `'${i}'`)    .join(',') 
like image 28
Lukasz Wiktor Avatar answered Oct 10 '22 00:10

Lukasz Wiktor