0

I'm trying to load a CSV into MySQL

Everything is importing well until there is a comma , in the field.

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\"");

As you can see, I'm trying to load the Nessus results scan (CSV) into 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""]",""

In the phrase "According to its banner, " ... that comma break into the next field.

I'm sure the FIELDS TERMINATED BY ',' is not correct but in my situation, a comma or double quote could be part of the actual field .

There must be a way to import it correctly.. any guidance would be appreciated!

Thanks

Shadow
  • 30,859
  • 10
  • 44
  • 56
enkiki
  • 359
  • 3
  • 10

1 Answers1

0

You need to tell MySQL that you have quotes around the values. You do that using the OPTIONALLY ENCLOSED BY clause.

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\"");
flip
  • 545
  • 3
  • 8
  • Thanks! I'm getting an error, perhaps it's due to the escaping in Perl system("mysql -uXXX -pXXX --local-infile XXX -e \"LOAD DATA LOCAL INFILE '/home/nessus.csv' INTO TABLE vulnerabilities FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'IGNORE 1 LINES\""); – enkiki Jan 31 '18 at 12:46
  • even without \" i get an error.. any idea? – enkiki Jan 31 '18 at 12:47
  • Not sure with Perl I'm afraid – flip Jan 31 '18 at 13:02