[Tut]REAL Register/Login - MySQL

**Blubb1337** · 05-22-2010

I'm going to show you how to embed a REAL login system using mysql.

This tutorial is kinda advanced, so take your time

Needed files for this tutorial:

- MySQL, I'm using 6.0.10(43,7 mb)
- MySQL connector(attachment)
- VB.Net
- SQLyog - This isn't freeware, you can either get a 30 days trial or get it from a warez site

I'm not giving you a link though.

What is SQLyog?

SQLyog MySQL GUI is the most powerful MySQL manager and admin tool, combining the features of MySQL Query Browser,
Administrator, phpMyAdmin and other MySQL Front Ends and MySQL GUI tools in a single intuitive interface.

We are going to use SQLyog to create our database and stuff.

- Last but not least, a brain - I cannot send you a link, since this is uncrackable, Sorrez.

Overview:

1. Setting up MySQL
2. Creating a database in SQLyog
3. Starting your project & adding a md5 module
4. Adding the MySQL Library(.dll)
5. Register to your database
6. Create the final login in VB.NET
7. Read your Login table in VB

Moving on...

__________________________________________________ ____________________________________

1. Setting up MySQL

Download MySQL and install it.

I won't show you how to click "Next" on each step =D.

Afterwards, the MySQL Server Instance Configuration Wizard is started.

You can always configure your settings by clicking Start > Programs > MySQL > MySQLServer 6.0 > MySQL Server Instance...

Next > Detailed Configuration > Next

Selecting the server type:

You see 3 types of server machines available, since each one has it's own description I do not have to explain it to you.

Depending on what you are going to do, you may select Developer Machine just to start off.

> Next

Selecting the database usage:

Same as before. This is well described and no need to be explained.

I'd either use mutlifunctional database or non-transactional database only.

> Next

This is kinda obvious, simply browse the folder you want MySQL's datafile settings to be saved to.

> Next

Approximate number of concurrent connections:

Depending on what you are going to do, you may start of with like 20 connections.

You can still higher it anytime.

> Next

Networking Options:

Enable both and tick the firewall exception. The default port is 3306, you may leave it default.

> Next

Default Character Set:

Not much to say, simply hit...

> Next

Windows Options:

I recommend you to change the service name, otherwise the service might not be startable.

I myself had the same problem. Simply change the service name to MySQL501 or so and you'll be fine

You may check the "Include Bin Directory in Windows PATH"-thingy, however this is not really neded.

> Next

Security Options:

Enter a password for your database access. It is recommended to use a 'difficult' password.

> Next

Hit execute and see if you get any errors.

> You're Done =D

You successfully installed MySQL, took you long enough =D

__________________________________________________ ____________________________________

2. Creating a database in SQLyog

Now, moving on to creating a database with tables, we need to download SQLyog

After downloading and install the program, start it.

New Connection... > Enter a Name > Hit OK

Default host address: localhost
Default username: root
Password: The one you entered in the setup
Default Port: 3306

Leave the rest by default.

Hit connect.

Righ***ick on root@localhost > Create Database

Name it "User" and hit Create.

Righ***ick on Tables > Create Table

Create Table...

Name it "Login"...Hit OK.

You are done with creating a database. Now we will have to go into VB

.

__________________________________________________ ____________________________________

3. Starting your project & adding a md5 module

Start your a new VB project...

Drag 1 Button and 3 Textboxes on the form.

Textbox1 = txtID
Textbox2 = txtPW
Textbox3 = txtEmail

We start off by creating an MD5 Module to actually 'secure' the passwords and not make them readable.

Solution-Explorer > Your Application > Righ***ick > Add Module > OK

Paste in the following code:

Code:

Imports System.Security.Cryptography
Imports System.Text
Module md5

	Public Function MD5StringHash(ByVal strString As String) As String
    	Dim MD5 As New MD5CryptoServiceProvider
    	Dim Data As Byte()
    	Dim Result As Byte()
    	Dim Res As String = ""
    	Dim Tmp As String = ""

    	Data = Encoding.ASCII.GetBytes(strString)
    	Result = MD5.ComputeHash(Data)
    	For i As Integer = 0 To Result.Length - 1
        	Tmp = Hex(Result(i))
        	If Len(Tmp) = 1 Then Tmp = "0" & Tmp
        	Res += Tmp
    	Next
    	Return Res
	End Function

End Module

__________________________________________________ ____________________________________

4. Adding the MySQL Library(.dll)

Download the attachment "mysql-connector-net-5.2.6" below.

Run it & install it.

Select your installation folder, if you left it default simply leave this default, too.

In Vb.Net go into the Solution-Explorer again.

Righ***ick on your application > Add Reference

In .Net components tab search for MySQL.Data.

