0

i really need your help. I have an example of a DataBase which table is "t_client (id_cli, name_cli)" I want to select the id of client(id_cli) from "selectInput" and the result gives me the name of client (name_cli) by reactive function. This is my code :

req1 = dbGetQuery(DB, "select id_cli, name_cli from t_client;")

selectInput(inputId = "id_cli", label = "Clients", choices = req1$id_cli)

databaseInput <- reactive({
  req1$id_cli = input$id_cli
  req2 = dbGetQuery(DB, "select *from t_client where id_cli = 'req1$id_cli';")
  req2
})

renderTable({
   databaseInput()
})

It doesnt work; i dont have the link between the item from selectInput and the real table "t_client"

Pinkette
  • 5
  • 2
  • `req1$id_cli` is a string in your code. Have a look at `paste` or `glue` package to deal with strings and variables – Clemsang Sep 16 '20 at 12:35
  • Hello, thanks for your answer, the `req1$id_cli` is a numeric and `name_cli` is char type. The table is like this : (1, toto), (2, tata). When i select `id_cli` (1 or 2) from `selectinput` i dont have only toto or only tata. I dont know where is the problem in my code. – Pinkette Sep 16 '20 at 12:42
  • in `req2` you use `req1$id_cli` as a string and not a variable – Clemsang Sep 16 '20 at 12:44
  • in req2 i cant write this`where id_cli=req1$id_cli` but only like this`where id_cli='req1$id_cli' ` – Pinkette Sep 16 '20 at 12:53
  • You need something like: `dbGetQuery(DB, sprintf("select * from t_client where id_cli = '%s';", req1$id_cli))` – ismirsehregal Sep 16 '20 at 12:55
  • Thanks a lot ismirsehregal it is really that. It's work like i want :) – Pinkette Sep 16 '20 at 13:12
  • You may want to consider parameterized queries vice constructing query strings manually. While there are security concerns about malicious [SQL injection](https://en.wikipedia.org/wiki/SQL_injection) (e.g., XKCD's [*Exploits of a Mom*](https://xkcd.com/327/) aka "Little Bobby Tables"), it is also a concern for malformed strings or Unicode-vs-ANSI mistakes, even if it's a single data analyst running the query. Both `DBI` (with `odbc`) and `RODBC` support parameterized queries, either natively or via add-ons. – r2evans Sep 17 '20 at 02:58
  • Said differently, if I type the following into your app as my name: `'; drop table t_client; --`, there is a reasonable likelihood that you would lose everything. This risk is completely mitigated by using parameter binding. https://dbi.r-dbi.org/reference/dbbind is a good reference, but it's as simple as `DBI::dbGetQuery(con, "select ... where id=?", params=list(req1$id_cli))` (that's right, no quotes around the question mark). As a "hello world" code, try `DBI::dbGetQuery(con, "select 1 where 'a'=?", params=list('a'))`, should return 1; then change one of the `'a'` to something else. – r2evans Sep 17 '20 at 03:03
  • Different DBMSes use different mechanisms for placeholders; my example is for SQL Server but is not uncommon. I think postgresql uses `$name`; sqlite uses `?`, `?num`, or `:name`; and at one time I was confident that all odbc connections (on windows at least) regardless of the remote DBMS all used `?`. Try it and see. – r2evans Sep 17 '20 at 03:06

0 Answers0