Page 1 of 1
BUG MARIADB SUPPORT FWH1906
Posted: Wed Sep 25, 2019 2:02 am
by 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
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
Re: BUG MARIADB SUPPORT FWH1906
Posted: Wed Sep 25, 2019 2:39 pm
by nageswaragunupudi
We will check
Re: BUG MARIADB SUPPORT FWH1906
Posted: Thu Sep 26, 2019 1:50 pm
by 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
#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?
Re: BUG MARIADB SUPPORT FWH1906
Posted: Fri Sep 27, 2019 1:16 am
by 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
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
;
Re: BUG MARIADB SUPPORT FWH1906
Posted: Fri Sep 27, 2019 2:37 am
by nnicanor
Hi, after change on fields type now works fine. thanks.
Code: Select all
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
;
Re: BUG MARIADB SUPPORT FWH1906
Posted: Fri Sep 27, 2019 3:48 am
by nageswaragunupudi
You can use either TINYBLOB and VARBINARY(n). Only depends on your taste.