How to Safely Create Tables with Dynamic Names in SQLite?

Variable Table Name in SQLite

In SQLite, you may encounter a situation where you need to dynamically create a table with a name stored in a variable. While it's common to use string constructors for this purpose, security concerns arise due to the potential for SQL injection attacks.

Unfortunately, SQLite does not allow parameter substitution for table names. However, best practices dictate the use of a parameter substitution to safeguard against injection attacks.

Alternative Solution: Scrubbing the Variable

Since table names cannot be parameterized directly, a viable solution is to scrub the variable containing the table name before passing it to the CREATE TABLE statement.

You can create a function that cleans the variable by removing all characters except alphanumeric ones. For example, the following Python function strips out punctuation, whitespace, and other non-alphanumeric characters:

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

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

Using this function, you can create the table with a variable table name, ensuring that malicious characters are removed, preventing injection attacks:

table_name = 'StarFrame' + self.name
cursor.execute('CREATE TABLE {} (etc etc)'.format(scrub(table_name)))

The above is the detailed content of How to Safely Create Tables with Dynamic Names in SQLite?. For more information, please follow other related articles on the PHP Chinese website!