How can I automatically add an additional apostrophe when entered by a user so as to not break their query?

I have a function where users specify start date (sd), end date (ed) and 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 Dave''s Site).

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, "'")

Function call:

plot_range("2021-02-01", "2021-02-28", "Dave's Site")

Answer

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

[1] "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.