[Tutor] SQLite, Python and SQL injection attacks

[Cameron Simpson]

On 14Aug2015 13:40, boB Stepp <robertvstepp at gmail.com> wrote:
>I was just looking at the sqlite3 docs at
>https://docs.python.org/3/library/sqlite3.html?highlight=sqlite#module-sqlite3
>and found the following cheery news:
>
>"Usually your SQL operations will need to use values from Python
>variables. You shouldn’t assemble your query using Python’s string
>operations because doing so is insecure; it makes your program
>vulnerable to an SQL injection attack ..."
>
>There followed this recommendation:
>
>"Instead, use the DB-API’s parameter substitution. Put ? as a
>placeholder wherever you want to use a value, and then provide a tuple
>of values as the second argument to the cursor’s execute() method..."
>
>I have to be honest -- I would have fallen into this potential trap if
>I had not read this.  It is not clear to me yet how the recommendation
>avoids this issue.  Does the placeholder enforce some sort of type
>checking so that arbitrary SQL strings will be rejected?

Well, better to say that it transcribes the values correctly, possibly with 
some type checking. You run the same risk constructing shell command lines too, 
which is why "shell=True" is generally discourages with subprocess.Popen.

So if you have:

  SELECT FROM tablename WHERE columnvalue = ?

and you have it a python string like "foo;bah", the SQL API will take care of 
quoting the string so that the ";" is inside the quotes. Likewise if the string 
contains SQL end of string markers (quotes). And if the value cannot be 
transcribed the API should raise an exception.

IN this way you know that the structure of the query has been preserved 
correctly. _And_ you do not need to worry about quoting values (or otherwise 
transcribing them) correctly; that is a solved and debugged problem.

You code is simpler and robust.

Cheers,
Cameron Simpson <cs at zip.com.au>

The Fano Factor, where theory meets reality.