0

I would like to run some queries with RStudio using the RODBC library. Normally, code like this works fine:

query_6 <- sqlQuery(con, "Select * from my_table where condition = more_than_sth")

I prefer to have some variable which will be defined by me before and stay for more_than_sth. Lets says it is x. Is there any method which would make me able to put this variable into the query string? Should I use some kind of paste, maybe before, or put it in directly?

Regards, Rafał

Steven C. Howell
  • 16,902
  • 15
  • 72
  • 97
RafMil
  • 138
  • 2
  • 2
  • 15
  • If you must stay with `RODBC`, use parameterized queries using [`RODBCext`](https://cran.r-project.org/web/packages/RODBCext/index.html), already discussed at https://stackoverflow.com/questions/16178640/parameterized-queries-with-rodbc. Suggested alternatives include [`odbc`](https://cran.r-project.org/web/packages/odbc/index.html) or [`DBI`](https://cran.r-project.org/web/packages/DBI/index.html)-based native drivers. – r2evans Feb 20 '18 at 16:10

1 Answers1

1

Concatenate function in R is paste, it automatically append a whitespace between each object, you can remove them by using paste(..., sep = "") or paste0().

more_than_sth <- "x"
query_6 <- sqlQuery(con, paste0("Select * from my_table where condition ='", more_than_sth, "'"))
Julien Navarre
  • 7,653
  • 3
  • 42
  • 69
  • 1
    No, this is not the right/best/suggested (or with me, tolerated) approach. [SQL injection](https://en.wikipedia.org/wiki/SQL_injection), whether intentional or accidental (quoting). (BTW: many (most? uncertain) SQL languages need single-quotes, not double-quotes, around string literals.) Also, consider if `more_than_sth <- 'foo"'`. – r2evans Feb 20 '18 at 16:07
  • 1
    Agree for simple quotes. But why would you care about SQL Injections when you are running queries from RStudio ? – Julien Navarre Feb 20 '18 at 16:27
  • 2
    Mostly for breakage than security, admittedly, but perhaps because the last couple decades have many examples of code being used in ways and places not originally intended. You can manually go through the steps to adequately safeguard against single quotes, double quotes, and even backslashes, or you can just do it the right/easy/already-working way by telling SQL from the outset that the value is "always data, no matter what it looks like" and never ever to be interpreted as possible code. – r2evans Feb 20 '18 at 16:32
  • 1
    When I'm doing quick searches, I often use single-quoted literals in the query, I confess. However, before I adopted parameterization as a default action, I had to troubleshoot many problems where the search string was broken due to quoting or something else. Initially I guarded against double-quotes. Then single-quotes. Then I was bitten by backslashes. Nothing bad (for me) has ever come from using parameters; many many bugs have been caused by choosing to not use them. (``) Questions on SE sites are looking for sage/informed advice. This technique is very well informed. – r2evans Feb 20 '18 at 18:26