CS350 Project

Idea

The idea behind this project was to create a neat and orgainzed way to store and search for public keys based on a username. This was meant to be incorperated into the Capstone Project that a friend and I were working on and decided that not using a databse would be easier. Our Capstone project is a secure messaging program that uses RSA public/private key encryption to establish symmetric keys between clients and allows them to then message back and forth. This program was to be written in Python and is currently working with some issues. The whole database idea was abandoned in spite of an easier method of storing keys. The project can be found here at this link on GitHub.

As it was I worked on putting up a server with Linux on it and installing some Database Management Software, I decided on Sqlite3 because of it's easy of use and python intergration.

I then created a database file using the following command:

sqlite3 messaging.db

The next step was to decide to which of the data I would need to store and how it was to be stored. What I decided on was:

create table messaging(username varchar(20) PRIMARY KEY, PubKey varchar(2000) UNIQUE);

The next issue was to develop the python code that would connect into the SQL database file and provide the queries, this is easily done due to python libraries, such as the following:

import sqlite3

This library provides a very convenient way to query the database. So far in my testing I have inserted a number of username and PubKey pairs. The most simple version of the python query code to show all the tuples in the database is as follows:

import sqlite3 conn = sqlite3.connect('messaging.db') conn.text_factory = str c = conn.cursor() c.execute('SELECT * FROM messaging') print c.fetchall() conn.close()

Which results in the following output:

The code that I developed to insert these into the database is fairly simple as well, the code is as follows:

#!/usr/bin/python # Proving functionality of insert into database import sqlite3 def insert_it(username, pubkey): conn = sqlite3.connect('messaging.db') c = conn.cursor() c.execute("INSERT INTO messaging VALUES(?, ?)", (username, pubkey)) conn.commit() conn.close() def main(): username = 'James' pubkey = '''-----BEGIN PUBLIC KEY----- MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEArj/abPHwdKnPUZLwBhVC oEDpPQgIHk0R0Lb4vnpXS+SSte/6IrIQ+Es9am5fyAM3rJSQt/kUQuMypuBMvH0O k0spCo32BCx85csAoKiDkeQeilN6j+T1DUHJc/OEkkoE40wdQeskKc75Xq3wAxRY 8wY9OT/PGZ6Fjsk/gjkLYihQBdew6gj6z7l2gtq+xybVy30770Hmi6awoh2IXFCE BZz43XbEdz6BVMUjhS9/81wFgn6EUGqJTjSKO1WnBTIrvKoy91j1ZnDHkvbkHoFL iTq8Q0iF/XgCsQppknyaXXTKgGR+j3HmZg1g8anZsr+Myb1sg2Ee3/dS+h5heVo9 ywIDAQAB -----END PUBLIC KEY-----''' insert_it(username, pubkey) return if __name__ == '__main__': main()

Below is the output after running the code from above, the output is different than the output from above because I made a different program that outputs the tuples in a different way.

The next part of the function of the program would have been to delete from the database, the following code will delete a user from the database based on their username:

#!/usr/bin/python # Proving functionality of deletion from the database based on username import sqlite3 def delete_it(username): conn = sqlite3.connect('messaging.db') c = conn.cursor() c.execute("DELETE FROM messaging WHERE username=(?)", (username,)) conn.commit() conn.close() return def main(): username = 'James' delete_it(username) if __name__ == '__main__': main()

The next piece of code that I felt would be necessary for the program to work would be a way to update your public key, below is the code to do so and a picture of all the steps:

#!/usr/bin/python # Proving functionality of updating a users key based on their username import sqlite3 def update_it(username, pubkey): conn = sqlite3.connect('messaging.db') c = conn.cursor() c.execute("UPDATE messaging SET PubKey = ? WHERE username = ?", (pubkey, username)) conn.commit() conn.close() def main(): username = 'James' pubkey = '-----BEGIN PUBLIC KEY----- MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAuMNuq7LCV2Pt5UnR hZc2pB3TqZM9TERPCmukyISZ7lDnTdJkpcMcsQQzNTa3YtAAb2muEjHXh7Wd zWgv5cPlQjJVtLkpbsZy4ARWay+o2bAJ1U9m8qzYmB49tQ4sfGfA2uO7g3ej XZMFuC5cIF30ySJ6ZMGIaJ3066d6ixxmCLg3e+BjqgDU5NHoiQkDU5pVZf+M Am+7LzVgGrqfSvvzjlSbCbjK0pzMVpW651c1CdlwWxhmV2JWrzNjaWrFao/h eqCRzy5U+WTalsq09j7V7ButYso553ETsUBf4/DZp5YaRnTp3dz5Q4bgjQ4C chDa8lRVXhyRkwP5ALNiBBGGFQIDAQAB -----END PUBLIC KEY-----' update_it(username, pubkey) if __name__ == '__main__': main()

At the end of all of this we decided that it would easier and safer to just use an array with usernames and store the public keys on their machine and read them in. You will find downloads for all the files that I made and used just below.