SQL*Plus


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?

Sumit Siddarth (Sid) has published an excellent whitepaper talking about hacking Oracle from the web. It shows many types and techniques of SQL injection and how to use an SQL injection vulnerability as a jumping point to extract data, take control of the database and even escape the database to the OS.

Security folks and DBAs out there, this is a must read!

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