How can I allow rapache/brew to securely connect to a MySQL database?

Question

I'm developing a web application using rapache and brew. Within the R code, I want to use the RMySQL package to query a MySQL database, but I am questioning the best way to access the login details for the database from within the R script.

Following some suggestions for a similar problem with PHP, one thought was to do the following in an interactive session to save the connection details to a file outside of /var/www:

con <- dbConnect(MySQL(), dbname = "mydb", user = "myuser", pass = "mypass")
save(con, file = "/home/myuser/sqlconnect.rda")

And then in the script run by rapache/brew, load the .rda file:

<%
load("/home/myuser/sqlconnect.rda")
query <- "MY QUERY"
result <- dbGetQuery(con, query)
%>

I haven't tried this approach yet. I'm not even sure that my sqlconnect.rda file will contain all of the information that it needs to connect.

Is there a more secure way to set up the dbConnect() statement?

Update

Saving the dbConnect() output to a file does not work, because the connection has timed out. However, sourceing a .R file from my user directory containing

library(RMySQL)
con <- dbConnect(MySQL(), dbname = "mydb", user = "myuser", pass = "mypass")

does work.

However, I don't know how secure this approach is.

Answer 1

Although I just do not see why would your suggestion boost security compared to having username/password in the R script file, you could store the parameters of your dbConnect function. Example:

con.details <- list("MySQL", dbname = "mydb", user = "myuser", pass = "mypass")
save(con.details, file='/nonpub/con.details')

And using those parameters in your brew files:

load('/nonpub/con.details')
con <- do.call(dbConnect, con.details)

As you are using rApache, loading con.details on every run just does not make any sense, I would rather put that line in REvalOnStartup, which could also save you this save/load issue :)

So I suggest to add library(RMySQL); con.details <- list("MySQL", dbname = "mydb", user = "myuser", pass = "mypass") to rApache startup and use that parameter list in the brew scripts.