SQL:   A Structured Notation for SQL Statements
1 Using the SQL Library
2 Statement Forms
sql
select
insert
update
delete
create-table
create-view
sql-statement?
sql-statement->string
3 S-expression Syntax for SQL
sql-ast->string
3.1 SQL Names and Identifiers
ident-qq
ident-ast?
name-qq
name-ast?
3.2 SQL Scalar Expressions
scalar-expr-qq
scalar-expr-ast?
3.2.1 Special Scalar Expressions
3.2.2 Placeholders and Unquoted Parameters
3.3 SQL Table References and Expressions
table-ref-qq
table-ref-ast?
table-expr-qq
table-expr-ast?
3.4 SQL Statements
statement-qq
statement-ast?
3.5 SQL DDL Statements
ddl-qq
ddl-ast?
3.6 SQL Dialect
current-sql-dialect
3.7 Dynamic Statement Composition and SQL Injection
make-ident-ast
make-name-ast
value->scalar-expr-ast
make-values*-table-expr-ast
7.1

SQL: A Structured Notation for SQL Statements

Ryan Culpepper <[email protected]>

 (require sql) package: sql

This library provides an S-expression notation for a subset of SQL. It provides forms that produce statements (as opaque values rather than strings) that can be used directly with Racket’s db library. It also provides macros and functions for creating and manipulating SQL ASTs.

1 Using the SQL Library

This library complements the db library. The database library manages connecting to databases and executing queries; this library helps construction of the queries to execute.

We’ll start by going through the examples Introduction to Using Database Connections using this library’s SQL notation instead.

> (require sql db)
> (define pgc ....)

First we create a temporary table to play around with:

