Viacheslav Eremin | Working with MySQL Blob

(CORE) CORE (2022)

Working with MySQL Blob

There two mode for working with MySQL BLOB, first mode is string mode. You can use BLOB only for storing long-long string value. In this case you can working with the same way.

However in other case you need to working with BLOB exactly as byte, for example if you store in db image or password.

This is more interesting way, look to this code (this code is part of this project BackendAPI (Net Core 5) project template with Custom Attribute, Service and Controller Example, MySQL database with masked password in config, SwaggerUI, EF Core and extension to EF Core).

   1:  Imports System.IO

   2:  Imports System.Text

   3:  Imports BackendAPI.Helper

   4:  Imports BackendAPI.Model

   5:  Imports BackendAPI.Services

   6:  Imports Microsoft.AspNetCore.Mvc

   7:  Imports Newtonsoft.Json.Linq

8:

   9:  Namespace WebApi.Controllers

10:

  11:      <ApiController>

  12:      <Route("[controller]/[action]")>

  13:      Public Class AesController

  14:          Inherits ControllerBase

15:

  16:          Private ReadOnly Aes As AesCryptor

  17:          Private ReadOnly _DB As ApplicationDbContext

  18:          Private ReadOnly _UserService As IUserService

19:

  20:          Public Sub New(ByVal UserService As IUserService, Cryptor As IAesCryptor, DbContext As ApplicationDbContext)

  21:              Aes = Cryptor

  22:              _DB = DbContext

  23:              _UserService = UserService

  24:          End Sub

25:

  26:          <HttpGet>

  27:          Public Function NetCrypt(Text As String, KeyString As String) As String

  28:              Return Aes.EncryptString(Text, KeyString)

  29:          End Function

30:

  31:          <HttpGet>

  32:          Public Function NetDeCrypt(CipherText As String, KeyString As String) As String

  33:              Return Aes.DecryptString(CipherText, KeyString)

  34:          End Function

35:

  36:          'Authorize because vulnerable to SQL injection

  37:          <Jwt.Authorize>

  38:          <HttpGet>

  39:          Public Function SqlCrypt(Text As String, KeyString As String) As JsonResult

  40:              Dim CurUsers = _UserService.GetCurrentUser(Request.Headers("JwtUserName")(0))

  41:              If CurUsers.IsAdmin Then

  42:                  Dim Buf1 As Byte() = New Byte() {}

  43:                  ReDim Buf1(100)

  44:                  _DB.RawSqlQuery(Of Byte())($"select aes_encrypt('{Text}','{KeyString}') as aes_encrypt", Function(X)

  45:                                                                                                               CType(X("aes_encrypt"), Byte()).CopyTo(Buf1, 0)

  46:                                                                                                               ReDim Preserve Buf1(CType(X("aes_encrypt"), Byte()).Length - 1)

  47:                                                                                                               Return Nothing

  48:                                                                                                           End Function)

  49:                  Dim Sb1 As New StringBuilder

  50:                  For Each One As Byte In Buf1.ToArray

  51:                      Sb1.Append(One.ToString("X2"))

  52:                  Next

  53:                  Dim Ret1 As New With {.ToBase64String = Convert.ToBase64String(Buf1), .Hex = Sb1.ToString, .Utf8String = UTF8Encoding.UTF8.GetString(Buf1)}

  54:                  Return New JsonResult(Ret1)

  55:              Else

  56:                  Return New JsonResult(Unauthorized())

  57:              End If

58:

  59:          End Function

60:

  61:          <Jwt.Authorize>

  62:          <HttpGet>

  63:          Public Function SqlDeCrypt(HexCipherText As String, KeyString As String) As JsonResult

  64:              Dim CurUsers = _UserService.GetCurrentUser(Request.Headers("JwtUserName")(0))

  65:              If CurUsers.IsAdmin Then

  66:                  Dim Buf1 As Byte() = New Byte() {}

  67:                  ReDim Buf1(100)

  68:                  _DB.RawSqlQuery(Of Byte())($"select aes_decrypt(X'{HexCipherText}','{KeyString}') as aes_decrypt", Function(X)

  69:                                                                                                                         CType(X("aes_decrypt"), Byte()).CopyTo(Buf1, 0)

  70:                                                                                                                         ReDim Preserve Buf1(CType(X("aes_decrypt"), Byte()).Length - 1)

  71:                                                                                                                         Return Nothing

  72:                                                                                                                     End Function)

  73:                  Dim Sb1 As New StringBuilder

  74:                  For Each One As Byte In Buf1.ToArray

  75:                      Sb1.Append(One.ToString("X2"))

  76:                  Next

  77:                  Dim Ret1 As New With {.ToBase64String = Convert.ToBase64String(Buf1), .Hex = Sb1.ToString, .Utf8String = UTF8Encoding.UTF8.GetString(Buf1)}

  78:                  Return New JsonResult(Ret1)

  79:              Else

  80:                  Return New JsonResult(Unauthorized())

  81:              End If

  82:          End Function

  83:      End Class

  84:  End Namespace

And look, please, to related topic.

Comments (

)

<00> <01> <02> <03> <04> <05> <06> <07> <08> <09> <10> <11> <12> <13> <14> <15> <16> <17> <18> <19> <20> <21> <22> <23>

Link to this page: http://www.vb-net.com/MySqlBlob/Index.htm