3
$\begingroup$

I am trying to write a piece of code for R to fetch data from an api with the following command:

data <- read.csv("https://...../api/dataservice?sql = select * from db where name IN (?)")

The parenthesis are where I need to input 218 different name tags. How can I do this programmatically? It is possible to write down few of them, but over 200 is not possible. Is there a way this? Can I use a character vector containing the names?

$\endgroup$
  • 1
    $\begingroup$ Aside: what happens if I call that API with sql=DROP TABLE students; --? $\endgroup$ – Philip Kendall Dec 13 '15 at 14:32
1
$\begingroup$

If you already have the names in R, you can iterate over the vector of names, and use the paste function. Here's the help page for it. sprintf would be another option, if you prefer that approach.

An example of this would be

names <- c("a", "b", "c")
querystring_a <- "https://...../api/dataservice?sql = select * from db where name IN "
querystring_b <- '\\'
querystring_c <- "\'"
for(n in names){
    cat(paste(querystring_a, querystring_b, querystring_c, n, querystring_c, sep=""), sep="\n")
}

Return:

https://...../api/dataservice?sql = select * from db where name IN \'a'
https://...../api/dataservice?sql = select * from db where name IN \'b'
https://...../api/dataservice?sql = select * from db where name IN \'c'
$\endgroup$
  • $\begingroup$ Do you mean for(nm in names){ data <- read.csv("https://...../api/dataservice?sql = select * from db where name IN nm ") and then append the data together ? $\endgroup$ – user62198 Dec 11 '15 at 20:06
  • $\begingroup$ Sorry, I should have added example code! I've updated my answer for you. $\endgroup$ – Kyle. Dec 11 '15 at 20:09
  • $\begingroup$ I am having difficulty: I need to input the names like this: where IN (\'name1\',\'name2\') . How do I insert that \ (back slash) in there. I tried paste0, Everytime, i tried to use past0, it comes out as: (\\'name1\\',\\'name2\\') which is giving error. So I do need a single back slash followed by the single quote. Any help as to how to use paste to get me the single backslash ? $\endgroup$ – user62198 Dec 11 '15 at 20:57
  • $\begingroup$ I'm not sure I completely follow...does the replacing the print line with the following fix it? print(paste("https://...../api/dataservice?sql = select * from db where name IN \'", n, "\'", sep="")) From that, I get the following example output: "https://...../api/dataservice?sql = select * from db where name IN 'a'" $\endgroup$ – Kyle. Dec 11 '15 at 22:04
  • $\begingroup$ As I understand, in order to use sql command inside read.csv, in the WHERE clause, we need to enter the names as \' name1\',\'name2\',etc and not simply as 'name1,name2, etc' .... now when i am trying to use the paste function, it is giving me \\'name1\\',\\'name2\\',etc. I couldn't get rid off those extra backslashes. I need single backslash not double. $\endgroup$ – user62198 Dec 11 '15 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.