perintah cheat SQL (part 4) "Ingres", "DB2" , "Informix"


(for education only)
Ingres 

Version
select dbmsinfo(’_version’);
Comments
SELECT 123; — comment
select 123; /* comment */
Current User
select dbmsinfo(’session_user’);
select dbmsinfo(’system_user’);
List Users
First connect to iidbdb, then:
select name, password from iiuser;
Create Users
create user testuser with password = ‘testuser’;– priv
List Password Hashes
First connect to iidbdb, then:
select name, password from iiuser;
List Privileges
select dbmsinfo(’db_admin’);
select dbmsinfo(’create_table’);
select dbmsinfo(’create_procedure’);
select dbmsinfo(’security_priv’);
select dbmsinfo(’select_syscat’);
select dbmsinfo(’db_privileges’);
select dbmsinfo(’current_priv_mask’);
List DBA Accounts
TODO
Current Database
select dbmsinfo(’database’);
List Databases
TODO
List Columns
select column_name, column_datatype, table_name, table_owner from iicolumns;
List Tables
select table_name, table_owner from iitables;
select relid, relowner, relloc from iirelation;
select relid, relowner, relloc from iirelation where relowner != ‘$ingres’;
Find Tables From Column Name
TODO
Select Nth Row
Astoundingly, this doesn’t seem to be possible! This is as close as you can get:
select top 10 blah from table;
select first 10 blah form table;
Select Nth Char
select substr(’abc’, 2, 1); — returns ‘b’
Bitwise AND
The function “bit_and” exists, but seems hard to use. Here’s an
example of ANDing 3 and 5 together. The result is a “byte” type
with value \001:
select substr(bit_and(cast(3 as byte), cast(5 as byte)),1,1);
ASCII Value -> Char
TODO
Char -> ASCII Value
TODO
(The “ascii” function exists, but doesn’t seem to do what I’d expect.)
Casting
select cast(123 as varchar);
select cast(’123′ as integer);
String Concatenation
select ‘abc’ || ‘def’;
If Statement
TODO
Case Statement
TODO
Avoiding Quotes
TODO
Time Delay
???
See Heavy Queries article for some ideas.
Make DNS Requests
TODO
Command Execution
TODO
Local File Access
TODO
Hostname, IP Address
TODO
Location of DB files
TODO
Default/System Databases
TODO
Installing Locally
The Ingres database can be downloaded for free from http://esd.ingres.com/
A pre-built Linux-based Ingres Database Server can be download from http://www.vmware.com/appliances/directory/832
Database Client
TODO
There is a client called “sql” which can be used for local connections (at least) in the database server package above.
Logging in from command line
$ su – ingres
$ sql iidbdb
* select dbmsinfo(’_version’); \go
Identifying on the network
TODO

DB2

