sql_syntax_reference
SQL Syntax Reference
This will cover some general SQL syntax for Oracle, MS-SQL, and MYSQL.
| Requirement | ASCII |
|---|---|
| Oracle: | ASCII('A') is equal to 65 |
| MS-SQL: | ASCII('A') is equal to 65 |
| MYSQL: | ASCII('A') is equal to 65 |
| Requirement | SUBSTRING |
|---|---|
| Oracle: | SUBSTR('ABCDE',2,3) is equal to BCD |
| MS-SQL: | SUBSTR('ABCDE',2,3) is equal to BCD |
| MYSQL: | SUBSTR('ABCDE',2,3) is equal to BCD |
| Requirement | Retrieve current database user |
|---|---|
| Oracle: | SELECT Sys.login_user from dual |
| Oracle: | SELECT user from dual |
| Oracle: | SYS CONTEXT('USERENV', 'SESSION USER') |
| MS-SQL: | select user |
| MS-SQL: | select suser sname() |
| MYSQL: | SELECT user() |
| Requirement | Cause a time delay |
|---|---|
| Oracle: | Utl_Http.request('http://madeupserver.com') |
| MS-SQL: | waitfor delay '0:0:10' |
| MS-SQL: | exec master..xp_cmdshell 'ping localhost' |
| MYSQL: | benchmark(50000, sha1('test') |
| Requirement | Retrieve database version string |
|---|---|
| Oracle: | select banner from v$version |
| MS-SQL: | select @@version |
| MYSQL: | select @@version |
| Requirement | Retrieve current database |
|---|---|
| Oracle: | SYS_CONTEXT('USERENV', 'DB_NAME') |
| MS-SQL: | select db_name() |
| MS-SQL: | The server name can be retrieved using: select @@servername |
| MYSQL: | select database() |
| Requirement | Retrieve current user's privileges |
|---|---|
| Oracle: | select * from session_privs |
| MS-SQL: | select grantee, table_name, privilege_type from INFORMATION SCHEMA.TABLE PRIVILEGES |
| MYSQL: | SHOW GRANTS FOR CURRENT_USER() |
| Requirement | Show user objects |
|---|---|
| Oracle: | SELECT object_name, object_type from user_objects |
| MS-SQL: | SELECT * from sysobjects |
| MYSQL: | There is no database metadata in MySQL |
| Requirement | Show user tables |
|---|---|
| Oracle: | select object_name, object_type, from user_objects WHERE object_type='TABLE' |
| Oracle: | To show all tables to which the user has access: SELECT table FROM all tables |
| MS-SQL: | SELECT * FROM sysobjects WHERE xtype='U' |
| MYSQL: | There is no database metadata in MySQL |
| Requirement | Show column names for table foo |
|---|---|
| Oracle: | SELECT column_name, Name from user_tab_columns where table_name = 'FOO' Use the ALL_TAB_COLUMNS table if the target data is not owned by the current application user. |
| MS-SQL: | SELECT syscolumns.* FROM syscolumns JOIN sysobjects ON syscolumns.id=sysobjects.id WHERE sysobjects.name='FOO' |
| MYSQL: | show columns from foo |
| Requirement | Interact with the operating system (simplest ways) |
|---|---|
| Oracle: | See the Oracle Hacker's Handbook, by David Litchfield |
| MS-SQL: | exec xp_cmdshell 'dir c:\' |
| MYSQL: | select load_file('/etc/passwd') |
