B4J Question MySql Data Encryption with jRDC2

[Nickle548]

I have Used jRDC2 on MySql and it works very fine and easy to use but I have a new Project which requires that Some Fields in a Table be encrypted like using JWT in PHP. I am able to do that in PHP but want to use jRDC for this project. Any ideas or advice on how to get same done with jRDC2

 

[DonManfred]

Can you provide more information on what exactly you are using in PHP? Some php-code showing the use of JWT?

 

[Nickle548]

Find PHP Code. It calls the encrypt/Decrypt functions

<?php
/*
* database connection
*/
$localhost='localhost';
$username='root';
$password='';
$db='voucherdistribution';
$table = 'users';

$link = mysqli_connect($localhost, $username, $password, $db);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

mysqli_query($link, "SET CHARACTER SET utf8");
mysqli_query($link,"SET NAMES 'utf8'");
$phone = ($_GET["phone"]);
$password = ($_GET["password"]);

    $res_set = mysqli_query($link, "SELECT * FROM $table WHERE Phone = '$phone'");

    if(!$res_set) {
        die('Could not retrieve data '.mysqli_error($link));
    }   
    while($row = mysqli_fetch_array($res_set,MYSQLI_ASSOC)) {

        // /*
        // * decode PIN
        // */
        if(!empty($row['encrypted_password'])) {
            $pin = JWT::decode($row['encrypted_password'], $row['salt']);
            if(!$pin) die('INVALID TOKEN');
            $res_arr = array();
            $res_arr['username']=$row['username'];
            $res_arr['phone'] = $row['phone'];
            $res_arr['created_at'] = $row['created_at']; 
            $res_arr['pin'] = $pin[0]->pin;
            $NewPin = $pin[0]->pin; //encoded Pin
           
            // Verify Pin Quality   
            if ($NewPin == $password) { //Password is correct
                $res_arr['error'] = 'TRUE';     
            } 
            else  { //Password is wrong
               
                $res_arr['error'] = 'FALSE';
            }
            print json_encode([$res_arr]);

        }
    }
    mysqli_close($link);


/**
 * UUID Class
 *
 * This implements the abilities to create UUID's for CodeIgniter.
 * Code has been borrowed from the followinf comments on php.net
 * and has been optimized for CodeIgniter use.
 * http://www.php.net/manual/en/function.uniqid.php#94959
 *
 * @category Libraries
 * @author Dan Storm
 * @link http://catalystcode.net/
 * @license GNU LPGL
 * @version 2.1 
 */
class Uuid 
{
    public function v4($trim = false) 
    {
       
        $format = ($trim == false) ? '%04x-%04x' : '%04x%04x';
       
        return sprintf($format,

            // 32 bits for "time_low"
            mt_rand(0, 0xffff), mt_rand(0, 0xffff),

            // 16 bits for "time_mid"
            mt_rand(0, 0xffff),

            // 16 bits for "time_hi_and_version",
            // four most significant bits holds version number 4
            mt_rand(0, 0x0fff) | 0x4000,

            // 16 bits, 8 bits for "clk_seq_hi_res",
            // 8 bits for "clk_seq_low",
            // two most significant bits holds zero and one for variant DCE1.1
            mt_rand(0, 0x3fff) | 0x8000,

            // 48 bits for "node"
            mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
        );
    }

    public function is_valid($uuid) 
    {
    return preg_match('/^\{?[0-9a-f]{8}\-?[0-9a-f]{4}\-?[0-9a-f]{4}\-?[0-9a-f]{4}\-?[0-9a-f]{12}\}?$/i', $uuid) === 1;
    }
}


/**
 * JSON Web Token implementation, based on this spec:
 * http://tools.ietf.org/html/draft-ietf-oauth-json-web-token-06
 *
 * PHP version 5
 *
 * @category Authentication
 * @package  Authentication_JWT
 * @author   Neuman Vong <[email protected]>
 * @author   Anant Narayanan <[email protected]>
 * @license  http://opensource.org/licenses/BSD-3-Clause 3-clause BSD
 * @link     https://github.com/firebase/php-jwt
 */
