We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
9may


--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `src/cowrie/output/mysql.py`
Content:
```
1 """
2 MySQL output connector. Writes audit logs to MySQL database
3 """
4 
5 
6 import MySQLdb
7 
8 from twisted.enterprise import adbapi
9 from twisted.internet import defer
10 from twisted.python import log
11 
12 import cowrie.core.output
13 from cowrie.core.config import CowrieConfig
14 
15 
16 class ReconnectingConnectionPool(adbapi.ConnectionPool):
17     """
18     Reconnecting adbapi connection pool for MySQL.
19 
20     This class improves on the solution posted at
21     http://www.gelens.org/2008/09/12/reinitializing-twisted-connectionpool/
22     by checking exceptions by error code and only disconnecting the current
23     connection instead of all of them.
24 
25     Also see:
26     http://twistedmatrix.com/pipermail/twisted-python/2009-July/020007.html
27     """
28 
29     def _runInteraction(self, interaction, *args, **kw):
30         try:
31             return adbapi.ConnectionPool._runInteraction(self, interaction, *args, **kw)
32         except (MySQLdb.OperationalError, MySQLdb._exceptions.OperationalError) as e:
33             if e.args[0] not in (2003, 2006, 2013):
34                 raise e
35             log.msg(f"RCP: got error {e}, retrying operation")
36             conn = self.connections.get(self.threadID())
37             self.disconnect(conn)
38             # Try the interaction again
39             return adbapi.ConnectionPool._runInteraction(self, interaction, *args, **kw)
40 
41 
42 class Output(cowrie.core.output.Output):
43     """
44     mysql output
45     """
46 
47     db = None
48     debug: bool = False
49 
50     def start(self):
51         self.debug = CowrieConfig.getboolean("output_mysql", "debug", fallback=False)
52         port = CowrieConfig.getint("output_mysql", "port", fallback=3306)
53         try:
54             self.db = ReconnectingConnectionPool(
55                 "MySQLdb",
56                 host=CowrieConfig.get("output_mysql", "host"),
57                 db=CowrieConfig.get("output_mysql", "database"),
58                 user=CowrieConfig.get("output_mysql", "username"),
59                 passwd=CowrieConfig.get("output_mysql", "password", raw=True),
60                 port=port,
61                 cp_min=1,
62                 cp_max=1,
63                 charset="utf8mb4",
64                 cp_reconnect=True,
65                 use_unicode=True,
66             )
67         except (MySQLdb.Error, MySQLdb._exceptions.Error) as e:
68             log.msg(f"output_mysql: Error {e.args[0]}: {e.args[1]}")
69 
70     def stop(self):
71         self.db.commit()
72         self.db.close()
73 
74     def sqlerror(self, error):
75         """
76         1146, "Table '...' doesn't exist"
77         1406, "Data too long for column '...' at row ..."
78         """
79         if error.value.args[0] in (1146, 1406):
80             log.msg(f"output_mysql: MySQL Error: {error.value.args!r}")
81             log.msg("MySQL schema maybe misconfigured, doublecheck database!")
82         else:
83             log.msg(f"output_mysql: MySQL Error: {error.value.args!r}")
84 
85     def simpleQuery(self, sql, args):
86         """
87         Just run a deferred sql query, only care about errors
88         """
89         if self.debug:
90             log.msg(f"output_mysql: MySQL query: {sql} {args!r}")
91         d = self.db.runQuery(sql, args)
92         d.addErrback(self.sqlerror)
93 
94     @defer.inlineCallbacks
95     def write(self, entry):
96         if entry["eventid"] == "cowrie.session.connect":
97             r = yield self.db.runQuery(
98                 f"SELECT `id`\" \"FROM `sensors`\" \"WHERE `ip` = {self.sensor}"
99             )
100 
101             if r:
102                 sensorid = r[0][0]
103             else:
104                 yield self.db.runQuery(
105                     f"INSERT INTO `sensors` (`ip`) \" \"VALUES ({self.sensor})"
106                 )
107 
108                 r = yield self.db.runQuery("SELECT LAST_INSERT_ID()")
109                 sensorid = int(r[0][0])
110             self.simpleQuery(
111                 "INSERT INTO `sessions` (`id`, `starttime`, `sensor`, `ip`) "
112                 "VALUES (%s, FROM_UNIXTIME(%s), %s, %s)",
113                 (entry["session"], entry["time"], sensorid, entry["src_ip"]),
114             )
115 
116         elif entry["eventid"] == "cowrie.login.success":
117             self.simpleQuery(
118                 "INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) "
119                 "VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))",
120                 (
121                     entry["session"],
122                     1,
123                     entry["username"],
124                     entry["password"],
125                     entry["time"],
126                 ),
127             )
128 
129         elif entry["eventid"] == "cowrie.login.failed":
130             self.simpleQuery(
131                 "INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) "
132                 "VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))",
133                 (
134                     entry["session"],
135                     0,
136                     entry["username"],
137                     entry["password"],
138                     entry["time"],
139                 ),
140             )
141 
142         elif entry["eventid"] == "cowrie.session.params":
143             self.simpleQuery(
144                 "INSERT INTO `params` (`session`, `arch`) " "VALUES (%s, %s)",
145                 (entry["session"], entry["arch"]),
146             )
147 
148         elif entry["eventid"] == "cowrie.command.input":
149             self.simpleQuery(
150                 "INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) "
151                 "VALUES (%s, FROM_UNIXTIME(%s), %s , %s)",
152                 (entry["session"], entry["time"], 1, entry["input"]),
153             )
154 
155         elif entry["eventid"] == "cowrie.command.failed":
156             self.simpleQuery(
157                 "INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) "
158                 "VALUES (%s, FROM_UNIXTIME(%s), %s , %s)",
159                 (entry["session"], entry["time"], 0, entry["input"]),
160             )
161 
162         elif entry["eventid"] == "cowrie.session.file_download":
163             self.simpleQuery(
164                 "INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) "
165                 "VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)",
166                 (
167                     entry["session"],
168                     entry["time"],
169                     entry.get("url", ""),
170                     entry["outfile"],
171                     entry["shasum"],
172                 ),
173             )
174 
175         elif entry["eventid"] == "cowrie.session.file_download.failed":
176             self.simpleQuery(
177                 "INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) "
178                 "VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)",
179                 (entry["session"], entry["time"], entry.get("url", ""), "NULL", "NULL"),
180             )
181 
182         elif entry["eventid"] == "cowrie.session.file_upload":
183             self.simpleQuery(
184                 "INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) "
185                 "VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)",
186                 (
187                     entry["session"],
188                     entry["time"],
189                     "",
190                     entry["outfile"],
191                     entry["shasum"],
192                 ),
193             )
194 
195         elif entry["eventid"] == "cowrie.session.input":
196             self.simpleQuery(
197                 "INSERT INTO `input` (`session`, `timestamp`, `realm`, `input`) "
198                 "VALUES (%s, FROM_UNIXTIME(%s), %s , %s)",
199                 (entry["session"], entry["time"], entry["realm"], entry["input"]),
200             )
201 
202         elif entry["eventid"] == "cowrie.client.version":
203             r = yield self.db.runQuery(
204                 "SELECT `id` FROM `clients` " "WHERE `version` = %s",
205                 (entry["version"],),
206             )
207 
208             if r:
209                 id = int(r[0][0])
210             else:
211                 yield self.db.runQuery(
212                     "INSERT INTO `clients` (`version`) " "VALUES (%s)",
213                     (entry["version"],),
214                 )
215 
216                 r = yield self.db.runQuery("SELECT LAST_INSERT_ID()")
217                 id = int(r[0][0])
218             self.simpleQuery(
219                 "UPDATE `sessions` " "SET `client` = %s " "WHERE `id` = %s",
220                 (id, entry["session"]),
221             )
222 
223         elif entry["eventid"] == "cowrie.client.size":
224             self.simpleQuery(
225                 "UPDATE `sessions` " "SET `termsize` = %s " "WHERE `id` = %s",
226                 ("{}x{}".format(entry["width"], entry["height"]), entry["session"]),
227             )
228 
229         elif entry["eventid"] == "cowrie.session.closed":
230             self.simpleQuery(
231                 "UPDATE `sessions` "
232                 "SET `endtime` = FROM_UNIXTIME(%s) "
233                 "WHERE `id` = %s",
234                 (entry["time"], entry["session"]),
235             )
236 
237         elif entry["eventid"] == "cowrie.log.closed":
238             self.simpleQuery(
239                 "INSERT INTO `ttylog` (`session`, `ttylog`, `size`) "
240                 "VALUES (%s, %s, %s)",
241                 (entry["session"], entry["ttylog"], entry["size"]),
242             )
243 
244         elif entry["eventid"] == "cowrie.client.fingerprint":
245             self.simpleQuery(
246                 "INSERT INTO `keyfingerprints` (`session`, `username`, `fingerprint`) "
247                 "VALUES (%s, %s, %s)",
248                 (entry["session"], entry["username"], entry["fingerprint"]),
249             )
250 
251         elif entry["eventid"] == "cowrie.direct-tcpip.request":
252             self.simpleQuery(
253                 "INSERT INTO `ipforwards` (`session`, `timestamp`, `dst_ip`, `dst_port`) "
254                 "VALUES (%s, FROM_UNIXTIME(%s), %s, %s)",
255                 (entry["session"], entry["time"], entry["dst_ip"], entry["dst_port"]),
256             )
257 
258         elif entry["eventid"] == "cowrie.direct-tcpip.data":
259             self.simpleQuery(
260                 "INSERT INTO `ipforwardsdata` (`session`, `timestamp`, `dst_ip`, `dst_port`, `data`) "
261                 "VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)",
262                 (
263                     entry["session"],
264                     entry["time"],
265                     entry["dst_ip"],
266                     entry["dst_port"],
267                     entry["data"],
268                 ),
269             )
270
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/src/cowrie/output/mysql.py b/src/cowrie/output/mysql.py
--- a/src/cowrie/output/mysql.py
+++ b/src/cowrie/output/mysql.py
@@ -95,14 +95,14 @@
     def write(self, entry):
         if entry["eventid"] == "cowrie.session.connect":
             r = yield self.db.runQuery(
-                f"SELECT `id`\" \"FROM `sensors`\" \"WHERE `ip` = {self.sensor}"
+                "SELECT `id`" "FROM `sensors`" f"WHERE `ip` = {self.sensor}"
             )
 
             if r:
                 sensorid = r[0][0]
             else:
                 yield self.db.runQuery(
-                    f"INSERT INTO `sensors` (`ip`) \" \"VALUES ({self.sensor})"
+                    "INSERT INTO `sensors` (`ip`) " f"VALUES ({self.sensor})"
                 )
 
                 r = yield self.db.runQuery("SELECT LAST_INSERT_ID()")

{"golden_diff": "diff --git a/src/cowrie/output/mysql.py b/src/cowrie/output/mysql.py\n--- a/src/cowrie/output/mysql.py\n+++ b/src/cowrie/output/mysql.py\n@@ -95,14 +95,14 @@\n     def write(self, entry):\n         if entry[\"eventid\"] == \"cowrie.session.connect\":\n             r = yield self.db.runQuery(\n-                f\"SELECT `id`\\\" \\\"FROM `sensors`\\\" \\\"WHERE `ip` = {self.sensor}\"\n+                \"SELECT `id`\" \"FROM `sensors`\" f\"WHERE `ip` = {self.sensor}\"\n             )\n \n             if r:\n                 sensorid = r[0][0]\n             else:\n                 yield self.db.runQuery(\n-                    f\"INSERT INTO `sensors` (`ip`) \\\" \\\"VALUES ({self.sensor})\"\n+                    \"INSERT INTO `sensors` (`ip`) \" f\"VALUES ({self.sensor})\"\n                 )\n \n                 r = yield self.db.runQuery(\"SELECT LAST_INSERT_ID()\")\n", "issue": "9may\n\n", "before_files": [{"content": "\"\"\"\nMySQL output connector. Writes audit logs to MySQL database\n\"\"\"\n\n\nimport MySQLdb\n\nfrom twisted.enterprise import adbapi\nfrom twisted.internet import defer\nfrom twisted.python import log\n\nimport cowrie.core.output\nfrom cowrie.core.config import CowrieConfig\n\n\nclass ReconnectingConnectionPool(adbapi.ConnectionPool):\n    \"\"\"\n    Reconnecting adbapi connection pool for MySQL.\n\n    This class improves on the solution posted at\n    http://www.gelens.org/2008/09/12/reinitializing-twisted-connectionpool/\n    by checking exceptions by error code and only disconnecting the current\n    connection instead of all of them.\n\n    Also see:\n    http://twistedmatrix.com/pipermail/twisted-python/2009-July/020007.html\n    \"\"\"\n\n    def _runInteraction(self, interaction, *args, **kw):\n        try:\n            return adbapi.ConnectionPool._runInteraction(self, interaction, *args, **kw)\n        except (MySQLdb.OperationalError, MySQLdb._exceptions.OperationalError) as e:\n            if e.args[0] not in (2003, 2006, 2013):\n                raise e\n            log.msg(f\"RCP: got error {e}, retrying operation\")\n            conn = self.connections.get(self.threadID())\n            self.disconnect(conn)\n            # Try the interaction again\n            return adbapi.ConnectionPool._runInteraction(self, interaction, *args, **kw)\n\n\nclass Output(cowrie.core.output.Output):\n    \"\"\"\n    mysql output\n    \"\"\"\n\n    db = None\n    debug: bool = False\n\n    def start(self):\n        self.debug = CowrieConfig.getboolean(\"output_mysql\", \"debug\", fallback=False)\n        port = CowrieConfig.getint(\"output_mysql\", \"port\", fallback=3306)\n        try:\n            self.db = ReconnectingConnectionPool(\n                \"MySQLdb\",\n                host=CowrieConfig.get(\"output_mysql\", \"host\"),\n                db=CowrieConfig.get(\"output_mysql\", \"database\"),\n                user=CowrieConfig.get(\"output_mysql\", \"username\"),\n                passwd=CowrieConfig.get(\"output_mysql\", \"password\", raw=True),\n                port=port,\n                cp_min=1,\n                cp_max=1,\n                charset=\"utf8mb4\",\n                cp_reconnect=True,\n                use_unicode=True,\n            )\n        except (MySQLdb.Error, MySQLdb._exceptions.Error) as e:\n            log.msg(f\"output_mysql: Error {e.args[0]}: {e.args[1]}\")\n\n    def stop(self):\n        self.db.commit()\n        self.db.close()\n\n    def sqlerror(self, error):\n        \"\"\"\n        1146, \"Table '...' doesn't exist\"\n        1406, \"Data too long for column '...' at row ...\"\n        \"\"\"\n        if error.value.args[0] in (1146, 1406):\n            log.msg(f\"output_mysql: MySQL Error: {error.value.args!r}\")\n            log.msg(\"MySQL schema maybe misconfigured, doublecheck database!\")\n        else:\n            log.msg(f\"output_mysql: MySQL Error: {error.value.args!r}\")\n\n    def simpleQuery(self, sql, args):\n        \"\"\"\n        Just run a deferred sql query, only care about errors\n        \"\"\"\n        if self.debug:\n            log.msg(f\"output_mysql: MySQL query: {sql} {args!r}\")\n        d = self.db.runQuery(sql, args)\n        d.addErrback(self.sqlerror)\n\n    @defer.inlineCallbacks\n    def write(self, entry):\n        if entry[\"eventid\"] == \"cowrie.session.connect\":\n            r = yield self.db.runQuery(\n                f\"SELECT `id`\\\" \\\"FROM `sensors`\\\" \\\"WHERE `ip` = {self.sensor}\"\n            )\n\n            if r:\n                sensorid = r[0][0]\n            else:\n                yield self.db.runQuery(\n                    f\"INSERT INTO `sensors` (`ip`) \\\" \\\"VALUES ({self.sensor})\"\n                )\n\n                r = yield self.db.runQuery(\"SELECT LAST_INSERT_ID()\")\n                sensorid = int(r[0][0])\n            self.simpleQuery(\n                \"INSERT INTO `sessions` (`id`, `starttime`, `sensor`, `ip`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s)\",\n                (entry[\"session\"], entry[\"time\"], sensorid, entry[\"src_ip\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.login.success\":\n            self.simpleQuery(\n                \"INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) \"\n                \"VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))\",\n                (\n                    entry[\"session\"],\n                    1,\n                    entry[\"username\"],\n                    entry[\"password\"],\n                    entry[\"time\"],\n                ),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.login.failed\":\n            self.simpleQuery(\n                \"INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) \"\n                \"VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))\",\n                (\n                    entry[\"session\"],\n                    0,\n                    entry[\"username\"],\n                    entry[\"password\"],\n                    entry[\"time\"],\n                ),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.params\":\n            self.simpleQuery(\n                \"INSERT INTO `params` (`session`, `arch`) \" \"VALUES (%s, %s)\",\n                (entry[\"session\"], entry[\"arch\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.command.input\":\n            self.simpleQuery(\n                \"INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s , %s)\",\n                (entry[\"session\"], entry[\"time\"], 1, entry[\"input\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.command.failed\":\n            self.simpleQuery(\n                \"INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s , %s)\",\n                (entry[\"session\"], entry[\"time\"], 0, entry[\"input\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.file_download\":\n            self.simpleQuery(\n                \"INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)\",\n                (\n                    entry[\"session\"],\n                    entry[\"time\"],\n                    entry.get(\"url\", \"\"),\n                    entry[\"outfile\"],\n                    entry[\"shasum\"],\n                ),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.file_download.failed\":\n            self.simpleQuery(\n                \"INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)\",\n                (entry[\"session\"], entry[\"time\"], entry.get(\"url\", \"\"), \"NULL\", \"NULL\"),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.file_upload\":\n            self.simpleQuery(\n                \"INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)\",\n                (\n                    entry[\"session\"],\n                    entry[\"time\"],\n                    \"\",\n                    entry[\"outfile\"],\n                    entry[\"shasum\"],\n                ),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.input\":\n            self.simpleQuery(\n                \"INSERT INTO `input` (`session`, `timestamp`, `realm`, `input`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s , %s)\",\n                (entry[\"session\"], entry[\"time\"], entry[\"realm\"], entry[\"input\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.client.version\":\n            r = yield self.db.runQuery(\n                \"SELECT `id` FROM `clients` \" \"WHERE `version` = %s\",\n                (entry[\"version\"],),\n            )\n\n            if r:\n                id = int(r[0][0])\n            else:\n                yield self.db.runQuery(\n                    \"INSERT INTO `clients` (`version`) \" \"VALUES (%s)\",\n                    (entry[\"version\"],),\n                )\n\n                r = yield self.db.runQuery(\"SELECT LAST_INSERT_ID()\")\n                id = int(r[0][0])\n            self.simpleQuery(\n                \"UPDATE `sessions` \" \"SET `client` = %s \" \"WHERE `id` = %s\",\n                (id, entry[\"session\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.client.size\":\n            self.simpleQuery(\n                \"UPDATE `sessions` \" \"SET `termsize` = %s \" \"WHERE `id` = %s\",\n                (\"{}x{}\".format(entry[\"width\"], entry[\"height\"]), entry[\"session\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.closed\":\n            self.simpleQuery(\n                \"UPDATE `sessions` \"\n                \"SET `endtime` = FROM_UNIXTIME(%s) \"\n                \"WHERE `id` = %s\",\n                (entry[\"time\"], entry[\"session\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.log.closed\":\n            self.simpleQuery(\n                \"INSERT INTO `ttylog` (`session`, `ttylog`, `size`) \"\n                \"VALUES (%s, %s, %s)\",\n                (entry[\"session\"], entry[\"ttylog\"], entry[\"size\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.client.fingerprint\":\n            self.simpleQuery(\n                \"INSERT INTO `keyfingerprints` (`session`, `username`, `fingerprint`) \"\n                \"VALUES (%s, %s, %s)\",\n                (entry[\"session\"], entry[\"username\"], entry[\"fingerprint\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.direct-tcpip.request\":\n            self.simpleQuery(\n                \"INSERT INTO `ipforwards` (`session`, `timestamp`, `dst_ip`, `dst_port`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s)\",\n                (entry[\"session\"], entry[\"time\"], entry[\"dst_ip\"], entry[\"dst_port\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.direct-tcpip.data\":\n            self.simpleQuery(\n                \"INSERT INTO `ipforwardsdata` (`session`, `timestamp`, `dst_ip`, `dst_port`, `data`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)\",\n                (\n                    entry[\"session\"],\n                    entry[\"time\"],\n                    entry[\"dst_ip\"],\n                    entry[\"dst_port\"],\n                    entry[\"data\"],\n                ),\n            )\n", "path": "src/cowrie/output/mysql.py"}], "after_files": [{"content": "\"\"\"\nMySQL output connector. Writes audit logs to MySQL database\n\"\"\"\n\n\nimport MySQLdb\n\nfrom twisted.enterprise import adbapi\nfrom twisted.internet import defer\nfrom twisted.python import log\n\nimport cowrie.core.output\nfrom cowrie.core.config import CowrieConfig\n\n\nclass ReconnectingConnectionPool(adbapi.ConnectionPool):\n    \"\"\"\n    Reconnecting adbapi connection pool for MySQL.\n\n    This class improves on the solution posted at\n    http://www.gelens.org/2008/09/12/reinitializing-twisted-connectionpool/\n    by checking exceptions by error code and only disconnecting the current\n    connection instead of all of them.\n\n    Also see:\n    http://twistedmatrix.com/pipermail/twisted-python/2009-July/020007.html\n    \"\"\"\n\n    def _runInteraction(self, interaction, *args, **kw):\n        try:\n            return adbapi.ConnectionPool._runInteraction(self, interaction, *args, **kw)\n        except (MySQLdb.OperationalError, MySQLdb._exceptions.OperationalError) as e:\n            if e.args[0] not in (2003, 2006, 2013):\n                raise e\n            log.msg(f\"RCP: got error {e}, retrying operation\")\n            conn = self.connections.get(self.threadID())\n            self.disconnect(conn)\n            # Try the interaction again\n            return adbapi.ConnectionPool._runInteraction(self, interaction, *args, **kw)\n\n\nclass Output(cowrie.core.output.Output):\n    \"\"\"\n    mysql output\n    \"\"\"\n\n    db = None\n    debug: bool = False\n\n    def start(self):\n        self.debug = CowrieConfig.getboolean(\"output_mysql\", \"debug\", fallback=False)\n        port = CowrieConfig.getint(\"output_mysql\", \"port\", fallback=3306)\n        try:\n            self.db = ReconnectingConnectionPool(\n                \"MySQLdb\",\n                host=CowrieConfig.get(\"output_mysql\", \"host\"),\n                db=CowrieConfig.get(\"output_mysql\", \"database\"),\n                user=CowrieConfig.get(\"output_mysql\", \"username\"),\n                passwd=CowrieConfig.get(\"output_mysql\", \"password\", raw=True),\n                port=port,\n                cp_min=1,\n                cp_max=1,\n                charset=\"utf8mb4\",\n                cp_reconnect=True,\n                use_unicode=True,\n            )\n        except (MySQLdb.Error, MySQLdb._exceptions.Error) as e:\n            log.msg(f\"output_mysql: Error {e.args[0]}: {e.args[1]}\")\n\n    def stop(self):\n        self.db.commit()\n        self.db.close()\n\n    def sqlerror(self, error):\n        \"\"\"\n        1146, \"Table '...' doesn't exist\"\n        1406, \"Data too long for column '...' at row ...\"\n        \"\"\"\n        if error.value.args[0] in (1146, 1406):\n            log.msg(f\"output_mysql: MySQL Error: {error.value.args!r}\")\n            log.msg(\"MySQL schema maybe misconfigured, doublecheck database!\")\n        else:\n            log.msg(f\"output_mysql: MySQL Error: {error.value.args!r}\")\n\n    def simpleQuery(self, sql, args):\n        \"\"\"\n        Just run a deferred sql query, only care about errors\n        \"\"\"\n        if self.debug:\n            log.msg(f\"output_mysql: MySQL query: {sql} {args!r}\")\n        d = self.db.runQuery(sql, args)\n        d.addErrback(self.sqlerror)\n\n    @defer.inlineCallbacks\n    def write(self, entry):\n        if entry[\"eventid\"] == \"cowrie.session.connect\":\n            r = yield self.db.runQuery(\n                \"SELECT `id`\" \"FROM `sensors`\" f\"WHERE `ip` = {self.sensor}\"\n            )\n\n            if r:\n                sensorid = r[0][0]\n            else:\n                yield self.db.runQuery(\n                    \"INSERT INTO `sensors` (`ip`) \" f\"VALUES ({self.sensor})\"\n                )\n\n                r = yield self.db.runQuery(\"SELECT LAST_INSERT_ID()\")\n                sensorid = int(r[0][0])\n            self.simpleQuery(\n                \"INSERT INTO `sessions` (`id`, `starttime`, `sensor`, `ip`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s)\",\n                (entry[\"session\"], entry[\"time\"], sensorid, entry[\"src_ip\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.login.success\":\n            self.simpleQuery(\n                \"INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) \"\n                \"VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))\",\n                (\n                    entry[\"session\"],\n                    1,\n                    entry[\"username\"],\n                    entry[\"password\"],\n                    entry[\"time\"],\n                ),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.login.failed\":\n            self.simpleQuery(\n                \"INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) \"\n                \"VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))\",\n                (\n                    entry[\"session\"],\n                    0,\n                    entry[\"username\"],\n                    entry[\"password\"],\n                    entry[\"time\"],\n                ),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.params\":\n            self.simpleQuery(\n                \"INSERT INTO `params` (`session`, `arch`) \" \"VALUES (%s, %s)\",\n                (entry[\"session\"], entry[\"arch\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.command.input\":\n            self.simpleQuery(\n                \"INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s , %s)\",\n                (entry[\"session\"], entry[\"time\"], 1, entry[\"input\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.command.failed\":\n            self.simpleQuery(\n                \"INSERT INTO `input` (`session`, `timestamp`, `success`, `input`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s , %s)\",\n                (entry[\"session\"], entry[\"time\"], 0, entry[\"input\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.file_download\":\n            self.simpleQuery(\n                \"INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)\",\n                (\n                    entry[\"session\"],\n                    entry[\"time\"],\n                    entry.get(\"url\", \"\"),\n                    entry[\"outfile\"],\n                    entry[\"shasum\"],\n                ),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.file_download.failed\":\n            self.simpleQuery(\n                \"INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)\",\n                (entry[\"session\"], entry[\"time\"], entry.get(\"url\", \"\"), \"NULL\", \"NULL\"),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.file_upload\":\n            self.simpleQuery(\n                \"INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)\",\n                (\n                    entry[\"session\"],\n                    entry[\"time\"],\n                    \"\",\n                    entry[\"outfile\"],\n                    entry[\"shasum\"],\n                ),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.input\":\n            self.simpleQuery(\n                \"INSERT INTO `input` (`session`, `timestamp`, `realm`, `input`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s , %s)\",\n                (entry[\"session\"], entry[\"time\"], entry[\"realm\"], entry[\"input\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.client.version\":\n            r = yield self.db.runQuery(\n                \"SELECT `id` FROM `clients` \" \"WHERE `version` = %s\",\n                (entry[\"version\"],),\n            )\n\n            if r:\n                id = int(r[0][0])\n            else:\n                yield self.db.runQuery(\n                    \"INSERT INTO `clients` (`version`) \" \"VALUES (%s)\",\n                    (entry[\"version\"],),\n                )\n\n                r = yield self.db.runQuery(\"SELECT LAST_INSERT_ID()\")\n                id = int(r[0][0])\n            self.simpleQuery(\n                \"UPDATE `sessions` \" \"SET `client` = %s \" \"WHERE `id` = %s\",\n                (id, entry[\"session\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.client.size\":\n            self.simpleQuery(\n                \"UPDATE `sessions` \" \"SET `termsize` = %s \" \"WHERE `id` = %s\",\n                (\"{}x{}\".format(entry[\"width\"], entry[\"height\"]), entry[\"session\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.session.closed\":\n            self.simpleQuery(\n                \"UPDATE `sessions` \"\n                \"SET `endtime` = FROM_UNIXTIME(%s) \"\n                \"WHERE `id` = %s\",\n                (entry[\"time\"], entry[\"session\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.log.closed\":\n            self.simpleQuery(\n                \"INSERT INTO `ttylog` (`session`, `ttylog`, `size`) \"\n                \"VALUES (%s, %s, %s)\",\n                (entry[\"session\"], entry[\"ttylog\"], entry[\"size\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.client.fingerprint\":\n            self.simpleQuery(\n                \"INSERT INTO `keyfingerprints` (`session`, `username`, `fingerprint`) \"\n                \"VALUES (%s, %s, %s)\",\n                (entry[\"session\"], entry[\"username\"], entry[\"fingerprint\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.direct-tcpip.request\":\n            self.simpleQuery(\n                \"INSERT INTO `ipforwards` (`session`, `timestamp`, `dst_ip`, `dst_port`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s)\",\n                (entry[\"session\"], entry[\"time\"], entry[\"dst_ip\"], entry[\"dst_port\"]),\n            )\n\n        elif entry[\"eventid\"] == \"cowrie.direct-tcpip.data\":\n            self.simpleQuery(\n                \"INSERT INTO `ipforwardsdata` (`session`, `timestamp`, `dst_ip`, `dst_port`, `data`) \"\n                \"VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)\",\n                (\n                    entry[\"session\"],\n                    entry[\"time\"],\n                    entry[\"dst_ip\"],\n                    entry[\"dst_port\"],\n                    entry[\"data\"],\n                ),\n            )\n", "path": "src/cowrie/output/mysql.py"}]}