BUG MARIADB SUPPORT FWH1906

[nnicanor]

Hi,

We have this bug using mariaDB support on last FWH Build, i have user table with data encryption by MySql AES_ENCYPT() Function, on last version of FWH code retrieve data but gets all fields blank, i run query on Heidi or sqlyog or Tdolphin and works ok and get data on query.

Code: Select all  Expand view  RUN

cSql:=" UPDATE usuarios "+;
           " SET USER=AES_ENCRYPT('"+AllTrim(::usuario)+"','mykey'),"+;
           " PASSWORD=AES_ENCRYPT('"+AllTrim(::pasword)+"','mykey'),"+;
           " NAME=AES_ENCRYPT('"+Alltrim(::nombre)+"','mykey'),"+;
           " EMAIL=AES_ENCRYPT('"+Alltrim(::email))+"','mykey'),"+;
           " NIVEL=AES_ENCRYPT('"+AllTrim(Str(::nivel,0))+"','mykey') "
           "Where id="+ClipValue2Sql( oTusuarios:id )
   
     .......

  cSql := "Select AES_DECRYPT(USER,'mykey') AS usuario,"+;
             "       AES_DECRYPT(PASSWORD,'mykey') AS pasword,"+;
             "       AES_DECRYPT(NAME,'mykey) AS nombre,"+;
             "       AES_DECRYPT(NIVEL,'mykey') AS level,"+;
             "       AES_DECRYPT(EMAIL,'mykey') AS correo "+;
             " from usuarios where id="+ClipValue2Sql( oTusuarios:id )


        oRs:= oCn:Rowset( cSql )

      Xbrowse( oRs )  // oRs Get Blank DAta

 

Regards

[nageswaragunupudi]

We will check

[nageswaragunupudi]

We tested with this program and it is working as expected. This program uses FWH provided free demo server.
You can copy this program to fwh\samples folder and build with buildh.bat or buildx.bat for testing, without making any changes.

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn
   local cSql, oRs
   local cTable   := "test_aes_encrypt"

   oCn   := FW_DemoDB()

   if oCn:TableExists( cTable ) .and. MsgNoYes( "Recreate the table?" )
      oCn:DropTable( cTable )

      TEXT INTO cSql
      CREATE TABLE `test_aes_encrypt` (
        `id`    int(11) NOT NULL AUTO_INCREMENT,
        `name`  varchar(20) DEFAULT NULL,
        `login` tinyblob,
        `pass`  varbinary(255),
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB CHARSET=latin1
      ENDTEXT

      oCn:Execute( cSql )
   endif

   cSql  := "INSERT INTO " + cTable + " ( name, login, pass ) VALUES ( " + ;
            "'andrew', AES_ENCRYPT( 'Andrew', 'myencryptkey' ), " + ;
            "AES_ENCRYPT( 'Pass123', 'myencryptkey' ) )"

   oCn:Execute( cSql )

   cSql  := "INSERT INTO " + cTable + " ( name, login, pass ) VALUES ( " + ;
            "'James', AES_ENCRYPT( 'James', 'myencryptkey' ), " + ;
            "AES_ENCRYPT( 'Pass@XYZ', 'myencryptkey' ) )"

   oCn:Execute( cSql )


   cSql  := "UPDATE " + cTable + " SET pass = AES_ENCRYPT( 'New@456', 'myencryptkey' ) WHERE id = 1"

   oCn:Execute( cSql )


   cSql  := "SELECT id,name,AES_DECRYPT( login, 'myencryptkey' ) AS login, " + ;
                    "AES_DECRYPT( pass, 'myencryptkey' ) AS pass FROM " + cTable

   oRs   := oCn:RowSet( cSql )
   xbrowser oRs

   oRs:End() // or Close()
   oCn:End() // or Close()

RETURN NIL
 

May we know the field type you have used for these fields, while creating the table?

[nnicanor]

Thanks your sample works fine, this is my table with fwh1802 works fine, i'm going to change data types and test, i'll inform about results

Code: Select all  Expand view  RUN

CREATE TABLE `usuarios` (
    `USER` CHAR(40) NULL DEFAULT NULL,
    `PASSWORD` CHAR(40) NULL DEFAULT NULL,
    `level` CHAR(40) NULL DEFAULT NULL,
    `NAME` CHAR(40) NULL DEFAULT NULL,
    `email` CHAR(100) NULL DEFAULT NULL,
    `ID` INT(10) NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`ID`),
     UNIQUE INDEX `id` (`ID`),
     INDEX `usuario` (`USER`, `PASSWORD`),
     INDEX `nombre` (`USER`),
     INDEX `nombre1` (`NAME`),
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=91
;

 

[nnicanor]

Hi, after change on fields type now works fine. thanks.

Code: Select all  Expand view  RUN

CREATE TABLE `usuarios` (
    `USER` TINYBLOB NULL DEFAULT NULL,
    `PASSWORD` VARBINARY(255) NULL DEFAULT NULL,
    `level` VARBINARY(255) NULL DEFAULT NULL,
    `NAME` VARCHAR(100) NULL DEFAULT NULL,
    `email` VARCHAR(100) NULL DEFAULT NULL,
    `ID` INT(10) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`ID`),
    UNIQUE INDEX `id` (`ID`),
    INDEX `usuario` (`USER`, `PASSWORD`),
    INDEX `nombre` (`USER`),
    INDEX `nombre1` (`NAME`),
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=91
;


 

[nageswaragunupudi]

You can use either TINYBLOB and VARBINARY(n). Only depends on your taste.