Why can parameter substitution not be used with tables in sqlite3?

Question

After trying for a long time to get this Python code to work:

#!/usr/bin/python3
import sqlite3

conn = sqlite3.connect("testdb.sqlite")
cur = conn.cursor()

#This doesn't work
cur.execute("DROP TABLE IF EXISTS :table_name", {"table_name": "Table1"})

I came across this and other posts that simply say parameter substitution cannot be used with tables. However, I haven't been able to find any explanations or details (nothing is mentioned in the relevant documentation either).

So my question is: is there a fundamental reason for this exception? If it's arbitrary, are tables the only exception? (This is so that we know what we can and cannot use.)

Answer 1

The SQL standards define parameters only for values; from the ISO/IEC 9075:1992 grammar:

6.2 <value specification> and <target specification>

Function
Specify one or more values, parameters, or variables.

Format

<value specification> ::=
       <literal>
     | <general value specification>

<general value specification> ::=
       <parameter specification>
     | <dynamic parameter specification>
     | <variable specification>
     ...

<parameter specification> ::=
     <parameter name> [ <indicator parameter> ]

But table/column/etc. names use different rules, which end up requiring the actual characters of the name:

5.4 Names and identifiers

Function
Specify names.

Format

<table name> ::=
       <qualified name>
     | <qualified local table name>

<qualified name> ::=
     [ <schema name> <period> ] <qualified identifier>

<qualified identifier> ::= <identifier>

<identifier> ::=
     [ <introducer><character set specification> ] <actual identifier>

<actual identifier> ::=
       <regular identifier>
     | <delimited identifier>

<regular identifier> ::= <identifier body>

<identifier body> ::=
     <identifier start> [ { <underscore> | <identifier part> }... ]

In theory, it might be possible for a database to change its grammar to allow parameters in other places. However, SQL statements can be compiled before the actual parameter values are known, so this would require more changes in the database's API and in how the database manages SQL execution. And it is already possible to execute dynamic SQL without parameters, so no database designer has deemed it worth the effort to make these changes.