topic-37805

Posts: 302

Joined: Fri Apr 23, 2010 04:30 AM

BUG MARIADB SUPPORT FWH1906
Posted: Wed Sep 25, 2019 02:02 AM

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 (fw): Select all Collapse

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

Nicanor Martinez M.
Auditoria y Sistemas Ltda.
MicroExpress Ltda.
FW + FWH + XHARBOUR + HARBOUR + PELLES C + XDEVSTUDIO + XEDIT + BCC + VC_X86 + VCC_X64 + MINGW + R&R Reports + FastReport + Tdolphin + ADO + MYSQL + MARIADB + ORACLE
nnicanor@yahoo.com

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: BUG MARIADB SUPPORT FWH1906
Posted: Wed Sep 25, 2019 02:39 PM

We will check

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: BUG MARIADB SUPPORT FWH1906
Posted: Thu Sep 26, 2019 01:50 PM

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 (fw): Select all Collapse

#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?

Regards

G. N. Rao.

Hyderabad, India

nnicanor

Posts: 302

Joined: Fri Apr 23, 2010 04:30 AM

Re: BUG MARIADB SUPPORT FWH1906
Posted: Fri Sep 27, 2019 01:16 AM

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 (fw): Select all Collapse

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
;

Nicanor Martinez M.
Auditoria y Sistemas Ltda.
MicroExpress Ltda.
FW + FWH + XHARBOUR + HARBOUR + PELLES C + XDEVSTUDIO + XEDIT + BCC + VC_X86 + VCC_X64 + MINGW + R&R Reports + FastReport + Tdolphin + ADO + MYSQL + MARIADB + ORACLE
nnicanor@yahoo.com

nnicanor

Posts: 302

Joined: Fri Apr 23, 2010 04:30 AM

Re: BUG MARIADB SUPPORT FWH1906
Posted: Fri Sep 27, 2019 02:37 AM

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

Code (fw): Select all Collapse

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
;

Nicanor Martinez M.
Auditoria y Sistemas Ltda.
MicroExpress Ltda.
FW + FWH + XHARBOUR + HARBOUR + PELLES C + XDEVSTUDIO + XEDIT + BCC + VC_X86 + VCC_X64 + MINGW + R&R Reports + FastReport + Tdolphin + ADO + MYSQL + MARIADB + ORACLE
nnicanor@yahoo.com

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: BUG MARIADB SUPPORT FWH1906
Posted: Fri Sep 27, 2019 03:48 AM

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

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion