How do I prevent MySQL Database Injection Attacks using vb.net?

Question

I am using vb.net in Visual Basic 2010 and using Query to edit my Online MySQL Database from the application (WinForms).

Here is a sample to insert a new user into the database:

MySQLCon.Open()
Dim SQLADD As String = "INSERT INTO members(member,gamertag,role) VALUES('" & memberToAdd.Text & "','" & membersGamertag.Text & "','" & membersRole.Text & "')"
COMMAND = New MySqlCommand(SQLADD, MySQLCon)
READER = COMMAND.ExecuteReader
memberToAdd.Text = ""
membersGamertag.Text = ""
membersRole.Text = ""
MySQLCon.Close()
MySQLCon.Dispose()

How to Prevent MySQL Database Injection Attacks?

--------------------------------------------------------------------------------

Is this Parameterized way also ideal for these sets of code?

Set 1:

Dim SQLReq As String = "UPDATE members SET req= '" & request & "' WHERE member= '" & My.Settings.username & "'"
submitRequest(SQLReq)

Set 2

MySQLCon.Open()
Dim SQLID As String = "SELECT * FROM members WHERE member='" & My.Settings.username & "'"
COMMAND = New MySqlCommand(SQLID, MySQLCon)
READER = COMMAND.ExecuteReader()
While READER.Read
xboxGamertag.Value2 = READER.GetString("gamertag")
vagueRole.Value2 = READER.GetString("role")
vagueID.Value2 = READER.GetInt32("id")
End While
MySQLCon.Close()
MySQLCon.Dispose()

Set 3

MySQLCon.Open()
Dim Query As String
Query = "SELECT member FROM members"
command = New MySqlCommand(Query, MySQLCon)
SDA.SelectCommand = command
SDA.Fill(dbDataSet)
bSource.DataSource = dbDataSet
vagueMembers.DataSource = bSource
SDA.Update(dbDataSet)
MySQLCon.Close()
MySQLCon.Dispose()

This is an edit for @Fred

Set 1 is now:

MySQLCon.Open()
Dim SQLADD As String = "UPDATE members SET req= @request WHERE member= @memberName"
COMMAND = New MySqlCommand(SQLADD, MySQLCon)
COMMAND.Parameters.AddWithValue("@request", request)
COMMAND.Parameters.AddWithValue("@memberName", My.Settings.username)
COMMAND.ExecuteNonQuery()
MySQLCon.Close()
MySQLCon.Dispose()

Set 2 is now:

MySQLCon.Open()
Dim SQLID As String = "SELECT * FROM members WHERE member= @member"
COMMAND = New MySqlCommand(SQLID, MySQLCon)
COMMAND.Parameters.AddWithValue("@member", My.Settings.username)
COMMAND.ExecuteNonQuery()
READER = COMMAND.ExecuteReader()
While READER.Read
xboxGamertag.Value2 = READER.GetString("gamertag")
vagueRole.Value2 = READER.GetString("role")
vagueID.Value2 = READER.GetInt32("id")
End While
MySQLCon.Close()
MySQLCon.Dispose()

Set 3 is now:

Same as usual cause you said it should be fine.

Are these correct? Protected from Injections?

Take a look at the answer to this question. You need to use parameters, just convert it to VB.Net http://stackoverflow.com/questions/13580993/mysqlcommand-command-parameters-add-is-obsolete — Fred, Jun 16 '15 at 07:13
@Fred i edited my question details and would like you to answer that as well. — AfterShotzZHD, Jun 16 '15 at 07:26
If you have another question then post that separately, otherwise we could go on all day trying to answer each edit — Matt Wilko, Jun 16 '15 at 07:35
ok sure i could do this in 7 days as i have no more available questions that i can post on Stackoverflow. This is why i ask now cause it's related to the question and i need this answered now rather than in 7 days. Thanks anyways @MattWilko — AfterShotzZHD, Jun 16 '15 at 07:38
@MattWilko no you may not post as many as you like. it limits me to 1-3 a weak. — AfterShotzZHD, Jun 16 '15 at 07:43
A user may ask 50 questions over any 30-day period six questions over any 24-hour period one question over any 30-second period users with less than 125 reputation can only post questions every 20 minutes http://meta.stackexchange.com/questions/4359/is-there-a-limit-on-how-many-questions-i-can-ask — Matt Wilko, Jun 16 '15 at 07:45

