11

I'm trying to change the user_version of an SQLite database via Python 2.6, and I've tried to do the following:

cur.execute( "PRAGMA user_version = ?" , (version,) )

It fails with the following error:

    cur.execute( "PRAGMA user_version = ?" , (version,) )
sqlite3.OperationalError: near "?": syntax error

I've tried the named style of substitution (instead of question marks) but that also fails with the same error.

If I drop a number in there as part of the SQL string or using Python's string operations it all works fine, but I'd rather not do either of those.

So why isn't this working?
And how do I safety insert a number from a variable in to this call?

DMA57361
  • 3,600
  • 3
  • 27
  • 36

1 Answers1

6

Only certain values can be parametrized. Even table and column names can not be parametrized. Your experiment shows pragma values also can not be parametrized.

The user_version is expected to be an integer. You can use string formatting and protect yourself at the same time with

cur.execute( "PRAGMA user_version = {v:d}".format(v=version) )

The format {v:d} will raise a ValueError if version is not an integer.

unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • Ok, I thought,that might be the case. So how do I safely insert values? If its only ever internal usage is simple string concat or Python's % acceptable here? – DMA57361 Sep 11 '11 at 13:16
  • 1
    That's a good way of handling the limitation when the parameter is an integer. If the parameter for another pragma was a string instead, there wouldn't be any need to verify the format (e.g., an integer like 7 is valid as "7") but parameters would still need to be escaped to avoid SQL injection. Is there any convenient way of doing that? – spaaarky21 May 15 '14 at 20:10
  • @spaaarky21: I'm not aware of any function provided by sqlite3 to do manual quoting. For other pragmas which accept strings, perhaps the safest way would be to enumerate a whitelist of admissible input and to check the user input against this set. – unutbu May 15 '14 at 20:27