修改 PHP 陣列以轉義單引號

Question

我正在將 .xlsx 檔案上傳到網站並將其讀入數組，然後將其插入到 ms sql server 中。程式碼運作完美，除非輸入檔中包含單引號。我認為問題是，我需要轉義插入語句或數組中的單引號，但我不確定如何轉義。我需要在這裡做什麼，以便 excel 文件中的數組中的單引號不會導致插入失敗？

protected function excel_to_assocative_array($file) {
        try {
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($file);
            $reader->setReadDataOnly(false);
            $reader->setReadEmptyCells(false);
            $spreadsheet = $reader->load($file);
            $worksheet = $spreadsheet->getActiveSheet();
            $values = $this->get_worksheet_values($worksheet);
    
            return $values;
        } catch(\Exception $e) {
            return new \WP_Error(500, 'Failed parsing Excel file.', 'executor');
        }
    }


    protected function insert_into_database($data) {
        $map = "company_map";
        $map = $map();
        $database_ready_data = $this->map_headers($map, $data);
        $database_ready_data = $this->add_fields($database_ready_data);

        if(is_wp_error($database_ready_data)) {
            return $database_ready_data;
        }

        $dsn = '';
        $user = '';
        $password = '';
    
        try {
            $dbh = new \PDO($dsn, $user, $password);
        } catch (\PDOException $e) {
            return new \WP_Error(500, "Could not connect to the database. ({$e->getMessage()}");
        }
    
        $column_names = implode('],[', array_keys($database_ready_data[0]));
        $values = array_map(function($value) {
            return "'" . implode("', '", $value) . "'";
        }, $database_ready_data);
        $values = "(" . implode("), (", $values) . ")";
        $sql = new SQL("INSERT INTO {$this->company_db_tables[$this->company]} ([{$column_names}]) VALUES {$values}");
        print_r($sql);
        $stmt = $dbh->prepare($sql->get_clause());
    
        try {
            $stmt->execute();
        }
        catch(\PDOException $e) {
            return new \WP_Error(500, __('Something went wrong, please contact your site administrator.', 'executor'));
        }
    
        return true;
    }

Answer 1

既然您已經使用了 PDO，那麼最好的方法就是使用帶有轉義參數的 PDO 準備語句。

您將傳遞給 execute() 兩個 參數，即帶有佔位符的 SQL 查詢 - 類似於

INSERT INTO table (fld1, fld2) VALUES (?, ?)

以及這些佔位符中要包含的值。

$names = [ ];
$posts = [ ];
$values= [ ];
foreach ($database_ready_data[0] as $key => $value) {
    $names[] = "[{$key}]";
    $posts[] = "?";
    $values[]= $value;
}

$sqlTbl   = $this->company_db_tables[$this->company];
$sqlNames = implode(',', $names);
$sqlPosts = implode(',', $posts);

$sql = "INSERT INTO {$sqlTbl} ({$sqlNames}) VALUES ({$sqlPosts})";

$stmt = $dbh->prepare($sql);

try {
     $stmt->execute($values);
}