[aur-dev] [PATCH 1/8] git-interface: Add database abstraction layer

5 Aug 2016

Add a new class that connects to the database specified in the
configuration file and provides an interface to execute SQL queries.
Prepared statements with qmark ("?") placeholders are supported.

Replace all direct database accesses with calls to the new abstraction
layer.

Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
---
 git-interface/db.py         |  38 ++++++++++++
 git-interface/git-auth.py   |  22 +++----
 git-interface/git-serve.py  |  92 ++++++++++++----------------
 git-interface/git-update.py | 144 +++++++++++++++++++++-----------------------
 4 files changed, 150 insertions(+), 146 deletions(-)
 create mode 100644 git-interface/db.py

diff --git a/git-interface/db.py b/git-interface/db.py
new file mode 100644
index 0000000..d3e1e69
--- /dev/null
+++ b/git-interface/db.py
@@ -0,0 +1,38 @@
+import configparser
+import mysql.connector
+import os
+
+
+class Connection:
+    _conn = None
+
+    def __init__(self):
+        config = configparser.RawConfigParser()
+        config.read(os.path.dirname(os.path.realpath(__file__)) + "/../conf/config")
+
+        aur_db_host = config.get('database', 'host')
+        aur_db_name = config.get('database', 'name')
+        aur_db_user = config.get('database', 'user')
+        aur_db_pass = config.get('database', 'password')
+        aur_db_socket = config.get('database', 'socket')
+
+        self._conn = mysql.connector.connect(host=aur_db_host,
+                                             user=aur_db_user,
+                                             passwd=aur_db_pass,
+                                             db=aur_db_name,
+                                             unix_socket=aur_db_socket,
+                                             buffered=True)
+
+    def execute(self, query, params=()):
+        query = query.replace('%', '%%').replace('?', '%s')
+
+        cur = self._conn.cursor()
+        cur.execute(query, params)
+
+        return cur
+
+    def commit(self):
+        self._conn.commit()
+
+    def close(self):
+        self._conn.close()
diff --git a/git-interface/git-auth.py b/git-interface/git-auth.py
index 83bd20c..7cd033c 100755
--- a/git-interface/git-auth.py
+++ b/git-interface/git-auth.py
@@ -1,12 +1,13 @@
 #!/usr/bin/python3
 
 import configparser
-import mysql.connector
 import shlex
 import os
 import re
 import sys
 
+import db
+
 
 def format_command(env_vars, command, ssh_opts, ssh_key):
     environment = ''
@@ -26,12 +27,6 @@ def format_command(env_vars, command, ssh_opts, ssh_key):
 config = configparser.RawConfigParser()
 config.read(os.path.dirname(os.path.realpath(__file__)) + "/../conf/config")
 
-aur_db_host = config.get('database', 'host')
-aur_db_name = config.get('database', 'name')
-aur_db_user = config.get('database', 'user')
-aur_db_pass = config.get('database', 'password')
-aur_db_socket = config.get('database', 'socket')
-
 valid_keytypes = config.get('auth', 'valid-keytypes').split()
 username_regex = config.get('auth', 'username-regex')
 git_serve_cmd = config.get('auth', 'git-serve-cmd')
@@ -42,15 +37,12 @@ keytext = sys.argv[2]
 if keytype not in valid_keytypes:
     exit(1)
 
-db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                             passwd=aur_db_pass, db=aur_db_name,
-                             unix_socket=aur_db_socket, buffered=True)
+conn = db.Connection()
 
-cur = db.cursor()
-cur.execute("SELECT Users.Username, Users.AccountTypeID FROM Users " +
-            "INNER JOIN SSHPubKeys ON SSHPubKeys.UserID = Users.ID "
-            "WHERE SSHPubKeys.PubKey = %s AND Users.Suspended = 0",
-            (keytype + " " + keytext,))
+cur = conn.execute("SELECT Users.Username, Users.AccountTypeID FROM Users " +
+                   "INNER JOIN SSHPubKeys ON SSHPubKeys.UserID = Users.ID "
+                   "WHERE SSHPubKeys.PubKey = ? AND Users.Suspended = 0",
+                   (keytype + " " + keytext,))
 
 if cur.rowcount != 1:
     exit(1)
