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

[Sergey Pisarenko]

Hi,

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

357c357
<             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.

/Sergey

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