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.)
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(',')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With