修改 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);
}