Fix bulkcreate/dumpdevices to handle escaping ',' and '"' in csv files

1 files changed, 55 insertions, 6 deletions

diff --git a/provisioning-model.lua b/provisioning-model.lua
index 0468a57..ebec3da 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -2303,9 +2303,48 @@ mymodule.bulk_create_devices = function(self, devicelist)
 	local res, err = pcall(function()
 		local connected = databaseconnect()
 
+		-- Need to split on ",", but not if found in quoted field
+		function csv_parseline(line,sep)
+			local res = {}
+			local pos = 1
+			sep = sep or ','
+			while true do
+				local c = string.sub(line,pos,pos)
+				if (c == "") then break end
+				if (c == '"') then
+					-- quoted value (ignore separator within)
+					local txt = ""
+					repeat
+						local startp,endp = string.find(line,'^%b""',pos)
+						txt = txt..string.sub(line,startp+1,endp-1)
+						pos = endp + 1
+						c = string.sub(line,pos,pos)
+						if (c == '"') then txt = txt..'"' end
+						-- check first char AFTER quoted string, if it is another
+						-- quoted string without separator, then append it
+						-- this is the way to "escape" the quote char in a quote. example:
+					until (c ~= '"')
+					table.insert(res,txt)
+					pos = pos + 1
+				else
+					-- no quotes used, just look for the first separator
+					local startp,endp = string.find(line,sep,pos)
+					if (startp) then
+						table.insert(res,string.sub(line,pos,startp-1))
+						pos = endp + 1
+					else
+						-- no separator found -> use rest of string and terminate
+						table.insert(res,string.sub(line,pos))
+						break
+					end
+				end
+			end
+			return res
+		end
+
 		local groups = mymodule.list_class_groups()
 
-		local headers = format.string_to_table(devicelist.value.bulkdevicedata.value[1], ",")
+		local headers = csv_parseline(devicelist.value.bulkdevicedata.value[1], ",")
 		local reverseheaders = {}
 		for i,h in ipairs(headers) do
 			reverseheaders[h] = i
@@ -2313,7 +2352,7 @@ mymodule.bulk_create_devices = function(self, devicelist)
 
 		runsqlcommand("BEGIN TRANSACTION")
 		for i=2,#devicelist.value.bulkdevicedata.value do
-			local values = format.string_to_table(devicelist.value.bulkdevicedata.value[i], ",")
+			local values = csv_parseline(devicelist.value.bulkdevicedata.value[i], ",")
 
 			-- Create the device
 			local device = get_device(nil, true)
@@ -2464,19 +2503,29 @@ mymodule.bulk_dump_devices = function(self, dumprequest)
 		table.sort(classes)
 		table.sort(columns)
 
+		function csv_escape(val)
+			-- Replace " with "" and surround with "'s if contains " or ,
+			local v,c = string.gsub(val or "", '"', '""')
+			if c>0 or string.find(v, ",") then
+				return '"'..v..'"'
+			else
+				return v
+			end
+		end
+
 		local device = {}
-		for i,c in ipairs(classes) do device[#device+1] = c end
-		for i,p in ipairs(columns) do device[#device+1] = p end
+		for i,c in ipairs(classes) do device[#device+1] = csv_escape(c) end
+		for i,p in ipairs(columns) do device[#device+1] = csv_escape(p) end
 		dumprequest.value.devices.value[#dumprequest.value.devices.value+1] = table.concat(device, ",")
 		for i,d in ipairs(devicevalues) do
 			device = {}
 			for j,c in ipairs(classes) do
-				device[#device+1] = devices.value.result.value[i][c] or ""
+				device[#device+1] = csv_escape(devices.value.result.value[i][c])
 			end
 			for j,p in ipairs(columns) do
 				local class,param = string.match(p, "([^.]*)%.(.*)")
 				if d.value[class] and d.value[class][param] ~= nil then
-					device[#device+1] = tostring(d.value[class][param])
+					device[#device+1] = csv_escape(tostring(d.value[class][param]))
 				else
 					device[#device+1] = ""
 				end