sleep function alternative for time-based sql injection

when we have an sql-injection, but don’t get to see the result of the query or any other reaction from the server, time-based exfiltration is the way to go. it’s standard procedure to use the database’s respective delay function like pg_sleep to trigger a delay in a response to determine the output of an arbitrary query

SELECT 
  CASE 
    WHEN (substr(token,1,1)='a') 
    THEN pg_sleep(10) ELSE pg_sleep(0) 
  END 
FROM password_reset_tokens

in one of my engagements i was faced with an interesting challenge - the application blocked the sleep function with no way of bypassing the filters. yet still it was possible to extract the database.

in this article i will demonstrate how to perform time-based blind exfiltration in postgres if the sleep function is not available.

why sleep is good for you

to search for a valuable substitute for the pg_sleep() function, we need to take a step back and reason why it is good in the first place.

low noise

first of all it is consistent

when we call pg_sleep(3) it really does wait for three seconds before resuming the execution - it’s literally what the function is defined to do. we get our response typically after little more than three seconds due to network and processing latency, but that is fine.

import time
import requests

start = time.time()
requests.get("https://target.com?u=';select+pg_sleep(3);--")
end = time.time()

if end-start >= 3:
     print("SQL Injection success!")

we can assume that the function will never terminate before the three seconds are over, and not earlier.

low resources

it does not use up too many resources

it is not computationally heavy and, as we see, uses only 6kb of memory. this result shows that it is safe to call in parallel without a risk for crippling the server’s availability.

possible candidates

we established what makes the pg_sleep() function great for triggering delays - its accurate and won’t kill our target server. let’s see what else fulfills these requirements

repeat + hash

in my engagement i went on with using the repeat and hash functions. my payload looked somewhat like this:

select md5(repeat('a',10000000))

it caused a notable delay in the execution and allowed me to extract some data from the database.

noise

to see how stable the delay is, i called this function repeatedly and measured the computation time using a simple pgsql loop

FOR i IN 1..100 LOOP
  start_time := clock_timestamp();
  PERFORM md5(repeat(random()::text,10000000)); -- random to prevent caching
  end_time := clock_timestamp();

  INSERT INTO hash_measurements (x, computation_duration)
  VALUES (i, end_time - start_time);
END LOOP;

and then i visualized the computation duration using the pgadmin’s built in graphing function

as we can see, the computation duration in milliseconds (y axis) is relatively stable throughout the runs (x axis). its coefficient of variation is 0.09 which is good - it means that the duration typically varies around 9% from the average.

some noise is expected due to system load, however we can see that none of the runs took less than 300ms, which is good enough for our oracle.

cost

when measuring the cost and ram usage of this construct i noticed that it uses a almost 100mb of ram

the memory is probably used to allocate the intermediate string generated by the repeat function. while 100mb is not devastating on its own, it might look different when the target server is under load or we parallelize our exfiltration exploit.

luckily there are better alternatives

factorial

while looking for potential candidates within postgres’ built-in functions, i found that it offers many math functions out of the box. my idea was to trigger a heavy computation which would not oom-kill the server if performed in parallel.

select factorial(20000);

for this writeup i chose factorial .

noise

i repeated the same experiment as i did with the repeat + hash function.

as we see, the computation time is again relatively stable with a coefficient of variance being 0.08.

for this particular function i went a bit deeper than with repeat + hash and analysed how input sizes (x axis) affect the execution time (y axis).

since the computation of a factorial can be optimized with dynamic programming, there was a possibility that postgres caches the intermediary steps of the calculation resulting in higher values being calculated faster. this is not the case, so we have a possible delay function candidate

i also calculated the coefficient of variation throughout the different values i tested which showed a very constant coefficient of around 10%.

cost

the factorial function in postgres does not use as many system resources as the repeat + hash method does

the ram usage is practically negligible and can be safely used in parallel. this makes the function a good candidate for a pg_sleep alternative

value limitation

the factorial function is limited by the size of the bigint datatype. if we try to calculate the factorial of any number greater than 32178, we get an error.

this could be problematic if we are attacking a particularly powerful server which performs the calculation so fast that we cannot trigger any measurable delay. then the function becomes useless for us.

we can scale the delay of this function by simply calling it multiple times in the row. there is no risk of overflowing by adding the factorial results, unless we chain the addition 32178 times in a row.

even then, we can simply multiply each argument by 0 and we’re good to go.

big problem - eager evaluation

after discovering factorial as a suitable candidate i tested it by using a time-based exfiltration query and simply replacing the pg_sleep function.

the query performed as expected at first and triggered a delay on a true condition

however, when i changed the condition from a simple 1=1 into a condition which was based on the outcome of the original query, postgres behaved unexpectadly.

even thou length(name)=1 was a false statement, and the query returned factorial(1)=1, it still caused a delay in execution.

apparently postgres performed eager evaluation on the constant expressions in the branches while retrieving data from the database. this is also true for repeat+hash.

this breaks the entire premise of out sleep function replacement - it does not work if the delay is triggered regardless of our condition.

luckily this can be mitigated by making the branch expression, i.e. our timing oracle, not constant. we can simply add a random integer, which in this case is always either 0 or 1, and the setup works as expected.

proof

to demonstrate that the factorial function is indeed a suitable alternative for pg_sleep i tired solving the portswigger’s blind SQL injection with time delays and information retrieval lab using the factorial function

as we see, using the factorial function with a random integer, we can trigger a conditional delay which allows us to extract the length of the administator’s password.

then by iterating over all characters of the password, we can eventually deduce the full administrator password without using the pg_sleep function

conclusion

the goal of time-based exfiltration is to cause a delay on an attacker-controlled condition. usually, the pg_sleep function is used to that, since it was designed for creating delays.

if that function is for whatever reason not available, we can use other functions which can generate server-side delays. by triggering heavy computations, which ideally won’t oom-kill the target, we can replace pg_sleep by factorial

due to eager evaluation of constant expressions in postgres, our new functions can’t be dropped as-they-are and have to be somewhat dynamic. adding a call to random() is sufficient.