diff --git a/git-interface/git-serve.py b/git-interface/git-serve.py
index e0ebd0e..ab612f0 100755
--- a/git-interface/git-serve.py
+++ b/git-interface/git-serve.py
@@ -1,21 +1,16 @@
 #!/usr/bin/python3
 
 import configparser
-import mysql.connector
 import os
 import re
 import shlex
 import sys
 
+import db
+
 config = configparser.RawConfigParser()
 config.read(os.path.dirname(os.path.realpath(__file__)) + "/../conf/config")
 
-aur_db_host = config.get('database', 'host')
-aur_db_name = config.get('database', 'name')
-aur_db_user = config.get('database', 'user')
-aur_db_pass = config.get('database', 'password')
-aur_db_socket = config.get('database', 'socket')
-
 repo_path = config.get('serve', 'repo-path')
 repo_regex = config.get('serve', 'repo-regex')
 git_shell_cmd = config.get('serve', 'git-shell-cmd')
@@ -27,12 +22,8 @@ maintenance_exc = config.get('options', 'maintenance-exceptions').split()
 
 
 def pkgbase_from_name(pkgbase):
-    db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                                 passwd=aur_db_pass, db=aur_db_name,
-                                 unix_socket=aur_db_socket)
-    cur = db.cursor()
-    cur.execute("SELECT ID FROM PackageBases WHERE Name = %s", [pkgbase])
-    db.close()
+    conn = db.Connection()
+    cur = conn.execute("SELECT ID FROM PackageBases WHERE Name = ?", [pkgbase])
 
     row = cur.fetchone()
     return row[0] if row else None
@@ -43,21 +34,18 @@ def pkgbase_exists(pkgbase):
 
 
 def list_repos(user):
-    db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                                 passwd=aur_db_pass, db=aur_db_name,
-                                 unix_socket=aur_db_socket)
-    cur = db.cursor()
+    conn = db.Connection()
 
-    cur.execute("SELECT ID FROM Users WHERE Username = %s ", [user])
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
     userid = cur.fetchone()[0]
     if userid == 0:
         die('{:s}: unknown user: {:s}'.format(action, user))
 
-    cur.execute("SELECT Name, PackagerUID FROM PackageBases " +
-                "WHERE MaintainerUID = %s ", [userid])
+    cur = conn.execute("SELECT Name, PackagerUID FROM PackageBases " +
+                       "WHERE MaintainerUID = ?", [userid])
     for row in cur:
         print((' ' if row[1] else '*') + row[0])
-    db.close()
+    conn.close()
 
 
 def create_pkgbase(pkgbase, user):
@@ -66,26 +54,25 @@ def create_pkgbase(pkgbase, user):
     if pkgbase_exists(pkgbase):
         die('{:s}: package base already exists: {:s}'.format(action, pkgbase))
 
-    db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                                 passwd=aur_db_pass, db=aur_db_name,
-                                 unix_socket=aur_db_socket)
-    cur = db.cursor()
+    conn = db.Connection()
 
-    cur.execute("SELECT ID FROM Users WHERE Username = %s ", [user])
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
     userid = cur.fetchone()[0]
     if userid == 0:
         die('{:s}: unknown user: {:s}'.format(action, user))
 
-    cur.execute("INSERT INTO PackageBases (Name, SubmittedTS, ModifiedTS, " +
-                "SubmitterUID, MaintainerUID) VALUES (%s, UNIX_TIMESTAMP(), " +
-                "UNIX_TIMESTAMP(), %s, %s)", [pkgbase, userid, userid])
+    cur = conn.execute("INSERT INTO PackageBases (Name, SubmittedTS, " +
+                       "ModifiedTS, SubmitterUID, MaintainerUID) VALUES " +
+                       "(?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), ?, ?)",
+                       [pkgbase, userid, userid])
     pkgbase_id = cur.lastrowid
 
