[Tutor] sqlite3 module

[Dennis Lee Bieber]

On Sun, 16 Apr 2023 17:27:28 -0700, Alex Kleider <alexkleider at gmail.com>
declaimed the following:


>    f_values = ", ".join([repr(value) for value in data.values()])

	NO... You want the API placeholder -- which for Python's sqlite3 module
is simple a ? for each value. There may also be the matter that
data.values() may not be in the same order as data.keys() (current Python
does claim that the keys will be returned in the same order if .keys() is
called multiple times, but I haven't seen if values() uses that order)

>    query = ("INSERT INTO Receipts ({}) VALUES ({});"
>                    .format(f_keys, f_values))

	You DO NOT want to stuff the actual data values into the query in this
manner -- it opens you up to injection attacks

https://xkcd.com/327/

	It is the responsibility of the API module to sanitize and escape data
values in accordance with the requirements of the actual database engine.
(for many years, the MySQL API interface used %s as the placeholder, and
used % formatting to fill a copy of the query with values -- BUT every
value was first passed through a sanitizing function which was responsible
for converting numeric data to string, escaping embedded quotes, etc.)

>    print(query)
>     cursor.execute(query, data)

	Without placeholders, passing "data" does nothing.


	#relying upon .keys() returning the same order on each call
	#list-comprehension may not be needed for the first
	f_keys = ", ".join(list(data.keys()))
	f_values = [data[key] for key in data.keys()]
	placeholder = ", ".join(["?" for _ in data.keys()])

	query = ("INSERT INTO Receipts ({}) VALUES ({});"
                    .format(f_keys, placeholder))

	cursor.execute(query, f_values)