2 SQL Formatting Utility

(require (planet jaz/mysql:1/format))

Provides a utility for formatting and properly escaping SQL data.

The format module exports a single procedure, format-sql, used to format and escape data for inclusion in SQL strings.

(format-sql form v ...) → string?

form : string?

v : any/c

Formats to an SQL string. form may contain the following formatting escapes:

• ~s formats the next argument as a string datum, properly quoting and escaping it

• ~d formats the next argument as an SQL DATE (’yyyy-mm-dd’); the argument must be an SRFI 19 date

• ~D formats the next argument as a SQL DATETIME (’yyyy-mm-dd hh:mm:ss’); the argument must be an SRFI 19 date

• ~t formats the next argument as a SQL TIME; the argument must be an SRFI 19 time

• ~i formats the next argument as an integer; the argument must be an integer

• ~r formats the next argument as a real number; the argument must be a real number

• ~c formats the next argument as a table or column name, escaping it properly; the argument must be a string or symbol

• ~b formats the next argument as a hex-encoded binary string; the argument must satisfy bytes?

• ~~ results in a literal tilde (~)

If form contains an escape character not listed above, or if the number of vs does not match the number of escape sequences in form exn:fail:contract is raised.

Note that the escaping rules are MySQL-specific. They are not ANSI SQL.

Examples:

> (display      (format-sql "SELECT ~c FROM foo WHERE id = ~i" 'foo-id 2))

SELECT `foo-id` FROM foo WHERE id = 2

> (display      (format-sql "UPDATE foo SET date_performed = ~d WHERE desc = ~s"                  (srfi-19:make-date 0 0 0 0 1 7 2009 #f)                  "Say \"Goodnight\" now.\nTime to go."))

UPDATE foo SET date_performed = '2009-07-01' WHERE desc = 'Say \"Goodnight\" now.\nTime to go.'

> (display      (format-sql "INSERT INTO binary_data (id, data) VALUES (~i, ~b)"                  sql-null #"\0hello"))

INSERT INTO binary_data (id, data) VALUES (NULL, x'0068656C6C6F')