-    cur.execute("INSERT INTO PackageNotifications (PackageBaseID, UserID) " +
-                "VALUES (%s, %s)", [pkgbase_id, userid])
+    cur = conn.execute("INSERT INTO PackageNotifications " +
+                       "(PackageBaseID, UserID) VALUES (?, ?)",
+                       [pkgbase_id, userid])
 
-    db.commit()
-    db.close()
+    conn.commit()
+    conn.close()
 
 
 def pkgbase_set_keywords(pkgbase, keywords):
@@ -93,34 +80,29 @@ def pkgbase_set_keywords(pkgbase, keywords):
     if not pkgbase_id:
         die('{:s}: package base not found: {:s}'.format(action, pkgbase))
 
-    db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                                 passwd=aur_db_pass, db=aur_db_name,
-                                 unix_socket=aur_db_socket)
-    cur = db.cursor()
+    conn = db.Connection()
 
-    cur.execute("DELETE FROM PackageKeywords WHERE PackageBaseID = %s",
-                [pkgbase_id])
+    conn.execute("DELETE FROM PackageKeywords WHERE PackageBaseID = ?",
+                 [pkgbase_id])
     for keyword in keywords:
-        cur.execute("INSERT INTO PackageKeywords (PackageBaseID, Keyword) "
-                    "VALUES (%s, %s)", [pkgbase_id, keyword])
+        conn.execute("INSERT INTO PackageKeywords (PackageBaseID, Keyword) " +
+                     "VALUES (?, ?)", [pkgbase_id, keyword])
 
-    db.commit()
-    db.close()
+    conn.commit()
+    conn.close()
 
 
 def pkgbase_has_write_access(pkgbase, user):
-    db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                                 passwd=aur_db_pass, db=aur_db_name,
-                                 unix_socket=aur_db_socket, buffered=True)
-    cur = db.cursor()
-
-    cur.execute("SELECT COUNT(*) FROM PackageBases " +
-                "LEFT JOIN PackageComaintainers " +
-                "ON PackageComaintainers.PackageBaseID = PackageBases.ID " +
-                "INNER JOIN Users ON Users.ID = PackageBases.MaintainerUID " +
-                "OR PackageBases.MaintainerUID IS NULL " +
-                "OR Users.ID = PackageComaintainers.UsersID " +
-                "WHERE Name = %s AND Username = %s", [pkgbase, user])
+    conn = db.Connection()
+
+    cur = conn.execute("SELECT COUNT(*) FROM PackageBases " +
+                       "LEFT JOIN PackageComaintainers " +
+                       "ON PackageComaintainers.PackageBaseID = PackageBases.ID " +
+                       "INNER JOIN Users " +
+                       "ON Users.ID = PackageBases.MaintainerUID " +
+                       "OR PackageBases.MaintainerUID IS NULL " +
+                       "OR Users.ID = PackageComaintainers.UsersID " +
+                       "WHERE Name = ? AND Username = ?", [pkgbase, user])
     return cur.fetchone()[0] > 0
 
 
diff --git a/git-interface/git-update.py b/git-interface/git-update.py
index 7aace9b..b7199e6 100755
--- a/git-interface/git-update.py
+++ b/git-interface/git-update.py
@@ -1,7 +1,6 @@
 #!/usr/bin/python3
 
 import configparser
-import mysql.connector
 import os
 import pygit2
 import re
@@ -11,15 +10,11 @@ import sys
 import srcinfo.parse
 import srcinfo.utils
 
+import db
+
 config = configparser.RawConfigParser()
 config.read(os.path.dirname(os.path.realpath(__file__)) + "/../conf/config")
 
-aur_db_host = config.get('database', 'host')
-aur_db_name = config.get('database', 'name')
-aur_db_user = config.get('database', 'user')
-aur_db_pass = config.get('database', 'password')
-aur_db_socket = config.get('database', 'socket')
-
 notify_cmd = config.get('notifications', 'notify-cmd')
 
 repo_path = config.get('serve', 'repo-path')