Fred · Accepted Answer · 2015-06-16T07:38:26.883

2

MySQLCon.Open()
Dim SQLADD As String = "INSERT INTO members(member,gamertag,role) VALUES(@memberToAdd, @memberGamingTag, @memberRole)"
COMMAND = New MySqlCommand(SQLADD, MySQLCon)
COMMAND.Parameters.AddWithValue("@memberToAdd", memberToAdd.Text)  
COMMAND.Parameters.AddWithValue("@memberGamingTag", membersGamertag.Text)  
COMMAND.Parameters.AddWithValue("@memberRole", membersRole.Text)  
COMMAND.ExecuteNonQuery()
memberToAdd.Text = ""
membersGamertag.Text = ""
membersRole.Text = ""
MySQLCon.Close()
MySQLCon.Dispose()

You don't need to use COMMAND.ExecuteReader as you are not retrieving data.

You should never build your queries like this:

UPDATE members SET req= '" & request & "' WHERE member= '" & My.Settings.username & "'"

It is vunerable to SQL Injection, you should parameterize your queries as I have in the example above. This applies to any query be it INSERT, UPDATE, SELECT

edited Jun 16 '15 at 07:38

answered Jun 16 '15 at 07:22

Fred

5,663
4
45
74

I like this format. But is it ideal for my above edits in my question details? – AfterShotzZHD Jun 16 '15 at 07:28
Set 1: pass your value into the function and set the parameter in the function. Set 2 yes this should be `member=@member` and set a parameter as above. Set 3 has no value to set so that is fine. – Fred Jun 16 '15 at 07:36
Thanks for your kindness and great help. I'm currently now checking your answer and the one that works. – AfterShotzZHD Jun 16 '15 at 07:40
i am going to want to clarify that i have these typed up right so i'll be replying to you with the 3 sets in the format if you can tell me if they are right. – AfterShotzZHD Jun 16 '15 at 07:53

Matt Wilko · Answer 2 · 2015-06-16T07:34:16.310

Instead of concatenating your SQL statement like this:

Dim SQLADD As String = "INSERT INTO members(member,gamertag,role) VALUES('" & memberToAdd.Text & "','" & membersGamertag.Text & "','" & membersRole.Text & "')"

You specify each parameter using an @ prefix like this without any quotes:

Dim SQLADD As String = "INSERT INTO members(member,gamertag,role) VALUES(@member, @gamertag, @role)"

Then you specify the values for each parameter like this using the correct variable type for the data type in your database:

command.Parameters.AddWithValue("@member", member)
command.Parameters.AddWithValue("@gamertag", gamertag)
command.Parameters.AddWithValue("@role", role)

Your example edit is not a parameterised query. You should never concatenate your SQL string with variables.

score -1 · Answer 3 · answered Jun 16 '15 at 07:24

You should put the insert query at the Stored Procedure.

conn.Open();
                    cmd.Connection = conn;

                    cmd.CommandText = "add_mem";
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@member", "Jones");
                    cmd.Parameters["@lname"].Direction = ParameterDirection.Input;

                    cmd.Parameters.AddWithValue("@gamertag", "Tom");
                    cmd.Parameters["@fname"].Direction = ParameterDirection.Input;

                    cmd.Parameters.AddWithValue("@role", "1940-06-07");
                    cmd.Parameters["@bday"].Direction = ParameterDirection.Input;


                    cmd.Parameters["@empno"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

How do I prevent MySQL Database Injection Attacks using vb.net?

3 Answers3

Linked