5
\$\begingroup\$

I've made this code to extract a conversation from a Skype database file with a certain user ID, all this being specified as a command line argument.

Since Skype uses SQLite to store the messages I can use the sqlite3 Python module to retrieve the messages.

The code first checks for the database file, opens it if it exists, parses it and finally outputs the chat either to stdout or a specified output file (it uses the partner's ID by default if nothing is provided). The output looks somewhat like what you would expect from an IRC conversation.

I'm quite satisfied with the script in general however I had several issues with unicode characters which made me have to use some "cheats". I'm not satisfied with all these unicode() conversions, and the fact I have to specify once more at the end via encode('utf-8') that I am indeed going to output other things than ASCII. I also think this code could be made more elegant, especially with the SQL query. If I ever want to add more options, such as sorting, I feel like putting everything in the SQL query looks ugly.

#!/usr/bin/env python2
# charset=utf-8

from __future__ import print_function

import sys
import sqlite3
import os.path
import datetime
import HTMLParser


def eprint(*args, **kwargs):
    print(*args, file=sys.stderr, **kwargs)

def eprint_use():
    eprint("usage : " + sys.argv[0] + " <file.db> <partner's skype ID> [output file]")

# actual code here
# first of all check argv and print the help message if it's wrong

if not len(sys.argv) in [3, 4]:
    eprint_use()

else:
    database_path = sys.argv[1]
    partner_id = sys.argv[2]
    output_path = sys.argv[3] if len(sys.argv) == 4 else partner_id + '.txt'

    if not os.path.isfile(database_path):
        sys.exit('the file %s does not exist' % (database_path))

    output_file = sys.stdout if output_path == '-' else open(output_path, 'w')

    connection = sqlite3.connect(database_path)
    cursor = connection.cursor()


    parser = HTMLParser.HTMLParser()

    cursor.execute("""
    SELECT timestamp,from_dispname,body_xml
    FROM Messages
    WHERE dialog_partner='""" + partner_id + """'
    ORDER BY timestamp
    """)

    for row in cursor.fetchall():
        timestamp = unicode(datetime.datetime.utcfromtimestamp(int(row[0])).strftime('%Y-%m-%d %H:%M:%S'))
        name_tag = unicode(row[1])
        message_body = unicode(parser.unescape(unicode(row[2])))

        output = "[%s] <%s> %s\n" % (timestamp, name_tag, message_body)

        output_file.write(output.encode('utf-8'))

Please keep in mind that I've barely been actively programming in Python for about a year and a half. I try to make my code as pythonic as possible, but as primarily being a C programmer I tend to neglect some of the aspects of the python language in favour of "C-tricks". If you see any such thing, please tell me as well.

\$\endgroup\$
4
\$\begingroup\$

First off, you should be using argparse for any argument parsing more complicated than python script.py arg1. It has automatic parsing of sys.argv, allow default values, custom help messages, custom argument types, prints a help message if any arguments mismatch and exits, ...

import argparse
import os.path
import sys

def is_file(f):
    if os.path.isfile(f):
        return f
    raise OSError("{} does not exist".format(f))

parser = argparse.ArgumentParser(descr="Skype history reader")
parser.add_argument('db_path', type=is_file, help="Database path")
parser.add_argument('partner_id', help="Partner to show history of")
parser.add_argument('output_path', help="Location to save output to", default="-")
args = parser.parse_args()

args.output_path = args.output_path or "{.partner_id}.txt".format(args)
output = sys.stdout if args.output == "-" else open(args.output_path, "w")

For your SQL, you should be using Prepared Statements (or the Python implementation of it)

cursor.execute("""
    SELECT timestamp, from_dispname, body_xml
    FROM Messages
    WHERE dialog_partner = ?
    ORDER BY timestamp
    """, partner_id)

This needs paramstyle='qmark'. Otherwise use %s instead of ?. It ensures that partner_id is correctly escaped, so you can't do SQL injection with something like

partner_id = "'; DROP TABLE Messages; SELECT * FROM USERS '"

(Not sure this exact string would work, but something similar to it would be able to delete your whole database. Or print all your chat partners. Or add swearwords to all your messages.)

You should always close your files, otherwise you could get file corruption. Normally I would advise using with open(file_name, "w") as output:, but here we need to also use sys.stdout. Therefore you need to add an explicit output_file.close() at the end, ideally by wrapping your code in a try...finally to ensure it is also closed if some code in between raises an exception.

Likewise, you should close your connection to the db at the end: connection.close().

\$\endgroup\$
  • \$\begingroup\$ I considered this SQL injection issue, but I did not know how I could solve it, I assumed the user did not want to destroy its own database file so left it aside for the time being, I'm glad it's easier than I thought it would be using this solution. Thanks for the argpars as well ! \$\endgroup\$ – saeleko Nov 28 '16 at 13:58
  • 1
    \$\begingroup\$ @Luda Yeah, never assume that the user is not malicious or stupid. Sometimes the two are very hard to disentangle ;). And since it is quite easy to do right (or at least much better than just trusting user input), one should do it. \$\endgroup\$ – Graipher Nov 28 '16 at 14:36
  • 1
    \$\begingroup\$ Yes I'm pleased to see it was that simple. Had it been an online system with remote control, I would have made fixing this issue top 1 priority. But you are right in that things should be made secure anyway. With good habits, less flaws. \$\endgroup\$ – saeleko Nov 28 '16 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.