[Tutor] SQLite, Python and SQL injection attacks

[boB Stepp]

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?

Having seen this example, are there any other security surprises that
I need to avoid by adopting certain coding techniques when I am using
Python with SQLite?

[Alan Gauld]

Post by boB Stepp
"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..."

This is not a Sqlite issue its true of any database.

Post by boB Stepp
I have to be honest -- I would have fallen into this potential trap

Me too, the first time I used a SQL database.
But it didn't take long before a more enlightened colleague
advised me of my ignorance! :-)

Post by boB Stepp
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?

Yes, it parses the inputs to detect potential issues,
such as rogue semi colons etc.

Post by boB Stepp
Having seen this example, are there any other security surprises that
I need to avoid by adopting certain coding techniques when I am using
Python with SQLite?

As I say, it's not just SQLite, its any database.

And the same is true of handling URLs etc you should always
use library parsing and escaping routines to build them.
Especially when inserting data from users or received
data files.

hth
--
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos


_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
h

[Emile van Sebille]

Post by boB Stepp
I was just looking at the sqlite3 docs at
https://docs.python.org/3/library/sqlite3.html?highlight=sqlite#module-sqlite3
"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 ..."

See http://bobby-tables.com/ for more info.

Emile



_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listin

[Cameron Simpson]

Post by boB Stepp
I was just looking at the sqlite3 docs at
https://docs.python.org/3/library/sqlite3.html?highlight=sqlite#module-sqlite3
"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 ..."
"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 <***@zip.com.au>

The Fano Factor, where theory meets reality.
_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://