@@ -65,27 +60,27 @@ def parse_dep(depstring):
         return (depname, depcond)
 
 
-def save_metadata(metadata, db, cur, user):
+def save_metadata(metadata, conn, user):
     # Obtain package base ID and previous maintainer.
     pkgbase = metadata['pkgbase']
-    cur.execute("SELECT ID, MaintainerUID FROM PackageBases "
-                "WHERE Name = %s", [pkgbase])
+    cur = conn.execute("SELECT ID, MaintainerUID FROM PackageBases "
+                       "WHERE Name = ?", [pkgbase])
     (pkgbase_id, maintainer_uid) = cur.fetchone()
     was_orphan = not maintainer_uid
 
     # Obtain the user ID of the new maintainer.
-    cur.execute("SELECT ID FROM Users WHERE Username = %s", [user])
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
     user_id = int(cur.fetchone()[0])
 
     # Update package base details and delete current packages.
-    cur.execute("UPDATE PackageBases SET ModifiedTS = UNIX_TIMESTAMP(), " +
-                "PackagerUID = %s, OutOfDateTS = NULL WHERE ID = %s",
-                [user_id, pkgbase_id])
-    cur.execute("UPDATE PackageBases SET MaintainerUID = %s " +
-                "WHERE ID = %s AND MaintainerUID IS NULL",
-                [user_id, pkgbase_id])
-    cur.execute("DELETE FROM Packages WHERE PackageBaseID = %s",
-                [pkgbase_id])
+    conn.execute("UPDATE PackageBases SET ModifiedTS = UNIX_TIMESTAMP(), " +
+                 "PackagerUID = ?, OutOfDateTS = NULL WHERE ID = ?",
+                 [user_id, pkgbase_id])
+    conn.execute("UPDATE PackageBases SET MaintainerUID = ? " +
+                 "WHERE ID = ? AND MaintainerUID IS NULL",
+                 [user_id, pkgbase_id])
+    conn.execute("DELETE FROM Packages WHERE PackageBaseID = ?",
+                 [pkgbase_id])
 
     for pkgname in srcinfo.utils.get_package_names(metadata):
         pkginfo = srcinfo.utils.get_merged_package(pkgname, metadata)
@@ -102,94 +97,94 @@ def save_metadata(metadata, db, cur, user):
                 pkginfo[field] = None
 
         # Create a new package.
-        cur.execute("INSERT INTO Packages (PackageBaseID, Name, " +
-                    "Version, Description, URL) " +
-                    "VALUES (%s, %s, %s, %s, %s)",
-                    [pkgbase_id, pkginfo['pkgname'], ver,
-                     pkginfo['pkgdesc'], pkginfo['url']])
-        db.commit()
+        cur = conn.execute("INSERT INTO Packages (PackageBaseID, Name, " +
+                           "Version, Description, URL) " +
+                           "VALUES (?, ?, ?, ?, ?)",
+                           [pkgbase_id, pkginfo['pkgname'], ver,
+                            pkginfo['pkgdesc'], pkginfo['url']])
+        conn.commit()
         pkgid = cur.lastrowid
 
         # Add package sources.
         for source_info in extract_arch_fields(pkginfo, 'source'):
-            cur.execute("INSERT INTO PackageSources (PackageID, Source, " +
-                        "SourceArch) VALUES (%s, %s, %s)",
-                        [pkgid, source_info['value'], source_info['arch']])
+            conn.execute("INSERT INTO PackageSources (PackageID, Source, " +
+                         "SourceArch) VALUES (?, ?, ?)",
+                         [pkgid, source_info['value'], source_info['arch']])
 
         # Add package dependencies.
         for deptype in ('depends', 'makedepends',
                         'checkdepends', 'optdepends'):
