Skip to content

Latest commit

 

History

History
147 lines (115 loc) · 5.65 KB

README.md

File metadata and controls

147 lines (115 loc) · 5.65 KB
Raw

RSQLCipher

Lifecycle: experimental

R-CMD-check Codecov test coverage GitHub R package version (branch) GitHub

RSQLCipher embeds the SQLCipher database engine in R and provides an interface compliant with the DBI package. SQLCipher is an open-source library that provides transparent and secure 256-bit AES encryption of SQLite database files. SQLite is a public-domain, single-user, very light-weight database engine that implements a decent subset of the SQL 92 standard, including the core table creation, updating, insertion, and selection operations, plus transaction management.

This project started as a fork of the amazing RSQLite package, for which its authors deserve full credit. It is intended to keep this package updated with new updates to the RSQLite package to keep both as compatible as possible.

This package allows for the use of regular SQLite database files or encrypted ones.

Currently, only the following differences exist between both drivers:

  • dbConnect() with RSQLCipher() driver accepts the parameter key when creating a new encrypted database or opening a connection to an existing one.

  • There are 3 RSQLCipher specific functions:

    • databaseKeyAdd(), to create an encrypted copy of an existing plain database.

    • databaseKeyChange(), to modify the key on an encrypted database.

    • databaseKeyRemove(), to create a decrypted copy of an encrypted database.

You can install the latest development version from GitHub with:

# install.packages("devtools")
devtools::install_github("bpvgoncalves/RSQLCipher")

Discussions associated with DBI and related database packages take place on R-SIG-DB. The website Databases using R describes the tools and best practices in this ecosystem.

Usage

This package has identical interface to RSQLite.

library(DBI)

# Create an two temporary RSQLCipher databases on disk.
# A regular database and an encrypted one.
tmp_plain <- tempfile()
con_plain <- dbConnect(RSQLCipher::SQLCipher(), 
                       tmp_plain)

tmp_enc <- tempfile()
con_enc <- dbConnect(RSQLCipher::SQLCipher(), 
                     tmp_enc, 
                     key = "0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF") 

# Both databases behave the same on regular usage...
dbWriteTable(con_plain, "mtcars", mtcars)
dbWriteTable(con_enc, "mtcars", mtcars)

stopifnot(identical(dbListTables(con_plain), 
                    dbListTables(con_enc)))
stopifnot(identical(dbListFields(con_plain, "mtcars"), 
                    dbListFields(con_enc, "mtcars")))
stopifnot(identical(dbReadTable(con_plain, "mtcars"), 
                    dbReadTable(con_enc, "mtcars")))

dbDisconnect(con_plain)
dbDisconnect(con_enc)

# ... but the database files on disk are different
if ("hexView" %in% installed.packages()) {
  cat(">> Plain database\n")
  print(hexView::readRaw(tmp_plain, nbytes = 128))
  cat(">> Encrypted database\n")
  print(hexView::readRaw(tmp_enc, nbytes = 128))
} else {
  cat(">> Plain database\n")
  print(suppressWarnings(readLines(tmp_plain)[1:10]))
  cat(">> Encrypted database\n")
  print(suppressWarnings(readLines(tmp_enc)[1:10]))
}
## >> Plain database
##   0  :  53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00  |  SQLite format 3.
##  16  :  10 00 01 01 00 40 20 20 00 00 00 01 00 00 00 02  |  .....@  ........
##  32  :  00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04  |  ................
##  48  :  00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00  |  ................
##  64  :  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  |  ................
##  80  :  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01  |  ................
##  96  :  00 2e 72 a2 0d 00 00 00 01 0f 31 00 0f 31 00 00  |  ..r.......1..1..
## 112  :  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  |  ................ 
## >> Encrypted database
##   0  :  3c 51 78 4c fb 3d 0e 79 a8 6e cd f0 d7 2b 34 1c  |  <QxL.=.y.n...+4.
##  16  :  c4 d0 99 f4 ee 37 20 00 f3 ef c3 d0 9d 33 81 0a  |  .....7 ......3..
##  32  :  04 47 f0 25 85 9a 90 a9 a9 38 6a ad 04 37 02 2d  |  .G.%.....8j..7.-
##  48  :  a0 4a 10 b2 63 62 0b 3e 62 ab 6e 31 9c a8 ae 67  |  .J..cb.>b.n1...g
##  64  :  f0 8c 74 90 eb 43 89 9b 31 2b a5 9e 46 f7 ef 7e  |  ..t..C..1+..F..~
##  80  :  4c 16 0b 67 c1 81 5e 29 64 92 3a 68 85 a8 33 ba  |  L..g..^)d.:h..3.
##  96  :  af d1 79 ab 26 90 a4 fa 1d 49 97 d7 ab 9c 1a 62  |  ..y.&....I.....b
## 112  :  21 c6 c9 44 ea ec 94 c5 71 6d c8 08 e7 1c b8 0b  |  !..D....qm......

file.remove(tmp_plain)
## [1] TRUE

file.remove(tmp_enc)
## [1] TRUE