从XLS导入MySQL时，array_map与mysqli_real_escape_string的放置位置

Hey there! Let's figure out where to slot in array_map with mysqli_real_escape_string to safely sanitize your Excel data before inserting it into MySQL.

The Ideal Place to Add the Escape Logic #

The perfect spot is right inside your final foreach loop that builds the INSERT query. This is because you have access to both your active database connection ($kapcs) and the row data from the Excel file here—both are required for mysqli_real_escape_string to work correctly.

Modified Code with array_map #

Here's how to adjust your existing code:

if(isset($_POST['submitButton'])) { 
    if($_FILES['file']['size'] != 0 ) { 
        if($_FILES["file"]["size"] > 5242880 ) { 
            $error[] = "A fájl mérete maximum 5 MB lehet."; 
        } 
        $filename = $_FILES['file']['name']; 
        $ext = pathinfo($filename, PATHINFO_EXTENSION); 
        if(!array_key_exists($ext, $fajl_types)) { 
            $error[] = "Nem engedélyezett fájl típus."; 
        } 
        if(count($error) == 0 ) { 
            $path = "../imports/" . date( "Y-m-d" ) . '-' . rand(1, 9999) . '-' . $_FILES['file']['name']; 
            if(move_uploaded_file($_FILES['file']['tmp_name'], $path )) { 
                $file_name = basename($path); 
                $objPHPExcel = PHPExcel_IOFactory::load('../imports/'.$file_name); 
                $dataArr = array(); 
                foreach($objPHPExcel->getWorksheetIterator() as $worksheet) { 
                    $worksheetTitle = $worksheet->getTitle(); 
                    $highestRow = $worksheet->getHighestRow(); 
                    $highestColumn = $worksheet->getHighestColumn(); 
                    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); 
                    for ($row = 1; $row <= $highestRow; ++ $row) { 
                        for ($col = 0; $col < $highestColumnIndex; ++ $col) { 
                            $cell = $worksheet->getCellByColumnAndRow($col, $row); 
                            $val = $cell->getValue(); 
                            $dataArr[$row][$col] = $val; 
                        } 
                    } 
                } 
                unset($dataArr[1]); 
                $user_pass = ""; 
                $user_reg_date = date("Y-m-d-H:i:s"); 
                $user_last_login = ""; 
                $user_aktivation = ""; 
                $user_vevocsoport = (int)0; 
                $user_newpass = ""; 
                $user_imported = (int)1; 

                // --- Start of modified section ---
                foreach($dataArr as $val) {
                    // Use array_map to escape all fields in the current row
                    $escapedFields = array_map(function($field) use ($kapcs) {
                        return mysqli_real_escape_string($kapcs, $field);
                    }, $val);

                    // Use escaped fields in the INSERT query
                    $sql = "INSERT INTO user ( user_vnev, user_knev ) VALUES ( '" . $escapedFields['0'] . "', '" . $escapedFields['1'] . "' )";
                    $import = mysqli_query($kapcs, $sql) or die("IMPORT-ERROR - " . mysqli_error($kapcs));
                    $ok = 1;
                }
                // --- End of modified section ---

            } else { 
                $error[] = "A fájl feltöltése nem sikerült."; 
            } 
        } 
    } else { 
        $error[] = "Nem választott ki fájlt."; 
    } 
}

Why This Works #

• array_map applies mysqli_real_escape_string to every element in the $val array (each row from your Excel file), ensuring all special characters are properly escaped to prevent SQL injection.
• The use ($kapcs) clause lets the anonymous function access your database connection—this is mandatory for mysqli_real_escape_string to function.
• By doing this right before building the SQL query, you guarantee sanitized data is used for insertion.

A Safer Alternative: Prepared Statements #

While manual escaping works, prepared statements are even more secure and maintainable (they handle sanitization automatically, so you don't have to worry about escaping rules). Here's how you could refactor the insert logic:

// Prepare the statement once outside the loop
$stmt = mysqli_prepare($kapcs, "INSERT INTO user (user_vnev, user_knev) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "ss", $userVnev, $userKnev);

foreach($dataArr as $val) {
    $userVnev = $val['0'];
    $userKnev = $val['1'];
    mysqli_stmt_execute($stmt);
}
mysqli_stmt_close($stmt);

内容的提问来源于stack exchange，提问作者KissTom87