How do I correct syntax whilst keeping the same Flyway checksum?-CodePudding

A planned software upgrade causes stricter SQL parsing of Flyway migration scripts. The syntax needs to fixed, but this will change the checksum and fail Flyway's validation. The semantics of the SQL do not change. Is there of making the scripts legal without clumsily repairing databases?

It looks like a 32-bit checksum, so that is unlikely to be secure. Ideally I'd like:

just a few magic printable US ASCII letters in a comment at the top of the file
not require me to give my SQL away
generated by code that I can understand
not need any special hardware or configuration

Does anyone have any cunning techniques?

CodePudding user response：

Great question. Unless the algorithm is designed to be particularly difficult, such as bcrypt, naively zipping through billions of possibilities for the 1 in 2^32 (~4 billion) chance ought to be doable. In fact Flyway munges the script and then applies the well known CRC32 error-detection code (whole process described here).

Whilst an inverse CRC32 function exists, it is much easier to brute force it. The technique also works for cryptographic hashes. Some CPUs have hardware CRC32 acceleration to make this even quicker. Longer files will take longer. If Java had a more extensive API, putting the bodged letters at the end could be used to speed it up.

The code below attempts to find a seven capital letter solution - 26^7 (~8 billion) guesses. Pass the desired checksum as an argument to the program and pipe the source SQL migration script through standard input. For convenience the program will print its calculation of the Flyway checksum for the original file and then, after some time, the first solution it finds without new lines. There may not be any solutions (there isn't one for the exact program itself), in which case try again with a minor change to the file.

java ReverseFlyway.java 16580903 < V42__add_bark.sql

Put the string XXXXXXX in the place where you want the text to be modified.

It is important that the semantics of the SQL do not change. It's unfortunately very easy to chang semantics of the script whilst retaining its checksum. For instance,

-- Robert-DROP TABLE Students;

has the same Flyway checksum as

-- Robert-
DROP TABLE Students;

(Moral: Normalise, don't delete sections.)

Exact details of how Flyway is implemented may change between versions. If you have weird stuff, such as BOMs, something might need to be modified.

If you prefer, the code is easily changed to search for two or three words, a number of spaces and tabs, a limerick, or whatever takes your fancy.

import java.io.*;
import java.util.zip.*;

class ReverseFlyway {
    private final Checksum checksum = new CRC32();
    private final int target;
    private final byte[] data;

    public static void main(String[] args) throws IOException {
        /** /
        new ReverseFlyway("Magic 'XXXXXXX'", Integer.MIN_VALUE);
        /*/
        String text = loadText();
        new ReverseFlyway(text, Integer.parseInt(args[0]));
        /**/
    }
    private ReverseFlyway(String text, int target) {
        this.target = target;
        this.data = text.getBytes();
        System.err.println(checksum());
        int magicLen = 7;
        int place = text.indexOf("X".repeat(magicLen));
        attempt(place, magicLen);
        System.err.println("No solutions found");
        System.exit(1);
    }
    private int checksum() {
        checksum.reset();
        checksum.update(data);
        return (/** /short/*/int/**/) checksum.getValue();
    }
    private void attempt(int place, int remaining) {
        if (remaining == 0) {
            if (target == checksum()) {
                System.out.println(new String(data));
                System.exit(0);
            }
        } else {
            for (byte letter = 'A'; letter <= 'Z';   letter) {
                data[place] = letter;
                attempt(place 1, remaining-1);
            }
        }
    }
    private static String loadText() throws IOException {
        StringBuilder buff = new StringBuilder();
        BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
        for (;;) {
            String line = in.readLine();
            if (line == null) {
                return buff.toString();
            }
            buff.append(line);
        }
    }
}

CodePudding user response：

You can use spoof to tell you have to change the bits in positions you specify in the message to get the desired CRC.

It is monumentally faster than just randomly poking at the message until you get the CRC you want.