Shepherd's DC21 schemaverse entry

DC21_Shepherd_Fleet_Script

--
-- Schemaverse Fleet Script for DefCon 21 (2013)
-- @author shepherd <chand@chandeeland.org>
--
-- Exploiting a missing constraint on insert/update to ship rules
--

UPDATE my_fleets SET
script_declarations = '
    num_miners integer;
    num_attackers integer;

    money bigint;
    up_amt integer;

    max_ship_skill integer;
    m_skill_price integer;
    a_skill_price integer;
    ship_price integer;

    sample_ship integer;
',
script = '
    SELECT INTO max_ship_skill get_numeric_variable(''MAX_SHIP_SKILL'');
    SELECT INTO m_skill_price cost FROM price_list WHERE code = ''PROSPECTING'';
    SELECT INTO a_skill_price cost FROM price_list WHERE code = ''ATTACK'';
    SELECT INTO ship_price cost FROM price_list WHERE code = ''SHIP'';

    SELECT count(*) INTO num_miners FROM my_ships WHERE name = ''M'';
    SELECT count(*) INTO num_attackers FROM my_ships WHERE name = ''A'';

    PERFORM CONVERT_RESOURCE(''FUEL'', fuel_reserve) FROM my_player;

    -- build 200 miners
    SELECT balance INTO money FROM my_player;
    WHILE num_miners < 200 AND money > ship_price LOOP

        --
        -- here is the hack, i am inserting an absurd range value.
        --
        INSERT INTO my_ships (range, name, attack, prospecting, engineering, defense) 
            VALUES (2147483640, ''M'', 0, 20, 0, 0);
        
        num_miners := num_miners + 1;
        money := money - ((max_ship_skill * m_skill_price) + ship_price);
    END LOOP;
    -- upgrade miners
    SELECT balance INTO money FROM my_player;
    SELECT INTO up_amt LEAST(max_ship_skill, FLOOR(money/m_skill_price));    
    PERFORM UPGRADE(id, ''PROSPECTING'', up_amt - prospecting) 
        FROM my_ships
        WHERE name = ''M'' AND prospecting < up_amt;

    -- build attackers
    SELECT balance INTO money FROM my_player;
    WHILE num_attackers < 1801 AND money > ship_price LOOP
        INSERT INTO my_ships (range, name, attack, prospecting, engineering, defense) 
            VALUES (2147483640, ''A'', 10, 10, 0, 0);
        
        num_attackers := num_attackers + 1;
        money := money - ((150 * a_skill_price) + ship_price);
    END LOOP;

    -- upgrade attackers
    IF num_attackers < 1800 THEN
        SELECT balance INTO money FROM my_player;
        PERFORM UPGRADE(id, ''ATTACK'', 150 - attack) 
            FROM my_ships
            WHERE name = ''A'' AND prospecting < 150;
    ELSE
        SELECT balance INTO money FROM my_player;
        PERFORM UPGRADE(id, ''ATTACK'', max_ship_skill - 10 - attack) 
            FROM my_ships
            WHERE name = ''A'' AND attack < (max_ship_skill - 10);
    -- mine
    PERFORM MINE(ship.id, target.id)
    FROM (
        SELECT id , row_number() OVER ()
        FROM (
            SELECT p.id 
            FROM my_player me, planets p 
            WHERE p.conqueror_id != me.id OR p.conqueror_id IS NULL
            ORDER BY conqueror_id DESC -- , POINT(location::text)<->POINT(0,0) ASC
            LIMIT num_miners
        ) foo
    ) target
    JOIN (
        SELECT id, row_number() OVER()
        FROM my_ships
        WHERE name = ''M''
    ) ship USING (row_number);

    IF num_attackers > 0 THEN
        -- ATTACK

        SELECT INTO sample_ship id FROM my_ships limit 1;

        PERFORM ATTACK(ship.id, target.id)
        FROM (
            SELECT id, row_number() OVER (ORDER BY player_id DESC) -- POINT(enemy_location::text)<->POINT(0,0) ASC) 
            FROM ships_in_range 
            WHERE ship_in_range_of = sample_ship
                AND health > 0
             ORDER BY player_id DESC
            LIMIT num_attackers
        ) target
        JOIN (
            SELECT id, row_number() OVER()
            FROM my_ships
            WHERE name = ''A''
        ) ship USING (row_number);
    
        -- mine when no ships to fight
        PERFORM MINE(ship.id, target.id)
        FROM(
            SELECT id , row_number() OVER ()
            FROM (
                SELECT p.id 
                FROM my_player me, planets p 
                WHERE p.conqueror_id != me.id OR p.conqueror_id IS NULL
                ORDER BY conqueror_id DESC
                LIMIT num_attackers
            ) foo
        ) target
        JOIN (
            SELECT id, row_number() OVER()
            FROM my_ships, tic_seq
            -- WHERE name = ''A''
            WHERE last_action_tic <> last_value
            order by name desc
        ) ship USING (row_number);
    END IF;

   PERFORM SHIP_COURSE_CONTROL(id, max_speed, null, POINT(-10000000,-10000000)) FROM my_ships;
    --
    -- another cheat!
    --
   UPDATE my_ships SET target_speed = 100000000;
   PERFORM CONVERT_RESOURCE(''MONEY'', balance) FROM my_player;
   PERFORM RE


' WHERE name = 'hack';
FUEL_SHIP(id) FROM my_ships;