If you cannot find it, you either forgot to install the mysql-connector(attachment) or you forgot to
restart VB.Net.

To embed the .dll you will need the following namespace above Form1:

Code:

Imports MySql.Data.MySqlClient

Public Class Form1

    
End Class

__________________________________________________ ____________________________________

5. Register to your database

In the next two steps make sure:

Your database is named 'user'
Your table is named 'login'
You have three columns named: 'ID', 'Password', 'Email'

On button click_event:

The code is commented.

Code:

 Dim conn As MySqlConnection
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;" & "user id=root;" & "password=YourPW;" & "database=user"

        Try
            conn.Open() ' open connection

        Catch myerror As MySqlException
            MsgBox("Couldn't connect to the database: " & myerror.Message.ToString)
        End Try

        Dim myAdapter As New MySqlDataAdapter
        Dim SQLQuery As String = "SELECT * FROM login WHERE ID='" + txtID.Text + "'" ' A query to see if the user already exists
        Dim myCommand As New MySqlCommand
        myCommand.Connection = conn
        myCommand.CommandText = SQLQuery

        myAdapter.SelectCommand = myCommand
        Dim myData As MySqlDataReader
        myData = myCommand.ExecuteReader() ' start query

        If myData.HasRows = 0 Then 'if the user doesn't exist yet, start the query
            conn.Close()
            conn.Open()
            Dim registerfinal As New MySqlDataAdapter
            Dim benutzer As String = txtID.Text
            Dim passwort As String = MD5StringHash(txtPW.Text)
            myCommand.CommandText = "INSERT INTO login(ID, Password, Email)" _
                                 & "VALUES('" & benutzer & "','" & passwort & "','" & txtEmail.Text & "')"
            myCommand.ExecuteNonQuery() ' start query and insert values into our "login" table
            MsgBox("The account: " & txtID.Text & " has been created successfully.")
            conn.Close()
        Else
            MsgBox("Account already exists")
        End If

__________________________________________________ ________________

6. Create the final login in VB.NET

Code:

Dim conn As MySqlConnection
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;" & "user id=root;" & "password=YourPW;" & "database=user"  'Server data

        Try
            conn.Open() ' open the connection
        Catch myerror As MySqlException
            MsgBox("Couldn't connect to the database: " & myerror.Message.ToString) ' catch the error
        End Try

        Dim ID As String = txtID.Text
        Dim PW As String = MD5StringHash(txtPW.Text)

        Dim myAdapter As New MySqlDataAdapter
        Dim SQLQuery As String = "SELECT * FROM login WHERE ID='" + Replace(id, " ", "") + "' AND Password='" & Replace(pw, " ", "") & "'" ' Das ist die Abfrage welche Prüft ob der Account existiert.Die txt_benutzer ist die Textbox in der der Benutzername eingeben wird und txt_passwort bekommt das Passwort
        Dim myCommand As New MySqlCommand
        myCommand.Connection = conn
        myCommand.CommandText = SQLQuery

        myAdapter.SelectCommand = myCommand
        Dim myData As MySqlDataReader
        myData = myCommand.ExecuteReader() ' start query
        If myData.HasRows Then ' if the pw/id is correct
            MsgBox("Successfully logged in.")
            conn.Close()
            conn.Open()

        Else 'Display error
            MsgBox("Wrong ID/PW.")
        End If

You can sure do everything instead of the MsgBox.

__________________________________________________ ________________

7. Read your Login table in VB

Drag a DataGridView(Toolbox) onto your form.

Button_event:

Code:

 Dim conn As MySqlConnection
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;" & "user id=root;" & "password=YourPW;" & "database=user"  'login data

        Try
            conn.Open() ' open connection
        Catch myerror As MySqlException
            MsgBox("Couldn't connect to the database: " & myerror.Message.ToString) ' catch error
        End Try

        Dim myAdapter As New MySqlDataAdapter
        Dim SQLQuery As String = "SELECT * FROM login" ' select everything from login
        Dim da As mysql.Data.MySqlClient.MySqlDataAdapter
        Dim dt As New DataTable

        da = New MySqlDataAdapter(SQLQuery, conn)

        Try
            da.Fill(dt) 'fill in the data
        Catch myerror As MySqlException 'catch the error if there's one
            MsgBox(myerror.Message.ToString)
        End Try

        DataGridView1.DataSource = dt

__________________________________________________ ________________

Now you are actually able to create an account/logging in using your VB application.

I do not recommend this method if you haven't got a RootServer/VServer/Online DB/24/7 running pc.

There are online databases to connect your VB project with.

I'm going to search for them and make another tutorial about it

I do hope you understood everything and enjoyed it.

Credits:
Rokky from vb-paradise for most(not everything) of the code

Me: Writing the whole tutorial(took me some time)

VirusScan for attachment