How to write a parameterized SQL query?

Question

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)

Answer 1

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),],"'")

Answer 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