CODEWITHSUNDEEP
pythonsqldatabasesqlite
Dan phils
Dan phils

Reputation: 11

Safe variable string for variable sqlite column (python)

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 -

  1. Would the above example be safe if I could guarantee that the ? only contains real letters or real numbers from 0-9?

  2. 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?

Upvotes: 0

Views: 56

Answers (2)

CL.
CL.

Reputation: 180080

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)), ...)

Upvotes: 1

sudonym
sudonym

Reputation: 4018

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

Upvotes: 0

Related Questions