(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
Link to this page: http://www.vb-net.com/MySqlBlob/Index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>