Functions of PHP adapted to MySQL

4

When trying to optimize some queries to not depend on PHP to do something that could come ready from the database, I was trying to adapt the two routines below into functions > from 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;
}

E:

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;
}

They are functions that encode an integer into a string and vice versa, an AlphaID , almost like YouTube. >

As I have never created a MySQL function , I have read the manual (which is not exactly user friendly) and I have arrived, at least as far as function from coding, in this:

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 created successfully but has some problems:

  • The original routine in PHP correctly encodes up to the maximum possible value, described by the constant PHP_INT_MAX (2147483647), however the adpaction to MySQL does not. At some point, which I could not debug in depth, the encoding apparently stops, and queries do not return data.
  • As far as coding works, it always acts "forward". For example, encoding the entire 2 , instead of returning the expected baaac strong> 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 even decodes and, I believe, by the way original author of the functions in PHP operated the results, formatting the mathematical calculation for float , operating as string and converting back to integer that I lacked knowledge in adapt correctly.

        
    asked by anonymous 14.07.2016 / 17:12

    1 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, if you need to generate a alpha_id password , you will save both encrypted content and the encrypted password in this format, and then in the query you use the alpha_id to fetch the content, eg

    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 do not want to encrypt 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
    
        
    14.07.2016 / 23:49