summaryrefslogtreecommitdiff
path: root/topics
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2022-08-17 14:29:12 +0300
committerFrederick Muriuki Muriithi2022-08-17 14:36:34 +0300
commit93eea6803c4206a1cdc7956413df746de60583ee (patch)
treefbc477e2b5b652b9e695a5b35f1392242b751f90 /topics
parente44c5aab8a931c46bf8ca20f8c5ab793cf67fe12 (diff)
downloadgn-gemtext-93eea6803c4206a1cdc7956413df746de60583ee.tar.gz
Topic: Queries (On preventing SQL Injection).
Diffstat (limited to 'topics')
-rw-r--r--topics/queries-and-prepared-statements-in-python.gmi87
1 files changed, 87 insertions, 0 deletions
diff --git a/topics/queries-and-prepared-statements-in-python.gmi b/topics/queries-and-prepared-statements-in-python.gmi
new file mode 100644
index 0000000..ca6510e
--- /dev/null
+++ b/topics/queries-and-prepared-statements-in-python.gmi
@@ -0,0 +1,87 @@
+# Queries and Prepared Statements in Python
+
+String interpolation when writing queries is a really bad idea; it leads to exposure to SQL Injection attacks. To mitigate against this, we need to write queries using placeholders for values, then passing in the values as arguments to the **execute** function.
+
+As a demonstration, using some existing code, do not write a query like this:
+
+> curr.execute(
+> """
+> SELECT Strain.Name, Strain.Id FROM Strain, Species
+> WHERE Strain.Name IN {}
+> and Strain.SpeciesId=Species.Id
+> and Species.name = '{}'
+> """.format(
+> create_in_clause(list(sample_data.keys())),
+> *mescape(dataset.group.species)))
+
+In the query above, we interpolate the values of the 'sample_data.keys()' values and that of the 'dataset.group.species' values.
+
+The code above can be rewritten to something like:
+
+> sample_data_keys = tuple(key for key in sample_data.keys())
+>
+> curr.execute(
+> """
+> SELECT Strain.Name, Strain.Id FROM Strain, Species
+> WHERE Strain.Name IN ({})
+> and Strain.SpeciesId=Species.Id
+> and Species.name = %s
+> """.format(", ".join(sample_data_keys)),
+> (sample_data_keys + (dataset.group.species,)))
+
+In this new query, the IN clause ends up being a string of the form
+
+> %s, %s, %s, ...
+
+for the total number of items in the 'sample_data_key' tuple.
+
+There is one more '%s' placeholder for the 'Species.name' value, so, the final tuple we provide as an argument to execute needs to add the 'dataset.group.species' value.
+
+**IMPORTANT 01**: the total number of placeholders (%s) must be the same as the total number of arguments passed into the 'execute' function.
+
+**IMPORTANT 02**: the order of the values must correspond to the order of the placeholders.
+
+### Aside
+
+The functions 'create_in_clause' and 'mescape' are defined as below:
+
+> from MySQLdb import escape_string as escape_
+>
+> def create_in_clause(items):
+> """Create an in clause for mysql"""
+> in_clause = ', '.join("'{}'".format(x) for x in mescape(*items))
+> in_clause = '( {} )'.format(in_clause)
+> return in_clause
+>
+> def mescape(*items):
+> """Multiple escape"""
+> return [escape_(str(item)).decode('utf8') for item in items]
+>
+> def escape(string_):
+> return escape_(string_).decode('utf8')
+
+
+## Parameter Style
+
+In the section above, we show the most common parameter style used in most cases.
+
+If you want to use a mapping object (dict), you have the option of using the '%(<text>)s' format for the query. In that case, we could rewrite the query above into something like:
+
+> sample_data_dict = {f"sample_{idx}: key for idx,key in enumerate(sample_data.keys())}
+>
+> curr.execute(
+> """
+> SELECT Strain.Name, Strain.Id FROM Strain, Species
+> WHERE Strain.Name IN ({})
+> and Strain.SpeciesId=Species.Id
+> and Species.name = %(species_name)s
+> """.format(", ".join([f"%({key})s" for key in sample_data_dict.keys()])),
+> {**sample_data_dict, "species_name": dataset.group.species})
+
+## Final Note
+
+While this has dealt mostly with the MySQLdb driver for Python3, the idea is the same for the psycopg2 (PostgreSQL) driver and others (with some minor variation in the details).
+
+The concept is also similar in many other languages.
+
+The main takeaway is that you really should not be manually escaping the values - instead, you should let the driver do that for you, by providing placeholders in the query, and the values to use separately.