Security through obscurity - object names
This post was inspired by question in one of Latvian forums - can we use only numbers in column names. Of course we can do it using quotes but that's starting point into dangerous path of how to write unmaintainable code. But why stop only with tables like C34 and columns 1, 2, 3? There are ways beyond that!
So let's start with the same table names differing only in upper/lower case.
SQL> create table A (big number);
Table created.
SQL> create table "a" (small number);
Table created.
So what are our tables A and a:
SQL> desc A
Name Null? Type
----------------------- -------- -------
BIG NUMBER
SQL> desc a
Name Null? Type
----------------------- -------- -------
BIG NUMBER
A bit strange, isn't it? But that is because all nonquoted identifiers are converted to uppercase so we have to be precise:
SQL> desc "a"
Name Null? Type
----------------------- -------- ------
SMALL NUMBER
But we can make things even more obscure! For example how about table and/or column name as space?
SQL> create table " " (A number, "a" number);
Table created.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
a
A
3 rows selected.
Previous example with 2 rows instead of reported three and next with lost columns definitely could make some nervous people start searching for Oracle bugs.
SQL> create table b (" " number, " " date);
Table created.
SQL> desc b
Name Null? Type
----------------------- -------- ------
NUMBER
DATEBut we can make things better. Why should we use only symbols on keybord? Remember the nice possibility of ALT key and numeric keypad keys. ALT + these keys gives us symbols according to their ASCII values, so just use your imagination. For example ALT+223 gives me this:
SQL> create table abnormal ("▀" number);
Table created.
SQL> desc abnormal
Name Null? Type
----------------------- -------- -------
▀ NUMBERNice little black square.
Next level might be making some columns a bit like hidden.
SQL> create table persons (name varchar2(10), "SYSDATE" date);
Table created.
SQL> insert into persons values ('John', sysdate-1);
1 row created.
SQL> select sysdate from dual;
SYSDATE
---------
31-JAN-09
SQL> select name, sysdate from persons;
NAME SYSDATE
---------- ---------
John 31-JAN-09
So where our column sysdate was gone? We inserted value sysdate-1 and got back the same sysdate? Although our column was in upper case, so our query column should be used? Naah, we should enclose it in quotes.
SQL> select name, "SYSDATE" from persons;
NAME SYSDATE
---------- ---------
John 30-JAN-09
So next time when you are starting to think about table name like C12, numeric column names, column names in quotes or something like that, don't be shy! Use the complete power of obscurity, so making your db more secure. If you'd try hard enough, you'd reach perfection when obscurity is so great that nobody, even you, won't be able to understand anything ;)
And I wasn't even suggesting to replace DUAL in your schema ;)
