1-Single User with Structured Data

In this scenario, there are users who have wallets with different balances. The system aims to protect sensitive information, such as user names and wallet balances, by encrypting the data before inserting it into a table.

1.1. Task

1. Encrypting and inserting wallet address, user name, and balance information into a table.

2. Querying the encrypted data to retrieve wallet information based on a specified condition, such as wallets with a balance greater than 15.0.

3. Decrypting the queried encrypted data to display the wallet information in a readable format.

1.2. Data

Wallet Address(text)	Name (text)	Balance (float4)
(public data)	(secret data)	(secret data)
0xB2F588A50E43f58FEb0c05ff86a30D0d0b1BF065	Alice	10.5
0x420c08373E2ba9C7566Ba0D210fB42A20a1eD2f8	Bob	20.8

1.3. Example code to Insert cipher

Typescript

import { DataType, MindLake } from 'mind-lake-sdk';

async function f() {
  const mindLake = await MindLake.getInstance("YOU OWN APP KEY");

  // connect to MindLake."5" is example of Goerli Testnet chainId
  const chainId = "5"
  const res1 = await mindLake.connect(chainId);
  if(res1.code !== 0) {
    console.error(res1.message);
    return
  }

  // create a table
  const dataLake = mindLake.dataLake;
  const res2 = await dataLake.createTable("wallet_balance", [{columnName: 'WalletAddress', type: DataType.text, encrypt: false}, {columnName: 'Name', type: DataType.text, encrypt: true}, {columnName: 'Balance', type: DataType.float4, encrypt: true}], ["id"]);
  if(res2.code !==0) {
    console.error(res2.message);
    return
  }

  // encrypt data
  const cryptor = mindLake.cryptor;
  const res3 = await cryptor.encrypt("Alice", 'wallet_balance.Name');
  if(res3.code !== 0) {
    console.error(res3.message);
    return
  }
  const encryptedName = res3.result;

  const res4 = await cryptor.encrypt(10.5, 'wallet_balance.Balance');
  if(res4.code !==0 ) {
    console.error(res4.message);
    return
  }
  const encryptedBalance = res4.result;

  // insert encrypted data
  const sql = `insert into wallet_balance (WalletAddress, Name, Balance) values ('0xB2F588A50E43f58FEb0c05ff86a30D0d0b1BF065', '${encryptedName}', '${encryptedBalance}')`;
  const res5 = await dataLake.query(sql);
  if(res5.code !== 0){
    console.error(res5.message);
    return
  }

  //query encrypted data;
  const selectSql = `select * from wallet_balance`;
  const res6 = await dataLake.query(selectSql);
  if(res6.code !== 0) {
    console.error(res6.message);
    return
  }
  const columnList = res6.result.columnList;
  //print encryption data from query result
  console.log("The data stored in Mind Lake:");
  for (const row of res6.result.data) {
    for (const idx in row) {
     console.log(`${columnList[idx]} >>>`, row[idx])
    }
  }
}

Python

import env
import mindlakesdk

# 1. connect to mindlake, '5' is example of Goerli Testnet chainID
chainID='5'
mindlake = mindlakesdk.connect(env.walletPrivateKeyAlice, env.appKey, chainID)
assert mindlake, mindlake.message

# 2. create a table
result = mindlake.datalake.createTable('wallet_balance',
        [
            mindlake.datalake.Column('WalletAddress', mindlake.DataType.text, False),
            mindlake.datalake.Column('Name', mindlake.DataType.text, True),
            mindlake.datalake.Column('Balance', mindlake.DataType.float4, True)
        ],
        primaryKey=['WalletAddress'])
assert result, result.message

# 3. encrypt and insert the data
result = mindlake.cryptor.encrypt('Alice','wallet_balance.Name')
assert result, result.message
encryptedName = result.data
result = mindlake.cryptor.encrypt(10.5,'wallet_balance.Balance')
assert result, result.message
encryptedBalance = result.data

