MySQL - 如果字段内有逗号，则 LOAD DATA LOCAL INFILE 不起作用？

Question

这个问题已经有答案了:

Import data in MySQL from a CSV file using LOAD DATA INFILE (15 个回答)

已关闭 5 年前。

我正在尝试将 CSV 加载到 MySQL

一切都导入良好，直到字段中出现逗号。

system("mysql -uXXXX -pxxx --local-infile XXXX -e \"LOAD DATA LOCAL INFILE '/home/nessus.csv'  INTO TABLE vulnerabilities FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'IGNORE 1 LINES\"");

如您所见，我正在尝试将 Nessus 结果扫描 (CSV) 加载到 MySQL 中。

"10.0","103.76.180.248","oooo.com","","80","PHP 5.6.x < 5.6.28 Multiple Vulnerabilities","According to its banner, the version of PHP running on the remote web server is 5.6.x prior to 5.6.28. It is, therefore, affected by multiple vulnerabilities :    - A flaw exists in the parse_url() function due to     returning the incorrect host. An unauthenticated, remote     attacker can exploit this to have a multiple impacts     depending on how the function is implemented, which can     include bypassing authentication or conducting open     redirection and server-side request forgery attacks.     (VulnDB 145227)    - An integer overflow condition exists in the     _php_imap_mail() function in file ext/imap/php_imap.c     when handling overly long strings. An unauthenticated,     remote attacker can exploit this to cause a     heap-based buffer overflow, resulting in a denial of     service condition or the execution of arbitrary code.     (VulnDB 146957)    - A flaw exists in the bzcompress() function when handling     overly long strings. An unauthenticated, remote attacker     can exploit this to cause a denial of service condition.     (VulnDB 146975)    - An integer overflow condition exists in the     gdImageAALine() function within file ext/gd/libgd/gd.c     due to improper validation of line limit values. An     unauthenticated, remote attacker can exploit this to     cause an out-of-bounds memory read or write, resulting     in a denial of service condition, the disclosure of     memory contents, or the execution of arbitrary code.     (VulnDB 147321)  Note that this software is reportedly affected by other vulnerabilities as well that have not been fixed yet in version 5.6.28.","Version source    : X-Powered-By: PHP/5.6.27   Installed version : 5.6.27   Fixed version     : 5.6.28","Upgrade to PHP version 5.6.28 or later.  Note that this software is reportedly affected by other vulnerabilities as well. Patches for these have been committed to the source code repository, but until they are incorporated into the next release of the software, manually installing an updated snapshot is the only known solution.","[""http://www.php.net/ChangeLog-5.php#5.6.28""]",""

在短语“根据其横幅，”...该逗号插入下一个字段。

我确信以“,”结尾的字段不正确，但在我的情况下，逗号或双引号可能是实际字段的一部分。

必须有一种方法可以正确导入它..任何指导将不胜感激!

谢谢

Answer 1

您需要告诉 MySQL 您在值周围加了引号。您可以使用 OPTIONALLY ENCLOSED BY 子句来执行此操作。

system("mysql -uXXXX -pxxx --local-infile XXXX -e \"LOAD DATA LOCAL INFILE '/home/nessus.csv'  INTO TABLE vulnerabilities FIELDS TERMINATED BY ',' OPTIOANLLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'IGNORE 1 LINES\"");