class JWT
{
    /**
     * Decodes a JWT string into a PHP object.
     *
     * @param string      $jwt    The JWT
     * @param string|null $key    The secret key
     * @param bool        $verify Don't skip verification process 
     *
     * @return object      The JWT's payload as a PHP object
     * @throws UnexpectedValueException Provided JWT was invalid
     * @throws DomainException          Algorithm was not provided
     * 
     * @uses jsonDecode
     * @uses urlsafeB64Decode
     */
    public static function decode($jwt, $key = null, $verify = true)
    {
        $tks = explode('.', $jwt);
        if (count($tks) != 3) {
            throw new UnexpectedValueException('Wrong number of segments');
        }
        list($headb64, $bodyb64, $cryptob64) = $tks;
        if (null === ($header = JWT::jsonDecode(JWT::urlsafeB64Decode($headb64)))) {
            throw new UnexpectedValueException('Invalid segment encoding');
        }
        if (null === $payload = JWT::jsonDecode(JWT::urlsafeB64Decode($bodyb64))) {
            throw new UnexpectedValueException('Invalid segment encoding');
        }
        $sig = JWT::urlsafeB64Decode($cryptob64);
        if ($verify) {
            if (empty($header->alg)) {
                throw new DomainException('Empty algorithm');
            }
            if ($sig != JWT::sign("$headb64.$bodyb64", $key, $header->alg)) {
                return false;
                // throw new UnexpectedValueException('Signature verification failed');
            }
        }
        return $payload;
    }

    /**
     * Converts and signs a PHP object or array into a JWT string.
     *
     * @param object|array $payload PHP object or array
     * @param string       $key     The secret key
     * @param string       $algo    The signing algorithm. Supported
     *                              algorithms are 'HS256', 'HS384' and 'HS512'
     *
     * @return string      A signed JWT
     * @uses jsonEncode
     * @uses urlsafeB64Encode
     */
    public static function encode($payload, $key, $algo = 'HS256')
    {
        $header = array('typ' => 'JWT', 'alg' => $algo);

        $segments = array();
        $segments[] = JWT::urlsafeB64Encode(JWT::jsonEncode($header));
        $segments[] = JWT::urlsafeB64Encode(JWT::jsonEncode($payload));
        $signing_input = implode('.', $segments);

        $signature = JWT::sign($signing_input, $key, $algo);
        $segments[] = JWT::urlsafeB64Encode($signature);

        return implode('.', $segments);
    }

    /**
     * Sign a string with a given key and algorithm.
     *
     * @param string $msg    The message to sign
     * @param string $key    The secret key
     * @param string $method The signing algorithm. Supported
     *                       algorithms are 'HS256', 'HS384' and 'HS512'
     *
     * @return string          An encrypted message
     * @throws DomainException Unsupported algorithm was specified
     */
    public static function sign($msg, $key, $method = 'HS256')
    {
        $methods = array(
            'HS256' => 'sha256',
            'HS384' => 'sha384',
            'HS512' => 'sha512',
        );
        if (empty($methods[$method])) {
            throw new DomainException('Algorithm not supported');
        }
        return hash_hmac($methods[$method], $msg, $key, true);
    }

    /**
     * Decode a JSON string into a PHP object.
     *
     * @param string $input JSON string
     *
     * @return object          Object representation of JSON string
     * @throws DomainException Provided string was invalid JSON
     */
    public static function jsonDecode($input)
    {
        $obj = json_decode($input);
        if (function_exists('json_last_error') && $errno = json_last_error()) {
            JWT::_handleJsonError($errno);
        } else if ($obj === null && $input !== 'null') {
            throw new DomainException('Null result with non-null input');
        }
        return $obj;
    }

    /**
     * Encode a PHP object into a JSON string.
     *
     * @param object|array $input A PHP object or array
     *
     * @return string          JSON representation of the PHP object or array
     * @throws DomainException Provided object could not be encoded to valid JSON
     */
    public static function jsonEncode($input)
    {
        $json = json_encode($input);
        if (function_exists('json_last_error') && $errno = json_last_error()) {
            JWT::_handleJsonError($errno);
        } else if ($json === 'null' && $input !== null) {
            throw new DomainException('Null result with non-null input');
        }
        return $json;
    }

    /**
     * Decode a string with URL-safe Base64.
     *
     * @param string $input A Base64 encoded string
     *
     * @return string A decoded string
     */
    public static function urlsafeB64Decode($input)
    {
        $remainder = strlen($input) % 4;
        if ($remainder) {
            $padlen = 4 - $remainder;
            $input .= str_repeat('=', $padlen);
        }
        return base64_decode(strtr($input, '-_', '+/'));
    }

    /**
     * Encode a string with URL-safe Base64.
     *
     * @param string $input The string you want encoded
     *
     * @return string The base64 encode of what you passed in
     */
    public static function urlsafeB64Encode($input)
    {
        return str_replace('=', '', strtr(base64_encode($input), '+/', '-_'));
    }

    /**
     * Helper method to create a JSON error.
     *
     * @param int $errno An error number from json_last_error()
     *
     * @return void
     */
    private static function _handleJsonError($errno)
    {
        $messages = array(
            JSON_ERROR_DEPTH => 'Maximum stack depth exceeded',
            JSON_ERROR_CTRL_CHAR => 'Unexpected control character found',
            JSON_ERROR_SYNTAX => 'Syntax error, malformed JSON'
        );
        throw new DomainException(
            isset($messages[$errno])
            ? $messages[$errno]
            : 'Unknown JSON error: ' . $errno
        );
    }

}


