Inserting Data into MySQLTable using Params

Question

I have this DataTable I want to insert Data to, but I get an error every time I execute this code.

"Unknown data type"

I have a Function on a Module that(so I don't have to rewrite it and I can call it from every form) to open the MySQL Connection.

Imports MySql.Data.MySqlClient
Imports System.Net.Mail

Module Module1
Public MyConnection As New MySqlConnection
Public lastLogon As String = Today.Day & "/" & Today.Month & "/" & Today.Year & TimeOfDay.Hour & ":" & TimeOfDay.Minute & ":" & TimeOfDay.Second
Function ServerConnection()
    Try
        MyConnection.ConnectionString = "server=127.0.0.1;user=root;password=password;database=titulo"
        MyConnection.Open()
        Return True
    Catch ex As Exception
        Return False
    End Function
    'Don't mind this
    + Function SendMail(SendTo As String, subject As String, message As String)
End Function
End Module

So I call this using the ServerConnection function and store the connection into a public var called MyConnection, which I can call from every other form.

The thing is, I have this code to create an account which inserts data into a table: (I'm sorry about the spanish. Just check the code and don't mind the SendMail function)

 Private Sub btnDo_Click(sender As Object, e As EventArgs) Handles btnDo.Click
    If txtUsername.Text <> "" And txtPassword.Text <> "" And txtPassword2.Text <> "" And txtEmail.Text <> "" Then
        If mailUsed = False Then
            If userUsed = False Then
                If txtPassword.Text <> txtPassword2.Text Then
                    lblError.Text = "Revise sus contraseñas."
                Else
                    lblError.Text = ""
                    Try
                        ServerConnection()
                        Dim MyCommand As New MySqlCommand("INSERT INTO titulo.accounts(id, accountName, accountPassword, accountMail, accountLogon) VALUES(0, @username, @password, @email, @logon)", MyConnection)
                        MyCommand.Parameters.Add("@username", SqlDbType.VarChar, 10).Value = txtUsername.Text
                        MyCommand.Parameters.Add("@password", SqlDbType.VarChar, 16).Value = txtPassword.Text
                        MyCommand.Parameters.Add("@email", SqlDbType.VarChar, 60).Value = txtEmail.Text
                        MyCommand.Parameters.Add("@logon", SqlDbType.VarChar, 22).Value = "First Session."
                        MyCommand.ExecuteNonQuery()
                        Try
                            SendMail(txtEmail.Text, "Nueva cuenta en TITULO", txtUsername.Text & Environment.NewLine & " Se ha creado una cuenta a su nombre en " & My.Application.Info.AssemblyName & Environment.NewLine & "Nombre de cuenta: " & txtUsername.Text & Environment.NewLine & "Contraseña: " & txtPassword.Text)
                        Catch ex As Exception
                            MsgBox(ex.Message)
                        Finally
                            MsgBox("Cuenta creada. Correo enviado a: " & txtEmail.Text & ".")
                        End Try

                    Catch ex As Exception
                        MessageBox.Show("Error en la base de datos." & Environment.NewLine & ex.Message)
                    Finally
                        MyConnection.Close()
                    End Try

                    MsgBox("La cuenta " & txtUsername.Text & " ha sido creada." & Environment.NewLine & "Revise su correo " & txtEmail.Text & " para revisar sus datos.")
                End If
            Else
                lblError.Text = "Ya existe una cuenta con ese nombre."
            End If
        Else
            lblError.Text = "Ese correo ya esta en uso."
        End If
    Else
        lblError.Text = "Revise sus datos."
    End If
End Sub

Again, sorry about the spanish. My accounts table on the titulo database have the following datatypes id INT NOT NULL AUTO_INCREMENT,

accountName VARCHAR(10) NOT NULL,

accountPassword VARCHAR(16) NOT NULL,

accountMail VARCHAR(60) NOT NULL,

accountLogon VARCHAR(22) NOT NULL,

I'll appreciate any help if provided.

score 1 · Accepted Answer · edited May 23 '17 at 11:46

1

MySql code should use MySqlDbType enumeration to define the datatype of the parameters. You are using the SqlDbType.

This code will show you that the two don't have the same value

int x = (int)SqlDbType.VarChar;
int y = (int)MySqlDbType.VarChar;
Console.WriteLine("SqlDbType.VarChar = " + x);   
Console.WriteLine("MySqlDbType.VarChar = " + y);

Output is

SqlDbType.VarChar = 22 
MySqlDbType.VarChar = 253

Unrelated to your actual problem, but I need to advise you about a big security risk in your code. Do not store passwords in plain text. Use always an hashing algorithm and a salt key to store irreversible bytes of the user password.

Best way to store password in database

edited May 23 '17 at 11:46

Community

1
1

answered Mar 08 '17 at 08:26

Steve

213,761
22
232
286

Thanks a lot for the correction. I'll be sure to try it and I'm aware of the password security issue but I haven't seen to it yet. Thanks a lot for the answer! – Reviyee Mar 08 '17 at 16:30
In the case I hash my password, which is stored in MySQL on a varchar(16) if the result of the hashing outputs, let's say, a 30 character password, I must expand the capacity on the password column, right? – Reviyee Mar 08 '17 at 16:43
If I am not wrong, the length of the resulting hash is fixed according to the algorithm used. MD5 produces a 16 bytes hash, SHA1 goes for 20 bytes, SHA-256 returns 32. But MD5 and SHA1 are well known to be breakable, so go for the more secure SHA-256/512 and enlarge your column. (Note that if you convert the hash to hex string then probably you need to double that sizes) – Steve Mar 08 '17 at 16:54
If I did it right, I think I used Hash512, which produced an 88 character password. Is it a good practice to use the user-input password as string and the username as Salt? Which is what I did. – Reviyee Mar 08 '17 at 17:33
Not really http://stackoverflow.com/questions/536584/non-random-salt-for-password-hashes/536756#536756 – Steve Mar 08 '17 at 17:49

Inserting Data into MySQLTable using Params

1 Answers1