We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
Mysql error? [twisted.internet.defer#critical] Unhandled error in Deferred
```
2021-01-11T16:54:56.352309Z [CowrieTelnetTransport,2,41.13.224.97] login attempt [b'root'/b'5up'] succeeded
2021-01-11T16:54:56.353787Z [CowrieTelnetTransport,2,41.13.224.97] Initialized emulated server as architecture: linux-x64-lsb
2021-01-11T16:54:56.354732Z [twisted.internet.defer#critical] Unhandled error in Deferred:
2021-01-11T16:54:56.354941Z [twisted.internet.defer#critical]
        Traceback (most recent call last):
          File "/home/cowrie/cowrie/cowrie-env/lib/python3.8/site-packages/twisted/logger/_legacy.py", line 93, in __call__
            self.legacyObserver(event)
          File "/home/cowrie/cowrie/src/cowrie/core/output.py", line 218, in emit
            self.write(ev)
          File "/home/cowrie/cowrie/cowrie-env/lib/python3.8/site-packages/twisted/internet/defer.py", line 1613, in unwindGenerator
            return _cancellableInlineCallbacks(gen)
          File "/home/cowrie/cowrie/cowrie-env/lib/python3.8/site-packages/twisted/internet/defer.py", line 1529, in _cancellableInlineCallbacks
            _inlineCallbacks(None, g, status)
        --- <exception caught here> ---
          File "/home/cowrie/cowrie/cowrie-env/lib/python3.8/site-packages/twisted/internet/defer.py", line 1418, in _inlineCallbacks
            result = g.send(result)
          File "/home/cowrie/cowrie/src/cowrie/output/mysql.py", line 147, in write
            (entry["session"], entry["time"], entry['url'], entry['outfile'], entry['shasum']))
        builtins.KeyError: 'url'

2021-01-11T16:54:56.413032Z [CowrieTelnetTransport,2,41.13.224.97] Warning: state changed and new state returned
```

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

{"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@@ -144,12 +144,12 @@\n         elif entry[\"eventid\"] == 'cowrie.session.file_download':\n             self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                              'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n-                             (entry[\"session\"], entry[\"time\"], entry['url'], entry['outfile'], entry['shasum']))\n+                             (entry[\"session\"], entry[\"time\"], entry.get(\"url\", \"\"), entry['outfile'], entry['shasum']))\n \n         elif entry[\"eventid\"] == 'cowrie.session.file_download.failed':\n             self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                              'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n-                             (entry[\"session\"], entry[\"time\"], entry['url'], 'NULL', 'NULL'))\n+                             (entry[\"session\"], entry[\"time\"], entry.get(\"url\", \"\"), 'NULL', 'NULL'))\n \n         elif entry[\"eventid\"] == 'cowrie.session.file_upload':\n             self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n", "issue": "Mysql error? [twisted.internet.defer#critical] Unhandled error in Deferred\n```\r\n2021-01-11T16:54:56.352309Z [CowrieTelnetTransport,2,41.13.224.97] login attempt [b'root'/b'5up'] succeeded\r\n2021-01-11T16:54:56.353787Z [CowrieTelnetTransport,2,41.13.224.97] Initialized emulated server as architecture: linux-x64-lsb\r\n2021-01-11T16:54:56.354732Z [twisted.internet.defer#critical] Unhandled error in Deferred:\r\n2021-01-11T16:54:56.354941Z [twisted.internet.defer#critical]\r\n        Traceback (most recent call last):\r\n          File \"/home/cowrie/cowrie/cowrie-env/lib/python3.8/site-packages/twisted/logger/_legacy.py\", line 93, in __call__\r\n            self.legacyObserver(event)\r\n          File \"/home/cowrie/cowrie/src/cowrie/core/output.py\", line 218, in emit\r\n            self.write(ev)\r\n          File \"/home/cowrie/cowrie/cowrie-env/lib/python3.8/site-packages/twisted/internet/defer.py\", line 1613, in unwindGenerator\r\n            return _cancellableInlineCallbacks(gen)\r\n          File \"/home/cowrie/cowrie/cowrie-env/lib/python3.8/site-packages/twisted/internet/defer.py\", line 1529, in _cancellableInlineCallbacks\r\n            _inlineCallbacks(None, g, status)\r\n        --- <exception caught here> ---\r\n          File \"/home/cowrie/cowrie/cowrie-env/lib/python3.8/site-packages/twisted/internet/defer.py\", line 1418, in _inlineCallbacks\r\n            result = g.send(result)\r\n          File \"/home/cowrie/cowrie/src/cowrie/output/mysql.py\", line 147, in write\r\n            (entry[\"session\"], entry[\"time\"], entry['url'], entry['outfile'], entry['shasum']))\r\n        builtins.KeyError: 'url'\r\n\r\n2021-01-11T16:54:56.413032Z [CowrieTelnetTransport,2,41.13.224.97] Warning: state changed and new state returned\r\n```\n", "before_files": [{"content": "\"\"\"\nMySQL output connector. Writes audit logs to MySQL database\n\"\"\"\n\nfrom __future__ import absolute_import, division\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(\n                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(\"RCP: got error {0}, retrying operation\".format(e))\n            conn = self.connections.get(self.threadID())\n            self.disconnect(conn)\n            # Try the interaction again\n            return adbapi.ConnectionPool._runInteraction(\n                self, interaction, *args, **kw)\n\n\nclass Output(cowrie.core.output.Output):\n    \"\"\"\n    mysql output\n    \"\"\"\n    db = None\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._exceptons.Error) as e:\n            log.msg(\"output_mysql: Error %d: %s\" % (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[0] in (1146, 1406):\n            log.msg(\"output_mysql: MySQL Error: {}\".format(error.value))\n            log.msg(\"MySQL schema maybe misconfigured, doublecheck database!\")\n        else:\n            log.err(\"output_mysql: MySQL Error: {}\".format(error.value))\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(\"output_mysql: MySQL query: {} {}\".format(sql, repr(args)))\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`\"\n                \"FROM `sensors`\"\n                \"WHERE `ip` = %s\",\n                (self.sensor,))\n\n            if r:\n                sensorid = r[0][0]\n            else:\n                yield self.db.runQuery(\n                    'INSERT INTO `sensors` (`ip`) '\n                    'VALUES (%s)',\n                    (self.sensor,))\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        elif entry[\"eventid\"] == 'cowrie.login.success':\n            self.simpleQuery('INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) '\n                             'VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))',\n                             (entry[\"session\"], 1, entry['username'], entry['password'], entry[\"time\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.login.failed':\n            self.simpleQuery('INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) '\n                             'VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))',\n                             (entry[\"session\"], 0, entry['username'], entry['password'], entry[\"time\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.session.params':\n            self.simpleQuery('INSERT INTO `params` (`session`, `arch`) '\n                             'VALUES (%s, %s)',\n                             (entry[\"session\"], entry[\"arch\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.command.input':\n            self.simpleQuery('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        elif entry[\"eventid\"] == 'cowrie.command.failed':\n            self.simpleQuery('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        elif entry[\"eventid\"] == 'cowrie.session.file_download':\n            self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                             (entry[\"session\"], entry[\"time\"], entry['url'], entry['outfile'], entry['shasum']))\n\n        elif entry[\"eventid\"] == 'cowrie.session.file_download.failed':\n            self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                             (entry[\"session\"], entry[\"time\"], entry['url'], 'NULL', 'NULL'))\n\n        elif entry[\"eventid\"] == 'cowrie.session.file_upload':\n            self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                             (entry[\"session\"], entry[\"time\"], '', entry['outfile'], entry['shasum']))\n\n        elif entry[\"eventid\"] == 'cowrie.session.input':\n            self.simpleQuery('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        elif entry[\"eventid\"] == 'cowrie.client.version':\n            r = yield self.db.runQuery(\n                'SELECT `id` FROM `clients` '\n                'WHERE `version` = %s',\n                (entry['version'],))\n\n            if r:\n                id = int(r[0][0])\n            else:\n                yield self.db.runQuery(\n                    'INSERT INTO `clients` (`version`) '\n                    'VALUES (%s)',\n                    (entry['version'],))\n\n                r = yield self.db.runQuery('SELECT LAST_INSERT_ID()')\n                id = int(r[0][0])\n            self.simpleQuery(\n                'UPDATE `sessions` '\n                'SET `client` = %s '\n                'WHERE `id` = %s',\n                (id, entry[\"session\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.client.size':\n            self.simpleQuery(\n                'UPDATE `sessions` '\n                'SET `termsize` = %s '\n                'WHERE `id` = %s',\n                ('%sx%s' % (entry['width'], entry['height']), entry[\"session\"]))\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        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        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        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        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                (entry[\"session\"], entry[\"time\"], entry[\"dst_ip\"], entry[\"dst_port\"], entry[\"data\"]))\n", "path": "src/cowrie/output/mysql.py"}], "after_files": [{"content": "\"\"\"\nMySQL output connector. Writes audit logs to MySQL database\n\"\"\"\n\nfrom __future__ import absolute_import, division\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(\n                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(\"RCP: got error {0}, retrying operation\".format(e))\n            conn = self.connections.get(self.threadID())\n            self.disconnect(conn)\n            # Try the interaction again\n            return adbapi.ConnectionPool._runInteraction(\n                self, interaction, *args, **kw)\n\n\nclass Output(cowrie.core.output.Output):\n    \"\"\"\n    mysql output\n    \"\"\"\n    db = None\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._exceptons.Error) as e:\n            log.msg(\"output_mysql: Error %d: %s\" % (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[0] in (1146, 1406):\n            log.msg(\"output_mysql: MySQL Error: {}\".format(error.value))\n            log.msg(\"MySQL schema maybe misconfigured, doublecheck database!\")\n        else:\n            log.err(\"output_mysql: MySQL Error: {}\".format(error.value))\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(\"output_mysql: MySQL query: {} {}\".format(sql, repr(args)))\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`\"\n                \"FROM `sensors`\"\n                \"WHERE `ip` = %s\",\n                (self.sensor,))\n\n            if r:\n                sensorid = r[0][0]\n            else:\n                yield self.db.runQuery(\n                    'INSERT INTO `sensors` (`ip`) '\n                    'VALUES (%s)',\n                    (self.sensor,))\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        elif entry[\"eventid\"] == 'cowrie.login.success':\n            self.simpleQuery('INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) '\n                             'VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))',\n                             (entry[\"session\"], 1, entry['username'], entry['password'], entry[\"time\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.login.failed':\n            self.simpleQuery('INSERT INTO `auth` (`session`, `success`, `username`, `password`, `timestamp`) '\n                             'VALUES (%s, %s, %s, %s, FROM_UNIXTIME(%s))',\n                             (entry[\"session\"], 0, entry['username'], entry['password'], entry[\"time\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.session.params':\n            self.simpleQuery('INSERT INTO `params` (`session`, `arch`) '\n                             'VALUES (%s, %s)',\n                             (entry[\"session\"], entry[\"arch\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.command.input':\n            self.simpleQuery('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        elif entry[\"eventid\"] == 'cowrie.command.failed':\n            self.simpleQuery('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        elif entry[\"eventid\"] == 'cowrie.session.file_download':\n            self.simpleQuery('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\", \"\"), entry['outfile'], entry['shasum']))\n\n        elif entry[\"eventid\"] == 'cowrie.session.file_download.failed':\n            self.simpleQuery('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        elif entry[\"eventid\"] == 'cowrie.session.file_upload':\n            self.simpleQuery('INSERT INTO `downloads` (`session`, `timestamp`, `url`, `outfile`, `shasum`) '\n                             'VALUES (%s, FROM_UNIXTIME(%s), %s, %s, %s)',\n                             (entry[\"session\"], entry[\"time\"], '', entry['outfile'], entry['shasum']))\n\n        elif entry[\"eventid\"] == 'cowrie.session.input':\n            self.simpleQuery('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        elif entry[\"eventid\"] == 'cowrie.client.version':\n            r = yield self.db.runQuery(\n                'SELECT `id` FROM `clients` '\n                'WHERE `version` = %s',\n                (entry['version'],))\n\n            if r:\n                id = int(r[0][0])\n            else:\n                yield self.db.runQuery(\n                    'INSERT INTO `clients` (`version`) '\n                    'VALUES (%s)',\n                    (entry['version'],))\n\n                r = yield self.db.runQuery('SELECT LAST_INSERT_ID()')\n                id = int(r[0][0])\n            self.simpleQuery(\n                'UPDATE `sessions` '\n                'SET `client` = %s '\n                'WHERE `id` = %s',\n                (id, entry[\"session\"]))\n\n        elif entry[\"eventid\"] == 'cowrie.client.size':\n            self.simpleQuery(\n                'UPDATE `sessions` '\n                'SET `termsize` = %s '\n                'WHERE `id` = %s',\n                ('%sx%s' % (entry['width'], entry['height']), entry[\"session\"]))\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        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        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        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        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                (entry[\"session\"], entry[\"time\"], entry[\"dst_ip\"], entry[\"dst_port\"], entry[\"data\"]))\n", "path": "src/cowrie/output/mysql.py"}]}