csv to excel数据字符串转义字符 [英] csv to excel data string escape character

问题描述

我正在使用System.IO.StreamReader类来读取文件数据以将.csv文件转换为.xls，并且一些数据在文本之间具有"，"逗号，例如"name1，name2，name3"。并且列分隔符是"，"，逗号。如何逃避"，"文本之间的
逗号

 string xlqs =" INSERT INTO [" + fileName +"]（" + fileColumn +"）VALUES（'" + csvtoxldata.Replace（"'"，"''"）。Replace（columnDelimiter，"'，'" ）.Trim（）+"'）" ;; 

提前谢谢你。

---

SQLEnthusiast

解决方案

< blockquote>

我强烈建议您放弃尝试自己解析CSV并使用第三方库。有很多可以通过NuGet添加的CVS阅读器。这完全消除了你的问题，并且代码相同。

关于如何转义文本的一般问题，引用代码没问题。对于逗号替换，您不需要删除逗号，因为您可以在字符串文字中使用逗号。所以"name1，name2，name3"的输入是"将
作为'name1，name2，name3'注入SQL.

但是你的代码是SQL注入攻击如何工作的一个主要例子。通过简单的查询更改，我可以做任何我想要的事情，包括擦除整个表格。我强烈建议你完全忘记这种方法。而是建立一个命令，
让它为你处理转换。

 //假设你的代码提供了filename和fileColumn，并验证了注入攻击
 var cmd = new OleDbCommand（
 
 
" INSERT INTO [{filename}]（{fileColumn}）VALUES（@values）"）; 
 
 //在这里使用@ from SQL，不知道它是什么用于Excel 
 cmd.Parameters.Add（" @ values"，value）; 

值只是CSV文件中的行。通过SQL插入时，引号将正确分隔，因为您正在使用参数。此代码可以保护您免受注入攻击。

Michael Taylor

http://www.michaeltaylorp3.net

Hi, I am using System.IO.StreamReader class to read file data to convert a .csv file to .xls and some data have ','comma in between text for instance "name1,name2,name3" and the column delimiter is "," comma. How to escape "," comma in between the text

string xlqs= "INSERT INTO [" + fileName + "] (" + fileColumn + ") VALUES('" + csvtoxldata.Replace("'", "''").Replace(columnDelimiter, "','").Trim() + "')";

Thank you in advance.

---

SQLEnthusiast

解决方案

I strongly recommend that you forego trying to parse CSV yourself and use a third party library. There are plenty of CVS readers that can be added via NuGet. This completely eliminates your problem and will be about the same code.

As for the general question of how to escape the text the quote code is fine. For the comma replacement you don't need to strip out the commas because you can have commas in a string literal. So the input of "name1,name2,name3" would be injected into the SQL as 'name1,name2,name3'.

But your code is a prime example of how SQL injection attacks work. With a simple change in your query I can do anything I want including wiping the entire table. I strongly recommend that you forget this approach altogether. Instead build up a command and let it handle the conversion for you.

//Assuming filename and fileColumn are provided by your code and validated for injection attack
var cmd = new OleDbCommand(

"INSERT INTO [{filename}] ({fileColumn}) VALUES (@values)"); //Using the @ from SQL here, not sure what it is for Excel cmd.Parameters.Add("@values", value);

Value is just the line from the CSV file. The quotes will get delimited properly when inserted via SQL because you're using a parameter. This code would protect you from an injection attack.

Michael Taylor
http://www.michaeltaylorp3.net

这篇关于csv to excel数据字符串转义字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！