PHP functions adapted to Mysql

Question

When trying to optimize some darlings not to depend on PHP to do something that could come ready from the database, I was trying to adapt the two routines below in the form of functions mysql:

function encode( $input, $index ) {

    $base    = strlen( $index );
    $input  += pow( $base, 4 );
    $output  = '';

    for( $i = floor( log( $input, $base ) ); $i >= 0; $i-- ) {

        $bcp     = bcpow( $base, $i );
        $start   = floor( $input / $bcp ) % $base;
        $output .= substr( $index, $start, 1 );
        $input   = $input - ( $start * $bcp );
    }

    return $output;
}

And:

function decode( $input, $index ) {

    $base   = strlen( $index );
    $output = 0;
    $length = strlen( $input ) - 1;

    for( $i = 0; $i <= $length; $i++ ) {

        $bcpow   =  bcpow( $base, $length - $i );

        $output += strpos( $index, substr( $input, $i, 1 ) ) * $bcpow;
    }

    $output -= pow( $base, 4 );

    $output = sprintf( '%F', $output );
    $output = substr( $output, 0, strpos( $output, '.' ) );

    return (int) $output;
}

Are functions that encode a integer in a string and vice versa, a Alphaid, almost like the one on Youtube.

As until the present time I had never created a Function from Mysql, I read the manual (which is not exactly user friendly) and arrived, at least as to Function coding, therein:

DROP FUNCTION IF EXISTS ENCODE_ALPHAID;

DELIMITER $$

CREATE FUNCTION ENCODE_ALPHAID( input integer ) RETURNS CHAR( 6 ) DETERMINISTIC

BEGIN

    DECLARE output CHAR( 6 );

    DECLARE letters CHAR( 62 );
    DECLARE base TINYINT( 2 );

    DECLARE iterator TINYINT( 2 );
    DECLARE bcp CHAR( 9 );
    DECLARE start TINYINT( 2 );

    SET output   = '';

    SET letters  = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    SET base     = CHAR_LENGTH( letters );
    SET input    = input + POW( base, 4 );

    SET iterator = FLOOR( LOG( base, input ) );

    ENCODING: LOOP

        SET bcp      = POW( base, iterator );
        SET start    = FLOOR( input / bcp ) % base + 1;

        SET output   = CONCAT( output, SUBSTR( letters, start, 1 ) );

        SET input    = input - ( start * bcp );

        SET iterator = iterator-1;

        IF iterator < 0 THEN LEAVE ENCODING; END IF;

    END LOOP ENCODING;

    RETURN output;

END $$

DELIMITER ;

The routine was successfully created but has some problems:

1. The original PHP routine codes correctly to the maximum possible value, described by the constant PHP_INT_MAX (2147483647), but the encoding for Mysql does not. At some point, which I could not fully debug, the encoding apparently stops, and the darlings do not return data.
2. As far as coding works, it always acts "one forward". For example, codify the whole 2, instead of returning to the expected baaac is returning baaad

As for the decoding function:

DROP FUNCTION IF EXISTS DECODE_ALPHAID;

DELIMITER $$

CREATE FUNCTION DECODE_ALPHAID( input integer ) RETURNS INTEGER( 10 ) DETERMINISTIC

BEGIN

    DECLARE output INT( 10 );

    DECLARE letters CHAR( 62 );
    DECLARE base TINYINT( 1 );
    DECLARE length TINYINT( 1 );

    DECLARE iterator TINYINT( 1 );
    DECLARE bcp CHAR( 9 );
    DECLARE dot CHAR( 1 );

    SET output   = 0;

    SET letters  = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    SET base     = CHAR_LENGTH( letters );
    SET length   = CHAR_LENGTH( input ) - 1;

    SET iterator = 0;
    SET dot = '.';

    ENCODING: LOOP

        SET bcp      = POW( base, length - iterator );

        SET output   = output + LOCATE( SUBSTR( input, iterator, 1 ), letters ) * bcp;

        SET iterator = iterator+1;

        IF iterator <= length THEN LEAVE ENCODING; END IF;

    END LOOP ENCODING;

    SET output = ( output - POW( base, 4 ) ) + 0.0;

    SET output = SUBSTR( output, 0, LOCATE( dot, output ) );

    RETURN output;

END $$

DELIMITER ;

It doesn’t even decode and, I believe, it’s by the way that the original author of the functions in PHP operated the results, formatting the mathematical calculus to float, operating as string and converting back to integer I lacked the knowledge to adapt correctly.

Answer 1

To record you can do the following:

INSERT INTO tabela (id, content)
VALUES (1, AES_ENCRYPT('seu conteúdo aqui', 'senha123_hash'));

And then recover like this:

SELECT CAST(AES_DECRYPT(content, 'senha123_hash') as CHAR) AS content_decrypted,
       content as content_encrypt
FROM tabela WHERE id=1;

In this case, you need to generate a password alpha_id you save both the encrypted content and the encrypted password in this format, and then in the query you use the hash alpha_id to search the content, example:

INSERT INTO tabela (id_tst, content, alpha_id)
VALUES (6, AES_ENCRYPT('seu conteúdo aqui', SHA2(MD5('senha123_hash'),512)),SHA2(MD5('senha123_hash'),512));

SELECT CAST(AES_DECRYPT(content, SHA2(MD5('senha123_hash'),512)  ) as CHAR) AS content_decrypted,
       content as content_encrypt,
       alpha_id
FROM tabela WHERE alpha_id='ea921eccc6b836f894710943da3b5a9ff546cb775e4d7c9e00a892d313165967e8a7eb2ebe105c8f1a0fcc74e47a77a2e901c8b450baa8de5f4a5a13b83c4415';

Or with 256:

 INSERT INTO tabela (id_tst, content, alpha_id)
 VALUES (6, AES_ENCRYPT('seu conteúdo aqui', SHA2(MD5('senha123_hash'),256)),SHA2(MD5('senha123_hash'),256));

 SELECT CAST(AES_DECRYPT(content, SHA2(MD5('senha123_hash'),256)  ) as CHAR) AS content_decrypted,
        content as content_encrypt,
        alpha_id
 FROM tabela WHERE alpha_id='069b8392f647410845de6b9d796b6c2e0a628194682bb2c3e5ec8b978e5787c9';

If you don’t make a point of encrypting your content in the database, you can also generate an alpha_id, just by converting the output to the hash:

SELECT content, CONCAT(CAST(content as CHAR), SHA2(id, 256)) AS alpha_id
FROM tabela WHERE id=1