SQLITE what are the restricted characters for identifiers

Question

I am trying to build a system where SQL parameters gets automatically bound to new queries. According to this thread:

SQL Server - Invalid characters in parameter names

The @, _ and # characters should be allowed inside identifiers. So I tried to build parameters as:

:tablename#fieldname

But when doing do I get error:

PBdatabase.select_query: [1] DB[2] prepare() SELECT * FROM creature WHERE pk = :creature#pk [near "#pk": syntax error]

It seems it does not like the # character, I tried with the underscore, it seem to work but I am already using the underscore in field names. This is why I wanted to use a different character.

Since the thread above talked about SQL server, the restricted characters could be different in SQLITE. I found the list of SQLITE restricted keywords, but not characters.

Does anybody know which other special character I could use?

---

Update

Somebody want to know what use I have for this. Here is an example, let say you have 2 tables with a 1 to N relationship: Fleet contains ships.

You want to display a form made of 2 blocks, where the top display 1 selected fleet at the time. Where the bottom block list all the ships in the fleet.

The first block query will be something like:

SELECT pk, number, location  FROM fleet;

Then the fields of the selected entry will be put in a registry of field with the following names (Assuming the # symbol would be valid):

:fleet#pk
:fleet#number
:fleet#location

Then the second query for the second block would be ran including the registered fields above. So the query would look like:

SELECT pk, fk_fleet, name  FROM ship WHERE fk_fleet = :fleet#pk

This query use a parameter from the query above. The identifier will be replaced by the value from the previous query. This allow to display only the ships linked with the selected fleet above instead of all the ships available.

Now some of you might say that I could simply save the variables I want and pass them in parameter to the next query. The problem is that all the queries are loaded from a database. I actually don't know which query I am going to run and which value I will need to save for another query. Instead I save them all in a registry and if another SQL statement ask for a parameter, the value will be available.

Answer 1

SQLite and SQL server are similar, but they do have slightly different syntax, just like how all implementations of SQL usually have different rules.

SQLite allows the letters, A-Z, a-z, 0-9, _, $, pairs of colons "::" after the first char, and Unicode character larger than u007f.

Additionally, at the end of the parameter, one can add parentheses: ( and ), between which can be any character except ASCII whitespace characters and the ) char. 0x00 is also never allowed (since that signifies the end of a C-string).

Examples

So the following parameters are valid:

:a, :_, :$, :a::, :A, :9, :, :  (U+2000, the Unicode en quad space character), :a(:/$asd().

While these are invalid:

:#, :::, ::, :;, :(, :( ) (a normal ASCII space)

Source

I found an article on the SQLite website (see https://www.sqlite.org/draft/tokenreq.html), which specified that named parameters (also known as variables) can consist of:

A "parameter name" is defined to be a sequence of one or more characters that consists of ALPHANUMERIC characters and/or dollar-signs (u0025) intermixed with pairs of colons (u003a) and optionally followed by any sequence of non-zero, non-WHITESPACE characters enclosed in parentheses (u0028 and u0029).

where an ALPHANUMERIC character is defined as:

ALPHABETIC Any of the characters in the range u0041 through u005a (letters "A" through "Z") or in the range u0061 through u007a (letters "a" through "z") or the character u005f ("_") or any other character larger than u007f.

and

NUMERIC Any of the characters in the range u0030 through u0039 (digits "0" through "9")

I also wrote a short Python script to confirm the above values. The Unicode characters 0xd800 to 0xdfff also didn't work, due to Python3 refusing to make a string with those values (see https://en.wikipedia.org/wiki/UTF-16#U+D800_to_U+DFFF for why), so this may work in the SQLite C code.

import sqlite3
import tqdm
import itertools

with sqlite3.connect(":memory:") as conn:
    with conn as cur:
        cur.execute("create table test(x);")

        strings_to_test = ["{}", "a{}", "{0}{0}", "a{0}{0}", "a({})"]
        # make a list to hold the invalid chars for each test item
        strings_to_test = {x: list() for x in strings_to_test}

        def attempt_insert(string, i, invalid_list):
            try:
                cur.execute("insert into test values ( :{} );".format(string)
                    .format(chr(i)),{"{}".format(string).format(chr(i)): 42})
            except Exception as e:
                invalid_list.append(i)
        
        # 0x10FFFF is the max value for UTF chars
        for char_num in tqdm.trange(0, 0x10FFFF):
            for string, invalid_char_list in strings_to_test.items():
                attempt_insert(string, char_num, invalid_char_list)

def gen_ranges(i):
    # from https://stackoverflow.com/a/4629241 with changes for Python3
    for a, b in itertools.groupby(enumerate(i), lambda x: x[1] - x[0]):
        b = list(b)
        yield b[0][1], b[-1][1]

def ranges(invalid_chars):
    return "{}".format(["0x{:06x} to 0x{:06x}".format(*range) \
        for range in gen_ranges(invalid_chars)])

print("Invalid Single Chars: ie :x were:                 {}".format(
    ranges(strings_to_test["{}"])))
print("Invalid Single Second Chars: ie :ax were:         {}".format(
    ranges(strings_to_test["a{}"])))
print("Invalid Double Chars: ie :xx were: {}".format(
    ranges(strings_to_test["{0}{0}"])))
print("Invalid Double Chars in second pos: ie :axx were: {}".format(
    ranges(strings_to_test["a{0}{0}"])))
print("Invalid Parenthesised Chars: ie :abc(x) were:     {}".format(
    ranges(strings_to_test["a({})"])))

Answer 2

In SQLite, query parameter names may only contain alphanumeric characters (except for ?NNN, which must be an integer); see the second paragraph of the sqlite3_bind_* function reference.