CODEWITHSUNDEEP
mysqlrvariables
John
John

Reputation: 5249

How to use a variable name in a SQL statement?

I'm using R to call a mySQL statement, where I define the variable outside the statement e.g.

foo = 23;
dbGetQuery(con, "select surname from names WHERE age = '.foo.' ;")

But this returns an empty set, I've googled around and tried'.&foo.' ".foo." '".&&foo."' and many different combinations, but none of them work, I think this should be a mysql question rather than an R specific problem I'm having, but not sure. Normally variables have $values but not in R.

Upvotes: 3

Views: 5559

Answers (5)

David Marx
David Marx

Reputation: 8568

The accepted answer gives bad advice which leaves your application vulnerable to SQL injection. You should always use bind variables instead of concatenating values directly into your query. Use the dbGetPreparedQUery method as described in this answer: Bind variables in R DBI

Upvotes: 1

Shane
Shane

Reputation: 100204

You may want to look at the answers to this question: Can I gracefully include formatted SQL strings in an R script?.

The simplest solution is to use the paste command as Robert suggested.

Upvotes: 2

Robert
Robert

Reputation: 848

This should work:

foo = 23;

sqlStatement <- paste("select surname from names WHERE age =",foo,'"',sep="")

dbGetQuery(con, sqlStatement;)

Upvotes: 8

LuI
LuI

Reputation: 49

AFAIK the command has to be a string, so you should append the single components. Not being familiar with R I cant help you out HOW to do that. In MS-VBA the string concatenation operator is '&'.

Upvotes: 0

Sarfraz
Sarfraz

Reputation: 382899

Adding the semi-colon at the end of query sometimes creates problem. Try changing your query from:

dbGetQuery(con, "select surname from names WHERE age = '.foo.' ;")

to:

dbGetQuery(con, "select surname from names WHERE age = '.foo.'")

Upvotes: 0

Related Questions