Android Question How to Decrypt a string received from MariaDB's AES_ENCRYPT() in B4A [Solved]

[Anser]

Hi,

In my B4A app, I receive a string from my MariaDB database via an SQL query. I am supposed to decrypt the received encrypted string in B4A

In MariaDB, the string is encrypted using the following SQL statement

SELECT CAST( AES_ENCRYPT(FirstName,'MyPassword') AS CHAR(50) ) AS 'EncryptedString' FROM MyTableName

I searched the forum and read a few posts, but then it is discussing the vector and cypher used etc. As you can see in the above SQL statement, nothing is mentioned about the cypher, vector etc, its just the scoring to be encrypted and the password to be used.

May I know which encryption/Decryption library is to be used in B4A ?

Any help will be appreciated

 

[DonManfred]

Is there no AES_DECRYPT method in SQL?

 

[DonManfred]

May I know which encryption/Decryption library is to be used in B4A ?

I suggest to use

[B4X] B4XEncryption

This library allows you to encrypt or decrypt data using the AES encryption method. It is simple to use and it is compatible with B4J jB4XEncryption library and B4i Encryption library (Encrypt and Decrypt method) which means that you can encrypt the data on one platform and decrypt it on a...

www.b4x.com

as it is Crossplatform.

 

[Anser]

Is there no AES_DECRYPT method in SQL?

Yes, there is AES_DECRYPT() available in the SQL. My requirement is to get the sensitive information from a remote database in an encrypted form and then receive the encrypted data in B4A and then get it decrypted in B4A.
Basically encryption happens in a different application (MariaDB) and decryption is to happen in another application (B4A)

I suggest to use

[B4X] B4XEncryption

This library allows you to encrypt or decrypt data using the AES encryption method. It is simple to use and it is compatible with B4J jB4XEncryption library and B4i Encryption library (Encrypt and Decrypt method) which means that you can encrypt the data on one platform and decrypt it on a...

www.b4x.com

as it is Crossplatform.

I hope that the cross-platform here means not just B4X

 

[Erel]

B4XEncryption will not help here.

You need to find the exact details behind AES_ENCRYPT and implement it with the Encryption library.

I hope that the cross-platform here means not just B4X

There is no such thing. There are many many ways to encrypt data.

 

[MarcoRome]

convert(AES_DECRYPT(FirstName,'MyPassword'),char) AS 'EncryptedString'

 

[Anser]

You need to find the exact details behind AES_ENCRYPT and implement it with the Encryption library.

I understand that it is AES 128 ECB

Searching AES 128 ECB decrypt points me to the following thread where the above said Encryption/Decryption is discussed.

AES128 ECB Decrypt

Hi, I am trying to decrypt a message using AES128 in ECB mode, and not CBC mode. The data I am trying to decrypt is sent to my B4J as AES-128 in ECB mode, and that data I have put into a string called 'dataToDecrypt' in my code below. When I run the following code I get the following error...

www.b4x.com

Unfortunately, I could not find the library named Cipher which is used in the solutions mentioned in that thread. I do not know whether Cipher is a part of some library or not

Dim C As Cipher

I could find only B4XCipher which has just 2 methods Encrypt and Decrypt. I hope that if I could find that Cipher library it may resolve my issue.

Edit:- Cipher library is here https://www.b4x.com/android/forum/threads/base64-and-encryption-library.6839/

 

[OliverA]

Why not use an encrypted connection to the database? Or the middleware program that is accessing the database (if that is the case here)?

 

[Peter Simpson]

From memory @Anser, I hope that this is correct...
SELECT AES_DECRYPT(FirstName, 'MyPassword') As Name FROM MyTableName;

My preferred method is the following. As I create apps in B4A, B4J, VB.Net and C#.Net. I've created libs to encrypt/decrypt in all the IDE's for me which can easily connect to just one DB. But you really should nit be having any issues with using AES_ENCRYPT or AES_DECRYPT directly in your db.

AES Encrypt/Decrypt (Encryption/Decryption) B4A and B4J library

This library works with both B4A and B4J. Yes yes yes there are other ways to do this, this is how I do it. I have lots of small libraries that I use for myself, so I thought that I would share this one with the community. Hello fellow B4X'ers, I've decided to release this simple to use AES...

www.b4x.com

 

[Anser]

Why not use an encrypted connection to the database? Or the middleware program that is accessing the database (if that is the case here)?

