0

I have a database in MySQL and I'm able to send queries and get data from my database without an issue. I also know about the dbWriteTable() command but that seems to be only used if you're writing in an entirely new entry. E.g., if my table has attributes, "name" "age" and "major" then dbWriteTable() must have all three attributes.

What I want is to update major while keeping the name and age that are already in the database. Please let me know if more info is needed. I'd really like to figure out how to do this :)

Edit: RMySQL is the R package.

Nicklovn
  • 219
  • 2
  • 11
  • 1
    Is there a reason you cannot just use a SQL "update"? `DBI::dbExecute(con, "UPDATE sometable SET somefield='somevalue' WHERE id=73")`. – r2evans Apr 19 '19 at 06:12
  • 1
    You were right. I realized that afterwards that I can just pass all of the queries through that command. Thank you for your reply. Naturally it seems trivial but this is the first time I've connected R and SQL. Thank you kindly! – Nicklovn Apr 20 '19 at 19:26
  • My example was literal, but to be clear: whatever solution you use should be secure to avoid SQL injection. For example, use `DBI::dbBind` or at least `glue::glue_sql` on all values for comparison and update. – r2evans Apr 21 '19 at 00:51
  • I'll look into those. I'm not too familiar with preventing SQL injections so I'll be sure to see what security concerns exist. Thank you. – Nicklovn Apr 21 '19 at 16:24
  • May I ask what these functions do? I read the documentation but don't full understand why they prevent SQL injection. – Nicklovn Apr 21 '19 at 16:25
  • See the difference between `with(list(studentid="0;drop table students"), sprintf("select * from students where id=%s", studentid))` (bad!) and `glue::glue_sql("select * from students where id={studentid}", studentid="0;drop table students", .con=con)` (good!) (good ref: https://xkcd.com/327/). If you still don't understand why the first is bad ... can you press the `I BELIEVE` button and trust that you should go with `DBI::dbBind` or `glue::glue_sql` to mitigate SQL injection? (https://stackoverflow.com/q/332365/3358272, https://stackoverflow.com/q/601300/3358272) – r2evans Apr 21 '19 at 20:34

0 Answers0