Modify PHP array to escape single quotes

Question

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;
    }

Answer 1

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 to

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

and 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);
}