PostgreSQL Time Based

Context

This article focuses on exploiting PostgreSQL time-based SQL injection to extract sensitive data from a database. The reader should have a solid understanding of SQL syntax, HTTP requests, and general database interaction. This technique leverages time delays to infer information about the database's structure and content without needing direct data output.

Theory

Time-Based SQL Injection in PostgreSQL

Time-based SQL injection is an attack technique that exploits time delays to infer information from a database. It does not rely on visible error messages or direct data output. Instead, this method exploits conditional delays to determine true or false conditions. The attack sequence involves injecting SQL queries with time delay functions to observe variations in server response times.

Key Functions for Time-Based Attacks

• pg_sleep(seconds): This function delays the execution of a SQL query for a specified number of seconds. It is crucial for executing time-based attacks as it helps create measurable delays on the server side based on logical conditions.

• clock_timestamp(): Returns the current timestamp and is used for timing attacks to help determine the exact point at which a condition was met or the delay was triggered.

Timing Attacks and Response Analysis

The core principle behind timing attacks is to use the application's response time to infer how the database responds to specific injected queries. By injecting conditional logic with pg_sleep(), attackers can test true and false conditions, extracting valuable information through careful analysis of response times.

Practice

Extracting Data via Time-Based SQL Injection

This technique involves manually injecting PostgreSQL SQL queries that use time-based functions to extract sensitive information based on server response times. Here are the typical steps involved:

• Verify User Existence:

  Inject a delay based on the presence of an admin user in the database:

  SELECT CASE WHEN (SELECT COUNT(*) FROM users WHERE username='admin') > 0 THEN pg_sleep(5) ELSE pg_sleep(0) END;
  

  If the server's response time is delayed, it indicates the admin user exists.

• Extract Character from Password:

  Check if a specific character in the admin's password matches a guess:

  SELECT CASE WHEN (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin')='a' THEN pg_sleep(5) ELSE pg_sleep(0) END;
  

  A delay suggests that the first character of the password is 'a'.

• Determine Password Length:

  Discover the length of the admin's password by observing response delays:

  SELECT CASE WHEN (SELECT LENGTH(password) FROM users WHERE username='admin')=8 THEN pg_sleep(5) ELSE pg_sleep(0) END;
  

  If the response is delayed, it indicates that the password is 8 characters long.

These steps allow the attacker to exfiltrate data by probing the database with conditional queries and analyzing the differences in response times to infer true/false conditions.

Tools

• sqlmap
• Burp Suite

These tools can automate the process of identifying and exploiting time-based SQL injection vulnerabilities.