Kindness, like a boomerang, always returns. Unknown
Idea Transcript
Databases using R
dplyr
from
DBI
Best Practices
Databases
Advanced
Run Queries Safely We will review four options to run SQL commands safely using the DBI package: Parameterised queries Using glue_sql Interpolation by “hand” Manual escaping
SQL Injection Attack The dbGetQuery() command allows us to write queries and retrieve the results. The query has to be written using the SQL syntax that matches to the database type. For example, here is a database that contains the airports data from NYC Flights data: dbGetQuery(con, "SELECT * FROM airports LIMIT 5")
## faa name lat lon alt tz dst ## 1 04G Lansdowne Airport 41.13047 -80.61958 1044 -5 A ## 2 06A Moton Field Municipal Airport 32.46057 -85.68003 264 -6 A ## 3 06C Schaumburg Regional 41.98934 -88.10124 801 -6 A ## 4 06N Randall Airport 41.43191 -74.39156 523 -5 A ## 5 09J Jekyll Island Airport 31.07447 -81.42778 11 -5 A
Often you need to write queries that depend on user input. For example, you might want to allow the user to pick an airport to focus their analysis on. To do this, it’s tempting to create the SQL string yourself by pasting strings together: airport_code