I have a function where users specify start date (
sd), end date (
site in order to produce a graph.
I have built a query that inputs the values entered accordingly; however, whenever
site contains an apostrophe, it naturally breaks the query.
In order to allow apostrophes within SQL queries, you must add it twice (i.e.
Dave's Site becomes
How can I allow for this to automatically add another apostrophe when one is entered by a user?
Relevant code snippet:
query <- paste0("SELECT * FROM CV.SAM.EventBASE WHERE site = '", site, "' AND start_date >= '", sd, "' AND end_date <= '", ed, "'")
plot_range("2021-02-01", "2021-02-28", "Dave's Site")
You could do a replacement here on all character variables which you intend to bind as string literals in your SQL query:
site <- "Dave's Site" site <- gsub("'", "''", site, fixed=TRUE) site  "Dave''s Site"
However, the typical way to handle this problem when interfacing with SQL is to use a prepared statement, which handles this type of escaping problem automatically. If R supports something like this, you should use that instead.