Can an arbitary column name be used in sqlite3 db?

Question

I am trying to use ? to allow me to set arbitrary column names.

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute("create table mytab (? text, ? real)", ('v1', 'v2'))

But I got the following error. So it is not possible to use ? when creating tables?

Traceback (most recent call last):
  File "/tmp/main1.py", line 10, in <module>
    c.execute("create table mytab (? text, ? real)", ('v1', 'v2'))
sqlite3.OperationalError: near "?": syntax error

Possible duplicate of [Variable table name in sqlite](https://stackoverflow.com/questions/3247183/variable-table-name-in-sqlite) (table/column names are equivalent in relation to the question) — Alex K., Mar 22 '19 at 17:35

score 0 · Answer 1 · answered Mar 22 '19 at 17:38

0

try something like this instead?

"create table mytab ({} text, {} real)".format('v1', 'v2')

answered Mar 22 '19 at 17:38

Aiden Zhao

633
4
15

1

Hi! while this answer may solve OP's problem, it is discouraged to post code only answers on SO. Providing some explanation will help OP understand this solution better, while also contributing to SO's mission of providing quality solutions to future users of the site. Thanks! – d_kennetz Mar 22 '19 at 17:58
Does this allow arbitrary column names? – user1424739 Mar 22 '19 at 18:18
what do you mean by arbitrary column names? – Aiden Zhao Mar 22 '19 at 18:56
For example, a column name that contains any ASCII code (such as ESC). – user1424739 Mar 22 '19 at 20:38
I have no idea, but I found this page, and could be useful for you. check user35443's answer: https://stackoverflow.com/questions/3373234/what-sqlite-column-name-can-be-cannot-be – Aiden Zhao Mar 25 '19 at 21:10

score 0 · Answer 2 · answered Mar 22 '19 at 17:51

The SQL parameter substitution is the solution SQL injection. It tries to separate the functional components (e.g. the keyword SELECT, the statement separator ;, etc) and data components (e.g. the number, strings, etc that are data that totally not related to the SQL function)

The point of parameter is to request to properly escape the input data if needed before passing on to the SQL engine, so data stays data, and functional components are functional components.

So you can do:

SELECT x FROM table_y WHERE z = ?

and pass on '""; DROP TABLE table_y' as parameter, and that is exactly a string to match. But you cannot do:

SELECT x FROM table_y WHERE ? = '""; DROP TABLE table_y'

and pass on 'z' as parameter and expect it to mean the same. Because when you pass in 'z', it is interpreted as string, not column name. Having this idea, you will see that the SQL on your example will only evolve into SQL syntax error.

Can an arbitary column name be used in sqlite3 db?

2 Answers2