How to Use Parameters for the Table Name in SQLite3

Can I use parameters for the table name in sqlite3?

Ooookay, should have looked more thoroughly on SO.

Answers:

- SQLite Parameters - Not allowing tablename as parameter

- Variable table name in sqlite

They are meant for Python, but I guess the same applies for C++.

tl;dr:

You can't pass the table name as a parameter.

If anyone have a link in the SQLite documentation where I have the confirmation of this, I'll gladly accept the answer.

Variable table name in sqlite

Unfortunately, tables can't be the target of parameter substitution (I didn't find any definitive source, but I have seen it on a few web forums).

If you are worried about injection (you probably should be), you can write a function that cleans the string before passing it. Since you are looking for just a table name, you should be safe just accepting alphanumerics, stripping out all punctuation, such as )(][;, and whitespace. Basically, just keep A-Z a-z 0-9.

def scrub(table_name):
    return ''.join( chr for chr in table_name if chr.isalnum() )

scrub('); drop tables --')  # returns 'droptables'

How to use variable for SQLite table name

You can't use parameter substitution for the table name. You need to add the table name to the query string yourself. Something like this:

query = 'SELECT * FROM {}'.format(table)
c.execute(query)

One thing to be mindful of is the source of the value for the table name. If that comes from an untrusted source, e.g. a user, then you need to validate the table name to avoid potential SQL injection attacks. One way might be to construct a parameterised query that looks up the table name from the DB catalogue:

import sqlite3

def exists_table(db, name):
    query = "SELECT 1 FROM sqlite_master WHERE type='table' and name = ?"
    return db.execute(query, (name,)).fetchone() is not None

C# SQLite: Can you use a parameter in place of a table name?

You can, since you control the content of the string.

You can construct the string by adding the table name within.

Example:
string sql = $"SELECT * FROM {tableName} WHERE EntryID = @ID"

This is called string interpolation. I use a similar approach in my Base Classes for CRUD operations.

Edit:

There is mention that is is open to SQL injection. If the parameter named tableName in OP's example, is controlled by the developer and does not take user input, this is safe. If for some very odd reason the parameter takes user input, this can be open to SQL injection and should NOT be done.

SQLite - How to use VARIABLE for TABLE NAME + VARIABLE FOR INSERT VALUES

The following line substitutes {} with the contents of the variable table and stores the result in the variable query. format works with any string:

query = 'SELECT * FROM {}'.format(table) 

On the other hand the kind of substitution done by c.execute replaces the ? in the string with the values in the list parameters:

c.execute("INSERT INTO TABLE_NAME VALUES(NULL, ?,?)", parameters)

You could combine them both to achieve the effect you want:

table = input("with what table do you want to work with? ")
query = 'INSERT INTO {} VALUES(NULL, ?, ?)'.format(table)
parameters = [order, theme]
c.execute(query, parameters)

Is it possible to parameterize table and column names in SQLite queries?

Generally things like column names (or table names) can not be parameterised - and the fact that there are different indices means that it will have to be a different plan internally. So you will have to use concatenation - but be careful to white-list the known column names to prevent sql injection:

    SQLiteCommand cmd = new SQLiteCommand(@"
    SELECT [ID],[email],[serializedata],[restrictions]
    FROM " + whiteListedUserTable + @"
    WHERE [" + whiteListedColumnName + @"] = @searchparam", SQLConnection);

    cmd.Parameters.Add(new SQLiteParameter("@searchparam"));
    ...
    Command.Parameters["@searchparam"].Value = searchdata;

SQLite Parameters - Not allowing tablename as parameter

Generally one cannot use SQL parameters/placeholders for database identifiers (tables, columns, views, schemas, etc.) or database functions (e.g., CURRENT_DATE), but instead only for binding literal values.

With server-side support for parameterized (a.k.a. prepared) statements, the DB engine parses your query once, remembering out the peculiars of any parameters -- their types, max lengths, precisions, etc. -- that you will bind in subsequent executions of the already-parsed query. But the query cannot be properly parsed into its syntactic elements if critical bits, like database objects, are unknown.

So, one generally has to substitute table names oneself, in a stored procedure or in client code which dynamically concats/interpolates/whatevers the SQL statement to be properly executed. In any case, please remember to use your SQL API's function for quoting database identifiers, since the API won't do it for you.

---

Related Topics