?>

 

[DonManfred]

Where is the encoding code? I don´t see which Algorythm is used in your code.
As far as i understand/know you need to initialize the correct Algorythm
for example

Algorithm algo = Algorithm.HMAC256(secret); ' secret is a String

 

[Nickle548]

There is a JWT Call in the class Uuid. But I am sure you get the idea. I dont want to focus on PHP. Want to know how I can have an encoded Field in a table decoded when used at client App Side using jRDC

if(!empty($row['encrypted_password'])) {
$pin = JWT::decode($row['encrypted_password'], $row['salt']);
if(!$pin) die('INVALID TOKEN');
$res_arr = array();
$res_arr['username']=$row['username'];
$res_arr['phone'] = $row['phone'];
$res_arr['created_at'] = $row['created_at'];
$res_arr['pin'] = $pin[0]->pin;
$NewPin = $pin[0]->pin; //encoded Pin

// Verify Pin Quality
if ($NewPin == $password) { //Password is correct
$res_arr['error'] = 'TRUE';
}
else { //Password is wrong

$res_arr['error'] = 'FALSE';
}
print json_encode([$res_arr]);

 

[DonManfred]

the JWT class i found only expects one parameter for decode. The Token.
Usually one uses a specific Algorythm to use and then SIGN a value (bytes) which returns a new bytearray.

I do not have any experience. My answer are based on this project i found: https://github.com/auth0/java-jwt

Can you provide some "encoded" values and their "real value" (to compare)? I´m just checking the github project and maybe build a small wrapper around it. Would be helpful to already have some values to "decode"....

 

[udg]

Have a look here for a JWT introduction. It doesn't look hard to write code to make use of it.

 

[keirS]

I am confused. Your PHP code is to generate JSON web tokens. JSON web tokens are not encrypted they are a JSON payload signed using a cryptographic signature. If you want to encrypt columns in MySQL you should use the AES_ENCRYPT AND AES_DECRYPT functions.

 

[Nickle548]

Guys, you are missing the point. The focus here is not to write a PHP code. What I shared is just an example of what can be done in PHP for encoding and decoding fields stored in the MySql table. I am trying to use jRDC2 which is a more powerful Middleware between client App and Backend Database. Problem is that there is no way to encode fields you want to store as encoded in Database then when you retrieve it, it is also Decoded by the middleware.

I hope this is clear enough to set the right discussion. if there is a way to encode fields to Mysql Database using jRDC middleware, kindly share.

 

[Erel]

Find PHP Code. It calls the encrypt/Decrypt functions

Like many of the PHP scripts posted in the forum this one is also vulnerable to SQL injection and should not be used.

What exactly are you trying to do? If you just want to encrypt the data then do it in the client and set the field to be a BLOB field.

Note that the connection itself will be encrypted if you use a SSL connection.

 

[keirS]

Guys, you are missing the point. The focus here is not to write a PHP code. What I shared is just an example of what can be done in PHP for encoding and decoding fields stored in the MySql table. I am trying to use jRDC2 which is a more powerful Middleware between client App and Backend Database. Problem is that there is no way to encode fields you want to store as encoded in Database then when you retrieve it, it is also Decoded by the middleware.

I hope this is clear enough to set the right discussion. if there is a way to encode fields to Mysql Database using jRDC middleware, kindly share.

About as clear as mud. Your topic title mentions encryption but you then mention encoding and decoding. So which do you actually mean? because they are not the same thing.

 

[DonManfred]

Basically it is kind of encryption when you add a payload into a JWT-Token. The more data it contains the more bytes the Token uses.

Parsing the JWT back you also get the Payload back.

    Dim jgen As JSONGenerator
   jgen.Initialize(CreateMap("Title": "Bla bla bla", "Info": "Blub blub blub","Value1":"Text Value1","Value2":"Text Value2","Value3":"Text Value3","Value4":"Text Value4","Value5":"Text Value5","Value6":"Text Value6","Value7":"Text Value7","Value8":"Text Value8","Value9":"Text Value9","Value10":"Text Value10","Value11":"Text Value11","Value12":"Text Value12","Value13":"Text Value13","Value14":"Text Value14","Value15":"Text Value15","Value16":"Text Value16","Value17":"Text Value17","Value18":"Text Value18","Value19":"Text Value19","Value20":"Text Value20"))

    Dim token As String = jt.Initialie.Payload(jgen.ToString&" ").signWith("HS512",privateKey).compact
    Log(token)
    Dim decObj As DecodedJWT = jwtdecoder.decode(token)
    Log(decObj.Algorithm)
    If decObj.Payload <> Null And decObj.Payload.Length > 0 Then
        Log(b64.DecodeStoS(decObj.Payload,"UTF8"))
    End If
    If decObj.Issuer <> Null Then
        Log($"Issuer=${decObj.Issuer}"$)
    End If
    If decObj.Subject <> Null Then
        Log($"Subject=${decObj.Subject}"$)
    End If
    Log($"Header=${decObj.Header} -> ${b64.DecodeStoS(decObj.Header,"UTF8")}"$)
    Log($"Token=${decObj.Token}"$)
    Log($"Payload=${b64.DecodeStoS(decObj.Payload,"UTF8")}"$)
    Log($"Audience=${decObj.Audience}"$)
    Log($"Id=${decObj.Id}"$)
    If decObj.Id <> Null Then
        If decObj.ExpiresAt <> Null Then
            Log(DateTime.Date(decObj.ExpiresAt))
        End If
        If decObj.IssuedAt <> Null Then
            Log(DateTime.Date(decObj.IssuedAt))
        End If
    End If

This is such a Token:

eyJhbGciOiJIUzUxMiJ9.eyJWYWx1ZTkiOiJUZXh0IFZhbHVlOSIsIlZhbHVlMyI6IlRleHQgVmFsdWUzIiwiVGl0bGUiOiJCbGEgYmxhIGJsYSIsIlZhbHVlNCI6IlRleHQgVmFsdWU0IiwiVmFsdWUyMCI6IlRleHQgVmFsdWUyMCIsIlZhbHVlMSI6IlRleHQgVmFsdWUxIiwiVmFsdWUyIjoiVGV4dCBWYWx1ZTIiLCJJbmZvIjoiQmx1YiBibHViIGJsdWIiLCJWYWx1ZTciOiJUZXh0IFZhbHVlNyIsIlZhbHVlOCI6IlRleHQgVmFsdWU4IiwiVmFsdWU1IjoiVGV4dCBWYWx1ZTUiLCJWYWx1ZTYiOiJUZXh0IFZhbHVlNiIsIlZhbHVlMTYiOiJUZXh0IFZhbHVlMTYiLCJWYWx1ZTE3IjoiVGV4dCBWYWx1ZTE3IiwiVmFsdWUxNCI6IlRleHQgVmFsdWUxNCIsIlZhbHVlMTUiOiJUZXh0IFZhbHVlMTUiLCJWYWx1ZTEyIjoiVGV4dCBWYWx1ZTEyIiwiVmFsdWUxMyI6IlRleHQgVmFsdWUxMyIsIlZhbHVlMTAiOiJUZXh0IFZhbHVlMTAiLCJWYWx1ZTExIjoiVGV4dCBWYWx1ZTExIiwiVmFsdWUxOCI6IlRleHQgVmFsdWUxOCIsIlZhbHVlMTkiOiJUZXh0IFZhbHVlMTkifSA.p5KOzYti-6K1Xel5lIdpxINnWWMFvWB_ilpnZoPQD_m5i-Md9036IVvfxq6C-wbImzMR8ttacB7NxVK45f-A4g

 

[keirS]

I know what a JWT is. Base64 is encoding. Encryption requires a key to transform plaintext to ciphertext and vice versa. That's why I was asking the OP which one he meant.

 

[OliverA]

Any ideas or advice on how to get same done with jRDC2

Both options below are meant to modify the jRDC2 server's source code

1) a) Create a separate Server Handler class that handles the login portion of your application.
b) Create a Filter Class that checks the access to the /rdc portion of the jRDC2 server. If the client has the proper credentials, allow access. If the client has no credentials, redirect to the Server Handler for login. If the client has the wrong credentials, deny access.
2) Just modify the /rdc handler to check for access and either allow/deny/or ask for login information

My 2 cents (added to the ones already given above):

You are going through a lot of trouble storing a JWT in your database for 0 gains in security (yes zero). Since the password is only Base64 encoded in your Database, a hacker can figure out the plaintext of all you passwords stored (once he realizes that they are just JWT tokens). Not only that, you store the password to create a new JWT in the database alongside the JWT! If the hacker figures that one out, he could modify your stored passwords without you ever realizing that he did it (since he just recreates a new JWT with the password so conveniently stored in the same table of your database). Technically, you should be storing the hash of the password only in your DB, with the salt (an actual salt, not a password) prepended to the hash. An example on how to do this can be found here (as a bonus, the example shows how to use a Filter Class to control access to a web site): https://www.b4x.com/android/forum/threads/server-login-system-filters-tutorial.39020/#content