Musings on Database Security

During the weekend, I stumbled across an interesting project named pysql. The project aims to replace SQL*Plus with a sane shell written in Python with history, tab completion and many extensions. Being a veteran of using SQL*Plus, I know that some of the above can be actually achieved on Linux/Unix environments with SQL*Plus using a handy utility called rlwrap but I was still curios.

The installation is a breeze (as long as you already have Python and cx_Oracle installed correctly). Just unzip into a directory and run pysql.sh. First impression – this is really cool stuff. Out of the box you get coloring, correct terminal handling, help, implemented macros and even visual graphs. The only problem I had was the lack of any documentation (that I could find) about how to use it all. Source code browsing is good and all but I would have preferred an easy how-to document.

Oh, and I stumbled across a bug (that Sébastien Renard told me is already fixed in the next version) that would not allow me to connect “/ as sysdba”. I did a small fix in pysqlshell.py that allowed me to easily fix the issue (just replace these 2 functions):

def __connect(self, connectString, mode=""):
    """Calls the PysqlDb class to connect to Oracle"""
    sid = None
    count=connectString.count("@")
    if count==1:
        (connectString, sid)=connectString.split("@")
    elif count > 1:
        raise PysqlException(_("Invalid connection string"))
    count=connectString.count("/")
    if count==0:
        user=connectString
        try:
            passwd=getpass()
        except (Exception):
            raise PysqlException(_("Invalid connection string"))
    elif count == 1:
        (user, passwd)=connectString.split("/")
    else:
        raise PysqlException(_("Invalid connection string"))
    if sid==None:
        connectString = user + "/" + passwd
    else:
        connectString = user + "/" + passwd + "@" + sid
    self.db=PysqlDb(connectString, mode)
    self.__setPrompt()

def __setPrompt(self, blank=False, multiline=False, finishedQuery=False):
    """Sets the prompt according to the connexion state
    @param blank: if true, no prompt is issue (default is False)
    @param finishedQuery: if true mark prompt with a * to notify a query is finished
    @type blank: bool
    @type finishedQuery: bool"""
    #TODO: do not update title for every line
    codec=self.conf.getCodec()
    if blank or not self.showPrompt:
        prompt=""
    elif multiline:
        prompt="> "
    else:
        if self.db is None:
            prompt=self.notConnectedPrompt
            # Update the title (without color else it is a huge mess)
            setTitle(_("Pysql - Not connected"), codec)
        else:
            sid = self.db.getDSN() if self.db.getDSN() != "None" else os.environ["ORACLE_SID"]
            userName = self.db.getUsername() if self.db.getUsername() != "" else "/"
            prompt=userName+"@"+sid+" "
            if finishedQuery:
                prompt+="* "
            setTitle("Pysql - %s" % prompt, codec)
    self.prompt=prompt.encode(codec, "replace")

This project is definitely on my “keep-an-eye-on” list.
Is anybody else using this?

It’s been a while since I’ve updated my blog. I feel guilty

Lately, I’ve been using a lot of Python to do my Oracle research and I needed a way to do simple selects across multiple versions and platforms from the same IDLE shell. On top of that, I need to connect as SYSDBA. Using cx_Oracle is problematic because I cannot connect directly (not through the listener) to both 64 and 32 bit Oracle.

So, to solve this problem, I came up with a lame implementation of running queries using SQL*Plus and emulating the same interface of cx_Oracle results. I’m sure that many out there did something similar but it was easier to write than to search.

Here it is in all of its glory:

#!/usr/bin/env python
#
# Implement selects using SQL*Plus
# Author:  Slavik Markovich (http://www.sentrigo.com)
# Version: 1.0
# Date:    2008-12-13

import os
import sys
import subprocess

if ‘win’ in sys.platform:
win = True
else:
win = False

class OraSQLPlus(object):
def __init__(self, home, sid):
self.home = home
self.sid = sid
if win:
cmd = ‘sqlplus.exe’
else:
cmd = ‘sqlplus’
self.sqlplus = os.path.join(self.home, ‘bin’, cmd)

def getEnv(self):
env = os.environ
env['ORACLE_HOME'] = self.home
env['ORACLE_SID'] = self.sid
if not win:
env['LD_LIBRARY_PATH'] = os.path.join(self.home, ‘lib’)
return env

def runSelect(self, stmt):
p = subprocess.Popen([self.sqlplus, '-s', '/ as sysdba'],
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE,
env=self.getEnv())
(out, err) = p.communicate(‘set head off ver off lines 200 pages 0 feed off colsep |\n’ + stmt + ‘;\nexit\n’)
# Get lines and strip away the prefix and post-fix of SQL*Plus
lines = out.strip().split(‘\n’)
return [[col.strip() for col in line.split('|')] for line in lines]

def version(self):
res = self.runSelect(‘select banner from v$version’)
return res[0][0].split(‘ ‘)[-3]

Also, you can download the file here