I am not directly connecting to the database, instead I am calling an API via httpjob. The API talks with the database and then returns the result. One of the values in the result is a URL and I will be using Webview from my B4A app to access a PHP WebApp. I need to pass few parameters to the PHP WebApp so that the WebApp understands which user is accessing the webapp

Dim j As HttpJob
j.Initialize("AnsTest",Me)
j.Download2("http://xxx.xxx.xxx.xxx:8080/myappname/api/user/moblogin", Array As String("username", cUserName, "password", cPassword)  )

' Now you should Base64 Encode the API Username and Password and then set the Base64 Encoded string as Authorization Header
Dim su As StringUtils
Dim cApiUserNameAndPass As String = $"${"ApiUser"}:${"ApiPassword"}"$
Dim cBasicAuth As String = su.EncodeBase64(cApiUserNameAndPass.GetBytes("UTF8"))
 
j.GetRequest.SetHeader("Authorization", "Basic " & cBasicAuth)
Wait for JobDone (Job As HttpJob)

From the above successful call, I receive the result JSON data which contains encrypted data. The encrypted data contained in the JSON result is encrypted using the following MariaDB/MySQL statement

SELECT CAST( AES_ENCRYPT(FirstName,'MyPassword') AS CHAR(50) ) AS 'EncryptedString' FROM MyTableName

The "MyPassword" used in the above SQL statement is available/shared with me. Now I should decrypt the received data in B4A to proceed further.

Solved the problem with the Library https://www.b4x.com/android/forum/threads/base64-and-encryption-library.6839/
Here is the code
Modified the SQL statement from CAST() to TO_BASE64(). Now the SQL statement looks as follows

SELECT TO_BASE64( AES_ENCRYPT(FirstName,'MyPassword') ) AS 'EncryptedString' FROM MyTableName

B4A Code

Sub MyDecrypt(cCryptedText As String, password As String)
    Dim c As Cipher
    Dim kg As KeyGenerator
    Dim su As StringUtils
    Dim b() As Byte = su.DecodeBase64(cCryptedText)
   
    kg.Initialize("AES")
    kg.KeyFromBytes(password.GetBytes("UTF8"))
 
    c.Initialize("AES/ECB/NoPadding")

    Dim decryptedBytes() As Byte = c.Decrypt(b, kg.Key , False)
    Log("Decrypted Text = " & BytesToString(decryptedBytes, 0, decryptedBytes.Length, "UTF8")) ' this seems to return the same encrypted text.
 
End Sub

Warm Regards

 

[Anser]

From memory @Anser, I hope that this is correct...
SELECT AES_DECRYPT(FirstName, 'MyPassword') As Name FROM MyTableName;

My preferred method is the following. As I create apps in B4A, B4J, VB.Net and C#.Net. I've created libs to encrypt/decrypt in all the IDE's for me which can easily connect to just one DB. But you really should nit be having any issues with using AES_ENCRYPT or AES_DECRYPT directly in your db.

AES Encrypt/Decrypt (Encryption/Decryption) B4A and B4J library

This library works with both B4A and B4J. Yes yes yes there are other ways to do this, this is how I do it. I have lots of small libraries that I use for myself, so I thought that I would share this one with the community. Hello fellow B4X'ers, I've decided to release this simple to use AES...

www.b4x.com

I am testing your library too. I understand that, to Decrypt, your library expects a string. In my case, it is as follows
The output of the MySQL AES_Encrypt() is binary data
From MySQL itself, the binary output is converted as a Base64 Encoded string in the result ie

SELECT TO_BASE64( AES_ENCRYPT(FirstName,'MyPassword') ) AS 'EncryptedString' FROM MyTableName

A binary data is converted to a Base64 string

I may start a new thread/post for that

Edit:- Converted bytes to String as per the following code, but then facing issue regarding the InitializationVector. No idea how to disable IV

Sub PeterDecrypt(cCryptedText As String, password As String)
    Dim su As StringUtils
    Dim b() As Byte = su.DecodeBase64(cCryptedText)
    
    Dim EncDec As AESEncryption
    EncDec.SecretKey = "1234567890123456"
    EncDec.InitializationVector=""  'No idea how to set this to false
    
    Dim cCode As String = BytesToString(b, 0, b.Length, "UTF8")
    
    Log($"Peter Decrypted = ${EncDec.AESDecrypt(EncDec.AESEncrypt(cCode))}"$)
    
End Sub

If I omit the line EncDec.InitializationVector then I get the following run time error
java.security.InvalidAlgorithmParameterException: expected IV length of 16 but was 0