Reference · Database

sql_name

sql_name(str | list) -> str

What it does

A table's or a column's name, checked and quoted, for SQL that cannot take it as a ? parameter: "title", or `title` when the program's database is MySQL. A list of names gives them separated by commas. A name is letters, digits and _, not starting with a digit and at most 63 bytes long; anything else is refused, so a name made from data (sql_name(`obj_${id}`)) cannot carry SQL into the query, and quillo check does not warn about SQL built with it.

Examples

print(sql_name("title"))print(sql_name(["title", "body"]))let cols = ["title", "body"]                       // the program's list, not the visitor'sprint(`insert into posts (${sql_name(cols)}) values (${sql_param(cols)})`)

"title""title", "body"insert into posts ("title", "body") values (:title, :body)

Edge cases

• Underscores and digits are fine inside a name, and a name may start with _.
• An empty list gives the empty string.
• 63 bytes is the longest name taken, on every database (PostgreSQL keeps no more of a name), so two names are never one there: 63 ASCII letters, 31 és.
• Letters and digits of any script count, and the marks written on a letter (Hindi's vowel signs, an accent written apart): sql_name("名前") is "名前". A character past U+FFFF (an emoji, which MySQL takes in no name) is refused, and so is a name starting with a digit of any script.
• Take the columns from the program, never from what the visitor sent: keys(request.form) names is_admin and id as well as email, and they are names, so it takes them. A sign-up form with is_admin=1 made an admin. A record type, { form: Signup }, keeps its fields only.
• The quotes are double quotes for SQLite and PostgreSQL; a program whose database is MySQL gets backticks, since MySQL reads "x" as text.
• It quotes for the program's database only. For SQL sent to another database, call the connection's own: shop.sql_name(col) on a db.connect connection quotes as that database reads names (see db.sql_name). With the program on SQLite and shop on MySQL, sql_name("title") in shop.query(...) is "title", which MySQL takes as text: every row would hold the word title.

print(sql_name("_x1"), sql_name("obj_42"))print(len(sql_name([])))let long = "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijk"print(len(long), len(sql_name(long)))print(sql_name("名前"), sql_name("prénom"), sql_name("नाम"))

"_x1" "obj_42"063 65"名前" "prénom" "नाम"

Errors

A name with anything but letters, digits and _, an empty one, one starting with a digit, one over 63 bytes, or a value that is not a string (or a list of strings) is a runtime error, not an error value: the request stops instead of running the SQL.

print(sql_name("posts; drop table posts"))

line 1: `sql_name`: "posts; drop table posts" is not a name SQL can take (letters, digits and _)

print(sql_name(["ok", "1abc"]))

line 1: `sql_name`: "1abc" is not a name SQL can take (letters, digits and _)

print(sql_name(repeat("a", 64)))

line 1: `sql_name`: "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" is not a name SQL can take (at most 63 bytes)

print(sql_name(42))

line 1: `sql_name` takes a name (a str) or a list of them, got int

Chaining

print(["id", "name"] | sql_name)let cols = ["title", "body"]let set = cols | map((c) => `${sql_name(c)} = ${sql_param(c)}`) | join(", ")print(`update posts set ${set} where id = :id`)

"id", "name"update posts set "title" = :title, "body" = :body where id = :id

fn table_of(id: int) = sql_name(`obj_${id}`)let rows = db.query(`select * from ${table_of(id)} order by id`)

See also

db.sql_name, sql_param, sql, keys, db.query

Edit this page on GitHub · every example's output was made by running it (tools/refcheck.mjs) · questions on Discord or r/quillo