-1

I am getting the following error when using sqlite3

OperationalError: near "TABLE": syntax error

The error occurs on this line:

c.execute('INSERT TABLE IF NOT EXISTS ' + bracketName + ' (player_1 TEXT, player_2 TEXT, winner TEXT, loser TEXT, player_1_score INTEGER, player_2_score INTEGER, round TEXT)')

Searching for this error suggests that the problem is caused when "table" is used as a name for a table, despite being a reserved word. This is not the case in my situation, as I'm naming the table whatever is stored in the variable "bracket."

I'm not sure how to add more code to make this a reproducible example, so I'm hoping the problem is obvious from syntax

Parseltongue
  • 11,157
  • 30
  • 95
  • 160
  • 2
    You shouldn't concatenate strings, use `?` for parameter substitution: `c.execute("CREATE TABLE IF NOT EXISTS ? (player_1 TEXT, player_2 TEXT, winner TEXT, loser TEXT, player_1_score INTEGER, player_2_score INTEGER, round TEXT)", [bracketName])`. Are you sure it's `INSERT` and not `CREATE`? – Andrej Kesely Jun 26 '19 at 17:10
  • 1
    Andrej I think got it right with `CREATE` keyword. But it is still critical for creating a *"reproducible example"* that you also include the value of the variable `bracketName`. It is not sufficient to simply say "...whatever is stored in the variable". That could be anything! It could be an invalid identifier, for example. – C Perkins Jun 26 '19 at 17:47
  • Thanks so much for your help! I am still getting errors. For example, when running: `c.execute('CREATE TABLE IF NOT EXISTS ? (id INTEGER, tag TEXT, placement INTEGER, unique(id, tag, placement)), [attend]')`. Where the value of attend is equal to `smash_at_the_paramount_attendees` The new error is now: `OperationalError: near "?": syntax error` – Parseltongue Jun 26 '19 at 17:53
  • @AndrejKesely Normally that's what you want to do, but table and column names have to be known when the statement is compiled and thus can't use parameters. – Shawn Jun 26 '19 at 19:45

1 Answers1

2

As the comments mentioned, the command to create a new table is CREATE TABLE. INSERT is used to create new rows in an existing table. However, as far as I've been able to tell (and as a comment on your question mentions), you cannot use parameter substitution for table names. Therefore, this will work:

c.execute('CREATE TABLE IF NOT EXISTS ' + bracketName + ' (player_1 TEXT, player_2 TEXT, winner TEXT, loser TEXT, player_1_score INTEGER, player_2_score INTEGER, round TEXT)')

However, as has been pointed out, this is not very secure. Drawing from This Answer: if you are worried about injection, try writing a function that cleans the string before passing it. That answer gives an example of a "cleaner" that will only pass alphanumeric characters to avoid injection attacks.

Theo
  • 613
  • 4
  • 22