0

I'm trying to create a query my MySQL database using RMySQL. I want to find all queries that contain a variable I provide through R.

For example:

z="name"
dbSendQuery(conn, "SELECT Address FROM Test WHERE Business Like '%z%'")

Wher conn is my connection to my database, Address and Business are columns in a table Test.

This doesn't work, as this causes the query to search for rows that contain the letter z, rather than what the variable z stands for. Any suggestions?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
BioBaker
  • 13
  • 4
  • PHP Variables start with a `$` and lines of code are terminated with a `;` Suggest you [start here](http://php.net/docs.php) – RiggsFolly Mar 23 '16 at 16:45
  • Note the other option of using `sprintf` mentioned in comments there. – joran Mar 23 '16 at 17:10
  • Also more general options [here](http://stackoverflow.com/q/16178640/324364). – joran Mar 23 '16 at 17:12
  • You should consider passing your variables as parameters instead of interpolating them into the string. Something like `dbSendQuery(conn, "SELECT Address FROM Test WHERE Business Like '%?%'", z)` exists in most database querying APIs. This will not only make it easy to pass the parameter, it will also protect you from SQL injection. – tanner0101 Mar 23 '16 at 17:13
  • @TannerNelson There is (currently) no general framework for parametrized queries from R. The RSQLite package has supported it for a while, and the general db interface system is currently being rewritten to include that kind of functionality, but generally speaking in R you build the query itself as a string. – joran Mar 23 '16 at 17:21

0 Answers0