Version
select versionnumber, version_timestamp from sysibm.sysversions;
Comments
select blah from foo; — comment like this
Current User
select user from sysibm.sysdummy1;
select session_user from sysibm.sysdummy1;
select system_user from sysibm.sysdummy1;
List Users
N/A (I think DB2 uses OS-level user accounts for authentication.)
Database authorities (like roles, I think) can be listed like this:
select grantee from syscat.dbauth;
List Password Hashes
N/A (I think DB2 uses OS-level user accounts for authentication.)
List Privileges
select * from syscat.tabauth; — privs on tables
select * from syscat.dbauth where grantee = current user;
select * from syscat.tabauth where grantee = current user;
List DBA Accounts
TODO
Current Database
select current server from sysibm.sysdummy1;
List Databases
SELECT schemaname FROM syscat.schemata;
List Columns
select name, tbname, coltype from sysibm.syscolumns;
List Tables
select name from sysibm.systables;
Find Tables From Column Name
TODO
Select Nth Row
select name from (SELECT name FROM sysibm.systables order by
name fetch first N+M-1 rows only) sq order by name desc fetch first N rows only;
Select Nth Char
SELECT SUBSTR(’abc’,2,1) FROM sysibm.sysdummy1; — returns b
Bitwise AND
This page seems to indicate that DB2 has no support for bitwise operators!
ASCII Value -> Char
select chr(65) from sysibm.sysdummy1; — returns ‘A’
Char -> ASCII Value
select ascii(’A') from sysibm.sysdummy1; — returns 65
Casting
SELECT cast(’123′ as integer) FROM sysibm.sysdummy1;
SELECT cast(1 as char) FROM sysibm.sysdummy1;
String Concatenation
SELECT ‘a’ concat ‘b’ concat ‘c’ FROM sysibm.sysdummy1; — returns ‘abc’
select ‘a’ || ‘b’ from sysibm.sysdummy1; — returns ‘ab’
If Statement
TODO
Case Statement
TODO
Avoiding Quotes
TODO
Time Delay
???See Heavy Queries article for some ideas.
Make DNS Requests
TODO
Command Execution
TODO
Local File Access
TODO
Hostname, IP Address
TODO
Location of DB files
TODO
Default/System Databases
TODO

Informix

Version
SELECT DBINFO(’version’, ‘full’) FROM systables WHERE tabid = 1;
SELECT DBINFO(’version’, ’server-type’) FROM systables WHERE tabid = 1;
SELECT DBINFO(’version’, ‘major’), DBINFO(’version’, ‘minor’), DBINFO(’version’, ‘level’) FROM systables WHERE tabid = 1;
SELECT DBINFO(’version’, ‘os’) FROM systables WHERE tabid = 1; — T=Windows, U=32 bit app on 32-bit Unix, H=32-bit app running on 64-bit Unix, F=64-bit app running on 64-bit unix
Comments
select 1 FROM systables WHERE tabid = 1; — comment
Current User
SELECT USER FROM systables WHERE tabid = 1;
select CURRENT_ROLE FROM systables WHERE tabid = 1;
List Users
select username, usertype, password from sysusers;
List Password Hashes
TODO
List Privileges
select tabname, grantor, grantee, tabauth FROM systabauth join systables on systables.tabid = systabauth.tabid; — which tables are accessible by which users
select procname, owner, grantor, grantee from sysprocauth join sysprocedures on sysprocauth.procid = sysprocedures.procid; — which procedures are accessible by which users
List DBA Accounts
TODO
Current Database
SELECT DBSERVERNAME FROM systables where tabid = 1; — server name
List Databases
select name, owner from sysdatabases;
List Columns
select tabname, colname, owner, coltype FROM syscolumns join systables on syscolumns.tabid = systables.tabid;
List Tables
select tabname, owner FROM systables;
select tabname, viewtext FROM sysviews join systables on systables.tabid = sysviews.tabid;
List Stored Procedures
select procname, owner FROM sysprocedures;
Find Tables From Column Name
select tabname, colname, owner, coltype FROM syscolumns join systables on syscolumns.tabid = systables.tabid where colname like ‘%pass%’;
Select Nth Row
select first 1 tabid from (select first 10 tabid from systables order by tabid) as sq order by tabid desc; — selects the 10th row
Select Nth Char
SELECT SUBSTRING(’ABCD’ FROM 3 FOR 1) FROM systables where tabid = 1; — returns ‘C’
Bitwise AND
select bitand(6, 1) from systables where tabid = 1; — returns 0
select bitand(6, 2) from systables where tabid = 1; — returns 2
ASCII Value -> Char
TODO
Char -> ASCII Value
select ascii(’A') from systables where tabid = 1;
Casting
select cast(’123′ as integer) from systables where tabid = 1;
select cast(1 as char) from systables where tabid = 1;
String Concatenation
SELECT ‘A’ || ‘B’ FROM systables where tabid = 1; — returns ‘AB’
SELECT concat(’A', ‘B’) FROM systables where tabid = 1; — returns ‘AB’
String Length
SELECT tabname, length(tabname), char_length(tabname), octet_length(tabname) from systables;
If Statement
TODO
Case Statement
select tabid, case when tabid>10 then “High” else ‘Low’ end from systables;
Avoiding Quotes
TODO
Time Delay
TODO
Make DNS Requests
TODO
Command Execution
TODO
Local File Access
TODO
Hostname, IP Address
SELECT DBINFO(’dbhostname’) FROM systables WHERE tabid = 1; — hostname
Location of DB files
TODO
Default/System Databases
These are the system databases:
sysmaster
sysadmin*
sysuser*
sysutils*
* = don’t seem to contain anything / don’t allow reading
Installing Locally
You can download Informix Dynamic Server Express Edition 11.5 Trial for Linux and Windows.
Database Client
There’s a database client SDK available, but I couldn’t get the demo client working.
I used SQuirreL SQL Client Version 2.6.8 after installing the Informix JDBC drivers (”emerge dev-java/jdbc-informix” on Gentoo).
Logging in from command line
If you get local admin rights on a Windows box and have a GUI logon:
  • Click: Start | All Programs | IBM Informix Dynamic Server 11.50 | someservername. This will give you a command prompt with various Environment variables set properly.
  • Run dbaccess.exe from your command prompt. This will bring up a text-based GUI that allows you to browse databases.
The following were set on my test system. This may help if you get command line access, but can’t get a GUI – you’ll need to change “testservername”:
set INFORMIXDIR=C:\PROGRA~1\IBM\IBMINF~1\11.50
set INFORMIXSERVER=testservername
set ONCONFIG=ONCONFIG.testservername
set PATH=C:\PROGRA~1\IBM\IBMINF~1\11.50\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\PROGRA~1\ibm\gsk7\bin;C:\PROGRA~1\ibm\gsk7\lib;C:\Program Files\IBM\Informix\Clien-SDK\bin;C:\Program Files\ibm\gsk7\bin;C:\Program Files\ibm\gsk7\lib
set CLASSPATH=C:\PROGRA~1\IBM\IBMINF~1\11.50\extend\krakatoa\krakatoa.jar;C:\PROGRA~1\IBM\IBMINF~1\11.50\xtend\krakatoa\jdbc.jar;
set DBTEMP=C:\PROGRA~1\IBM\IBMINF~1\11.50\infxtmp
set CLIENT_LOCALE=EN_US.CP1252
set DB_LOCALE=EN_US.8859-1
set SERVER_LOCALE=EN_US.CP1252
set DBLANG=EN_US.CP1252
mode con codepage select=1252
Identifying on the network
My default installation listened on two TCP ports: 9088 and 9099. When I created a new “server name”, this listened on 1526/TCP by default. Nmap 4.76 didn’t identify these ports as Informix:
$ sudo nmap -sS -sV 10.0.0.1 -p- -v –version-all
…
1526/tcp open pdap-np?
9088/tcp open unknown
9089/tcp open unknown
…
TODO How would we identify Informix listening on the network?

di poskan oleh Unknown at Selasa, September 28, 2010
Labels: ,

No Response to "perintah cheat SQL (part 4) "Ingres", "DB2" , "Informix""

Posting Komentar

Supported by Doteasy.com -The Free Web Hosting Provider
Wordpress Theme by Graph Paper Press

Copyright 2010 by Work-a-holic Blogger Template.
Blogger Template by Blogspot Templates