SqlPlus Injection

Despite that at the very first sight it might look stupid you may be hacked by a colleague in a very rude way. Suppose one developer asks you to create a new user for an upcoming system. Because he's a nice guy, he also hands you a simple script which creates this user along with all the required grants. Of course, even you like your colleague and appreciate his effort, you carefully inspect that script before running it. Let's see a preview of this script in a plain vim window:

Oookey! The script has nice comments, nothing unusual... You run it in your sqlplus SYS session and... BANG! your SYSTEM user is compromised and you'll even don't know that. If you still have the WTF face, then look again.
The catch is in the last comment. We used to think that in sqlplus a multiline  comment start with an /* (and because sqlplus is quite picky it has to be further followed by a space or CR) and then, everything till the closing */ is taken as a comment. This assumption is wrong because, in sqlplus, a # at the very beginning of a line means "execute the command on that line". In fact, it doesn't have to be # but this is the symbol configured by default for sqlprefix setting. Just check it out:

SQL> show sqlprefix
sqlprefix "#" (hex 23)

However, we are simply fooled by our editor which, with its nice code highlighting feature, just marked our comments accordingly. Of course, it doesn't know anything about the sqlplus "sqlprefix" setting. So, before running any third-party scripts you should carefully look at them, even at comments.

To DESC or @DESC...

There is one SQLPLUS command which seems to be out of my control as far as its displaying layout is concerned. As you already guess from the post title it's about the well known DESC command. Seems that it has a “self-tuning” layout which takes into consideration the current LINESIZE settings.

For example, using a high value for the LINESIZE setting we obtain:

scott@DDB> set linesize 300
scott@DDB> desc emp
 Name                                                                          
         Null?     Type
 ------------------------------------------------------------------------------
-------------------------------------------------------------------------------
---------------- -------- -----------------------------------------------------
---------------------------------------------------------------
 EMPNO                                                                         
         NOT NULL NUMBER(4)
 ENAME                                                                         
  VARCHAR2(10)
 JOB                                                                           
  VARCHAR2(9)
 MGR                                                                           
  NUMBER(4)
 HIREDATE                                                                      
  DATE
 SAL                                                                           
  NUMBER(7,2)
 COMM                                                                          
  NUMBER(7,2)
 DEPTNO                                                                        
  NUMBER(2)

But using a lower value for the LINESIZE setting the output looks fine:

scott@DDB> set linesize 80
scott@DDB> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

From my point of view this behavior is quite ugly especially when I am working within a console with horizontal scrolling capabilities. Using a high value for LINESIZE allows me to execute various 'SELECT * FROM' without worrying about output wrapping but when DESC command comes in place I will always end up scrolling to the right side till I reach the displaying of the columns type, which is really, really ugly! So, in the end I've decided to create a script which provides the expected nice output. The main idea is to decrease the LINESIZE setting, to execute DESC and then to restore the old LINESIZE setting. Not a big deal! Below is my script:

store set crr_settings.cfg replace
set linesize 80
desc &1
@crr_settings.cfg

Now, all I have to do is to take care to prefix with a @ the DESC command:

scott@DDB> set linesize 300
scott@DDB> @desc emp
Wrote file crr_settings.cfg
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Have fun! :)

SQLPLUS and Custom Languages

Even I'm from Romania, I don't like how the translation of various messages sounds into my own language. However, sometimes it's useful to enable such a feature if, for example, the error messages which come from the Oracle server must be displayed into the client application but using the native language. So, at the very beginning you might think it's enough just to issue an “ALTER SESSION SET NLS_LANGUAGE=ROMANIAN;” but most of the time you'll find that nothing changes. This is because Oracle needs some additional translation files which, of course, must be installed. To install them you'll need the well known Oracle Universal Installer and you have to provide -addLangs as a parameter. You'll be led into a special wizard page like the one shown below:


After choosing and installing the preferred language we can go on and test if now SQLPLUS speaks our lingo.

SQL> ALTER SESSION SET NLS_LANGUAGE=romanian;

Sesiune modificata.

SQL> select * from no_table;
select * from no_table
           *
EROARE la linia 1:
ORA-00942: tabelul sau vizualizarea nu exist?

SQL> select eroare bre;
select eroare bre
             *
EROARE la linia 1:
ORA-00923: cuvantul cheie FROM nu este g?sit in locul a?teptat

So far so good! Nevertheless, as I previously said the translated messages are quite funny. By the way, I already found three translation mistakes into the above output but I will let Romanian readers to find them. Aaaa, I almost forgot to say: those strange question marks are in place there because my database character set is not appropriate for displaying romanian characters therefore please don't count this as a translation error. :)