Results 1 to 25 of 25

Thread: [RESOLVED] Open another reader, gives ex, "This MySqlConnection is already in use. See https://f

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Resolved [RESOLVED] Open another reader, gives ex, "This MySqlConnection is already in use. See https://f

    Basically read some stuff.

    Then later in the sub within the TRY block open another reader to read something else

    results in an ex when this line runs
    Code:
    Using RDR2 = cmd1.ExecuteReader()
    How do you solve that?

    first rdr works fine
    Code:
      Try
          conn.Open()
          Using RDR = cmd1.ExecuteReader()
              Do While RDR.Read
                  Combo1.Items.Add(RDR.Item("MYUser").ToString())
                  'one user needs to be full admin, and this is old, needs to be using AES field
                  If (RDR.Item("cast(aes_decrypt(priv_aes, '0123456789') AS char)").ToString()) = "A11111111111111111111" Then Xx += 1
              Loop
          End Using
    
    
      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()
    
    
    
    then do this and whammo, the ex happens
    I tried opening closing conn, even tried a new conn2, a new rdr2
    Tried a separate TRY block too.
    
    
             conn.Close()
             conn.Open()
    
             cmd1.CommandText = "SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR)"
    
             cmd1.ExecuteReader()
             Dim textrdr As String
             Using RDR2 = cmd1.ExecuteReader()
                 Do While RDR2.Read
                     textrdr = RDR2.Item("CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR)").ToString()
                 Loop
    
             End Using
    Code:
    ?ex
    {"This MySqlConnection is already in use. See https://fl.vu/mysql-conn-reuse"}
        Data: {System.Collections.ListDictionaryInternal}
        HResult: -2146233079
        HelpLink: Nothing
        InnerException: Nothing
        Message: "This MySqlConnection is already in use. See https://fl.vu/mysql-conn-reuse"
        Source: "MySqlConnector"
        StackTrace: "   at MySqlConnector.Core.ServerSession.StartQuerying(ICancellableCommand command)" & 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.<ExecuteReaderAsync>d__84.MoveNext()" & vbCrLf & "   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()" & vbCrLf & "   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)" & vbCrLf & "   at MySqlConnector.MySqlCommand.ExecuteReader()" & vbCrLf & "   at WindowsApplication1.frmLogon.LoadComboBox() in C:\vbnetJess1\fonttest-marcbreaker test\FontTestVS2008 - Copy\FontTestVS2008\frmLogon.vb:line 751"
        TargetSite: {Void StartQuerying(MySqlConnector.Core.ICancellableCommand)}

  2. #2

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Is this the fix?
    Add using statements?

    https://stackoverflow.com/questions/...tareaders-in-c

    Code:
    class SomeClass
    {
     public static void Main(String[] args)
     {
      using (SqlConnection sqlConn = new SqlConnection("some connection string"))
      {
       sqlConn.Open();
    
       using (SqlCommand comm = new SqlCommand("some query", conn))
       using (var sqlReader = comm.ExecuteReader())
       {
        while (sqlReader.Read())
        {
         //some stuff here
        }
       }
    
       using (SqlCommand comm = new SqlCommand("some query", conn))
       using (var sqlReader = comm.ExecuteReader())
       {
        while (sqlReader.Read())
        {
         //some other stuff here
        }
       }
      }
     }
    }

  3. #3
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,225

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    I don't know about your fix but your problem is this,

    Code:
             cmd1.ExecuteReader()
             Dim textrdr As String
             Using RDR2 = cmd1.ExecuteReader()
    Your calling execute reader twice after closing/opening the connection. I have no idea what the first call is for.

    This works fine,

    Code:
            Using cmd As New SqlCommand, con As New SqlConnection(My.Settings.BooksConnectionString)
                cmd.CommandText = "Select * from Books"
                cmd.Connection = con
                con.Open()
                Using rdr1 = cmd.ExecuteReader
                    MessageBox.Show(rdr1.HasRows.ToString)
                    con.Close()
                    con.Open()
                    cmd.CommandText = "Select * from Author"
    
                    Using rdr2 = cmd.ExecuteReader
                        MessageBox.Show(rdr2.HasRows.ToString)
                    End Using
    
                End Using
            End Using

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

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Why did you close and open the connection? It shouldn't hurt anything, but it also won't help anything. I'm not sure if anything actually happens when you close and open like that. It might get optimized away, or it might waste a few clock cycles. I assume you were fiddling around with the exception, which would be good. If you were thinking that it is one command execution per connection opening, it doesn't work that way.

    The whole issue is that first cmd.ExecuteReader, which does nothing except ensure that the second cmd.ExecuteReader won't work.
    My usual boring signature: Nothing

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,225

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Quote Originally Posted by Shaggy Hiker View Post
    Why did you close and open the connection? It shouldn't hurt anything, but it also won't help anything. I'm not sure if anything actually happens when you close and open like that. It might get optimized away, or it might waste a few clock cycles. I assume you were fiddling around with the exception, which would be good. If you were thinking that it is one command execution per connection opening, it doesn't work that way.

    The whole issue is that first cmd.ExecuteReader, which does nothing except ensure that the second cmd.ExecuteReader won't work.
    Because you get this error if you don't close/open the connection.

    'There is already an open DataReader associated with this Command which must be closed first.'
    Well, you could just close the the first reader instead.

    If you want both readers open at the same time I would just create a new command and connection. Give them meaningful names to prevent confusion.
    Last edited by wes4dbt; Jun 8th, 2024 at 08:43 PM.

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

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    He did close the first reader. It's in a Using block, so it goes away at the End Using.

    If you want two readers open at the same time, then something isn't right. They are read only, forward only, so having two open at the same time generally means that the design could be improved, as there isn't much value in having two such simplistic operations open at the same time. Whenever my first thought was that I needed two readers open at the same time, I always realized that there was a better way to approach the problem. It may be that there's a scenario where having two open at the same time is useful, but in that case you might as well just populate a datatable with one of them, in which case you only have one open.

    However, in the case shown, the second datareader doesn't make much sense. All you are doing in the loop is taking a value and sticking it into a string variable. If there are multiple rows, then each one will overwrite the previous one, and the variable will end up with the value from the last row. If there is only one row, then a datareader is overkill. If you want one value from one row, then ExecuteScalar is what you want. It's faster and lighter than a datareader, it just gets only the value from the first column of the first row returned. Therefore, you can write a query with multiple columns, but you'll only get the value from the first field, so returning the other fields would be a waste of effort. You can also write a query that returns multiple rows, which would probably mean that the query is poorly written if using ExecuteScalar, since only the first record will matter.
    My usual boring signature: Nothing

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Quote Originally Posted by Shaggy Hiker View Post
    Why did you close and open the connection? It shouldn't hurt anything, but it also won't help anything. I'm not sure if anything actually happens when you close and open like that. It might get optimized away, or it might waste a few clock cycles. I assume you were fiddling around with the exception, which would be good. If you were thinking that it is one command execution per connection opening, it doesn't work that way.

    The whole issue is that first cmd.ExecuteReader, which does nothing except ensure that the second cmd.ExecuteReader won't work.
    Closing and opening just seeing if it would help.
    The first one is filling a combobox with user names.

    This code is messy as I am trying to figure out things.

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    first reader is this one, it does fill a combobox
    I also test to see if any one user has all privileges, if no one does as in Xx = 0, then later on in the sub, add a 'New' user that can access the program fully.

    Code:
     Using RDR = cmd1.ExecuteReader()
              Do While RDR.Read
                  'add Myuser names to combo1
                  Combo1.Items.Add(RDR.Item("MYUser").ToString())
                  'one user needs to be full admin, and this is old, needs to be using AES field
                  If (RDR.Item("cast(aes_decrypt(priv_aes, '0123456789') AS char)").ToString()) = "A11111111111111111111" Then Xx += 1
              Loop
          End Using

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Part of trying to figure out hashing with MySQL I then ran some database commands, as if Xx = 0.

    I just threw that code onto the end of the reader.

    Then I was trying to read the last database command with a reader and it blows up with an exception.

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Quote Originally Posted by wes4dbt View Post
    Because you get this error if you don't close/open the connection.



    Well, you could just close the the first reader instead.

    If you want both readers open at the same time I would just create a new command and connection. Give them meaningful names to prevent confusion.
    I had also tried new connection and new reader from the limited bit I knew
    but it also gave an ex.

    like this, and renamed reader #2 to use it.
    It is however the same connection string, just called con2, I also tried cm2 for a new mysql command.

    So far all I came up with online is someone said running execute nonqueries after a reader is causing the problem...leaving something open, and the second reader fails.
    Code:
                    ' Dim conn2 As New MySqlConnection(ConStrUserVar)
    
                    'cmd2.Connection = conn2
    Last edited by sdowney1; Jun 9th, 2024 at 08:37 AM.

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    ok, here is another attempt on my own to try stuff

    First reader works

    when execute nonquery says cannot access a disposed object

    just how here would you make this work?
    Is it the extra TRY Catch stuff?
    When is conn disposed in the sub?

    Code:
                Dim PwordAes, PrivilAes As String
                Dim Xx As Integer = 0
                Dim ConStrUserVar As String = frmlogonConnectstring & "Allow User Variables=True;"
                Dim conn As New MySqlConnection(ConStrUserVar)
                '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
                    conn.Open()
                    Using conn
                        Dim cmd1 As New MySqlCommand
                        cmd1.CommandText = "select myuser, cast(aes_decrypt(password_aes, '0123456789') AS char), cast(aes_decrypt(priv_aes, '0123456789') AS char) from usertable"
                        'add the names to the list as in loop till it is full from uertable
                        cmd1.Connection = conn
    
                        Using RDR = cmd1.ExecuteReader()
                            Do While RDR.Read
                                Combo1.Items.Add(RDR.Item("MYUser").ToString())
                                'one user needs to be full admin, and this is old, needs to be using AES field
                                If (RDR.Item("cast(aes_decrypt(priv_aes, '0123456789') AS char)").ToString()) = "A11111111111111111111" Then Xx += 1
                            Loop
    
                        End Using
                    End Using
                Catch ex As Exception
                    Dim msg2 = ex.ToString()
                    MsgBox(msg2,  , "Error")
                End Try
    
                Try
    
                    Using conn 
                        Dim cmd1 As New MySqlCommand
                        cmd1.Connection = conn
                        conn.Open()
                        'Insert new user id
                        Dim MyName As String = "New"
                        MyUserID = "New"
                        pword = ""
                        priv = ""
                        priv = "G00000000000000000000" '21chars long
                        PwordAes = "New"
                        PrivilAes = "A11111111111111111111"
    
                        'this has exception
                        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()
    
                Catch ex As Exception
                    Dim msg = ex.ToString()
                    MsgBox(msg,  , "Error")
                End Try

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

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    You are disposing of the connection at the end of the first code block...

    Don't do that and you won't get an ObjectDisposedException

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

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    You're close. End Using cleans up (closes and disposes, in this case) of the object that is being used. Therefore, the connection is destroyed by the first Using, and then won't be around for the second Using. What you want to do is create and use in one step:

    Instead of:
    Code:
    Dim conn As New MySqlConnection(ConStrUserVar)
    Using conn
    End Using
    Just this:
    Code:
    Using conn As New MySqlConnection(ConStrUserVar)
    
    End Using
    The connection resides entirely within the Using block, and is disposed at the end. You COULD have multiple connections opened and closed within a single method like this, but it wouldn't be the most efficient way to handle it. One thing to note is that these two:

    Code:
    Using conn
     Try
       'Suff
     Catch 
       'Stuff
     End Try
    End Using
    and this
    Code:
    Try
      Using conn
       'stuff
      End Using
    Catch
    
    End Try
    Are functionally the same. The End Using will properly clean up the object, regardless of whether or not an exception is thrown in either layout. In fact, it will properly clean up the object even if you have a Return statement in there such that the actual text of the End Using doesn't quite get reached. That's really convenient for laying out the code.

    In your case, you have two exception handling blocks, which looks like a very good idea, in that code, as the two places you might have exceptions are really not the same. You'd want to have finer control than a single try...catch block would allow, so having two blocks looks right. However, the connection doesn't have to be opened and closed for each. You can just write it:
    Code:
    Using conn As New MySqlConnection(ConStrUserVar)
      Try
        'Do the first stuff.
      Catch
        'Do whatever
      End Try
    
      Try
        'Do the next stuff.
      Catch
       'Do whatever.
      End Try
    End Using
    The connection will be there throughout.

    You can also use the Using construct for the command object, which is nice, cause that's something you might want to dispose of, as well:

    Code:
    Using conn As New MySqlConnection(ConStrUserVar)
     Using cmd As MySqlCommand = conn.CreateCommand
      Try
        'Do the first stuff.
      Catch
        'Do whatever
      End Try
    
      Try
        'Do the next stuff.
      Catch
       'Do whatever.
      End Try
     End Using
    End Using
    Now, both are created and cleaned up nicely. There are other ways to create a command object, such as the way you have it. I do it the way I have shown, but only because I got into that habit. One is not better or worse than the other.
    My usual boring signature: Nothing

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,589

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Good Lord! You really are working overtime to take something simple and make it difficult. Create an object with a Using block and then that object gets disposed at the End Using statement. Don't try to that object again later. If you're using a MySqlConnection then do this:
    Code:
    Using connection As New MySqlConnection(connectionString)
        connection.Open()
    
        'Use connection here.
    End Using
    That's it, that's all. If you have multiple commands to execute over that same connection then put them all INSIDE that one Using block. If you want to read data and then insert data then do it like this:
    Code:
    Using connection As New MySqlConnection(connectionString)
        connection.Open()
    
        Using selectCommand As New MySqlCommand(selectSql, connection),
              reader = selectCommand.ExecuteReader()
            While reader.Read()
                'Read the current record.
            End While
        End Using
    
        Using insertCommand As New MySqlCommand(insertSql, connection)
            'Add parameters here.
    
            insertCommand.ExecuteNonQuery()
        End Using
    End Using
    The connection is created and opened and then not closed/disposed until you've finished using it. The second command is not created and used until the data reader has been created, used and closed.

    Note that you should absolutely use Using blocks for the connection and the data reader, because they need to be closed and disposing will do that. There's not really any need to use a Using block with a command because there won't actually be anything to be done when disposing them. That said, I recommend that you ALWAYS use a Using block for EVERY object that supports disposal. That way, you can never mistakenly not do it when it is required.

    Also, stop using string concatenation to insert values into SQL code and learn how to use parameters. You're just opening yourself up to all sorts of potential issues by not doing so. You can follow the Blog link in my signature below and check out my blog post on the subject, but there's lots of information around about that.

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,589

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    To be clear, you DO NOT need to declare a variable before a Using statement and almost universally SHOULD NOT. The Using statement itself is the declaration. By not declaring the variable outside the block, you ensure that you cannot accidentally use the variable outside the block.

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Thanks, will check out all these posts.

    I am just getting a feel for how things are supposed to go together.

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Made a few mods. and this works.
    I tried keeping the two TRY CATCH blocks but keep having error there.

    I dropped the second reader which see, I was trying figure out that MySQL hashing function which is tripping me up.
    I was running the set vars code block mysql showed then trying to read the result which kept causing the second reader to blow up.

    So I am back to just using AES encrypt for now.

    This here does function.

    Code:
        Private Sub loadcombobox()
            Dim PwordAes, PrivilAes As String
            Dim Xx As Integer = 0
            Dim ConStrUserVar As String = frmlogonConnectstring & "Allow User Variables=True;"
            Dim conn As New MySqlConnection(ConStrUserVar)
            '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
                conn.Open()
                Using conn
                    Dim cmd1 As New MySqlCommand
                    cmd1.CommandText = "select myuser, cast(aes_decrypt(password_aes, '0123456789') AS char), cast(aes_decrypt(priv_aes, '0123456789') AS char) from usertable"
                    'add the names to the list as in loop till it is full from uertable
                    cmd1.Connection = conn
    
                    Using RDR = cmd1.ExecuteReader()
                        Do While RDR.Read
                            Combo1.Items.Add(RDR.Item("MYUser").ToString())
                            'one user needs to be full admin, and this is old, needs to be using AES field
                            If (RDR.Item("cast(aes_decrypt(priv_aes, '0123456789') AS char)").ToString()) = "A11111111111111111111" 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 = ""
                        priv = "G00000000000000000000" '21chars long
                        PwordAes = "New"
                        PrivilAes = "A11111111111111111111"
    
                        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()
                    End If
                End Using
            Catch ex As Exception
                Dim msg = ex.ToString()
                MsgBox(msg,  , "Error")
            End Try
    
        End Sub
    It is this here I was trying to figure out a read on the last statement with a second reader
    https://dev.mysql.com/doc/refman/8.0...on_aes-encrypt

    Which remains elusive to me for now.
    I can execute all these statements
    But the last one I was tying to use with a reader to see what I could see

    Code:
    mysql> SET block_encryption_mode = 'aes-256-cbc';
    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);
    mysql> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
    +-------------------------------------------------------------+
    | CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
    +-------------------------------------------------------------+
    | text                                                        |
    +-------------------------------------------------------------+

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    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'))"
    I can set this with parameters except for the aes_encrypt DB function. I don't comprehend any way to do that as this aes_encrypt is part of Mysql.

    For example like this

    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 = "Gotcha"
        oSQLCommand.Parameters.Add("myuser", MySqlDbType.VarChar).Value = "Gotcha"
        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()
    
    
        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()
    
    
        Combo1.Items.Add("New")

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

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    If you are wanting to execute multiple statements then you are probably better creating a stored procedure https://dev.mysql.com/doc/refman/8.4...procedure.html and then call this procedure from you application.

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Ok, since I was having so much trouble with second reader, I ran those commands in the MariaDB command window
    And they all run ok.

    They use 'My secret passphrase' and 'text' as an example
    CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) is like a fake column, it is a returned result of 'text' being set and then decrypted as 'text'.

    But I still don't see how this would actually be implemented in my program as a hash of 'text'.
    It sort of seems like you need to store that 'My secret passphrase' in a column?
    The user supplies 'text' for a password?
    You run this and it returns 'text'? which verifies the user?

    Or is it the other way round?





    Code:
    MariaDB [booksgood]> delete from usertable;
    Query OK, 1 row affected (0.006 sec)
    
    MariaDB [booksgood]> SET block_encryption_mode = 'aes-256-cbc';
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> SET @key_str = SHA2('My secret passphrase',512);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> SET @init_vector = RANDOM_BYTES(16);
    Query OK, 0 rows affected (0.001 sec)
    
    MariaDB [booksgood]> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
    +-------------------------------------------------------------+
    | CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
    +-------------------------------------------------------------+
    | text                                                        |
    +-------------------------------------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]>
    a select * on the table shows nothing added column wise
    Code:
    MariaDB [booksgood]> select * from usertable;
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    | Id | MyName | Myuser | password | Priveliges            | TS   | UserDate            | password_aes     | priv_aes                         |
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    | 16 | New    | New    | pass     | G00000000000000000000 | NULL | 2024-06-09 12:17:34 | ??A??j;Y???l???A           | ?_?F??r??? ??O?LM??
    ???]? D                 |
    +----+--------+--------+----------+-----------------------+------+---------------------+------------------+----------------------------------+
    1 row in set (0.001 sec)
    
    MariaDB [booksgood]>
    Last edited by sdowney1; Jun 9th, 2024 at 11:35 AM.

  21. #21

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    I changed inputs to 'what is this' and 'freaky'

    And 'freaky' comes back

    Code:
    MariaDB [booksgood]> SET block_encryption_mode = 'aes-256-cbc';
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> SET @key_str = SHA2('What is this',512);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> SET @init_vector = RANDOM_BYTES(16);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> SET @crypt_str = AES_ENCRYPT('freaky',@key_str,@init_vector);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> select CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
    +-------------------------------------------------------------+
    | CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
    +-------------------------------------------------------------+
    | freaky                                                      |
    +-------------------------------------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]>

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

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Nowhere in the code you posted are you doing an Insert or Update, so why would you expect the table to be modified?

    The 4 lines beginning with 'SET' are simply setting variables, the Select is using these variables with AES_DECRYPT to return a value.

    If you want to store the encrypted password (although it spotless be hashed, not encrypted) then you need to use the value generated by AES_ENCRYPT in your Insert statement.

  23. #23

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Quote Originally Posted by PlausiblyDamp View Post
    Nowhere in the code you posted are you doing an Insert or Update, so why would you expect the table to be modified?

    The 4 lines beginning with 'SET' are simply setting variables, the Select is using these variables with AES_DECRYPT to return a value.

    If you want to store the encrypted password (although it spotless be hashed, not encrypted) then you need to use the value generated by AES_ENCRYPT in your Insert statement.
    yes, makes sense I suppose.

    How is that value here found out from that SET line?
    See their example does not say. They about always leave out critical information for your application, which tells me they assume too much about what you know.
    I have noticed that a lot over the years. Or they over document to the point of overwhelming you with info that does not actually tell you how to do something. I find there are a few people who actually post working code on help sites like here and stack exchange.

    MariaDB [booksgood]> SET @crypt_str = AES_ENCRYPT('freaky',@key_str,@init_vector);
    Query OK, 0 rows affected (0.000 sec)

    I pretty much know what will happen for now, I will be leaving things as they are. I can also encrypt the passkey to MySQL's AES_encrypt function using my XOR function which has it's own secret passphrase, which could be individualized for every user like include their user name in the key.

    But for my APP, it still is way over what is needed.

    I am interested in hashing passwords, but after a while you got to move on.

  24. #24
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,589

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Quote Originally Posted by sdowney1 View Post
    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'))"
    I can set this with parameters except for the aes_encrypt DB function. I don't comprehend any way to do that as this aes_encrypt is part of Mysql.
    The function is executed on the database so the function call must be part of the SQL but the value(s) you pass to the function can be provided using parameters, just like any other values, e.g.
    Code:
    Dim command As New MySqlCommand("INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, SomeMySqlFunction(@Value2))", connection)
    
    With command.Parameters
        .Add("@Column1", MySqlDbType.VarChar, 50).Value = column1
        .Add("@Value2", MySqlDbType.VarChar, 50).Value = value2
    End With

  25. #25

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2024
    Posts
    862

    Re: Open another reader, gives ex, "This MySqlConnection is already in use. See https

    Thanks jmcilhinney for the parameter idea can be useful.
    I do like parameters as writing out sql commands the other way is difficult for the syntax.

    Actually, in this sub, there is no user input, it is just looking to see if any user has all the privileges for the program
    to be fully managed by at least someone. If it finds none, then it will create a default 'New' user with all privileges.

    On a different form I called frmMaintain, an ADMIN user with full rights could delete every single user including themselves, and the program will then always create a user 'New' s an admin with full rights to do everything. So here in the frmLogin, same idea.

    I did this just in case all users are deleted who do have full authorizations in the program, cause if there are none, it will be a big problem doing certain things like adding other users, deleting records, adding records, etc...
    The granular control thru MySQL grants is not fine enough, so I had to create my own user table to give users the ability to do some 20 different things.

    About the hashing Mysql example they give.
    I also found out you can select what those example commands set
    And if you run those sets, but don't set 'text', then their select example returns a null


    Musing here
    it seems to me those 3 sets results have to be saved in the database
    then you use those to create 'text' and 'text' is what is the user's password
    'my secret passphrase' could be your own salt value.

    These 3 generated user values then can be used to bring back a value of 'text'
    So, if I ran these 2 code lines on another PC, I ought to always get 'text' as a result

    MariaDB [booksgood]> SET block_encryption_mode = 'aes-256-cbc';

    MariaDB [booksgood]> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);

    I should get 'text' returned.
    And 'text' and what the user's password claimed on a form input as 'text' will match and user is authorized.

    I have other PC running Mysql and can try that.
    It also should work for either any MySQL or MariaDB server as long as the version allows for this function.

    OR, it COULD be I don't have a clue for what I am thinking, and am way off base here.
    I just woke up and thought about it some and posted what I thought might be true.
    I will ask this question in the DB forum on this site.

    Code:
    MariaDB [booksgood]> SET block_encryption_mode = 'aes-256-cbc';
    Query OK, 0 rows affected (0.001 sec)
    
    MariaDB [booksgood]> SET @key_str = SHA2('My secret passphrase',512);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]> SET @init_vector = RANDOM_BYTES(16);
    Query OK, 0 rows affected (0.001 sec)
    
    MariaDB [booksgood]> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
    +-------------------------------------------------------------+
    | CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
    +-------------------------------------------------------------+
    | NULL
        |
    +-------------------------------------------------------------+
    1 row in set (0.000 sec)
    
    
    
    
    MariaDB [booksgood]>  SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [booksgood]>  SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
    +-------------------------------------------------------------+
    | CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
    +-------------------------------------------------------------+
    | text
        |
    +-------------------------------------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]> select @crypt_str;
    +------------------+
    | @crypt_str       |
    +------------------+
    | ?S?13N?????1?tv         |
    +------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]> select @key_str;
    +----------------------------------------------------------------------------------------------------------------------------------+
    | @key_str
    
                   |
    +----------------------------------------------------------------------------------------------------------------------------------+
    | fb9958e2e897ef3fdb49067b51a24af645b3626eed2f9ea1dc7fd4dd71b7e38f9a68db2a3184f952382c783785f9d77bf923577108a88adaacae5c141b1576b0 |
    +----------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]> select @init_vector;
    +------------------+
    | @init_vector     |
    +------------------+
    | ?7#T|?q????d[       |
    +------------------+
    1 row in set (0.000 sec)
    
    MariaDB [booksgood]>

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