safeSQL: Return a function that safely performs sql queries on a...

In MotusWTS/motusServer: Operate a server to process data for http://motus.org

Return a function that safely performs sql queries on a connection.

Description

This uses the 'params' parameter for DBI::dbGetQuery and DBI::dbExecute (for RSQLite and ":" parameters) or dbQuoteStrings (for MySQL or RSQLite " prevent e.g. SQL injection attacks.

Usage

safeSQL(con)

Arguments

con	RSQLite connection to database, as returned by safeSQLiteConnect(), or character scalar giving path to SQLite database, or MySQLConnection, or dplyr::src

Value

a function, S with class "safeSQL" taking two or more parameters:

• query sqlite query with parameter handling indicated by:

  • words beginning with ":", which does parameter binding for RSQLite,

  • or sprintf-style formatting codes (e.g. "%d") which does parameter substitution for RSQLite or MySQL

• ... list of named (for ":" binding) or unnamed (for " query. For ":" binding, all items must be named and have the same length. For "

For RSQLite, these items are passed to data.frame, along with the parameter stringsAsFactors=FALSE.

• ":"-binding example; SQLite only: S("insert into contacts values(:address, :phone)", address=c("123 West Blvd, Truro, NS", "5 East St., Digby, NS"), phone=c("902-555-1234", "902-555-6789"))

• " S("insert into contacts values(\" S("insert into contacts values(\"

• .CLOSE boolean scalar; if TRUE, close the underlying database connection, disabling further use of this function.

• .QUOTE boolean scalar; if TRUE, the default, quote string parameters using

MotusWTS/motusServer documentation built on Aug. 8, 2024, 10:23 p.m.