0

I have a php porgram which inserts data from an excel file to the mysql database but it doesn't work with files bigger than 4 MB and since I do not have access to the php.ini file I thought of chunking the insert statements. I found plupload but didn't figure out how to use it for insert statements. Any other method would be fine as well.

function insertRow($conn, $row, $header) {
// find indexes

// vorname
$firstNameIndex = array_search('vorname', $header, true);

// name / nachname
$lastNameIndex = array_search('name', $header, true);
if($lastNameIndex === false) $lastNameIndex = array_search('nachname', $header, true);

// anrede
$anredeIndex = array_search('anrede', $header, true);

// street
$streetIndex = array_search('straße', $header, true);

// Hausnummer
$hausnummerIndex = array_search('hausnummer', $header, true);

// Telefon
$telefonIndex = array_search('telefon', $header, true);

// PLZ
$plzIndex = array_search('plz', $header, true);

// ORT
$ortIndex = array_search('ort', $header, true);


$anrede = $row[$anredeIndex];
$firstName = $row[$firstNameIndex];
$lastName = $row[$lastNameIndex];
$street = $row[$streetIndex];
$houseNumber = $row[$hausnummerIndex];
$telefon = $row[$telefonIndex];
$plz = $row[$plzIndex];
$ort = $row[$ortIndex];
$title = 'unbekannt';
$geburtsdatum = 0;
$hnr_zusatz = "";
$timestamp = time();


$sql = <<<EOSQL
INSERT INTO adressen (anrede, vorname, nachname, strasse, hnr, telefon1, id_lieferung, priv_gew, 
status, titel, geburtsdatum, hnr_zusatz, plz, ort, telefon2, mail, sperrung, sperrgrund, optin, 
optindat, timestamp, zusatz1) VALUES ("$anrede", "$firstName", "$lastName", "$street", 
"$houseNumber", "$telefon", 0, 0, 0, "$title", $geburtsdatum, "$hnr_zusatz", "$plz", "$ort", 0, "", 
0, 0, 0, 0, $timestamp, 0);
EOSQL;

if (!$conn->query($sql) === TRUE) {
    echo "Error: cant add this row " . $sql . "<br/>" . $conn->error;
    print_r($row);
    echo "___________________<br/>";
}

}

I know this isn't safe at all and I am open for sql injections but I will change that after this. Thank you in advance.

Mad Scientist
  • 857
  • 4
  • 16
  • 43
  • If you're stuck on this, fix your SQL injection problems now. It takes *literally a minute* and can save you hours of frustration, if not potentially your career should this habit persist beyond this "not safe" code. – tadman Nov 24 '20 at 07:51
  • What's your server's [`max_allowed_packet`](https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html) set to? What's the PHP [file upload limit](https://stackoverflow.com/questions/2184513/change-the-maximum-upload-file-size)? – tadman Nov 24 '20 at 07:52
  • Tip: A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so errors resulting from simple mistakes made aren’t easily ignored. Without exceptions you must pay close attention to return values, many of these indicate problems you must resolve or report to the user. Exceptions allow for more sophisticated flow control as they can “bubble up” to other parts of your code where it’s more convenient to handle them. – tadman Nov 24 '20 at 07:52
  • Since I have no access to the php.ini file I guess I can't see the max_allowed_packet and file upload limit – Mad Scientist Nov 24 '20 at 07:55
  • 1
    You can always check with [`phpinfo`](https://www.php.net/manual/en/function.phpinfo.php) and [`SHOW VARIABLES`](https://dev.mysql.com/doc/refman/8.0/en/show-variables.html) even if you can't change them. If you're not able to change those, consider using an object-store like Amazon S3 to handle files. – tadman Nov 24 '20 at 07:55
  • upload_max_filesize is 15 MB and max_file_uploads is 20 – Mad Scientist Nov 24 '20 at 08:00
  • 1
    what happen when you upload more than 4MB? do you get any error? I think you just run out of memory when reading the 4MB Excel File. – zimorok Nov 24 '20 at 08:20

0 Answers0