[R-SIG-Finance] quantmod getSymbols executes MySQL queries without escaping symbol/ticker data

Sergey Pisarenko drseergio at gmail.com
Wed Jan 25 07:02:46 CET 2012


For anyone interested here's the change (it's a trivial fix) for getSymbols.R:

<             query <- paste("SELECT
",paste(db.fields,collapse=','),paste(" FROM `",Symbols[[i]],"`",
sep='')," ORDER BY date")
>             query <- paste("SELECT ",paste(db.fields,collapse=',')," FROM ",Symbols[[i]]," ORDER BY date")

Works fine afterwards.


On Wed, Jan 25, 2012 at 6:38 AM, Sergey Pisarenko <drseergio at gmail.com> wrote:
> Hi,
> I have opened the source of getSymbols.R and indeed there is no
> escaping going on. I will modify the source and add escaping. I will
> then test and submit a patch.
> /Sergey
> On Tue, Jan 24, 2012 at 11:02 PM, Sergey Pisarenko <drseergio at gmail.com> wrote:
>> Hi fellow R programmers,
>> I am using quantmod to retrieve historic price data for a range of
>> stocks. The issue is that the program fails at certain points because
>> apparently the values that are sent to MySQL are not escaped properly
>> when quantmod executes sql queries. For example, following stocks will
>> cause issues: "ABV.C" (because of dot MySQL will think we are trying
>> to access table C), "ALL" (MySQL thinks this is a reserved sequence).
>> Here's the code I have:
>> # code omitted
>> ..
>> tickers <- dbListTables(con)  # con is connection to MySQL db where
>> tables with stock data exist
>> for (ticker in tickers) {
>>  bars <- getSymbols(ticker, src="MySQL", auto.assign=FALSE)  # it
>> fails here when data contains special characters
>> }
>> Is this expected behavior and is there something I can do about it?
>> I have tried using "try" and "tryCatch" but the execution breaks anyway.
>> /Sergey

More information about the R-SIG-Finance mailing list