-            cur.execute("SELECT ID FROM DependencyTypes WHERE Name = %s",
-                        [deptype])
+            cur = conn.execute("SELECT ID FROM DependencyTypes WHERE Name = ?",
+                               [deptype])
             deptypeid = cur.fetchone()[0]
             for dep_info in extract_arch_fields(pkginfo, deptype):
                 depname, depcond = parse_dep(dep_info['value'])
                 deparch = dep_info['arch']
-                cur.execute("INSERT INTO PackageDepends (PackageID, " +
-                            "DepTypeID, DepName, DepCondition, DepArch) " +
-                            "VALUES (%s, %s, %s, %s, %s)",
-                            [pkgid, deptypeid, depname, depcond, deparch])
+                conn.execute("INSERT INTO PackageDepends (PackageID, " +
+                             "DepTypeID, DepName, DepCondition, DepArch) " +
+                             "VALUES (?, ?, ?, ?, ?)",
+                             [pkgid, deptypeid, depname, depcond, deparch])
 
         # Add package relations (conflicts, provides, replaces).
         for reltype in ('conflicts', 'provides', 'replaces'):
-            cur.execute("SELECT ID FROM RelationTypes WHERE Name = %s",
-                        [reltype])
+            cur = conn.execute("SELECT ID FROM RelationTypes WHERE Name = ?",
+                               [reltype])
             reltypeid = cur.fetchone()[0]
             for rel_info in extract_arch_fields(pkginfo, reltype):
                 relname, relcond = parse_dep(rel_info['value'])
                 relarch = rel_info['arch']
-                cur.execute("INSERT INTO PackageRelations (PackageID, " +
-                            "RelTypeID, RelName, RelCondition, RelArch) " +
-                            "VALUES (%s, %s, %s, %s, %s)",
-                            [pkgid, reltypeid, relname, relcond, relarch])
+                conn.execute("INSERT INTO PackageRelations (PackageID, " +
+                             "RelTypeID, RelName, RelCondition, RelArch) " +
+                             "VALUES (?, ?, ?, ?, ?)",
+                             [pkgid, reltypeid, relname, relcond, relarch])
 
         # Add package licenses.
         if 'license' in pkginfo:
             for license in pkginfo['license']:
-                cur.execute("SELECT ID FROM Licenses WHERE Name = %s",
-                            [license])
+                cur = conn.execute("SELECT ID FROM Licenses WHERE Name = ?",
+                                   [license])
                 if cur.rowcount == 1:
                     licenseid = cur.fetchone()[0]
                 else:
-                    cur.execute("INSERT INTO Licenses (Name) VALUES (%s)",
-                                [license])
-                    db.commit()
+                    cur = conn.execute("INSERT INTO Licenses (Name) " +
+                                       "VALUES (?)", [license])
+                    conn.commit()
                     licenseid = cur.lastrowid
-                cur.execute("INSERT INTO PackageLicenses (PackageID, " +
-                            "LicenseID) VALUES (%s, %s)",
-                            [pkgid, licenseid])
+                conn.execute("INSERT INTO PackageLicenses (PackageID, " +
+                             "LicenseID) VALUES (?, ?)",
+                             [pkgid, licenseid])
 
         # Add package groups.
         if 'groups' in pkginfo:
             for group in pkginfo['groups']:
-                cur.execute("SELECT ID FROM Groups WHERE Name = %s",
-                            [group])
+                cur = conn.execute("SELECT ID FROM Groups WHERE Name = ?",
+                                   [group])
                 if cur.rowcount == 1:
                     groupid = cur.fetchone()[0]
                 else:
-                    cur.execute("INSERT INTO Groups (Name) VALUES (%s)",
-                                [group])
-                    db.commit()
+                    cur = conn.execute("INSERT INTO Groups (Name) VALUES (?)",
+                                       [group])
+                    conn.commit()
                     groupid = cur.lastrowid
-                cur.execute("INSERT INTO PackageGroups (PackageID, "
-                            "GroupID) VALUES (%s, %s)", [pkgid, groupid])
+                conn.execute("INSERT INTO PackageGroups (PackageID, "
+                             "GroupID) VALUES (?, ?)", [pkgid, groupid])
 
     # Add user to notification list on adoption.
     if was_orphan:
