CODEWITHSUNDEEP
pythonmysqlsecurity
Anand Rajagopal
Anand Rajagopal

Reputation: 1633

how to secure mysql connection using python

I'm writing python script which uses a mysql db. I want to secure mysql connection and the database will not be accessible to local user . Is there a good way to product this?

#!/usr/bin/python

import MySQLdb

# Open database connection

db = MySQLdb.connect("localhost","username","password","db")


# prepare a cursor object using cursor() method

cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.

cursor.execute("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, userid VARCHAR(30), 
activity_id VARCHAR(30), date_time VARCHAR(30), screenshot_filename VARCHAR(255), screenshot_md5 VARCHAR(255), num_clicks INT, num_of_mouse_movements INT, num_pause INT );")


try:

   # Execute the SQL command

   cursor.execute(sql)

   # Commit your changes in the database

   db.commit()

except:

   # Rollback in case there is any error

   db.rollback()

   # disconnect from server

   db.close()

Upvotes: 2

Views: 7111

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53830

The standard way to do this is to have the web/application server access the database server over a private (local) network, rather than over a public network (Internet).

If the database server is on the same machine as the web/application server, you can host the database server on the loopback IP address (127.0.0.1), which is only directly accessible from the same machine.

Upvotes: 0

Jason Heo
Jason Heo

Reputation: 10236

MySQL supports SSL connection like 'https' (secure connection between Web Server and Web Browser). Client code need to be modified for making connection. This makes your data invisible by other user. a client needs to be modified for making secure connection as follows. excerpted from http://www.mysqlperformanceblog.com/2013/06/22/setting-up-mysql-ssl-and-secure-connections/

[root@centos6 ~]# cat mysql-ssl.py
#!/usr/bin/env python
import MySQLdb
ssl = {‘cert’: ‘/etc/mysql-ssl/client-cert.pem’, ‘key’: ‘/etc/mysql-ssl/client-key.pem’}
conn = MySQLdb.connect(host=’127.0.0.1′, user=’ssluser’, passwd=’pass’, ssl=ssl)
cursor = conn.cursor()
cursor.execute(‘SHOW STATUS like “Ssl_cipher”‘)
print cursor.fetchone()

Upvotes: 3

Related Questions