-1

I am importing a text file with marks for each student into my MySQL database. I am using preg_split to read each line and break the info into separate data which is working except for students with a surname containing a ['] such as O'Neil.

Error: INSERT INTO student_data (studentID,student_number, termID, course_number, course_name, storecode, grade, percent) VALUES ('13269 3100876170 Jimmy O'Neil 12 2701 MT11 MATHEMATICS 11 Q1 74 74 ','','','','','','','') You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Neil 12 2701 MT11 MATHEMATICS 11 Q1 74 74 ','','','','','','','')' at line 231093

The code to split the separate the file is:

 ini_set("auto_detect_line_endings", true);

  $file = fopen($target_file,"r");

 while(! feof($file))
  {
 $test= fgets($file);

 $array = preg_split('/\s"+/', $test);
fclose($file);
}

I am guessing that the issue is the ('/\s"+/',$test) part of the code. I have looked for clear documentation on what is going on and most of it makes sense but I couldn't find how you include all white spaces but exclude ' from being used as a delineator.

chris85
  • 23,846
  • 7
  • 34
  • 51
Vincent
  • 83
  • 1
  • 1
  • 9
  • 3
    This has nothing to do with `preg_split()`: You have an sql injection problem and you should switch to prepared statements: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – jeroen Dec 04 '17 at 14:03
  • Could you post an example of the data of your file on your question? Just one line, like the one with the ' character if possible. – jeprubio Dec 04 '17 at 14:26
  • Hi, here is the sample data without the " ". 13143 3101139339 Ethan Webb 10 2701 CD10 CAREER DEVELOPMENT 10 Q1 94 94 – Vincent Dec 04 '17 at 14:41

1 Answers1

1

Don't use regular expressions for delimited data. Use a parser, in this case you can use either:

  1. http://php.net/manual/en/function.fgetcsv.php
  2. http://php.net/manual/en/function.str-getcsv.php

The CSV in the name is the default, you can define your delimiter. In your case a space is the delimiter and a double quote is for encapsulation. This will get you half way there. Once you have this data broken out correctly you should use parameterized queries so your mysql driver escapes the data as needed.

Example using str_getcsv:

$string = '"13269" "3100876170" "Jimmy O\'Neil" "12" "2701" "MT11" "MATHEMATICS" "11" "Q1" "74" "74"';
$data = str_getcsv($string, ' ', '"');
print_r($data);

Demo: https://3v4l.org/Phpp7

Parameterized query:

INSERT INTO student_data (studentID, student_number, termID, course_number, course_name, storecode, grade, percent) VALUES (?, ?, ?, ?, ?, ?, ?, ?)

then if using PDO you can pass the $data directly to the execute function.

chris85
  • 23,846
  • 7
  • 34
  • 51