Safe variable string for variable sqlite column (python)

Question

I have two questions.

I understand that something like c.execute("INSERT INTO ? VALUES ...") is a big no no, since the ? is reserved for queries, not column names that could include anything from foreign or corrupt characters, injection to anything else. So here are my questions -

Would the above example be safe if I could guarantee that the ? only contains real letters or real numbers from 0-9?
if the answer to 1 is yes, then can I do this somehow by taking any user-given string, and rejecting it if it contains anything besides alphanumeric characters (0-9, a-z)? How would I do that?

eg:

str="some potentially corrupt string from the user"

If (not_alphanumeric(str)):
   pass
else: 
   c.execute("INSERT INTO ? VALUES ...", (str,))

So in essence, if the answer to 1 is "yes", then how would I code not_alphanumeric for the conditionl test?

CL. · Accepted Answer · 2018-06-15 21:25:45Z

1

You cannot use parameters for table/column names, regardless of what their contents are.

The safest way to insert a user-specified table name is to check that it is in a list of known valid names:

valid_tables = ["ThisTable", "ThatTable"]
if str not in valid_tables:
    raise Exception("invalid table name")

If you do not have such a known list, correctly quote the table name as an identifer; this only requires doubling any quote character inside:

def sql_quote_identifier(x):
    return '"' + x.replace('"', '""') + '"'

c.execute("INSERT INTO {} VALUES ...".format(sql_quote_identifier(str)), ...)

answered Jun 15, 2018 at 21:25

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sudonym · Accepted Answer · 2018-06-15 04:34:08Z

0

Can't answer 1 but can answer 2.

To test if a sting is alphanumeric directly, do this:

if not str.isalnum(your_string):
       # your c.execute command 
else:  # not necessary
    pass

edited Jun 15, 2018 at 4:34

answered Jun 15, 2018 at 4:12

sudonym

4,0384 gold badges40 silver badges63 bronze badges

1 Comment

Dan phils Over a year ago

That's what I was after, thanks! Now just to find out about Q1.

Collectives™ on Stack Overflow

Safe variable string for variable sqlite column (python)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related