0

I am trying this query to fetch my value from a MS SQL database on basis of two conditions but still I am getting exception in the syntax part.

Can anybody tell me what is the correct way to write a parameterized query in R?

Following is the query I used:

query<- paste0("SELECT [value] FROM [RiskDashboard].[dbo].[tbl_Simulation]
                  where Row_Id=", row_id[c(1),] ," AND Script_Id=", script_id[c(1),] ,)

T_data<-sqlQuery(ch,query)

print(T_data)
Scarabee
  • 5,437
  • 5
  • 29
  • 55
user3682373
  • 61
  • 8

2 Answers2

2

Parameterizing data is very important - especially from a security perspective. The examples you have are string concatenations and are subject to SQL injections.

The RODBCext package does have support for parameterization.

First - standard SQL parameterization syntax:

SELECT ColA, ColB FROM MyTable where FirstName = ? and LastName = ?

Each ? mark indicates in order the values that will appear in a vector. This syntax is true for ODBC regardless of platform. Others have extended to support position. eg. OLEDB supports @P1, @P2 etc.

While maybe not important for your R queries - in a multi-user system parameterized queries execute faster because the query plan is stored by the database-server (true of both Oracle and SQL Server).

To semi-plagiarize from the documentation:

library(RODBCext)
connHandle <- odbcConnect("myDatabase")
data <- sqlExecute(connHandle, "SELECT * FROM myTable WHERE column = ?", 'myValue', fetch = TRUE) 
odbcClose(connHandle)

Documentation is here: https://cran.r-project.org/web/packages/RODBCext/vignettes/Parameterized_SQL_queries.html

More discussion here: Parameterized queries with RODBC

Scarabee
  • 5,437
  • 5
  • 29
  • 55
ripvlan
  • 460
  • 6
  • 14
0

i got the correct way as follows:

query<- paste0("SELECT [Row_Id],[Script_id],[value] FROM 
[RiskDashboard].[dbo].      [tbl_Simulation] where Row_Id='",row_id[c(i),],"'
AND   Script_Id='",script_id[c(i),],"'")
user3682373
  • 61
  • 8