[R-SIG-Finance] RFC: quantmod::getSymbols.MySQL

[Mark Knecht]

On Wed, Oct 29, 2014 at 12:30 PM, Paul Gilbert <pgilbert902 at gmail.com> wrote:
>
>
> On 10/29/2014 10:36 AM, Mark Knecht wrote:
>>
>> On Wed, Oct 29, 2014 at 6:38 AM, Zachary Deane-Mayer
>> <zach.mayer at gmail.com> wrote:
>>>
>>> Hi Mark,
>>>
>>> It'd be a little cumbersome, but couldn't you also source() an R script
>>> at
>>> the start of your session that loads the parameters from .my.cnf and
>>> passes
>>> them to options()?
>>>
>>> Alternatively, maybe getSymbols.MySQL could look for a cnf_file option,
>>> and
>>> then if that is not present look for a username and password?
>>>
>>> -Zach
>>
>>
>> Hi Zach & Josh,
>>     Yes, I'm sure there's lots of ways to do this, and as an individual
>> user just working by myself I'll be happy for anything that works.
>>
>>     That said, the nice security feature about the way it was done
>> before was TTBOMK the R environment never saw the MySQL username &
>> password. That was previously all done completely outside of R. R Asks
>> MySQL to get some data from a specific database. MySQL wasn't given a
>> username and password so it automatically accesses .my.cnf which is
>> secure. If it finds an entry with the right database name, then it
>> gets username and password from that file, gets the data from the
>> database and gives it back to R. If the username/password aren't there
>> the user can pass them explicitly (as I'm doing with this recent
>> change) or else he doesn't get access to that database.
>
>
> Not being a user of the packages under discussion, other than RMySql (and
> assuming it is RMySQL you are talking about underneath), I am a bit confused
> about why you think this has been broken by changes to defaults? If
> anything, it seems to depend on R not doing anything, and letting the driver
> handle it. I still use this approach in my own packages that use RMySQL. I
> think it is the default if host/user/password are not explicitly specified
> in another way. I also believe it is generally considered the most secure
> approach because sensitive information is not left sitting around in more
> viewable places. Am I missing something (as usual)?
>
> Paul

Hi Paul,
    Yeah, I may be talking out of my arse at this point. Looking back
at my code here's the changes that we've had to make due to the
defaults stuff getting taken out:

if (UseMySQL){
  if (MyAdjust) { dbName = paste0(dbName, "Adjust")}
  dbc = dbConnect(MySQL(), dbname=dbName)
#  setDefaults(getSymbols.MySQL, user="myName", password="myPassword",
dbname=dbName)
}

if (DownloadNewData){
  if (!UseMySQL){
    for (i in 1:length(TestSym)){
      print(paste("From ",SymbolSrc," -- ",TestSym[i]))
      getSymbolsCont(TestSym[i], from = DataStart, to = DataEnd,
adjust = MyAdjust, src=SymbolSrc)
    }
  } else {
    for (i in 1:length(TestSym)){
      print(paste("From MySQL -- ",TestSym[i]))
#      getSymbols(TestSym[i], src="MySQL")
      getSymbols(TestSym[i], src="MySQL", user="myName",
password="myPassword", dbname=dbName)
      assign(TestSym[i], get(TestSym[i])[paste0(DataStart,"/",DataEnd)])
    }
    dbDisconnect(dbc)
  }
}

I guess it doesn't matter where I set the name & password. I'm
confusing this whole thing with a different thread.

Sorry,
Mark