readxl 1.1.0

Security fixes for libxls

readxl embeds the library libxls in order to read xls files. Three security vulnerabilities have been identified in libxls and were shown to affect readxl (CVE = Common Vulnerabilities and Exposures):

• CVE-2017-12110
• CVE-2017-2896
• CVE-2017-2897

Those have now been addressed upstream and version 1.1.0 of readxl embeds a version of libxls that includes those fixes.

Two other CVEs mention readxl, but actually concern parts of libxls unused by readxl or problems that were identified and fixed before readxl existed. CVE-2017-12111, CVE-2017-2919

The recent maintenance of libxls by Evan Miller (@evanmiller) is a very positive development and has allowed us to close many readxl issues related to crashes or other unsavory behaviour seen when reading specific xls files.

Is it xls or xlsx?

readxl has 3 main functions for reading Excel files:

• read_xlsx() reads input as xlsx
• read_xls() reads input as xls
• read_excel() determines whether the input is xls or xlsx, then calls one of the functions above

read_excel() has gotten better at inferring the file format and this functionality is now exposed, in case others need access. This is implemented in excel_format(), which first calls format_from_ext() to consult the extension and may then fall back to format_from_signature() to consult the file’s signature, a.k.a. its “magic number”.

format_from_ext() and, therefore, excel_format(), now recognize more file extensions in the xlsx family, namely .xlsm, .xltx, and .xltm, in addition to .xlsx.

library(readxl)
library(tibble)

tibble(
  excel_paths  = c("a.xlsx", "b.xls", "c.xlsm", "d.xltx", "e.jpg", "f.csv", "g"),
  format = excel_format(excel_paths, guess = FALSE) # consult extension **only**
)
#> # A tibble: 7 x 2
#>   excel_paths format
#>   <chr>       <chr> 
#> 1 a.xlsx      xlsx  
#> 2 b.xls       xls   
#> 3 c.xlsm      xlsx  
#> 4 d.xltx      xlsx  
#> 5 e.jpg       <NA>  
#> 6 f.csv       <NA>  
#> 7 g           <NA>

format_from_signature() and, therefore, excel_format() can determine the format of extensionless Excel files.

guess_my_format <- c(
  readxl_example("clippy.xls"),
  readxl_example("datasets.xlsx"),
  file.path(R.home("doc"), "html", "logo.jpg")
)

## copy and strip the extension
filename <- tools::file_path_sans_ext(basename(guess_my_format))
tmpdir <- tempdir()
file.copy(from = guess_my_format, to = file.path(tmpdir, filename))
#> [1] TRUE TRUE TRUE

tibble(
  filename = filename,
  format = excel_format(file.path(tmpdir, filename))
)
#> # A tibble: 3 x 2
#>   filename format
#>   <chr>    <chr> 
#> 1 clippy   xls   
#> 2 datasets xlsx  
#> 3 logo     <NA>

readxl in the real world

According to METACRAN, readxl has over 2.7 million cumulative downloads and is typically in the top 50 CRAN packages in terms of monthly downloads. readxl has relatively few reverse dependencies (only about 70), so I believe the vast majority of usage occurs in users’ R scripts. And these users throw an incredibly diverse set of Excel files at readxl, mostly with quiet success.

A recent failure, however, inspired an upgrade of readxl’s parsing of xlsx files. Christine Zhang (@underthecurve) did the data work behind a front-page Los Angeles Times story about homeless arrests. The Los Angeles Police Department provides daily arrest logs in the form of xlsx files, written by an unknown third-party tool. These files are a literal implementation of the “minimal conformant SpreadsheetML package” and we had never seen such a specimen before. Both R/tidyverse/readxl and Python/pandas/xlrd refused to read these files. Undeterred, Christine took advantage of the fact that xlsx is “just” a bunch of XML files and took matters into her own hands. But I got busy, making sure that readxl would be ready the next time.

Read the full backstory behind the LA Times story here: How We Found New Patterns in LA’s Homeless Arrest Data. In the end, Christine got her job done and readxl is a more capable package as a result of this challenge.

Future outlook

I predict the next user-visible changes in readxl will be improvements around column type specification, cell coercion, and reading from more general forms of input, e.g., from a URL. You can see other improvements and bug fixes that are on the radar in readxl’s GitHub issues.

The next release will also have a breaking-ish change around name repair. readxl will switch to tibble::set_tidy_names(), which remediates missing and duplicate variable names. I will make this change soon in the dev version, so that interested users can begin to adjust.

Acknowledgements

I am very thankful to the maintainers of the embedded libraries, especially the recent work on libxls by David Hoerl (@dhoerl) and Evan Miller (@evanmiller). readxl includes a great deal of compiled code, from disparate sources, and Jim Hester (@jimhester) is a fantastic troubleshooter. David Hood (@thoughtfulbloke) delivered some delightful Clippy photos (featured here), in response to an absurd Twitter request.

A big thanks goes out to the 86 users who contributed issues and pull requests since the previous readxl release: @aaa34169, @afdta, @alexeyknorre, @alexhallam, @anjurad, @arnyeinstein, @batpigandme, @bbrewington, @bellafeng, @burchill, @chrisholbrook, @Courvoisier13, @danielsjf, @DavisVaughan, @dchiu911, @Deepu298, @dpprdan, @ea-guerette, @espinielli, @gergness, @gp2x, @heseber, @hlynurhallgrims, @hrecht, @huftis, @hughmarera, @iiLaurens, @ilpepe, @Ironholds, @jameshowison, @jamesLSI, @jcolomb, @jebyrnes, @jekriske-lilly, @jennybc, @jeroen, @jimhester, @jjcad, @Jmarks199, @KKPMW, @krlmlr, @kwstat, @KyleHaynes, @Lu2017, @lz1nwm, @m-macaskill, @mbeer, @mdbauer, @melikovk, @MichaelChirico, @MidhunT, @MikhailLagutin, @mplatzer, @msgoussi, @nacnudus, @nealrichardson, @nick-ulle, @nortonle, @oozdmr, @PMassicotte, @ramanan82, @reinderien, @reinierv4, @robbriers, @RobertMyles, @rsbivand, @rstub, @ruaridhw, @sebastianschweer, @shoebodh, @simonthelwall, @slfan2013, @smasuda, @sncr-github, @stephlocke, @steve4444, @sz-cgt, @t-kalinowski, @tarunparmar, @tbeu, @thothal, @tomsing1, @tres-pitt, @vkapartzianis, @willtudorevans, and @zauster.