|
I'm not planning to write one for MS Access, but there's a great MS Access Cheat Sheet here.
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;
|
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
|
The following areas are interesting enough to include on this page, but I haven't researched them for other databases:
| Description |
SQL / Comments |
Batching Queries Allowed?
|
Not via DBI in PERL. Subsequent statements seem to get ignored:
select blah from table where foo = 1; select ... doesn't matter this is ignored. |
| FROM clause mandated in SELECTs? |
No. You don't need to select form "dual" or anything. The following is legal:
select 1; |
UNION supported
|
Yes. Nothing tricky here. The following is legal:
select 1 union select 2; |
Enumerate Tables Privs
|
select table_name, permit_user, permit_type from iiaccess; |
| Length of a string |
select length('abc'); -- returns 3
|
Roles and passwords
|
First you need to connect to iidbdb, then:
select roleid, rolepass from iirole; |
List Database Procedures
|
First you need to connect to iidbdb, then:
select dbp_name, dbp_owner from iiprocedure; |
| Create Users + Granting Privs |
First you need to connect to iidbdb, then:
create user pm with password = 'password';
grant all on current installation to pm; |
|