> (query-exec pgc
    (create-table #:temporary the_numbers
      #:columns [n integer #:not-null] [d varchar]))
> (query-exec pgc
    (insert #:into the_numbers #:set [n 0] [d "nothing"]))

Let’s take a look at the statements that just went by:

> (create-table #:temporary the_numbers
    #:columns [n integer #:not-null] [d varchar])

(sql-statement

 "CREATE TEMPORARY TABLE the_numbers (n integer NOT NULL, d varchar)")

> (insert #:into the_numbers #:set [n 0] [d "nothing"])

(sql-statement "INSERT INTO the_numbers (n, d) VALUES (0, 'nothing')")

Now let’s add another row, using “computed” values rather than literals. We can use unquote (or ,) in a scalar expression position to insert a Racket value:

> (define n1 1)
> (define d1 "the loneliest number")
> (query-exec pgc
    (insert #:into the_numbers #:set [n ,n1] [d ,d1]))

Let’s look at that last statement:

> (insert #:into the_numbers #:set [n ,n1] [d ,d1])

(sql-statement

 "INSERT INTO the_numbers (n, d) VALUES (?, ?)"

 1

 "the loneliest number")

The unquoted expressions turned into parameter placeholders, and the statement stores their values separately. Strangely, the placeholders appear as ?, and PostgreSQL doesn’t understand ? placeholders; they should have been $1 and $2. But the statement seems to have worked. What’s going on?

We need to set the interactive printing dialect to PostgreSQL. This has no effect on the query operations; they set the dialect independently based on the database connection.
> (parameterize ((current-sql-dialect 'postgresql))
    (print (insert #:into the_numbers #:set [n ,n1] [d ,d1])))

(sql-statement "INSERT INTO the_numbers (n, d) VALUES ($1, $2)" 1 "the loneliest number")

And now we see $1 and $2 as expected.

We can introduce placeholders explicitly (although unquote is usually more convenient). An explicit placeholder is written ?, regardless of the dialect. The parameters are given in the query call as usual:

> (query-exec pgc
    (insert #:into the_numbers #:set [n ?] [d ?])
    (+ 1 1) "company")

It is not currently possible to mix explicit placeholders and unquote parameters:
> (query-exec pgc
    (insert #:into the_numbers #:set [n ,3] [d ?])
    "a crowd")

eval:2:0: insert: cannot use both placeholders and unquoted

values

  in: (insert #:into the_numbers #:set (n (unquote 3)) (d

?))

You can, of course, mix constant literals and unquotes (or placeholders).

> (query-exec pgc
    (insert #:into the_numbers #:set [n 3] [d ,"a crowd"]))

SELECT statements are constructed similarly, and they follow the same rules regarding parameters. The statements work the same with all of the query operations.

> (query pgc
    (select n d #:from the_numbers #:where (= (% n 2) 0)))

(rows-result

 '(((name . "n") (typeid . 23) (type-size . 4) (type-mod . -1))

   ((name . "d") (typeid . 1043) (type-size . -1) (type-mod . -1)))

 '(#(0 "nothing") #(2 "company")))

> (query-rows pgc
    (select n d #:from the_numbers #:where (= (+ n n) (* n n))))

'(#(0 "nothing") #(2 "company"))

> (query-row pgc
    (select n d #:from the_numbers #:where (< n 1)))

'#(0 "nothing")

> (query-list pgc
    (select d #:from the_numbers #:where (= 0 (% n 2))))

'("nothing" "company")

> (query-value pgc
    (select (string_agg d ", ") #:from the_numbers #:where (= 0 (% n 2))))

"nothing, company"

There are S-expression notations for many common SQL operators and expression forms. See SQL Scalar Expressions for details.

The rest of this manual uses the default SQL1992 dialect for printing results:

2 Statement Forms

The macros in this section create statement values suitable for passing to the query functions of the db library. These statement values satisfy the db library’s statement? predicate. They are different from the statement ASTs produced by statement-qq.

The printing of a statement value is controlled by (current-sql-dialect), but the code it generates when passed to a query function is determined by the dialect of the connection the query is performed on.

syntax

(sql statement)

(sql ddl-statement)
Produces a statement value that can be passed to a db query function. The syntax corresponds to the syntax of the statement or ddl-statement nonterminals from S-expression Syntax for SQL.

Examples:
> (sql (select a b c #:from mytable #:where (> a 10)))

(sql-statement "SELECT a, b, c FROM mytable WHERE (a > 10)")

> (sql (insert #:into mytable #:set [a 1] [b 2] [c 3]))

(sql-statement "INSERT INTO mytable (a, b, c) VALUES (1, 2, 3)")

> (sql (create-table numbers
         #:columns [n integer #:not-null] [t text]
         #:constraints (primary-key n)))

(sql-statement

 "CREATE TABLE numbers (n integer NOT NULL, t text, PRIMARY KEY (n))")

syntax

(select select-item ... select-clause ...)

(select select-clause ...)

syntax

(insert #:into table-name assign-clause maybe-conflict)

(insert #:into table-name maybe-columns #:from table-expr maybe-conflict)

syntax

(update table-name assign-clause maybe-where)

syntax

(delete #:from table-name maybe-where)

Like sql, but specialized to the syntax of the select-statement, insert-statement, update-statement, and delete-statement nonterminals from S-expression Syntax for SQL, respectively.

Examples:
> (select a b c #:from mytable #:where (> a 10))

(sql-statement "SELECT a, b, c FROM mytable WHERE (a > 10)")

> (insert #:into mytable #:set [a 1] [b 2] [c 3])

(sql-statement "INSERT INTO mytable (a, b, c) VALUES (1, 2, 3)")

> (insert #:into mytable
          #:from (select a b c
                         #:from other_table
                         #:where (is-not-null d)))

(sql-statement

 "INSERT INTO mytable SELECT a, b, c FROM other_table WHERE (d IS NOT NULL)")

Equivalent to
(sql (select select-item ... select-clause ...))
(sql (insert #:into table-name assign-clause))
and so forth.

Changed in version 1.1 of package sql: Added #:or-ignore for insert.

syntax

(create-table maybe-temp maybe-ifnotexists table-name
   #:columns column-def ...
   maybe-constraints)
(create-table maybe-temp maybe-ifnotexists table-name
   #:as statement)

syntax

(create-view maybe-temp view-name
  statement)
Like sql, but specialized to the syntax of the DDL create-table-statement and create-view-statement, respectively.

Example:
> (create-table numbers
    #:columns [n integer #:not-null] [t text]
    #:constraints (primary-key n))

(sql-statement

 "CREATE TABLE numbers (n integer NOT NULL, t text, PRIMARY KEY (n))")

Equivalent to
(sql (create-table maybe-temp table-name
       #:columns column-def ...
       maybe-constraints))
and so forth.

Changed in version 1.1 of package sql: Added #:if-not-exists option.

procedure

(sql-statement? v)  boolean?

  v : any/c
Returns #t if v is a statement value returned by one of the forms in this section such as select, #f otherwise.

procedure

(sql-statement->string statement [dialect])  string?

  statement : sql-statement?
  dialect : (or/c symbol? #f) = (current-sql-dialect)
Produces SQL code as a string for the given statement according to the rules of dialect.

3 S-expression Syntax for SQL

This section describes this library’s S-expression syntax for a subset of SQL. The SQL support is organized by nonterminals (eg statement, table-ref, and scalar-expr); the grammar handled by this library is adapted from A Guide to the SQL Standard, 4th ed. by C. J. Date and Hugh Darwen. Each non-terminal has the following:
  • an S-expression syntax,

  • an AST type predicate, and

  • a quasiquotation macro to produce AST values from the S-expression syntax.

All literals are recognized symbolically, rather than by identifier binding, to avoid cluttering the namespace. The AST type representations are not considered public; they are likely to change in future versions of this library.

procedure

(sql-ast->string ast [dialect])  string?

  ast : 
(or/c name-ast? scalar-expr-ast? table-expr-ast?
      table-ref-ast? statement-ast? ddl-ast?)
  dialect : (or/c symbol? #f) = (current-sql-dialect)
Produces SQL code as a string for the given AST to a string according to the rules of dialect. Examples are given throughout the following sections.

3.1 SQL Names and Identifiers

A name is either an unqualified identifier or an identifier qualified with another name, which depending on its usage might represent a catalog, schema, table, range variable, etc.

Concrete SQL has both unquoted and quoted identifiers. Different SQL environments (eg, database backends) have different restrictions on unquoted identifiers, regarding illegal characters and reserved words. Most (but not all) systems also apply some case-folding rule to unquoted identifiers (eg, PostgreSQL converts to lowercase, some others convert to uppercase).

Similarly, this library has both “tagged” and “untagged” notations for identifiers and names. Untagged identifiers are written as raw symbols; they are short and convenient, but they run the risk of confusion with operators and special symbols used by this library. Examples of special symbols include select, as, and from. Examples of identifiers containing operator characters include hello-goodbye and first/last. These identifiers must be written in tagged form.

  ident = symbol
  | (Ident: string)
  | (Ident: symbol)
     
  name = symbol
  | ident
  | (Name: name ...+)

(Ident: symbol)

Unquoted if possible; case-folded and quoted according the SQL dialect’s rules if symbol is a reserved word or contains illegal characters.

(Ident: MyTable)                ; MyTable
(Ident: Select)                 ; "SELECT"
(Ident: a+b.c)                  ; "a+b.c"
(Ident: string)

Always quoted without case-folding.

(Ident: "MyTable")              ; "MyTable"
(Ident: "Select")               ; "Select"
(Ident: "x1.$!!")               ; "x1.$!!"
(Name: name ...+)

Qualified name; each name except the last qualifies the name to its right.

(Name: x y z)                   ; x.y.z
(Name: x y.z)                   ; x.y.z
(Name: x (Ident: y.z))          ; x."y.z"
symbol

Must not be a special symbol; otherwise an error is raised.

Equivalent to (Ident: symbol) if symbol contains no dot (.) characters and matches the pattern #px"^(?:\\p{L}|_)(?:\\p{L}|\\p{N}|[_$])*$"that is, a letter or underscore followed by zero or more letters, numbers, underscores, and dollar signs.

If symbol consists of dot-separated parts satisfying the rule above, it is equivalent to (Name: part ...).

MyTable                         ; MyTable
x.y.z                           ; x.y.z
x.select.as                     ; x."SELECT"."AS"

Because case-folding behavior is system-dependent, it is wisest to either always quote a given name or never quote it.

syntax

(ident-qq ident)

procedure

(ident-ast? v)  boolean?

  v : any/c
Quasiquotation macro and predicate, respectively, for ident.

Examples:
> (sql-ast->string (ident-qq MyTable))

"MyTable"

> (sql-ast->string (ident-qq (Ident: MyTable)))

"MyTable"

> (sql-ast->string (ident-qq (Ident: "MyTable")))

"\"MyTable\""

> (sql-ast->string (ident-qq Select))

"\"SELECT\""

> (sql-ast->string (ident-qq (Ident: Select)))

"\"SELECT\""

> (sql-ast->string (ident-qq (Ident: "Select")))

"\"Select\""

> (sql-ast->string (ident-qq (Ident: a+b.c)))

"\"A+B.C\""

> (sql-ast->string (ident-qq select))

eval:17.0: ident-qq: special symbol cannot be used as

untagged identifier

  at: select

  in: (ident-qq select)

  parsing context:

   while parsing Ident

    term: select

    location: eval:17.0

> (sql-ast->string (ident-qq a+b.c))

eval:18.0: ident-qq: illegal character in untagged

identifier

  at: a+b.c

  in: (ident-qq a+b.c)

  parsing context:

   while parsing Ident

    term: a+b.c

    location: eval:18.0

syntax

(name-qq name)

procedure

(name-ast? v)  boolean?

  v : any/c
Quasiquotation macro and predicate, respectively, for name.

Examples:
> (sql-ast->string (name-qq (Name: x y z)))

"x.y.z"

> (sql-ast->string (name-qq (Name: x.y z)))

"x.y.z"

> (sql-ast->string (name-qq x.y.z))

"x.y.z"

> (sql-ast->string (name-qq x.select.as))

"x.\"SELECT\".\"AS\""

3.2 SQL Scalar Expressions

A scalar expression is either a name, a literal integer or string value, or an application of some function or operator. Note: not every kind of expression is supported in every SQL dialect.

  scalar-expr = name
  | exact-integer
  | string
  | (exists table-expr)
  | (in scalar-expr #:from table-expr)
  | (in scalar-expr #:values scalar-expr ...)
  | (case [scalar-expr scalar-expr] ... maybe-else)
  | (case #:of scalar-expr [scalar-expr scalar-expr] ... maybe-else)
  | (compare-operator scalar-expr #:some table-expr)
  | (compare-operator scalar-expr #:all table-expr)
  | (name scalar-expr ...)
  | table-expr
  | (operator/special scalar-expr ...)
  | ?
  | (unquote racket-expr)

(exists table-expr)

Produces an EXISTS expression:

(exists (select 1 #:from t))        ; EXISTS (SELECT 1 FROM t)

(in scalar-expr #:from table-expr)
(in scalar-expr #:values scalar-expr ...)

There are two forms of IN expression, one for table expressions and one for lists of scalar expressions:

(in x #:from (select y #:from ys))  ; x IN (SELECT y FROM ys)
(in x #:values 1 2 3)               ; x IN (1, 2, 3)
(case [scalar-expr scalar-expr] ... maybe-else)
(case #:of scalar-expr [scalar-expr scalar-expr] ... maybe-else)

There are two forms of CASE expression, one like Racket’s cond and the other like Racket’s case:

(case [(= x 0) "zero"] [else "no"])  ; CASE WHEN x = 0 THEN 'zero' ELSE 'no' END
(case #:of x [0 "zero"] [else "no"]) ; CASE x WHEN 0 THEN 'zero' ELSE 'no' END
(compare-operator scalar-expr #:some table-expr)
(compare-operator scalar-expr #:all table-expr)

Produces an “all-or-any” comparison between a scalar (or row) expression and a table expression.

(= x #:some (select y #:from ys))   ; x = SOME (SELECT y FROM ys)
(< x #:all (select y #:from ys))    ; x < ALL (select y FROM ys)
(name scalar-expr ...)

Represents an ordinary function call; no arity checking is done.

(coalesce x y z)              ; coalesce(x, y, z)

table-expr

Represents a subquery; the query must return at most one row.

(select y #:from ys #:where (x = 0))  ; (SELECT y FROM ys WHERE x = 0)

syntax

(scalar-expr-qq scalar-expr)

procedure

(scalar-expr-ast? v)  boolean?

  v : any/c
Quasiquotation macro and predicate, respectively, for scalar-expr.

Examples:
> (sql-ast->string (scalar-expr-qq mytable.mycolumn))

"mytable.mycolumn"

> (sql-ast->string (scalar-expr-qq 42))

"42"

> (sql-ast->string (scalar-expr-qq "Salutations"))

"'Salutations'"

> (sql-ast->string (scalar-expr-qq "a 'tricky' string"))

"'a ''tricky'' string'"

> (sql-ast->string (scalar-expr-qq (log (- 1 p))))

"log((1 - p))"

> (sql-ast->string (scalar-expr-qq (and (> x 10) (< x 55))))

"((x > 10) AND (x < 55))"

> (sql-ast->string (scalar-expr-qq (coalesce x y z)))

"coalesce(x, y, z)"

> (sql-ast->string (scalar-expr-qq (cast "2015-03-15" DATE)))

"CAST('2015-03-15' AS \"DATE\")"

> (sql-ast->string (scalar-expr-qq (extract YEAR dob)))

"EXTRACT(\"YEAR\" FROM dob)"

> (sql-ast->string (scalar-expr-qq (is-null mytable.mycolumn)))

"(mytable.mycolumn IS NULL)"

> (sql-ast->string (scalar-expr-qq (like ph_num "555-____")))

"(ph_num LIKE '555-____')"

> (sql-ast->string (scalar-expr-qq (|| lname ", " fname)))

"(lname || ', ' || fname)"

3.2.1 Special Scalar Expressions
(operator/special scalar-expr ...)

This function-like syntax is used to represent uses of SQL operators, standard SQL functions that don’t use ordinary function-call notation, and a few other special cases, listed below.

3.2.2 Placeholders and Unquoted Parameters

There are two variants of scalar-expr that enable the construction of parameterized queries. The first is the placeholder, written ? (regardless of the notation used by the database the query is to be sent to). The second is the unquote form, which is equivalent to inserting a placeholder and also providing the expression as a query parameter.

  scalar-expr = ....
  | ?
  | (unquote racket-expr)

Note: Due to limitations in the db library, unquote parameters and ordinary placeholders cannot be mixed in the same statement.

Example:
> (select a #:from mytable #:where (= b ?))

(sql-statement "SELECT a FROM mytable WHERE (b = ?)")

The resulting statement can be used with parameters thus:

(query-value c (select a #:from mytable #:where (= b ?)) 10)

Using the unquote form eliminates the need to keep track of positional parameters; instead, the parameter value is written as a Racket expression within the statement. It is automatically translated to SQL code containing placeholders.

Examples:
> (define b-param 10)
> (select a #:from mytable #:where (= b ,b-param))

(sql-statement "SELECT a FROM mytable WHERE (b = ?)" 10)

The resulting statement must be called without additional parameters:

(query-value c (select a #:from mytable #:where (= b ,b-param)))

Note that placeholder syntax varies between SQL dialects. We can see the code a statement produces for a specific dialect by setting the current-sql-dialect parameter:

> (parameterize ((current-sql-dialect 'postgresql))
    (sql-statement->string (select a #:from mytable #:where (= b ,b-param))))

"SELECT a FROM mytable WHERE (b = $1)"

3.3 SQL Table References and Expressions

A table reference is either a reference to a defined table (or view) or a computed table with a name or named components. A table expression can be formed using join and set operations.

  table-ref = table-name
  | (as table-name range-var-ident)
  | (as table-expr range-var-ident)
  | table-expr

Note: in the final variant of table-ref, the table-expr must be a join table expression, specifically.

  table-expr = (cross-join table-ref table-ref)
  | (join-op table-ref table-ref join-condition)
  | 
(set-op table-ref table-ref
        maybe-all correspond-clause)
  | (values scalar-expr ...)
  | (values* (scalar-expr ...) ...)
  | select-statement
     
  join-op = inner-join
  | left-join
  | right-join
  | full-join
  | union-join
     
  join-condition = #:natural
  | #:using column-ident ...
  | #:on scalar-expr
     
  set-op = union
  | except
  | intersect
     
  maybe-all = 
  | #:all
     
  correspond-clause = 
  | #:corresponding
  | #:corresponding-by column-ident ...

syntax

(table-ref-qq table-ref)

procedure

(table-ref-ast? v)  boolean?

  v : any/c
Quasiquotation macro and predicate, respectively, for table-ref.

Examples:
> (sql-ast->string (table-ref-qq supplier))

"supplier"

> (sql-ast->string (table-ref-qq (as supplier s)))

"supplier AS s"

> (sql-ast->string (table-ref-qq (inner-join supplier part #:using supply_id)))

"supplier INNER JOIN part USING (supply_id)"

syntax

(table-expr-qq table-expr)

procedure

(table-expr-ast? v)  boolean?

  v : any/c
Quasiquotation macro and predicate, respectively, for table-expr.

3.4 SQL Statements

A statement is one of the four standard DML statements or a WITH statement that combines them with one or more common table expressions.

  statement = select-statement
  | insert-statement
  | update-statement
  | delete-statement
  | with-statement

Select

  select-statement = (select distinct-clause select-item ... select-clause ...)
  | (select distinct-clause select-clause ...)
     
  select-clause = #:values select-item ...
  | #:from table-ref ...
  | #:where condition-scalar-expr ...
  | #:group-by column-ident ...
  | #:having condition-scalar-expr ...
  | #:order-by order-item ...
  | #:limit scalar-expr
  | #:offset scalar-expr
     
  select-item = scalar-expr
  | (as scalar-expr ident)
     
  distinct-clause = 
  | #:all
  | #:distinct
     
  order-item = scalar-expr #:asc
  | scalar-expr #:desc
  | scalar-expr

A select-statement can contain each kind of select-clause at most once. The clauses can occur in any order. If the first form of select-statement is used (that is, with the initial select-items), then the #:values clause may not also be used.

Changed in version 1.2 of package sql: Added distinct-clause

Insert

  insert-statement = (insert #:into table-name assign-clause maybe-conflict)
  | 
(insert #:into table-name maybe-columns
        #:from table-expr maybe-conflict)
     
  assign-clause = #:set [column-ident scalar-expr] ...
     
  maybe-columns = 
  | #:columns column-ident ...
     
  maybe-conflict = 
  | #:or-ignore

Changed in version 1.1 of package sql: Added #:or-ignore.

Update

  update-statement = (update table-name assign-clause maybe-where)
     
  assign-clause = #:set [column-ident scalar-expr] ...
     
  maybe-where = 
  | #:where condition-scalar-expr ...

Delete

  delete-statement = (delete #:from table-name maybe-where)
     
  maybe-where = 
  | #:where condition-scalar-expr ...

With

  with-statement = 
(with maybe-rec ([table-ident/columns statement])
  statement)
     
  maybe-rec = 
  | #:recursive
     
  table-ident/columns = table-ident
  | (table-ident column-ident ...)

syntax

(statement-qq statement)

procedure

(statement-ast? v)  boolean?

  v : any/c
Quasiquotation macro and predicate, respectively, for statement.

Examples:
> (sql-ast->string
   (statement-qq (select a b c #:from mytable #:where (> a 10))))

"SELECT a, b, c FROM mytable WHERE (a > 10)"

> (sql-ast->string
   (statement-qq (insert #:into mytable #:set [a 1] [b 2] [c 3])))

"INSERT INTO mytable (a, b, c) VALUES (1, 2, 3)"

3.5 SQL DDL Statements

  ddl-statement = create-table-statement
  | create-view-statement
     
  create-table-statement = 
(create-table maybe-temp maybe-ifnotexists table-name
  #:columns column-def ...
  maybe-constraints)
  | 
(create-table maybe-temp maybe-ifnotexists table-name
  #:as statement)
     
  maybe-temp = 
  | #:temporary
     
  maybe-ifnotexists = 
  | #:if-not-exists
     
  column-def = [column-ident type maybe-not-null]
     
  maybe-not-null = 
  | #:not-null
     
  maybe-constraints = 
  | #:constraints constraint-decl ...
     
  constraint-decl = (constraint constraint-ident constraint)
  | constraint
     
  constraint = (primary-key column-ident ...)
  | (unique column-ident ...)
  | (check scalar-expr)
  | 
(foreign-key column-ident ...
   #:references table-ident/columns)
     
  create-view = 
(create-view maybe-temp view-name
  statement)

Changed in version 1.1 of package sql: Added #:if-not-exists option for create-table.

syntax

(ddl-qq ddl-statement)

procedure

(ddl-ast? v)  boolean?

  v : any/c
Quasiquotation macro and predicate, respectively, for ddl-statement.

3.6 SQL Dialect

parameter

(current-sql-dialect)  (or/c symbol? #f)

(current-sql-dialect dialect)  void?
  dialect : (or/c symbol? #f)
Controls the default dialect used when converting SQL ASTs to strings using functions such as sql-ast->string.

This parameter does not affect statement (sql-statement?) values used with connection query methods; generation of SQL code for a query method automatically uses the dialect associated with the connection the query is performed on.

3.7 Dynamic Statement Composition and SQL Injection

This library allows the dynamic composition of statements and the injection of SQL text using the following extensions to the SQL grammar.

Warning: Never use the INJECT forms to include SQL computed from an untrusted source. Use placeholders or unquote parameters instead; see Placeholders and Unquoted Parameters.

  ident = ....
  | (Ident:AST (unquote ast-racket-expr))
     
  name = ....
  | (Name:AST (unquote ast-racket-expr))
     
  scalar-expr = ....
  | (ScalarExpr:AST (unquote ast-racket-expr))
  | (ScalarExpr:INJECT (unquote string-racket-expr))
     
  table-expr = ....
  | (TableExpr:AST (unquote ast-racket-expr))
  | (TableExpr:INJECT (unquote string-racket-expr))
     
  table-ref = ....
  | (TableRef:AST (unquote ast-racket-expr))
  | (TableRef:INJECT (unquote string-racket-expr))

Changed in version 1.2 of package sql: Added Ident:AST and Name:AST

procedure

(make-ident-ast s)  ident-ast?

  s : (or/c symbol? string?)
Produces an identifier AST from s by applying the rules in SQL Names and Identifiers to the term `(Ident: ,s).

Added in version 1.2 of package sql.

Examples:
> (sql-ast->string (make-ident-ast 'MyTable))

"MyTable"

> (sql-ast->string (make-ident-ast "MyTable"))

"\"MyTable\""

> (sql-ast->string (make-ident-ast 'Select))

"\"SELECT\""

> (sql-ast->string (make-ident-ast 'a+b.c))

"\"A+B.C\""

procedure

(make-name-ast s)  name-ast?

  s : (flat-rec-contract C symbol? name-ast? (listof C))
Produces a name AST from s according to the following rules:
  • If s is a symbol, it is parsed according to the symbol case of the name nonterminal (see SQL Names and Identifiers).

  • If s is a name-ast?, it is returned unchanged.

  • If s is a list, each component is parsed a name-ast? and the components are joined.

Added in version 1.2 of package sql.

Examples:
> (sql-ast->string (make-name-ast 'x))

"x"

> (sql-ast->string (make-name-ast 'x.y.z))

"x.y.z"

> (sql-ast->string (make-name-ast (list 'x.y (make-name-ast 'a.b))))

"x.y.a.b"

procedure

(value->scalar-expr-ast v)  scalar-expr-ast?

  v : any/c
Produces a scalar expression AST representing the Racket value value.

Equivalent to (scalar-expr-qq ,value).

Added in version 1.2 of package sql.

procedure

(make-values*-table-expr-ast l-rows)  table-expr-ast?

  l-rows : (listof (listof scalar-expr-ast?))
Produces a table expression AST of the same kind as the (values* (scalar-expr ...) ...) case of the table-expr nonterminal, but where the number of rows and columns may be determined dynamically.

Note that all of the rows (i.e. the inner lists) must be of the same length.

Added in version 1.3 of package sql.

Examples:
> (struct produce-item (name num type) #:transparent)
> (define (insert-produce items)
    (insert
     #:into produce
     #:columns name num type
     #:from
     (TableExpr:AST
      ,(make-values*-table-expr-ast
        (for/list ([item (in-list items)])
          (match-define (produce-item name num type)
            item)
          (map value->scalar-expr-ast
               (list name num type)))))))
> (sql-statement->string
   (insert-produce
    (list (produce-item "apples" 1 "standard")
          (produce-item "bananas" 5 "organic")
          (produce-item "cranberries" 50 "canned"))))

"INSERT INTO produce (name, num, type) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)"