Page 1 of 2 12 LastLast
Results 1 to 40 of 43

Thread: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL to hashing

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Resolved [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL to hashing

    I did this in adodb differently as an addnew and then an update for AES columns, but thought I would try an insert for the whole row, which is less steps.

    Does anyone know if the cast goes with the column names, or does it go in the values of the insert into statement?

    What this does is encrypt to AES 256 a user password and their privileges into the program.
    Just to prevent snooping and stealing a password etc..., which likely would never happen...

    Code:
     'Insert new user id
     MyUserID = "New"
     pword = ""
     priv = ""
     PwordAes = "New"
     PrivilAes = "A11111111111111111111"
    
     cmd1.CommandText = "Insert into usertable (myname, myuser, password, priveliges, userdate, cast(aes_decrypt(password_aes, '0123456789') AS char), cast(aes_decrypt(priv_aes, '0123456789') AS char)) VALUES ('" & MyUserID & "', '" & pword & "', '" & priv & "', '" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "', '" & PwordAes & "','" & PrivilAes & "')"
     cmd1.ExecuteNonQuery()
    Last edited by sdowney1; Jun 13th, 2024 at 06:39 AM.

  2. #2

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    In vb6, I used an update to do this, after I had inserted a row using an adodb add new

    But I am certain, it could be done all at once.

    Code:
           'encrypt the priv string and keep original for aes_ field
           email = priv
            
           'cant do it says row cannot be located for updating, but can do this on an add new
            
           frmlogonrsusertable.Update
           
      'now update the blobs and current time
           David = Trim(txtPassword.Text) 'the password of the 'New' user
           frmlogoncnConnector.Execute "update usertable  set password_aes = AES_ENCRYPT('" & David & "','0123456789'), priv_aes =  AES_ENCRYPT('" & email & "','0123456789') where myuser = '" & Trim(Combo1.Text) & "'"
           'update the date time of the update for user
           frmlogoncnConnector.Execute "update usertable set userdate =  current_timestamp() where myuser = '" & Trim(Combo1.Text) & "'"

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Looks like it goes on the values side
    https://dev.mysql.com/doc/refman/8.0...functions.html

    There example
    Code:
    INSERT INTO t
    VALUES (1,AES_ENCRYPT('text', UNHEX(SHA2('My secret passphrase',512))));
    These code lines are pretty complex to write. And have to be perfect.

    Or you get that you have an error in your syntax message where... and it's not always obvious, the problem.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    So right now guessing maybe this, should be encrypt as it's an insert into.
    And it probably wont work.

    Code:
      
    cmd1.CommandText = "Insert into usertable (myname, myuser, password, priveliges, userdate, password_aes, priv_aes) VALUES ('" & MyUserID & "', '" & pword & "', '" & priv & "', '" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "', 'cast(aes_encrypt(" & PwordAes & ", '0123456789') AS char)', 'cast(aes_encrypt(" & PrivilAes & ", '0123456789') AS char)')"

  5. #5
    Lively Member
    Join Date
    Jun 2023
    Posts
    79

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    I'm not entirely sure about your question, but one thing I'll suggest that'll make it a bit easier: use parameters instead of string concatenation. Concatenation leaves you open to SQL Injection attacks, and parameters help prevent that.

    Maybe less important but very helpful: They make your queries easier to read and spot syntax errors.

    Code:
    _query = "Insert Into MyTable (Name, Number, Feets) Values (@Name, @Number, @Feets) "
    Dim oSQLCommand as New SqlCommand(_query, _conn)
    oSQLCommand.Parameters.Add("Name", SqlDataType.NVarChar).Value = strName.Trim
    oSQLCommand.Parameters.Add("Number", SqlDataType.Int).Value = intNumber
    oSQLCommand.Parameters.Add("Feets", SqlDataType.Float).Value = sngFeets
    Just as a quick and dirty example of how you would use them.

    Code:
    'This is harder to read
    "Insert Into MyTable (Name, Number, Feets) Values ('" & strName.Trim & "', " & intNumber & ", " & sngFeets & ") "
    'Than this
    "Insert Into MyTable (Name, Number, Feets) Values (@Name, @Number, @Feets) "

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Ok, will look at that, thanks

    I created a reader and if no row then created an insert into for this table, and it works
    I was having small issue as the table columns were not what I wanted, extra columns and not the same order, using an old DB, so deleted and recreated the missextra table.
    Then the insert into worked without naming columns.

    From here, people were saying leave out a value for auto increment column, that failed, use '' failed, only NULL worked.
    I highlighted in red.
    Disadvantage of not specifying column names in an insert is what happens if the table gets altered, it will fail.
    But I did figure it out for here, which encouraged me.

    https://stackoverflow.com/questions/...ed-columnfield

    For the Auto increment key, nothing worked for me except to put NULL for that value in the insert into statement.

    Code:
    	Public Sub Determinemissextra()
    		Dim tempto As String
    
    		Dim con2 As New MySqlConnection(frmlogonConnectstring)
    		Dim cmd2 As New MySqlCommand
    		cmd2.Connection = con2
    		con2.Open()
    
    		'cmd2.CommandText = "DROP TABLE IF EXISTS missextra"
    		'cmd2.ExecuteNonQuery()
    
    		cmd2.CommandText = "CREATE TABLE IF NOT EXISTS missextra" & "(Id INT AUTO_INCREMENT PRIMARY KEY," & "WhichBarc VARCHAR(10) DEFAULT ''," & "WhichBarcPatron VARCHAR(10) DEFAULT ''," & "LoadCombo char(1) DEFAULT ''," & "BarAN char(1) DEFAULT ''," & "FUMS char(1) DEFAULT '', " & "FUMA char(1) DEFAULT '', " & "DailyFine VARCHAR(10) DEFAULT ''," & "PChangeDate DATETIME, " & "ChangeDate DATETIME, " & "WhichCallNumb VARCHAR(10) DEFAULT ''," & "Libraryinfo1 VARCHAR(255) DEFAULT ''," & "Libraryinfo2 VARCHAR(255) DEFAULT ''," & "rNameFont VARCHAR(255) DEFAULT ''," & "rSizeFont INT DEFAULT 0, " & "rCharFont INT DEFAULT 0, " & "DSIN CHAR(1) DEFAULT ''," & "SLIMIT INT DEFAULT 0, " & "STIME INT DEFAULT 0, " & "LibraryName VARCHAR(45) Default '') ENGINE = INNODB"
    		cmd2.ExecuteNonQuery()
    		con2.Close()
    
    		Dim conn As New MySqlConnection(frmlogonConnectstring)
    		Dim cmd1 As New MySqlCommand
    		cmd1.Connection = conn
    		cmd1.CommandText = "Select * from missextra"
    
    		Try
    			conn.Open()
    
    			Using RDR = cmd1.ExecuteReader()
    				If RDR.Read Then
    					'You've got a row.
    					frmlogonWhichBarcode = RDR.Item("WhichBarc").ToString()
    					frmlogonWhichBarcodePatron = RDR.Item("WhichBarcPatron").ToString()
    					frmlogonLoadYesNoCombo = RDR.Item("LoadCombo").ToString()
    					frmlogonWhichCallNumber = RDR.Item("WhichCallNumb").ToString()
    					frmlogonRichFontName = RDR.Item("rNameFont").ToString()
    					frmlogonRichFontSize = RDR.Item("rSizeFont").ToString()
    					frmlogonRichFontChar = RDR.Item("rCharFont").ToString()
    					tempto = RDR.Item("BarAN").ToString()
    					frmlogonFUMA = RDR.Item("FUMA").ToString()
    					frmlogonFUMS = RDR.Item("FUMS").ToString()
    					DSIN = RDR.Item("DSIN").ToString()
    					SlimitR = RDR.Item("Slimit").ToString()
    					Stime = RDR.Item("Stime").ToString()
    
    					If frmlogonWhichBarcode = "" Then frmlogonWhichBarcode = "External"
    					If frmlogonWhichBarcodePatron = "" Then frmlogonWhichBarcodePatron = "External"
    					If frmlogonLoadYesNoCombo = "" Then frmlogonLoadYesNoCombo = "T"
    					If frmlogonWhichCallNumber = "" Then frmlogonWhichCallNumber = "Dewey"
    					If frmlogonRichFontName = "" Then frmlogonRichFontName = "MS Sans Serif"
    					If frmlogonRichFontSize = 0 Then frmlogonRichFontSize = 10
    
    					'frmLogon.RichTextBox1.Font = VB6.FontChangeName(frmLogon.RichTextBox1.Font, frmlogonRichFontName)
    					'frmLogon.RichTextBox1.Font = VB6.FontChangeSize(frmLogon.RichTextBox1.Font, frmlogonRichFontSize)
    					'frmLogon.RichTextBox1.Font = VB6.FontChangeGdiCharSet(frmLogon.RichTextBox1.Font, frmlogonRichFontChar)
    					If tempto = "" Then
    						frmlogonBarcAN = False
    						tempto = "F"
    					End If
    					If tempto = "F" Then
    						frmlogonBarcAN = False
    					Else
    						frmlogonBarcAN = True
    					End If
    					If frmlogonFUMA = "" Then frmlogonFUMA = "F"
    					If frmlogonFUMS = "" Then frmlogonFUMS = "F"
    					If DSIN = "" Then DSIN = "F"
    					If SlimitR <= 0 Then SlimitR = 20000
    					If Stime <= 0 Then Stime = 250
    
    				Else
    					'You don't have a row, add one.
    					'set the values for the program to use
    					frmlogonWhichBarcode = "External"
    					frmlogonWhichBarcodePatron = "External"
    					frmlogonLoadYesNoCombo = "T"
    					frmlogonBarcAN = False
    					frmlogonFUMS = "F"
    					frmlogonFUMA = "F"
    					frmlogonWhichCallNumber = "Dewey"
    					frmlogonRichFontName = "MS Sans Serif"
    					frmlogonRichFontSize = 10
    					frmlogonRichFontChar = 0
    					DSIN = "F"
    					SlimitR = 20000
    					Stime = 250
    
    					'insert a row
    					cmd1.CommandText = "Insert into missextra VALUES (NULL,'External', 'External', 'T', 'F', 'F', 'F', '0.25', '" &
    					DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "', '" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") &
    					"', 'Dewey', ' ', ' ', 'MS Sans Serif', '10', '0', 'F', '20000', '250', ' ')"
    
    					cmd1.ExecuteNonQuery()
    
    				End If
    				conn.Close()
    
    			End Using
    
    		Catch ex As Exception
    			If conn.State = 1 Then conn.Close()
    			Dim msg = ex.ToString()
    			MsgBox(msg,  , "Error")
    
    		End Try
    
    		'default values to insert
    		'	WhichBarc = "External"
    		'	WhichBarcPatron = "External"
    		'	LoadCombo = "T"
    		'	BarAN = "F"
    		'	FUMS = "F"
    		'	FUMA = "F"
    		'	Dailyfine = "0.25"
    		'	pchangedate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
    		'	changedate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
    		'	WhichCallNumb = "Dewey"
    		'   libraryinfo1
    		'   libraryinfo2
    		'	rNameFont = "MS Sans Serif"
    		'	rSizeFont = 10
    		'	rCharFont = 0
    		'	DSIN = "F"
    		'	Slimit = 20000
    		'	Stime = 250
    		'   LibraryName
    
    	End Sub

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Still have not been able to test my first post code, as I am working my way through frmLogon, to convert it's adodb coding.
    But was good to see a fairly complex insert into in the above sub.

    The app being what it is, none will be doing any attacks on the server from my code. It's not online and not like a lot of people will use it. But it is interesting to know.

  8. #8
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,619

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Quote Originally Posted by sdowney1 View Post
    Still have not been able to test my first post code, as I am working my way through frmLogon, to convert it's adodb coding.
    But was good to see a fairly complex insert into in the above sub.

    The app being what it is, none will be doing any attacks on the server from my code. It's not online and not like a lot of people will use it. But it is interesting to know.
    Not directly relevant to the code itself, but rather than encrypting a password it is generally better to hash it instead. By design encrypted data can be decrypted, that means if someone did get access to the database they could potentially decrypt the passwords. Hashes are one way and cannot be decrypted, this makes them a much more secure option - I know this isn't really relevant in this case but it is always worth knowing best practices...

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Quote Originally Posted by PlausiblyDamp View Post
    Not directly relevant to the code itself, but rather than encrypting a password it is generally better to hash it instead. By design encrypted data can be decrypted, that means if someone did get access to the database they could potentially decrypt the passwords. Hashes are one way and cannot be decrypted, this makes them a much more secure option - I know this isn't really relevant in this case but it is always worth knowing best practices...
    Thanks, I thought even the 256 bit AES encrypt function was overkill for my app.
    But when talking to MariaDB software company said to use it, so I did.
    I agree , if they got the password for that AES function, then it can be decrypted.

    I have my own, another encryption function.
    It will encrypt and decrypt, just pass the string back in and it makes or breaks encryption
    However, MariaDB, had serious problems using it, on a nul char in the string, it truncated the returned string.
    It does however work fine in windows registry, and MSSQL, and MySQL.
    Something about their MariaDB ODBC driver, as if I used the MySQL ODBC driver to connect to their MariaDB, it worked fine.

    So that forced my change to use their DB function if I wanted to encrypt a password in MariaDB.
    I just then did the same for a MySQL connection, and left MSSQL to use my sub

    I was only looking to prevent casual snooping, not a sophisticated attack from an expert hacker.

    I store a connection string in the windows registry and encrypt it using this function, that way no one can just read a connection string from registry and connect to the DB server.

    Code:
    Public Function Encrypt(ByRef secret As String, ByRef PassWord As String) As String
    	Dim yada As String
    	Dim L As Short
    
           Dim char_Renamed As String
    	Dim X As Short
    	
    	yada = frmlogonConnectstring
    	'????
    	'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
    	' priv = Encrypt("A11111111111111111111", "1234567890")
    	
    	' secret = "A11111111111111111111"
    	'  PassWord = "1234567890"
    	
    	L = Len(PassWord)
    	For X = 1 To Len(secret)
    		char_Renamed = CStr(Asc(Mid(PassWord, (X Mod L) - L * CShort((X Mod L) = 0), 1)))
    		Mid(secret, X, 1) = Chr(Asc(Mid(secret, X, 1)) Xor char_Renamed)
    	Next 
    	
    	Encrypt = secret
    	frmlogonConnectstring = yada
    	Exit Function

  10. #10
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    39,275

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Some good suggestions here. SQL injection is something that doesn't seem to get pointed out all that much when starting out, so it's something you learn 'along the way'. I certainly did. It makes no sense, either, as it's such a serious flaw that everybody should learn it up front: NEVER concatenate into a SQL statement anything that the user can enter. In this case, it isn't clear that the user CAN enter any of that data, so maybe it doesn't apply, but parameters would probably also answer the original question as a side effect.

    I feel that hashing is actually a pretty cool idea: We don't want to store your password, we want to store what your password would be if it were scrambled such that the password itself was lost. Lot safer that way. You can also salt the hash for even more security, and it's easy.

    However, to get to the initial question, I think you have it backwards. I'm not certain, because SQL can do some impressive things, but I think what you want is:

    INSERT INTO SomeTable (someColumn) VALUES (theConvertedValueHere)

    That's what makes the most sense to me. The first part is, "where do I put it", while the VALUES part is, "what am I putting there." SQL may allow you to do it either way, but the way you have it reads to me "convert whatever and put here" while the VALUES part is just "some value". The former approach just seems more like English to me.

    However, the parameters seems to make it even more clear, because it just gives a line of code saying "here's the value to put into this field", so the place to do the conversion seems kind of explicit in this case.
    My usual boring signature: Nothing

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    The first post, insert into, no values are directly entered from a form.
    The whole sub I posted, a few values can be entered from a form, but not in that module sub.

    No text inputs from text boxes are part of the values statement, except for daily fine amount can be changed which is a number not a string on a different form.
    Libraryinfo1 and 2 and library name are not part of that statement. They are columns in the table, I just write the statement to use ' ' for those. There is no form interaction in that sub I posted.

    I do have an update statement where they can be entered from text boxes in a different form called frmGlobalSettings.
    That sub routine I posted is just running in a module.vb file, no form involved.

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Playing with an insert into and getting UTF8 errors?
    Occurs when running cmd1.ExecuteNonQuery()

    When I run this insert statement
    Is it a syntax error or what? Seems like some other kind of error?
    Nothing I notice is utf8 about these strings being inserted?

    Could it be a driver issue?
    Lost here, about to try the other way of doing an insert earlier in this thread.

    Code:
       cmd1.CommandText = "Insert into usertable (myname, myuser, password, priveliges, userdate, password_aes, priv_aes) VALUES ('New', 'New', 'pass', 'G00000000000000000000', '" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "', cast(aes_encrypt('" & PwordAes & "', '0123456789') AS char), cast(aes_encrypt('" & PrivilAes & "', '0123456789') AS char))"
        cmd1.ExecuteNonQuery()
    Code:
        'Insert new user id
        Dim MyName As String = "New"
        MyUserID = "New"
        pword = ""
        priv = ""
        priv = "G00000000000000000000" '21chars long
        PwordAes = "New"
        PrivilAes = "A11111111111111111111"
    
        'https://www.tutorialspoint.com/encrypt-and-decrypt-a-string-in-mysql#:~:text=To%20encrypt%20and%20decrypt%20in%20MySQL%2C%20use%20the,values%28AES_ENCRYPT%28yourValue%2CyourSecretKey%29%29%3B%20select%20cast%28AES_DECRYPT%28yourColumnName%2C%20yourSecretKey%29%20as%20char%29%20from%20yourTableName%3B
        '   insert into demo63 values(AES_ENCRYPT('John','PASS'));
    
    
        cmd1.CommandText = "Insert into usertable (myname, myuser, password, priveliges, userdate, password_aes, priv_aes) VALUES ('New', 'New', 'pass', 'G00000000000000000000', '" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "', cast(aes_encrypt('" & PwordAes & "', '0123456789') AS char), cast(aes_encrypt('" & PrivilAes & "', '0123456789') AS char))"
        cmd1.ExecuteNonQuery()
    Code:
    ?ex
    {"Invalid utf8mb4 character string: '\x8B\xDAA\xFA\xABj;Y\xCD\xCC\xCDl\xC7\xD9\xD3A'"}
        Data: {System.Collections.ListDictionaryInternal}
        ErrorCode (System.Runtime.InteropServices.ExternalException): -2147467259
        ErrorCode: InvalidCharacterString {1300}
        HResult: -2147467259
        HelpLink: Nothing
        InnerException: Nothing
        IsTransient: False
        Message: "Invalid utf8mb4 character string: '\x8B\xDAA\xFA\xABj;Y\xCD\xCC\xCDl\xC7\xD9\xD3A'"
        Number: 1300
        Source: "MySqlConnector"
        SqlState: "HY000"
        StackTrace: "   at MySqlConnector.Core.ServerSession.<ReceiveReplyAsync>d__112.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at MySqlConnector.Core.ResultSet.<ReadResultSetHeaderAsync>d__2.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken)" & vbCrLf & "   at MySqlConnector.MySqlDataReader.<InitAsync>d__107.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)" & vbCrLf & "   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)" & vbCrLf
     & "   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__78.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)" & vbCrLf & "   at MySqlConnector.MySqlCommand.ExecuteNonQuery()" & vbCrLf & "   at WindowsApplication1.frmLogon.LoadComboBox() in C:\vbnetJess1\fonttest-marcbreaker test\FontTestVS2008 - Copy\FontTestVS2008\frmLogon.vb:line 704"
        TargetSite: {Void MoveNext()}
    Last edited by sdowney1; Jun 7th, 2024 at 03:44 PM.

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Here is the usertable having an insert being done

    Code:
    MariaDB [booksgood]> explain usertable;
    +--------------+-------------+------+-----+---------------------+----------------+
    | Field        | Type        | Null | Key | Default             | Extra          |
    +--------------+-------------+------+-----+---------------------+----------------+
    | Id           | int(11)     | NO   | PRI | NULL                | auto_increment |
    | MyName       | varchar(50) | YES  |     |                     |                |
    | Myuser       | varchar(50) | YES  | MUL |                     |                |
    | password     | varchar(50) | YES  |     |                     |                |
    | Priveliges   | char(21)    | YES  |     |                     |                |
    | TS           | timestamp   | YES  |     | NULL                |                |
    | UserDate     | datetime    | YES  |     | current_timestamp() |                |
    | password_aes | blob        | YES  |     | NULL                |                |
    | priv_aes     | blob        | YES  |     | NULL                |                |
    +--------------+-------------+------+-----+---------------------+----------------+
    9 rows in set (0.024 sec)
    
    MariaDB [booksgood]>

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    https://mysqlconnector.net/

    Says this is compatible with MariaDB 10.x (10.6, 10.11), 11.x (11.2)

    But, I am using 11.4???

    Does anyone know if that is an issue?

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Well, don't think it is that net driver. I switched to Mysql version and still getting the error
    To switch, I went to the properties references, removed mysqlconnector, and browsed to program files x86 mysql net driver and selected their data dll file.

    I think I prefer the mysqlconnector, first driver I installed, if nothing is wrong with it. Supposed to be faster.

    Who knows what's happening, must be something about the way code line is written?

    Code:
    ?ex
    {"Invalid utf8mb4 character string: '\x8B\xDAA\xFA\xABj;Y\xCD\xCC\xCDl\xC7\xD9\xD3A'"}
        Code: 0
        Data: {System.Collections.ListDictionaryInternal}
        ErrorCode: -2147467259
        HResult: -2147467259
        HelpLink: Nothing
        InnerException: Nothing
        Message: "Invalid utf8mb4 character string: '\x8B\xDAA\xFA\xABj;Y\xCD\xCC\xCDl\xC7\xD9\xD3A'"
        Number: 1300
        Source: "MySql.Data"
        SqlState: Nothing
        StackTrace: "   at MySql.Data.MySqlClient.MySqlStream.<ReadPacketAsync>d__30.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)" & vbCrLf & "   at MySql.Data.MySqlClient.NativeDriver.<GetResultAsync>d__45.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)" & vbCrLf & "   at MySql.Data.MySqlClient.Driver.<GetResultAsync>d__85.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)" & vbCrLf & "   at MySql.Data.MySqlClient.Driver.<NextResultAsync>d__84.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf 
    & "   at MySql.Data.MySqlClient.MySqlDataReader.<NextResultAsync>d__97.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at MySql.Data.MySqlClient.MySqlDataReader.<NextResultAsync>d__97.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)" & vbCrLf & "   at MySql.Data.MySqlClient.MySqlCommand.<ExecuteReaderAsync>d__111.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at MySql.Data.MySqlClient.MySqlCommand.<ExecuteReaderAsync>d__111.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at MySql.Data.MySqlClient.MySqlCommand.<ExecuteReaderAsync>d__111.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at System.Runtime.CompilerServic
    es.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)" & vbCrLf & "   at MySql.Data.MySqlClient.MySqlCommand.<ExecuteNonQueryAsync>d__100.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)" & vbCrLf & "   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()" & vbCrLf & "   at WindowsApplication1.frmLogon.LoadComboBox() in C:\vbnetJess1\fonttest-marcbreaker test\FontTestVS2008 - Copy\FontTestVS2008\frmLogon.vb:line 704"
        TargetSite: {Void MoveNext()}

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Quote Originally Posted by sdowney1 View Post
    I did this in adodb differently as an addnew and then an update for AES columns, but thought I would try an insert for the whole row, which is less steps.

    Does anyone know if the cast goes with the column names, or does it go in the values of the insert into statement?

    What this does is encrypt to AES 256 a user password and their privileges into the program.
    Just to prevent snooping and stealing a password etc..., which likely would never happen...

    Code:
     'Insert new user id
     MyUserID = "New"
     pword = ""
     priv = ""
     PwordAes = "New"
     PrivilAes = "A11111111111111111111"
    
     cmd1.CommandText = "Insert into usertable (myname, myuser, password, priveliges, userdate, cast(aes_decrypt(password_aes, '0123456789') AS char), cast(aes_decrypt(priv_aes, '0123456789') AS char)) VALUES ('" & MyUserID & "', '" & pword & "', '" & priv & "', '" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "', '" & PwordAes & "','" & PrivilAes & "')"
     cmd1.ExecuteNonQuery()

    I typed this in, but of course it wont work right as there is no encryption on the last 2 data columns
    Mysql and Mariadb have their own aes encryption builtin for use in the insert statement


    It does execute with no error, but it wont be ok, not yet.
    Highlighted red, no encryption, since it's a DB function, how can that be coded into parameters?


    Code:
                     Sqlquery = "Insert into usertable (myname, myuser, password, priveliges, userdate, password_aes, priv_aes) VALUES (@Myname, @Myuser, @password, @priveliges, @userdate, @password_aes, @priv_aes)"
    
                    Dim oSQLCommand As New MySqlCommand(Sqlquery, conn)
                    oSQLCommand.Parameters.Add("myname", MySqlDbType.VarChar).Value = "New"
                    oSQLCommand.Parameters.Add("myuser", MySqlDbType.VarChar).Value = "New"
                    oSQLCommand.Parameters.Add("password", MySqlDbType.VarChar).Value = "pass"
                    oSQLCommand.Parameters.Add("priveliges", MySqlDbType.VarChar).Value = "G00000000000000000000"
                    oSQLCommand.Parameters.Add("userdate", MySqlDbType.DateTime).Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
    
                    oSQLCommand.Parameters.Add("password_aes", MySqlDbType.Blob).Value = "New"
                    oSQLCommand.Parameters.Add("priv_aes", MySqlDbType.Blob).Value = "New"
    
                    oSQLCommand.ExecuteNonQuery()
    I can look into doing a general insert, then do an update to that row using cast aes function.
    That did work ok in vb6.
    Last edited by sdowney1; Jun 7th, 2024 at 04:53 PM.

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    I know these db command windows look like a mess
    but it did write the data into the table, course with no encrypting on the last inserted row.


    Code:
    MariaDB [booksgood]> select * from usertable;
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    | Id | MyName | Myuser | password | Priveliges            | TS   | UserDate            | password_aes     | priv_aes                         |
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    |  1 |        | New    |          |                       | NULL | 2024-04-15 11:11:34 | ??A??j;Y???l???A           | ?_?F??r???        ??O?LM??
    ???]? D                 |
    |  2 | eee    | eee    |          |                       | NULL | 2024-04-16 13:39:31 | _m?A"?~H{f??q#    | ?_?F??r?????O?LM??
    ???]? D                 |
    |  3 | New    | New    | pass     | G00000000000000000000 | NULL | 2024-06-07 17:25:22 | New              | New
                         |
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    3 rows in set (0.001 sec)
    
    MariaDB [booksgood]>

  18. #18
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,619

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Quote Originally Posted by sdowney1 View Post
    Playing with an insert into and getting UTF8 errors?
    Occurs when running cmd1.ExecuteNonQuery()

    When I run this insert statement
    Is it a syntax error or what? Seems like some other kind of error?
    Nothing I notice is utf8 about these strings being inserted?

    Could it be a driver issue?
    Lost here, about to try the other way of doing an insert earlier in this thread.
    What does the sql command look like before you execute it? Again, as Shaggy already mentioned, this would probably be a lot easier to debug if you were using parameters....

    The only place I can imagine that Unicode sequence is coming from is the aes_encrypt function, also do you need to cast the result of aes_encrypt as it returns a string (or a binary string) anyway?


    Then again I would probably use .net to hash the password and just pass that in as a parameter instead of getting the database to encrypt it.

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Quote Originally Posted by PlausiblyDamp View Post
    What does the sql command look like before you execute it? Again, as Shaggy already mentioned, this would probably be a lot easier to debug if you were using parameters....

    The only place I can imagine that Unicode sequence is coming from is the aes_encrypt function, also do you need to cast the result of aes_encrypt as it returns a string (or a binary string) anyway?


    Then again I would probably use .net to hash the password and just pass that in as a parameter instead of getting the database to encrypt it.
    I wrote a sub to use parameters, works fine but there won't be any AES encryption. Post #16

    Seems silly of MySQL designers to create a function that does not work well like AES?

    I don't know how to do that hash a password, have you got a link or something?
    I don't mind if it is not going to be a pain to figure it out.
    See you're talking to someone who has never done that, but I have heard of it.

    I do think I can figure out the cast thingy, I have been able to do it on updates to rows, just not on inserts.
    And I just don't know why that is.
    I had to do that also in vb6. Do a row update after an insert.

    Stuff like this just takes hours, sometimes days to figure out, really simple stuff, to me what I had should have just worked on the insert into statement, but no, it gives out some incomprehensible Unicode error. I have seen a few web examples of the MySQL AES function being used singly to insert a row, no examples though of multiple columns and also using cast as char in the statement.

  20. #20
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,619

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Probably easiest way to has a string is...
    Code:
    Dim pass = "Your password"
    Dim res = SHA256.HashData(Encoding.UTF8.GetBytes(pass))
    Console.WriteLine(Convert.ToBase64String(res))

  21. #21

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Quote Originally Posted by PlausiblyDamp View Post
    Probably easiest way to has a string is...
    Code:
    Dim pass = "Your password"
    Dim res = SHA256.HashData(Encoding.UTF8.GetBytes(pass))
    Console.WriteLine(Convert.ToBase64String(res))
    Will try that. Thanks

    And an update works for mysql aes encrypt function. An insert into does not for the aes encrypt function, which also did not work years ago for me in vb6.

    Just got up this am and tried that
    This worked after doing an insert into without any aes MySQL function in the insert statement.
    Code:
       'now update the blobs 
       'David = Trim(txtPassword.Text) 'the password of the 'New' user
       David = "Newer"
       email = "Newer"
       'oSQLCommand.CommandText = "update usertable  set password_aes = AES_ENCRYPT('" & David & "','0123456789'), priv_aes =  AES_ENCRYPT('" & email & "','0123456789') where myuser = '" & Trim(Combo1.Text) & "'"
    
       oSQLCommand.CommandText = "update usertable  set password_aes = AES_ENCRYPT('" & David & "','0123456789'), priv_aes =  AES_ENCRYPT('" & email & "','0123456789') where myuser = 'Gotcha'"
    
       oSQLCommand.ExecuteNonQuery()
    Results in table. Must be something wrong with the insert into statement, oh well.
    Maybe that cast as char thing.


    Code:
    MariaDB [booksgood]> select * from usertable;
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    | Id | MyName | Myuser | password | Priveliges            | TS   | UserDate            | password_aes     | priv_aes                         |
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    |  1 |        | New    |          |                       | NULL | 2024-04-15 11:11:34 | ??A??j;Y???l???A           | ?_?F??r???        ??O?LM??
    ???]? D                 |
    |  2 | eee    | eee    |          |                       | NULL | 2024-04-16 13:39:31 | _m?A"?~H{f??q#    | ?_?F??r?????O?LM??
    ???]? D                 |
    |  3 | New    | New    | pass     | G00000000000000000000 | NULL | 2024-06-07 17:25:22 | New              | New
                         |
    |  4 | Gotcha | New    | pass     | G00000000000000000000 | NULL | 2024-06-08 08:49:57 | New              | New
                         |
    |  5 | Gotcha | Gotcha | pass     | G00000000000000000000 | NULL | 2024-06-08 08:51:37 | @~R{G?m3?*?%??      | @~R{G?m3?*?%??                      |
    |  6 | Gotcha | Gotcha | pass     | G00000000000000000000 | NULL | 2024-06-08 08:53:41 | @~R{G?m3?*?%??      | @~R{G?m3?*?%??                      |
    |  7 | Gotcha | Gotcha | pass     | G00000000000000000000 | NULL | 2024-06-08 08:55:11 | @~R{G?m3?*?%??      | @~R{G?m3?*?%??                      |
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    7 rows in set (0.000 sec)
    
    MariaDB [booksgood]>

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    yes, it was cast as char, you are only supposed to use that in the select statement to get the results out, not use it to put values into a table.
    That was why it was saying invalid utf8 string

    this code now works on an insert into.
    It is nice to see something finally figured out as to why.

    Code:
         cmd1.CommandText = "Insert into usertable (myname, myuser, password, priveliges, userdate, password_aes, priv_aes) VALUES ('New', 'New', 'pass', 'G00000000000000000000', '" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "', aes_encrypt('" & PwordAes & "', '0123456789'), aes_encrypt('" & PrivilAes & "', '0123456789'))"
         cmd1.ExecuteNonQuery()
    last row shows the above code did work for the insert


    MariaDB [booksgood]> select * from usertable;
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    | Id | MyName | Myuser | password | Priveliges | TS | UserDate | password_aes | priv_aes |
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    | 1 | | New | | | NULL | 2024-04-15 11:11:34 | ??A??j;Y???l???A | ?_?F??r??? ??O?LM??
    ???]? D |
    | 2 | eee | eee | | | NULL | 2024-04-16 13:39:31 | _m?A"?~H{f??q# | ?_?F??r?????O?LM??
    ???]? D |
    | 3 | New | New | pass | G00000000000000000000 | NULL | 2024-06-07 17:25:22 | New | New
    |
    | 4 | Gotcha | New | pass | G00000000000000000000 | NULL | 2024-06-08 08:49:57 | New | New
    |
    | 5 | Gotcha | Gotcha | pass | G00000000000000000000 | NULL | 2024-06-08 08:51:37 | @~R{G?m3?*?%?? | @~R{G?m3?*?%?? |
    | 6 | Gotcha | Gotcha | pass | G00000000000000000000 | NULL | 2024-06-08 08:53:41 | @~R{G?m3?*?%?? | @~R{G?m3?*?%?? |
    | 7 | Gotcha | Gotcha | pass | G00000000000000000000 | NULL | 2024-06-08 08:55:11 | @~R{G?m3?*?%?? | @~R{G?m3?*?%?? |
    | 8 | New | New | pass | G00000000000000000000 | NULL | 2024-06-08 09:06:22 | ??A??j;Y???l???A | ?_?F??r??? ??O?LM??
    ???]? D |
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    8 rows in set (0.000 sec)

    MariaDB [booksgood]>

  23. #23

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    https://stackoverflow.com/questions/...ashdata-method

    unfortunately hashdata is not a member of sha256 unless you are targeting dot net 5 and higher.
    This app is on 4.8
    Last time I switched to higher like, 8 or 6, it created lots of errors.
    Such as 'My.Application.Info.Title' went to an empty string and a few other strange things happened, such as

    all coding like this failed with errors
    Code:
            Dim position2 = Image1.PointToClient(Label2.PointToScreen(Point.Empty))
            With Label2
                .Parent = Image1
                .Location = position2
            End With


    I wonder if net 5 would do that too, probably.

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    looking at hashing documentation for MYSQL .
    https://dev.mysql.com/doc/refman/8.0...on_aes-encrypt

    @key_str, @init_vector, @crypt_str, are they COLUMN NAMES?

    And those values are being set into a table's column names? In a row?

    I figure this is just a db command, not a column name
    mysql> SET block_encryption_mode = 'aes-256-cbc';

    And these are column names being assigned values
    mysql> SET @key_str = SHA2('My secret passphrase',512);
    mysql> SET @init_vector = RANDOM_BYTES(16);
    mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);

    Then this must be the data retrieval statement here to compare to a user password?
    mysql> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);

    And the result output in a cmd window
    +-------------------------------------------------------------+
    | CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
    +-------------------------------------------------------------+
    | text |
    +-------------------------------------------------------------+

    They just never tell you the whole story do they.

  25. #25
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,619

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    It looks like key_str etc. are just parameters to the aes_encrypt function.

    Code:
     SET @key_str = SHA2('My secret passphrase',512);
    The above code is just setting a variable called key_str to the result of the SHA2 function.


    Code:
    SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
    is just returning the result of the CAST(....) statement

  26. #26

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Quote Originally Posted by PlausiblyDamp View Post
    It looks like key_str etc. are just parameters to the aes_encrypt function.

    Code:
     SET @key_str = SHA2('My secret passphrase',512);
    The above code is just setting a variable called key_str to the result of the SHA2 function.


    Code:
    SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
    is just returning the result of the CAST(....) statement
    If so, from what column? Where is it getting a value?
    Doesn't a hash have to be stored in a column in the database, if so, where is it in this example?

    If a db stores things, something must be stored somewhere.

    mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);

  27. #27

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Is it possible that this function is supposed to return a collectable value from this code line.
    Except there is no SELECT here to do that, it is just setting a value to something.
    I use set to set values into a column in a DB on an update like this, and it works.

    red text are columns in a table called usertable

    "update usertable set password_aes = AES_ENCRYPT('" & David & "','0123456789'), priv_aes = AES_ENCRYPT('" & email & "','0123456789') where myuser = 'Gotcha'"

    The process here escapes me.

    mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);

    Then you take that value and store it in a column, as in the row for the user.

    And somehow use that column to run this code line

    mysql> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);

    To retrieve the decrypted text?
    They don't really say exactly how to implement their code example.
    Last edited by sdowney1; Jun 8th, 2024 at 01:21 PM.

  28. #28

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Put it into the sub like this, to see if I can make something work like they show.

    Code:
                    cmd1.CommandText = "SET block_encryption_mode = 'aes-256-cbc'"
                    cmd1.ExecuteNonQuery()
    
                    cmd1.CommandText = "SET @key_str = SHA2('My secret passphrase',512)"
                    cmd1.ExecuteNonQuery()
    
                    cmd1.CommandText = "SET @init_vector = RANDOM_BYTES(16)"
                    cmd1.ExecuteNonQuery()
    
                    cmd1.CommandText = "SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector)"
                    cmd1.ExecuteNonQuery()
    
                    cmd1.CommandText = "SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR)"
                    cmd1.ExecuteNonQuery()
    second execute has an ex, first line passed ok

    set 'Allow User Variables=true' in connection string, have to see how to do that.

    Code:
    ?ex
    {"Parameter '@key_str' must be defined. To use this as a variable, set 'Allow User Variables=true' in the connection string."}
        Data: {System.Collections.ListDictionaryInternal}

  29. #29

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    found explanation here https://stackoverflow.com/questions/...%3DPassword%3B

    Example shows the way to do that
    Code:
    string connectionString = @"server=ServerName;database=DatabaseName;uid=UserName;pwd=Password;
                                               Allow User Variables=True";
    Doing this allows all the statements to execute from their example code, which is good.
    Now need to play with how that is going to help me.

    Code:
      Dim ConStrUserVar As String = frmlogonConnectstring & "Allow User Variables=True;"
      Dim conn As New MySqlConnection(ConStrUserVar)
    Last edited by sdowney1; Jun 8th, 2024 at 01:59 PM.

  30. #30

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: Insert new user into usertable using cast(aes_decrypt for MySQL and MariaDB

    Complete rewrite using salted hashes
    It works.
    Not saying it is final, but I finally figured it all out with help from several people, here and on stack.

    Since I have a basic model to work from, adapting other forms should be much easier to work with this hashing stuff.
    I will likely drop the Timestamp column TS, as DateTime is more useful to me.
    Some of those other columns I used with MSSQL and frankly, not going to bother with any MSSQL Server stuff until I get all this worked out with MySQL and MariaDB.

    This reads the table and compares privilege hashes.
    if it finds none with all privs, it adds a new user to the table with hashes too.
    Helps a lot to say 'as HashA1' in the select to cut down the verbosity of writing out the column name created from the select action.

    No user input here, so did not use parameters for the insert.

    Code:
       Private Sub loadcombobox()
    
           Dim ConStrUserVar As String = frmlogonConnectstring & "Allow User Variables=True;"
           Dim conn As New MySqlConnection(ConStrUserVar)
           Dim PwordAes, PrivilAes As String
           Dim Xx As Integer = 0
           Dim hashsalt, hashpass, hashpriv, saltedhash, hashcomparepriv As String
           hashsalt = ""
           hashpass = ""
           hashpriv = ""
           hashcomparepriv = ""
    
           'to test a user for all priv, need only one!! make sure one exist.
           'load combo1 and make sure someone has all admin priveliges
           Combo1.Items.Clear()
    
           Try
               Using conn
                   conn.Open()
                   Dim cmd1 As New MySqlCommand
                   cmd1.Connection = conn
                   ' cmd1.CommandText = "DROP TABLE IF EXISTS usertable"
                   'cmd1.ExecuteNonQuery()
                   'create user table
                   'cmd1.CommandText = "CREATE TABLE IF NOT EXISTS usertable " & "(Id INT AUTO_INCREMENT PRIMARY KEY," & "MyName VARCHAR(50) DEFAULT ''," & "Myuser VARCHAR(50) DEFAULT ''," & "password VARCHAR(50) DEFAULT ''," & "Priveliges CHAR (21) DEFAULT '',TS TIMESTAMP," & "UserDate DATETIME DEFAULT CURRENT_TIMESTAMP, " & "password_aes varchar(150), " & "priv_aes varchar(150), " & "salt2 varchar(20)) ENGINE = INNODB"
                   'cmd1.ExecuteNonQuery()
    
                   'cmd1.CommandText = "CREATE INDEX MyuserIndex On usertable(Myuser)"
                   'cmd1.ExecuteNonQuery()
    
                   'add the names to combobox as in loop till it is full from uertable
                   'need to figure out the hash for = "A11111111111111111111"  & the stored salt2 in the retrieved row
                   'will be different every time as salt2 changes for every row
                   'hash A1111111111111111 & salt2 value and compare to stored hash, if matches then someone has all privs.
                   cmd1.CommandText = "Select myuser, priv_aes, SHA2(concat('A11111111111111111111',salt2), 512) as HashA1 from usertable"
    
                   Using RDR = cmd1.ExecuteReader()
                       Do While RDR.Read
                           Combo1.Items.Add(RDR.Item("MYUser").ToString())
                           hashpriv = RDR.Item("priv_aes").ToString()
                           'one user needs to be full admin
                           hashcomparepriv = RDR.Item("HashA1").ToString()
                           If hashcomparepriv = hashpriv Then Xx += 1
                       Loop
                   End Using
    
                   If Xx = 0 Then 'as in no one has all privileges
                       'Insert new user id
                       Dim MyName As String = "New"
                       MyUserID = "New"
                       pword = ""
                       priv = "" '21chars long
                       PwordAes = "New"
                       PrivilAes = "A11111111111111111111"
    
                       cmd1.CommandText = "Select Hex(RANDOM_BYTES(10))" 'this makes a string 20 chars long
                       Using RDR = cmd1.ExecuteReader()
                           If RDR.Read Then
                               hashsalt = RDR.Item("Hex(RANDOM_BYTES(10))").ToString()
                           End If
                       End Using
    
                       'append salt to create a salted hash for password
                       saltedhash = "New" & hashsalt
    
                       cmd1.CommandText = "SELECT SHA2('" & saltedhash & "', 512)"
                       Using RDR = cmd1.ExecuteReader()
                           If RDR.Read Then hashpass = RDR.Item("SHA2('" & saltedhash & "', 512)").ToString()
                       End Using
    
                       'append salt to create a salted hash for priveliges
                       saltedhash = "A11111111111111111111" & hashsalt
    
                       cmd1.CommandText = "SELECT SHA2('" & saltedhash & "', 512)"
                       Using RDR = cmd1.ExecuteReader()
                           If RDR.Read Then hashpriv = RDR.Item("SHA2('" & saltedhash & "', 512)").ToString()
                       End Using
    
                       'now we have hashpass, hashpriv, hashsalt to insert into a row
                       cmd1.CommandText = "Insert into usertable (myname, myuser, password, priveliges, userdate, password_aes, priv_aes, salt2) VALUES ('New', 'New', '', '', '" & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & "', '" & hashpass & "', '" & hashpriv & "', '" & hashsalt & "')"
                       cmd1.ExecuteNonQuery()
                       Combo1.Items.Add("New")
                   End If
               End Using
           Catch ex As Exception
               Dim msg = ex.ToString()
               MsgBox(msg,  , "Error")
           End Try
    
       End Sub

  31. #31
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,347

    Re: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL and Ma

    https://security.stackexchange.com/q...-to-store-salt is something you should read up to understandand basic points about storing credentials.

    Also note that MySQL is completely ill equiped with crypto primitives. Using AES_EN/DECRYPT to store passwords is counter-productive and not recommended. You don't need your passwords being able to be decrypted/reversed at all (not even by your app).

    You need KFD. Precisely you need HMAC instead of raw hash functions or some wrapper over HMAC like PBKDF2 or even better completely ready-made Nuget packages with scrypt which is implemented over HMAC.

    Use client code to compute password's hash and issue INSERT usertable(..., password) VALUES (..., $computedHash) so that passwords do not hit wire/database in plain-text.

    Btw, if you insist on using MySQL crypto primitives you might as well store password in plain-text -- there is no use of any of the crypto efforts you posted here and your passwords will get decrypted immediately when leaked.

    cheers,
    </wqw>

  32. #32

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL and Ma

    Quote Originally Posted by wqweto View Post
    https://security.stackexchange.com/q...-to-store-salt is something you should read up to understandand basic points about storing credentials.

    Also note that MySQL is completely ill equiped with crypto primitives. Using AES_EN/DECRYPT to store passwords is counter-productive and not recommended. You don't need your passwords being able to be decrypted/reversed at all (not even by your app).

    You need KFD. Precisely you need HMAC instead of raw hash functions or some wrapper over HMAC like PBKDF2 or even better completely ready-made Nuget packages with scrypt which is implemented over HMAC.

    Use client code to compute password's hash and issue INSERT usertable(..., password) VALUES (..., $computedHash) so that passwords do not hit wire/database in plain-text.

    Btw, if you insist on using MySQL crypto primitives you might as well store password in plain-text -- there is no use of any of the crypto efforts you posted here and your passwords will get decrypted immediately when leaked.

    cheers,
    </wqw>
    I am happy with sha2 512 salted hashes, that was what people here suggested I do.
    I read there is a SHA3 in the works which I suppose will someday be needed.

    I dont think that Sha2 stuff is as bad as plain text. This app also no one is going to want to hack, its not online, it only runs on a LAN.

  33. #33

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL and Ma

    Look at the intense coding you have to do to generate a SHA2 512 hash in vs2022 versus Mysql single DB command which people say is a waste of time as they can just sit there and watch raw text fly between the PC and the DB server.

    I am on 4.8 NET, is there an easier way to do that?
    You know what, it's never going to be good enough is it.


    Code:
        Public Function PWDhash(ByVal password As String)
            Using sha512Hash As SHA512 = SHA512.Create()
                Return GetHash(sha512Hash, password)
            End Using
        End Function
    
    
        Private Function GetHash(ByVal hashAlgorithm As HashAlgorithm, ByVal input As String) As String
    
            ' Convert the input string to a byte array and compute the hash.
            Dim data As Byte() = hashAlgorithm.ComputeHash(Encoding.UTF8.GetBytes(input))
    
            ' Create a new Stringbuilder to collect the bytes
            ' and create a string.
            Dim sBuilder As New StringBuilder()
            ' Loop through each byte of the hashed data 
            ' and format each one as a hexadecimal string.
            For i As Integer = 0 To data.Length - 1
                sBuilder.Append(data(i).ToString("x2"))
            Next
            ' Return the hexadecimal string.
            Return sBuilder.ToString()
        End Function
    result matches MySQL
    Code:
    ?hashedpwd
    "7ab08d4a457dfd55762628078459fad10624422c6a5619dd4b2877bd58cac62b04aa0a5c14d46f02e66e47ac5e59cf52b2cb324a3405cb38d1ce9978cc0376fa"

    Code:
    MariaDB [booksgood]> SELECT SHA2('password here', 512);
    
    +----------------------------------------------------------------------------------------------------------------------------------+
    | SHA2('password here', 512)
               |
    +----------------------------------------------------------------------------------------------------------------------------------+
    | 7ab08d4a457dfd55762628078459fad10624422c6a5619dd4b2877bd58cac62b04aa0a5c14d46f02e66e47ac5e59cf52b2cb324a3405cb38d1ce9978cc0376fa |
    +----------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.004 sec)
    
    MariaDB [booksgood]>

  34. #34
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    6,094

    Re: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL and Ma

    I dont think that Sha2 stuff is as bad as plain text. This app also no one is going to want to hack, its not online, it only runs on a LAN.
    Then why all the trouble at all?
    And why do you call it “intense coding”?

  35. #35
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,619

    Re: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL and Ma

    Code:
    Public Function PWDhash(ByVal password As String)
        Using sha512Hash As SHA512 = SHA512.Create()
            Return GetHash(sha512Hash, password)
        End Using
    End Function
    
    Private Function GetHash(ByVal hashAlgorithm As HashAlgorithm, ByVal input As String) As String
        ' Convert the input string to a byte array and compute the hash.
        Dim data As Byte() = hashAlgorithm.ComputeHash(Encoding.UTF8.GetBytes(input))
    
        Return BitConverter.ToString(data).Replace("-"c, String.Empty)
    End Function
    should do the same as your code.

    In fact the entire thing could be just
    Code:
    Public Function PWDhash(ByVal password As String)
        Using sha512Hash As SHA512 = SHA512.Create()
            Dim data As Byte() = sha512Hash.ComputeHash(Encoding.UTF8.GetBytes(password))
            Return BitConverter.ToString(data).Replace("-"c, String.Empty)
        End Using
    End Function
    If you were on .Net 8 it could be reduced to
    Code:
    Public Function PWDhash(ByVal password As String)
        Dim data As Byte() = SHA512.HashData(Encoding.UTF8.GetBytes(password))
        Return BitConverter.ToString(data).Replace("-"c, String.Empty)
    End Function
    Last edited by PlausiblyDamp; Jun 12th, 2024 at 05:05 PM.

  36. #36

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL and Ma

    Quote Originally Posted by PlausiblyDamp View Post
    Code:
    Public Function PWDhash(ByVal password As String)
        Using sha512Hash As SHA512 = SHA512.Create()
            Return GetHash(sha512Hash, password)
        End Using
    End Function
    
    Private Function GetHash(ByVal hashAlgorithm As HashAlgorithm, ByVal input As String) As String
        ' Convert the input string to a byte array and compute the hash.
        Dim data As Byte() = hashAlgorithm.ComputeHash(Encoding.UTF8.GetBytes(input))
    
        Return BitConverter.ToString(data).Replace("-"c, String.Empty)
    End Function
    should do the same as your code.

    In fact the entire thing could be just
    Code:
    Public Function PWDhash(ByVal password As String)
        Using sha512Hash As SHA512 = SHA512.Create()
            Dim data As Byte() = sha512Hash.ComputeHash(Encoding.UTF8.GetBytes(password))
            Return BitConverter.ToString(data).Replace("-"c, String.Empty)
        End Using
    End Function
    If you were on .Net 8 it could be reduced to
    Code:
    Public Function PWDhash(ByVal password As String)
        Dim data As Byte() = SHA512.HashData(Encoding.UTF8.GetBytes(password))
        Return BitConverter.ToString(data).Replace("-"c, String.Empty)
    End Function
    Thanks will look at that.
    One advantage I can see is if I ever recode to also use MSSQL, having it all generated on the PC makes it easier to manage hashing.

    I gave up trying net 8 at least for a while since it gave me trouble with things.

  37. #37

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL and Ma

    Quote Originally Posted by Arnoutdv View Post
    Then why all the trouble at all?
    And why do you call it “intense coding”?
    Because it is never good enough.
    I prefer using simple code methods.
    But for this, I could see the reality of some weirdo watching plain text fly by looking for an opportunity to cause havoc cause they like doing that for fun.
    Why put anyone who might end up using your code, have such a problem if you can stop it. And looks like I can do that, so decided might as well.

  38. #38

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    873

    Re: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL and Ma

    I am getting a lot of conflicting information on who is doing the hashing, server or client side.

    https://security.stackexchange.com/q...ash-a-password

    Seems like either way is acceptable and also unacceptable depending on nothing at all, just people's opinions.
    What is your opinion?

    I don't really get this argument here

    Think about the purpose of hashing.

    If someone breaks into your database they will not find passwords but only hashes which need to be brute forced into passwords in order to be useful.

    By allowing the client to send a hash instead of the password you effectively make the hash the new password and all benefits are lost. i.e. if someone gets into the database they can instantly log into all accounts with the data they find there.

    Therefor the client should always send the password and the server should do the hashing.
    If you google this you will see what I am seeing, maybe....

    Maybe it depends on TLS securing traffic between server and client. Some have argued client-side hashing ruins password managers.
    And with https it is not a concern to send a plain text password to be hashed on the server side.

    To me it makes more sense to hash on the client and send the hash to be stored on the server.

  39. #39
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,347

    Re: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL and Ma

    Quote Originally Posted by sdowney1 View Post
    I am getting a lot of conflicting information on who is doing the hashing, server or client side.
    There are different kind of servers. I would prefer hashing to be performed on app servers instead of on *the* database server. The former scale (horizontally) quite effortlessly and scrypt is CPU and memory intensive i.e. it deliberately takes close to a second to calculate a single hash while the database server is usually the single most expensive hardware and license-wise server and cannot easily scale in any direction so performing scrypt (or other hashing) there is a complete waste of company resources.

    cheers,
    </wqw>

  40. #40
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,619

    Re: [RESOLVED] Insert new user into usertable using cast(aes_decrypt for MySQL and Ma

    Quote Originally Posted by sdowney1 View Post
    I am getting a lot of conflicting information on who is doing the hashing, server or client side.

    https://security.stackexchange.com/q...ash-a-password

    Seems like either way is acceptable and also unacceptable depending on nothing at all, just people's opinions.
    What is your opinion?

    I don't really get this argument here

    If you google this you will see what I am seeing, maybe....

    Maybe it depends on TLS securing traffic between server and client. Some have argued client-side hashing ruins password managers.
    And with https it is not a concern to send a plain text password to be hashed on the server side.

    To me it makes more sense to hash on the client and send the hash to be stored on the server.
    I am normally not a big fan of pointing people to youtube to learn things, however I am happy to make an exception for certain channels - https://www.youtube.com/watch?v=8ZtInClXe1Q is well worth watching when it comes to security.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width