-        cur.execute("SELECT COUNT(*) FROM PackageNotifications WHERE " +
-                    "PackageBaseID = %s AND UserID = %s",
-                    [pkgbase_id, user_id])
+        cur = conn.execute("SELECT COUNT(*) FROM PackageNotifications WHERE " +
+                           "PackageBaseID = ? AND UserID = ?",
+                           [pkgbase_id, user_id])
         if cur.fetchone()[0] == 0:
-            cur.execute("INSERT INTO PackageNotifications " +
-                        "(PackageBaseID, UserID) VALUES (%s, %s)",
-                        [pkgbase_id, user_id])
+            conn.execute("INSERT INTO PackageNotifications " +
+                         "(PackageBaseID, UserID) VALUES (?, ?)",
+                         [pkgbase_id, user_id])
 
-    db.commit()
+    conn.commit()
 
 
-def update_notify(db, cur, user, pkgbase_id):
+def update_notify(conn, user, pkgbase_id):
     # Obtain the user ID of the new maintainer.
-    cur.execute("SELECT ID FROM Users WHERE Username = %s", [user])
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
     user_id = int(cur.fetchone()[0])
 
     # Execute the notification script.
@@ -233,10 +228,7 @@ else:
 if refname != "refs/heads/master":
     die("pushing to a branch other than master is restricted")
 
-db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                             passwd=aur_db_pass, db=aur_db_name,
-                             unix_socket=aur_db_socket, buffered=True)
-cur = db.cursor()
+conn = db.Connection()
 
 # Detect and deny non-fast-forwards.
 if sha1_old != "0000000000000000000000000000000000000000" and not privileged:
@@ -339,13 +331,13 @@ if metadata_pkgbase != pkgbase:
 
 # Ensure that packages are neither blacklisted nor overwritten.
 pkgbase = metadata['pkgbase']
-cur.execute("SELECT ID FROM PackageBases WHERE Name = %s", [pkgbase])
+cur = conn.execute("SELECT ID FROM PackageBases WHERE Name = ?", [pkgbase])
 pkgbase_id = cur.fetchone()[0] if cur.rowcount == 1 else 0
 
-cur.execute("SELECT Name FROM PackageBlacklist")
+cur = conn.execute("SELECT Name FROM PackageBlacklist")
 blacklist = [row[0] for row in cur.fetchall()]
 
-cur.execute("SELECT Name, Repo FROM OfficialProviders")
+cur = conn.execute("SELECT Name, Repo FROM OfficialProviders")
 providers = dict(cur.fetchall())
 
 for pkgname in srcinfo.utils.get_package_names(metadata):
@@ -358,13 +350,13 @@ for pkgname in srcinfo.utils.get_package_names(metadata):
         repo = providers[pkgname]
         warn_or_die('package already provided by [{:s}]: {:s}'.format(repo, pkgname))
 
-    cur.execute("SELECT COUNT(*) FROM Packages WHERE Name = %s AND " +
-                "PackageBaseID <> %s", [pkgname, pkgbase_id])
+    cur = conn.execute("SELECT COUNT(*) FROM Packages WHERE Name = ? AND " +
+                       "PackageBaseID <> ?", [pkgname, pkgbase_id])
     if cur.fetchone()[0] > 0:
         die('cannot overwrite package: {:s}'.format(pkgname))
 
 # Store package base details in the database.
-save_metadata(metadata, db, cur, user)
+save_metadata(metadata, conn, user)
 
 # Create (or update) a branch with the name of the package base for better
 # accessibility.
@@ -377,7 +369,7 @@ repo.create_reference('refs/heads/' + pkgbase, sha1_new, True)
 repo.create_reference('refs/namespaces/' + pkgbase + '/HEAD', sha1_new, True)
 
 # Send package update notifications.
-update_notify(db, cur, user, pkgbase_id)
+update_notify(conn, user, pkgbase_id)
 
 # Close the database.
-db.close()
+conn.close()
-- 
2.9.2

    