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') |