result = mindlake.datalake.query(f"""
INSERT INTO "wallet_balance" 
       ("WalletAddress", "Name", "Balance") 
VALUES ('{'0xB2F588A50E43f58FEb0c05ff86a30D0d0b1BF065'}',
        '{encryptedName}',
        '{encryptedBalance}') returning *""")
assert result, result.message

# 4. a compact way to encrypt and insert
result = mindlake.datalake.query(f"""
INSERT INTO "wallet_balance" ("WalletAddress", "Name", "Balance")
VALUES ('0x420c08373E2ba9C7566Ba0D210fB42A20a1eD2f8',
    '{mindlake.cryptor.encrypt('Bob','wallet_balance.Name').data}',
    '{mindlake.cryptor.encrypt(20.5,'wallet_balance.Balance').data}') returning *
""")
assert result, result.message

# 5. query all the encrypted data
result = mindlake.datalake.query('SELECT * FROM "wallet_balance"')
assert result, result.message

# 6. print encryption data from query result
print('The data stored in Mind Lake:')
print('-'*77)
print('|', result.data["columnList"][0], " "*28, '|', result.data["columnList"][1],
      " "*8, '|', result.data["columnList"][2], " "*5, '|')
print('-'*77)
for row in result.data['data']:
    print('|', row[0], '|', row[1][:10]+'...', '|', row[2][:10]+'...', '|')
print('-'*77)

1.4. Result Table in MindLake

Wallet Address	Name	Balance
(text, encrypt=False)	(text, encrypt=True)	(float4, encrypt=True)
0xB2F588A50E43f58FEb0c05ff86a30D0d0b1BF065	0x1111	0x2211
0x420c08373E2ba9C7566Ba0D210fB42A20a1eD2f8	0x1122	0x2222

1.5. Example code to query on cipher

TypeScript

const selectSql = `select * from wallet_balance`;
const res9 = await dataLake.query(selectSql);
if(res9.code !== 0) {
  console.error(res9.message);
  return
}
const columnList = res9.result.columnList;
//print encryption data from query result
console.log("==The data stored in Mind Lake:==");
const cryptor = mindLake.cryptor;
for (const row of res9.result.data) {
  for (const idx in row) {
    // @ts-ignore
    if(idx == 0) {
      console.log(`${columnList[0]} >>>`, row[0])
    }else {
      const decryptData = await cryptor.decrypt(row[idx]);
      console.log(`${columnList[idx]} >>>`, decryptData.result)
    }
  }
}

Python

# 7. query encrypted data with condition and decrypt the data
#   Note: the condition must be encrypted
result = mindlake.datalake.query(
    f'''SELECT * FROM "wallet_balance" WHERE "Balance" > \
        '{mindlake.cryptor.encrypt(15.0, mindlake.DataType.float4).data}' ''')
assert result, result.message

print()
print('The data after decryption:')
print('-'*66)
print('|', result.data["columnList"][0], " "*28, '|', result.data["columnList"][1],
      " "*3, '|', result.data["columnList"][2], '|')
print('-'*66)
for row in result.data['data']:
    walletAddress = row[0]
    resultName = mindlake.cryptor.decrypt(row[1])
    resultBalance = mindlake.cryptor.decrypt(row[2])
    assert resultName, resultName.message
    assert resultBalance, resultBalance.message
    print('|', walletAddress, '|', resultName.data, '\t|', resultBalance.data, '\t  |')
print('-'*66)

1.6. Output

The data stored in Mind Lake:
-----------------------------------------------------------------------------
| WalletAddress                              | Name          | Balance       |
-----------------------------------------------------------------------------
| 0xB2F588A50E43f58FEb0c05ff86a30D0d0b1BF065 | \x333942b9... | \xc81942ac... |
| 0x420c08373E2ba9C7566Ba0D210fB42A20a1eD2f8 | \xc6394244... | \x581942c4... |
-----------------------------------------------------------------------------

The data after decryption:
------------------------------------------------------------------
| WalletAddress                              | Name     | Balance |
------------------------------------------------------------------
| 0x420c08373E2ba9C7566Ba0D210fB42A20a1eD2f8 | Bob      | 20.5    |
------------------------------------------------------------------