+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Software Development (https://hackforums.net/forumdisplay.php?fid=118)
+---- Thread: [.NET] [TUT] How to use MySQL databases with ASP.NET [HOW TO] [VB.NET] (/showthread.php?tid=1210405)
Pages: Pages
1
2
[TUT] How to use MySQL databases with ASP.NET [HOW TO] [VB.NET] - HSPSoftware - Apr 13, 2011
Hello out there!
This tutorial is done by request from Ethereal so I hope he will read this.
Today we are gonna learn how to connect to a MySQL database from ASP.NET. Let me go though the headlines of this tutorial.
- Installing MySQL and MySQL Workbench on your local machine
- Connecting with .NET
- Making a basic login system
- Download
So let's just cut the crap and get started!!! :)
Installing MySQL and MySQL Workbench on your local machine
To do this go to http://dev.mysql.com/downloads/ and get the community edition.
On the next page, scrool down and select the correct MSI installer (either 32 bit or 64 bit).
On the page after that, again, just scrool down and select "» No thanks, just take me to the downloads!".
Now select a mirror and wait for download to finish...
Then the installation starts. Select as follows.
Page 1: Typical -> Next
Page 2: Leave the checkbox "Configure server now" on -> Finish
Now a new screen comes up meaning you are ready to configure the server. Do as follows.
Page 1: Next
Page 2: Developer machine -> Next
Page 3: Multifunctional Database -> Next
Page 4: Make sure that enable TCP/IP is set and the port is 3306. -> Next
Page 5: Standard character set -> Next
Page 6: Install as Windows service should be checked -> Next
Page 7: Check "Modify security settings" and set a password. (Mine is just "hackforums" while writing this tutorial.) -> Next
Page 8: Execute -> Finish.
Congratulations now you have the MySQL server up and running. But we also need something to administrate it. Okay you could go for command line (which I do sometimes) but the MySQL Workbench is MUCH easier to work with! :)
Download it from here:
http://dev.mysql.com/downloads/workbench/
Here just select 32Bit MSI no matter what (there are no alternatives and I tested on 64bit windows so it works).
That installation is pretty straight forward and I'm tired of writing each point down by now ;)
Then it's installed you can open it and you should see something with:
"Local instance MYSQL" under "Open connection" on the main page.
Doubleclick it and write your password. Next step we will create a database and add some stuff to it.
Connecting with .NET
I know, I know. By now you probably just want to see some code. But you will have to wait a minute! We need to make some junk first to run our query against.
WARNING - FOR HARDCORE USERS ONLY:
For hardcore users just create db "netdb" with table "VBTest" with columns id, user and pass. user and pass should be varchars where pass should be varchar with 64 length for SHA256 hash. just add some random logins with pass = sha256 of the password chosen.
All "mortal" people, read on ;)
So inside our MySQL Workbench, right click a little under "Schemas" on some blank space and select "Create schema".
Call it "netdb" and click apply. Just click finish on the popup or something like that, and then close the previous window where you created the Schema.
Now you should see "netdb" under Schemas to the left. If not, try and right click under schemas and select "Refresh all".
The database is up and running, now we just need a table with some content. Expand "netdb" and right click on "Tables" and choose "Create table".
Name it "VBTest" and go down to the tab "Columns" at the bottom. Rename "idVBTest" to "id" for readability.
and add the columns "user" and "pass" by doubleclicking on the blank space right under the previous entry.
At the "pass" column, change the "varchar(45)" to "varchar(64)" as we will store passwords hashed with SHA256.
Click apply, finish and close.
Now expand tables, and right click on "VBTest" and select "Edit table data".
Add the user "HSP" with pass "32cdb619196200050ab0af581a10fb83cfc63b1a20f58d4bafb6313d55a3f0e9"
and add the user "Ethereal" with pass "244d2dbb29b07bc256bf737c1946aa78980329091ce8be0b19384446fc0918fa"
(adding here is the same way as with the columns, click on blank space).
It does not need to be those values but that's just for example purposes.
NOW! We are down to actually code something.
Start out by opening visual studio and selecting new project -> visual basic -> web -> ASP.NET Empty web application.
Name it something random and then continue.
Now before actually coding (sorry!) we need to have the MySQL connectors for .NET. You can get the assemblies with my download at the bottom inside the "MySQL connectors" folder. Or you can get them for yourself by doing this:
Download them from: http://www.mysql.com/products/connector/ (You will need the NET one) (select the largest zip file with the binaries)
it comes as a zipped up MSI file so run the msi and you should find the assemblies somewhere like:
C:\Program Files (x86)\MySQL\MySQL Connector Net 6.3.6\Assemblies
and then in the folder with your framework. (or a version older if it is 3.5).
NOW we are going to move on to the fun! 🤗
Save your project and copy the exe's over into the "bin" folder and afterward add a reference to them ... why copying first manually? Well you are welcome to try and just add reference but it fixes a bug with me where there the namespaces didn't show up.
Next up right click on the project in solution explorer in Visual Studio / Visual Web developer.
Select: Add -> New item -> Choose "Webform" and call it "Default.aspx" -> Add.
Rightclick on the new item and select "View markup".
Add the following right under the first line:
Code
<%@ Import Namespace = "MySql.Data.MySqlClient" %>Go down into the code and insert the following between the div tags
Code
<%
Dim con As New MySqlConnection("server=localhost; user id=root; password=hackforums; database=netdb; pooling=false;")
con.Open()
%>What this does is that it logs you in as root on localhost with password "hackforums " and select our netdb... Set hackforums to whatever you chose as password for MySQL.
Try and debug the project (F5) and if no errors occured your connection was successful. Otherwise post the errors here and I will try and help out ;)
Making a basic login system
By now the tutorial starts to get pretty lengthy. So I will just post the code I used with comments. Feel free to ask any questions you want, it's way faster to answer them than to step trough the code line by line.
In markupview of Default.aspx insert the following:
Code
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Demonstration of MySQL in .NET by HSPSoftware</title>
<style type="text/css">
body { background-color:#015965; margin:0px; padding:0px; color:#33CCCC; }
#main { width:200px; margin:0 auto; background-color:#1D7373; border: #009999 1px solid; padding:10px; }
#spacer { width:1px; height:250px;}
</style>
</head>
<body>
<div id="spacer"></div>
<div id="main">
<%
'Checks if user is sending in post request by checking if both forms have some value
If Not Request.Form("user") Is Nothing And Not Request.Form("pass") Is Nothing Then
'Initiates a new connection to localhost with root@hackforums as login and chooses netdb as database
Dim con As New MySqlConnection("server=localhost; user id=root; password=hackforums; database=netdb; pooling=false;")
con.Open()
'Makes the new SQL command to run against the DB. Please DON'T whine about SQL injections.
'This is a socalled "parameterized sql command" and it's considered way safer than
'sanitizing the data by stripping out characters. But of course in a real application
'sanitize anyway to prevent XSS. This only protects vs SQL injections
Dim SQLCommand As New MySqlCommand("SELECT * FROM VBTest WHERE user = @user AND pass = @pass", con)
'Adds the paramters.
SQLCommand.Parameters.Add("@user", MySqlDbType.VarChar, 45)
SQLCommand.Parameters.Add("@pass", MySqlDbType.VarChar, 64)
'sets the values of the parameters.
SQLCommand.Parameters("@user").Value = Request.Form("user")
SQLCommand.Parameters("@pass").Value = getSHA256Hash(Request.Form("pass"))
'Defines "reader" and sets it to the result of the prevoius command
Dim reader As MySqlDataReader = SQLCommand.ExecuteReader()
'Checks if the reader has rows.
If reader.HasRows Then
'If it has we read the first one and writes some text.
reader.Read()
Response.Write("<b>Welcome " & reader("user") & "</b>")
Else
'If not we shows an error message with some real fancy JS to go back ;)
%>
<b>Login error! Please <a href="javascript:history.back()">go back</a> and try again!</b>
<%
End If
Else
%>
[b]<!--[/b] our login form -->
<form name="login" action="Default.aspx" method="post" enctype="application/x-www-form-urlencoded">
<label for="user">Username:</label>
<input type="text" name="user" />
<br />
<label for="pass">Password:</label>
<input type="password" name="pass" />
<br />
<input type="submit" value="Log in" />
</form>
<%
End If
%>
</div>
</body>
</html>Right under the first two lines in the file (I didn't include them as they might be different for you)(Or actually only the first line but who cares?).
By now it's complaining about "GetSHA256Hash" doesn't exist. So right click once again on Default.aspx and select "Code view"
Add this single function:
Code
Public Function getSHA256Hash(ByVal input As String) As String
Dim SHA256 As New System.Security.Cryptography.SHA256Managed
Dim inputBytes() As Byte = System.Text.Encoding.UTF8.GetBytes(input)
inputBytes = SHA256.ComputeHash(inputBytes)
Dim returnValue As String = ""
For Each b As Byte In inputBytes
returnValue += b.ToString("x2")
Next
Return returnValue
End FunctionTry and run the project now (F5) and see how it goes :)
You should be able to login with your credentials (if you used the demo ones try)
User: "HSP"
Pass: "cake"
User: "Ethereal"
Pass: "happy now?"
Download
If you are lazy, want to get the DLL's or just want to see the source check it out here:
http://www.sendspace.com/file/h158zh
Sorry for the kinda fail host but my browser just got "IO Error" everwhere else (tried 8 others or so).
Please remember to say thanks! I HATE to see leechers who doesn't leave some feedback. Leech all you want but just say THANKS for God's sake!!! -.-'
Final note
This tutorial was done on request as I stated earlier.
I know my way of working in ASP.NET may seem strange (keeping my code inside my "GUI") ... But that's because I'm coming from PHP and classic ASP. all that MVC and so on isn't really me (if I can get away with don't using it at least).
And I know, the design sucks but wtf. It's the code that counts in this tut. Go to the graphics section if you like nice graphics ;)
Remember to say thanks!!! ;)
That's all for now!🤝
* - Ethereal - Apr 13, 2011
Thanks for this, nubcakes.
Bookmarked for later ^_^
Remove the Rep request at the bottom though, you'll get negged and repfucked.
* - HSPSoftware - Apr 13, 2011
(Apr 13, 2011 09:15 AM)Ethereal Wrote: Thanks for this, nubcakes.
Bookmarked for later ^_^
Remove the Rep request at the bottom though, you'll get negged and repfucked.
So little ;)
Sorry I though that would happen but I'm just an impatient bitch by nature 😅
* - raamee - Apr 13, 2011
Cool man. Thanks for the tut. I am beginning with asp.net and mssql server. This will help.
* - H3x0S - Apr 13, 2011
Great tutorial. It's good to see someone using parameters to avoid SQLi attacks. You should expand this tutorial with more information about Forms Authentication because otherwise, this login system is useful.
(Apr 13, 2011 09:29 AM)raamee Wrote: Cool man. Thanks for the tut. I am beginning with asp.net and mssql server. This will help.
Actually, it's not MSSQL Server. MSSQL = SQL Server. 🙂
* - raamee - Apr 13, 2011
(Apr 13, 2011 09:29 AM)H3x0S Wrote: Great tutorial. It's good to see someone using parameters to avoid SQLi attacks. You should expand this tutorial with more information about Forms Authentication because otherwise, this login system is useful.
Actually, it's not MSSQL Server. MSSQL = SQL Server. 🙂
Oh,,,😯 thanks for the info.
* - HSPSoftware - Apr 13, 2011
(Apr 13, 2011 09:29 AM)raamee Wrote: Cool man. Thanks for the tut. I am beginning with asp.net and mssql server. This will help.Glad you liked it. This is for MySQL server but it should be around the same anyway.
(Apr 13, 2011 09:29 AM)H3x0S Wrote: Great tutorial. It's good to see someone using parameters to avoid SQLi attacks. You should expand this tutorial with more information about Forms Authentication because otherwise, this login system is useful.
Actually, it's not MSSQL Server. MSSQL = SQL Server. 🙂
Well it's not meant as a complete login system. If it was it should have some kind of sessions and if you ask me, maybe javascript based public key encryption and properly also a "remember me" feature.
Would you be interested if I did a writeup about making a complete login system? :)
* - H3x0S - Apr 13, 2011
(Apr 13, 2011 09:33 AM)HSPSoftware Wrote: Would you be interested if I did a writeup about making a complete login system? :)
That would be great. I already know how to do it, but I think many people want to learn about it. Don't forget to mention roles. ASP.NET is amazing language for web development, especially if you know VB.NET/C#.
* - HSPSoftware - Apr 13, 2011
(Apr 13, 2011 09:32 AM)raamee Wrote: Oh,,,😯 thanks for the info.
If it is, Google is your friend. It's not that hard to connect to a MS Sql server, it's just like Access.
http://www.startvbdotnet.com/ado/sqlserver.aspx
Feel free to ask me, if you ask nice enough I may set up a server tomorrow and write some kind of tutorial for that to ;)
* - H3x0S - Apr 13, 2011
Also, it's better to use MSSQL in ASP.NET applications instead of MySQL because it's a lot easier.
(Apr 13, 2011 09:36 AM)HSPSoftware Wrote: If it is, Google is your friend. It's not that hard to connect to a MS Sql server, it's just like Access.
http://www.startvbdotnet.com/ado/sqlserver.aspx
Feel free to ask me, if you ask nice enough I may set up a server tomorrow and write some kind of tutorial for that to ;)
The only different thing is connection string. Everything else is same.
* - HSPSoftware - Apr 13, 2011
(Apr 13, 2011 09:37 AM)H3x0S Wrote: Also, it's better to use MSSQL in ASP.NET applications instead of MySQL because it's a lot easier.
The only different thing is connection string. Everything else is same.
Yeah I know but Ethereal wanted it to be MySQL. Also it's one of the topics which less info and sometimes people actually only have MySQL even though it's rare if they have ASP.NET.
* - TLF-KoBE - Apr 13, 2011
Nice and informative tutorial.. Good job.
* - ii_SNiPEzZ_xX - May 18, 2011
Can You Tell Me How To Fix This Plz
Problem :
http://screensnapr.com/v/faI8G5.png
Iv Tryed Opening The Port And Its On TCP, Also I Opened It In My Bullguard And I Keep Getting This, HELP ME PLZ
* - BaussHacker - May 18, 2011
Why would you use MySQL, when MSSQL is already intergrated? That's stupid.
* - HSPSoftware - May 22, 2011
(May 18, 2011 11:19 AM)BaussHacker Wrote: Why would you use MySQL, when MSSQL is already intergrated? That's stupid.
Well.. Sometimes it's easier if you already have some system that uses PHP / MySQL for example. And there is a lot of tutorials on the MS SQL, and as far as I remember there isn't any rule against documenting doing things that you only do rarely.