#lang scribble/doc @(require scribble/manual scribble/bnf scribble/eval (for-label scheme "../format.ss")) @(define (FmtMark . s) (apply litchar "~" s)) @(define the-eval (let ((e (make-base-eval))) (e '(require (planet jaz/mysql:1/format) (only-in (planet jaz/mysql:1) sql-null) (prefix-in srfi-19: srfi/19))) e)) @; -------------------------------------------------------- @title[#:style '(toc)]{SQL Formatting Utility} @defmodule[(planet jaz/mysql:1/format)]{Provides a utility for formatting and properly escaping SQL data.} The format module exports a single procedure, @scheme[format-sql], used to format and escape data for inclusion in SQL strings. @defproc[(format-sql [form string?] [v any/c] ...) string?]{Formats to an SQL string. @scheme[form] may contain the following formatting escapes: @itemize[ @item{@FmtMark{s} formats the next argument as a string datum, properly quoting and escaping it} @item{@FmtMark{d} formats the next argument as an SQL DATE ('yyyy-mm-dd'); the argument must be an SRFI 19 date} @item{@FmtMark{D} formats the next argument as a SQL DATETIME ('yyyy-mm-dd hh:mm:ss'); the argument must be an SRFI 19 date} @item{@FmtMark{t} formats the next argument as a SQL TIME; the argument must be an SRFI 19 time} @item{@FmtMark{i} formats the next argument as an integer; the argument must be an integer} @item{@FmtMark{r} formats the next argument as a real number; the argument must be a real number} @item{@FmtMark{c} formats the next argument as a table or column name, escaping it properly; the argument must be a string or symbol} @item{@FmtMark{b} formats the next argument as a hex-encoded binary string; the argument must satisfy @scheme[bytes?]} @item{@FmtMark{~} results in a literal tilde (~)} ] If @scheme[form] contains an escape character not listed above, or if the number of @scheme[v]s does not match the number of escape sequences in @scheme[form] @scheme[exn:fail:contract] is raised. Note that the escaping rules are MySQL-specific. They are not ANSI SQL. @examples[#:eval the-eval (display (format-sql "SELECT ~c FROM foo WHERE id = ~i" 'foo-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.")) (display (format-sql "INSERT INTO binary_data (id, data) VALUES (~i, ~b)" sql-null #"\0hello"))] }