I'm uploading a .xlsx file to a website and reading it into an array and then inserting it into ms sql server. The code runs perfectly unless the input file contains single quotes. I think the problem is, I need to escape the single quotes inside the insert statement or array, but I'm not sure how. What do I need to do here so that single quotes in the array in the excel file don't cause the insertion to fail?
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;
} 
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Now that you are already using PDO, the best approach is to use a PDO prepared statement with escaped parameters.
You will pass to
execute()two parameters, i.e. the SQL query with placeholders - similar toand the values to be included in these placeholders.
$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); }