Creating Cyphers in MySQL

  2021-01-16


I needed to obfuscate data massively in a MySQL database, so I created a cypher function that was a bit more complicated than a simple ROT13.

 1

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

CREATE FUNCTION bastardCypher(stringIn VARCHAR(500)) RETURNS VARCHAR(500)
BEGIN
DECLARE v1 INT DEFAULT 1;
DECLARE stringOut VARCHAR(200) DEFAULT '';
DECLARE str VARCHAR(1) DEFAULT '';

            WHILE v1 <span style="color: #666666">&lt;=</span> <span style="color: #007020; font-weight: bold">LENGTH</span>(stringIn) <span style="color: #007020; font-weight: bold">DO</span>
            <span style="color: #007020; font-weight: bold">SET</span> str <span style="color: #666666">=</span> SUBSTR(stringIn,v1,<span style="color: #40a070">1</span>);
            <span style="color: #007020; font-weight: bold">CASE</span> <span style="color: #007020">BINARY</span> str
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;A&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;T&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;a&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;e&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;B&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;R&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;b&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;i&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;C&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;E&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;c&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;r&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;D&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;I&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;d&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;e&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;E&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;M&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;e&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;m&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;F&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;A&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;f&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;a&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;G&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;K&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;g&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;k&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;H&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;C&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;h&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;c&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;I&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;H&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;i&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;h&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;J&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;U&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;j&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;z&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;K&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;P&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;k&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;p&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;L&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;Y&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;l&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;y&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;M&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;Z&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;m&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;u&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;N&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;L&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;n&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;l&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;O&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;D&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;o&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;d&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;P&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;F&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;p&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;f&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;Q&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;V&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;q&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;v&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;R&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;J&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;r&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;j&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;S&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;G&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;s&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;s&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;T&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;N&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;t&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;n&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;U&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;X&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;u&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;x&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;V&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;W&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;v&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;w&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;W&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;S&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;w&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;g&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;X&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;Q&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;x&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;b&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;Y&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;O&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;y&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;o&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;Z&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;B&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;z&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;q&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;0&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;1&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;1&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;3&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;2&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;2&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;3&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;7&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;4&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;8&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;5&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;0&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;6&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;4&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;7&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;6&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;8&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;5&#39;</span>);
            <span style="color: #007020; font-weight: bold">WHEN</span> <span style="color: #4070a0">&#39;9&#39;</span> <span style="color: #007020; font-weight: bold">THEN</span> <span style="color: #007020; font-weight: bold">SET</span> stringOut <span style="color: #666666">=</span> CONCAT(stringOut , <span style="color: #4070a0">&#39;9&#39;</span>);
            <span style="color: #007020; font-weight: bold">END</span> <span style="color: #007020; font-weight: bold">CASE</span>;

            <span style="color: #007020; font-weight: bold">SET</span> v1 <span style="color: #666666">=</span> v1 <span style="color: #666666">+</span> <span style="color: #40a070">1</span>;

            <span style="color: #007020; font-weight: bold">END</span> WHILE;            

            <span style="color: #007020; font-weight: bold">RETURN</span> stringOut;
            <span style="color: #007020; font-weight: bold">END</span>

Data could then be obfuscated with a simple update:

1
UPDATE footable SET name=bastardCypher(name), address=bastardCypher(address);