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.
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.)
"''") or empty string? ("")
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(',')
join() without parameters because by default it joins using ,let keys = ['key1','key2']
let keyWithQoutes = keys.map((it) => {return `'${it}'`})
let sql = `SELECT .... FROM ... WHERE id IN (${keyWithQoutes})`
console.log(sql)
output: "SELECT .... FROM ... WHERE id IN ('key1','key2')"
The simple fix is adding the quotes yourself
for(var i=0; i<items.length; i++){
items[i] = "'" + items[i] + "'";
}
var list_with_quotes = items.join(",");
Do note that I completely ignore SQL injection issues here.
Use JSON.stringify and join them
["hello", "there", 21, 32]
.map(entry => JSON.stringify(entry))
.join(",");
returns
"hello","there",21,32
you can also use string literal and join
var arr = ['item1','item2','item3','item4'];
var quotedAndCommaSeparated = `${arr.join(",")}`;
Store the quotes:
var names = ["'item1'","'item1'","'item3'"];
alert('IN (' + names[1] + ')'); // IN ('item1')
