[RESOLVED] Found a 'bug' in MariaDB ODBC connector 32 bit version 3.1 and 3.2

[sdowney1]

I was on a help forum at slack for MariaDB https://app.slack.com/client/T01UEBHVADU/C02R1CJ0LD6

and suggestion was post it at
https://jira.mariadb.org/browse/ODBC-418

It seems to me extended char support in their driver is not as extensive as the Mysql 8 ODBC driver which just works even on a MariaDB fine using my encrypt function.

And this function has worked fine since mysql 3 server and also the past 25 years of ms sql server. No problems updating, inserting etc...extended chars in the char column, not until I tried MariaDB and their connector. What is also true is the Mysql ODBC driver version 8.0.35 both unicode and ANSI work fine with MariaDB version 11.3 64 bit using this encrypt function, which points out it's their ODBC driver that differs markedly.


That function swaps chars around into extended chars and back again when the encrypted string is run back thru the function.

Since the behavior of the driver differs so much from MySQL and MS SQL, I still think of it as a bug. Using UTF8 was no help

[sdowney1]

This is the function and this also works to encrypt a string to put in the windows registry, where I store the connection string to a database

Public Function Encrypt(secret As Variant, PassWord As Variant) As Variant

'The passwords and privileges are encrypted with "1234567890"
'the connection string is encrypted with "123456789"
'Win98 had a problem with 1234567890

' secret = the string you wish to encrypt or decrypt.
' PassWord = the password with which to encrypt the string.
On Error GoTo errhandler

L = Len(PassWord)
For X = 1 To Len(secret)
Char = Asc(Mid(PassWord, (X Mod L) - L * ((X Mod L) = 0), 1))
Mid(secret, X, 1) = Chr(Asc(Mid(secret, X, 1)) Xor Char)
Next

Encrypt = secret
Exit Function
errhandler:

Err.Number = 0
On Error GoTo errhandler2

'like chinese unicode???

L = Len(PassWord)
For X = 1 To Len(secret)
Char = AscW(Mid(PassWord, (X Mod L) - L * ((X Mod L) = 0), 1))
Mid(secret, X, 1) = ChrW(AscW(Mid(secret, X, 1)) Xor Char)
Next

Encrypt = secret
Exit Function
errhandler2:

Encrypt = secret


End Function

[sdowney1]

Their ODBC driver inputs a string of 21 characters into the char(21) column
Did an insert on an empty table using the program I made.

Query the database and the length shows 21

MariaDB [weird3]> select priveliges, LENGTH(priveliges) from usertable as lengthofname;
+-----------------------+--------------------+
| priveliges | LENGTH(priveliges) |
+-----------------------+--------------------+
| p | 21 |
+-----------------------+--------------------+
1 row in set (0.006 sec)


BUT when my program using their ODBC driver selects that same data, it comes back out as only 10 chars.
shown using the intermediate window in vb6

?len(frmlogon.rsusertable!priveliges)
10
p 



My opening of that recordset code
frmLogon.rsusertable.CursorLocation = adUseClient
SQLQuery = "Select myuser, password, priveliges from usertable"

'okay, it has the password in the string before the open, after the open, the password is removed.
frmLogon.cnConnector.ConnectionString = frmLogon.Connectstring
frmLogon.cnConnector.Open
'changed 3/19/2024 from adopendynamic
frmLogon.rsusertable.Open SQLQuery, frmLogon.cnConnector, adOpenForwardOnly, adLockOptimistic

frmlogo.rsusertable!priveliges only gets 10 chars back out, when the length should be 21 chars

Now all this above code works fine with MySql and MS SQL.

[sdowney1]

the connection string to the MariaDB is this

Provider=MSDASQL.1;DRIVER={MariaDB ODBC 3.1 Driver};DATABASE=weird3;SERVER=localhost;UID=root;PASSWORD=root;PORT=3308;OPTION=2;

All of the data in this column are ascii and extended ASCII
I went thru all 21 chars in a fail to retrieve properly event after a seemingly good insert of 21 ascii chars.
Decimal values are
112,3,2,5,4,7,6,9,8,1,0,3,2,5,4,7,6,9,8,1,0

and the description of these ascii decimals is for each character

p, ETX,STX,ENQ,EOT,BEL,ACK,TAB,BS,SOH,NUL,ETX,STX,ENQ,EOT,BEL,ACK,TAB,BS,SOH,NUL

if it gets 10 chars out, what happens at position 11?
position 11 is NUL

could be the reason their driver fails at a nul char??

[sdowney1]

Same MariaDB but using the MySQL 8.0 driver
Returned string is now 21 characters long

?passwordy
New
?frmlogon.rsusertable!priveliges
p 
 


21

using MariaDB driver this
10
p 

?frmlogon.Connectstring

Provider=MSDASQL.1;DRIVER={MySQL ODBC 8.0 ANSI Driver};DATABASE=weird3;SERVER=localhost;UID=root;PASSWORD=root;PORT=3308;OPTION=2;

Provider=MSDASQL.1;DRIVER={MariaDB ODBC 3.1 Driver};DATABASE=weird3;SERVER=localhost;UID=root;PASSWORD=root;PORT=3308;OPTION=2;

[sdowney1]

MariaDB wants an ODBC trace. Was thinking it can be found in the ODBC 32 bit data source admin program.
Going to delete the database. Start my program, let it create its database. What it does is an insert if no user is found in my program in table usertable. Then go to user maintenance in my program, view that user and try an update, where the whole thing crashes.

[sdowney1]

It is resolved but not solved.
I rewrote my program to us aes_encrypt aes_decrypt, which does also work fine in vb6 using ado recordsets.
I left TSQL MS as it was, and I just used the same aes functions with mysql.

Some command structures differ between MariaDB and MySQL. I distinguished between the ones that differ by looking thru the connection string.
So far, I like MariaDB better, I am using 11.3 64 bit than both the others. It works fine for me, bit I am also not doing anything super complex.