DISCLAIMER
Please note that I have NOT actually tested any of the code below, and it may require some additional modifications to work in your environment.
I agree with @RamiZebian with regards to implementing the use of parameterized queries to help prevent SQL injection. The relatively simple queries you gave as examples could be implemented rather quickly in the way he describes, but I believe it will require one minor modification. Since you appear to be using Odbc, I believe you'll need to change the @ to a question mark (?). This would change his example to look more like this (if I have it correct - I don't use the Odbc connector often):
MyCommand = New SqlCommand("SELECT * FROM tbl_user WHERE username=?username AND password=?password", myconnection)
MyCommand.Parameters.Add("?username", SqlDbType.VarChar, 50).Value = strUsername
MyCommand.Parameters.Add("?password", SqlDbType.VarChar, 50).Value = strPassword
Even with this minor change, his fairly direct method should work and get you up and running with minimal extra work.
TL;DR - Use the method @RamiZebian proposed for quick implementation. The rest of this answer describes additional measures you can take if you have the time and means to improve your protection against SQL injection as well as other potential issues when programming for database communication.
That being said, however, if you have the ability, you might also want to consider taking things "one step further". In this case, my suggestion would be, if you have access to and control of the MySQL server (or, at least sufficient privileges), to create a stored routine (procedure and/or function). It requires a bit more setup, but using this method comes with the following benefits:
- It helps to take the burden of the logic out of your application so that you don't necessarily have to change your programming any time there are changes to the database.
- This method also can provide an added layer of security (if needed) by allowing or preventing certain users or groups access to the routine through the database permissions rather than controlling it in your programming.
- You don't have to worry so much about
escape characters like the apostrophe (') as these are internally handled by the database.
- Additionally, in the cases of long and complex SQL queries, it can help simplify your programming by allowing you to refer to the routine instead of constructing the entire query in your code.
- One final benefit of this method would be that the database itself is able to handle some of the data validation instead of you having to write code to make certain that the values you're passing are "legitimate" for the query.
Check out this answer from another SO question for some clarification on the differences between Procedures and Functions: MySQL stored procedure vs function, which would I use when?
Once you have the stored routine set up in the database, you can then use a parameterized query in your programming to call it. Here's an example. Please note that this is somewhat simplified and just going off of the code from your OP. You may want to include some additional validation within your methods to ensure the data you're getting back is what you expect.
STEP 1: Create the routines you'll need in the MySQL database.
Below you'll see that the exact same queries are being used, but all of the actual "operational" SQL for each is wrapped up in a PROCEDURE.
A) First, for the SELECT statement:
DELIMITER $$
CREATE PROCEDURE `UserSelect`(
IN inUsername VARCHAR(50),
IN inPassword VARCHAR(60))
BEGIN
SELECT *
FROM tbl_user
WHERE username = inUsername
AND password = inPassword;
END$$
DELIMITER ;
B) And then, the INSERT statement for the registration:
DELIMITER $$
CREATE PROCEDURE `UserRegister`(
IN inFirstName VARCHAR(50),
IN inLastName VARCHAR(60)),
IN inAddress VARCHAR(60),
IN inEMail VARCHAR(60),
IN inUsername VARCHAR(60),
IN inPassword VARCHAR(60))
BEGIN
INSERT INTO tbl_user
(firstname,
lastname,
address,
email,
username,
password)
VALUES
(inFirstName,
inLastName,
inAddress,
inEMail,
inUsername,
inPassword);
END$$
DELIMITER ;
STEP 2: Write the VB.NET code to use your new stored routines
One thing you might consider at this stage is that, while the Odbc namespace can provide you with most or all of the database access you will currently need, you may find that you get improved performance and utility from the MySQL .NET Connector (Connector/NET). The syntax is virtually identical, but it's designed and optimized to work specifically with a MySQL database/server. I'll make my example here using Odbc to reduce confusion, but I definitely recommend looking into the more provider-specific connector. Also, please remember that this code has not been tested and may very likely require some modification to work in your environment.
A) The "login" method
Try
mysqlConn.Open()
sqlCmd = New Odbc.OdbcCommand
sqlCmd.Connection = mysqlConn
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.CommandText = "CALL UserSelect(@Username, @Password)"
sqlCmd.Parameters.Add("@Username", Odbc.OdbcType.VarChar, 50).Value = TextBox01.Text
sqlCmd.Parameters.Add("@Password", Odbc.OdbcType.VarChar, 50).Value = TextBox02.Text
Try
dr = sqlCmd.ExecuteReader
dr.Read()
getUsername = dr("username")
MessageBox.Show("You are now login!", "System", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Me.Hide()
frmMenu.Show()
TextBox01.Text = ""
TextBox02.Text = ""
Catch ex As Exception
MessageBox.Show("Invalid Username or Password!", "System", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
TextBox01.Focus()
End Try
Catch ex As Exception
MessageBox.Show("Database connection error!", "System", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
If Not mysqlConn Is Nothing Then
mysqlConn.Close()
mysqlConn.Dispose()
End If
If Not sqlCmd Is Nothing Then sqlCmd.Dispose()
If Not dr Is Nothing Then dr.Dispose()
End Try
B) And the "register" method
Try
mysqlConn.Open()
sqlCmd = New Odbc.OdbcCommand
sqlCmd.Connection = mysqlConn
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.CommandText = "CALL UserRegister(@Firstname, @Lastname, @Address, @Email, @Username, @Password)"
sqlCmd.Parameters.Add("@Firstname", Odbc.OdbcType.VarChar, 50).Value = TextBox01.Text.Trim
sqlCmd.Parameters.Add("@Lastname", Odbc.OdbcType.VarChar, 50).Value = TextBox02.Text.Trim
sqlCmd.Parameters.Add("@Address", Odbc.OdbcType.VarChar, 50).Value = TextBox03.Text.Trim
sqlCmd.Parameters.Add("@Email", Odbc.OdbcType.VarChar, 50).Value = TextBox04.Text.Trim
sqlCmd.Parameters.Add("@Username", Odbc.OdbcType.VarChar, 50).Value = TextBox05.Text.Trim
sqlCmd.Parameters.Add("@Password", Odbc.OdbcType.VarChar, 50).Value = TextBox06.Text.Trim
Try
sqlCmd.ExecuteNonQuery
MessageBox.Show("You are now register!", "System", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Me.Hide()
frmMenu.Hide()
frmLogin.Show()
Catch ex As Exception
MessageBox.Show("Registration failed!", "System", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
TextBox01.Focus()
End Try
Catch ex As Exception
MessageBox.Show("Database connection error!", "System", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
If Not mysqlConn Is Nothing Then
mysqlConn.Close()
mysqlConn.Dispose()
End If
If Not sqlCmd Is Nothing Then sqlCmd.Dispose()
End Try
FINAL THOUGHTS/NOTES
Now, there are some additional changes I might suggest to "optimize" your code, such as moving to the MySQL .NET Connector and possibly some Using statements. Here's a link to a very general example of how to use stored routines with the MySQL .NET Connector: Using Stored Routines from Connector/NET. To update the "login" method above for Connector/NET and add some Using statements to help manage all of the objects:
Imports MySql.Data
Imports MySql.Data.MySqlClient
Private Sub CheckLoginUser()
Dim TempUsername As String = String.Empty
Dim ConnectString As New MySqlConnectionStringBuilder()
With ConnectString
.Server = <SERVERNAME>
.Port = <PORTNUMBER>
.UserID = <USERNAME>
.Password = <PASSWORD>
End With
Using MySQLDB As New MySqlConnection(ConnectString.ConnectionString)
Using MySQLCmd As New MySqlCommand
Try
MySQLDB.Open()
With MySQLCmd
.Connection = MySQLDB
.CommandType = CommandType.StoredProcedure
.CommandText = "CALL UserSelect(@Username, @Password)"
.Parameters.AddWithValue("@Username", TextBox01.Text)
.Parameters("@Username").Direction = ParameterDirection.Input
.Parameters.AddWithValue("@Password", TextBox02.Text)
.Parameters("@Password").Direction = ParameterDirection.Input
End With
Dim Reader As MySqlDataReader
Try
Reader = MySQLCmd.ExecuteReader
Reader.Read()
TempUsername = Reader("username")
' -- Possibly include some additional validation here
' -- e.g., check for null/empty string, etc.
MessageBox.Show("You are now login!", "System", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Me.Hide()
frmMenu.Show()
TextBox01.Text = ""
TextBox02.Text = ""
Catch ex As Exception
MessageBox.Show("Invalid Username or Password!", "System", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
TextBox01.Focus()
End Try
Catch ex As Exception
MessageBox.Show("Database connection error!", "System", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Using
End Using
End Sub
The stored routine for this method could be "optimized" a bit as well to only return the value from the username column since that appears to be the only one you're interested in. Something like this:
DELIMITER $$
CREATE PROCEDURE `UserSelect`(
IN inUsername VARCHAR(50),
IN inPassword VARCHAR(60),
OUT outUsername VARCHAR(50))
BEGIN
SELECT username INTO outUsername
FROM tbl_user
WHERE username = inUsername
AND password = inPassword;
END$$
DELIMITER ;
This change would change the example code as follows:
...
With MySQLCmd
.Connection = mysqlConn
.CommandType = CommandType.StoredProcedure
.CommandText = "CALL UserSelect(@Username, @Password, @Response)"
.Parameters.AddWithValue("@Username", TextBox01.Text)
.Parameters("@Username").Direction = ParameterDirection.Input
.Parameters.AddWithValue("@Password", TextBox02.Text)
.Parameters("@Password").Direction = ParameterDirection.Input
.Parameters.AddWithValue("@Response", MySqlDbType.VarChar)
.Parameters("@Response").Direction = ParameterDirection.Output
End With
' Not required anymore
' Dim Reader As MySqlDataReader
Try
TempUsername = Convert.ToString(MySQLCmd.ExecuteScalar)
MessageBox.Show("You are now login!", "System", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
...
As a last point, I'd also recommend you try to make use of the internal MySQL users table (mysql.user) instead of creating your own users table. The password storage would be more secure and you'd be able (if you ever found the need) to apply user-level permissions to individual tables, routines, etc.
Even with these caveats, however, the structure presented above - with quite possibly some modification for your specific environment - should work for what you're trying to accomplish.
