Insert data into MySQL database using parameters

Question

Recently I developed some software that inserts data into MySQL but someone told me I should use parameters. I Googled them but didn't found any data.

Here is my code:

Dim Query As String
Query = "insert into baza.artikli(kod,naziv,nabavna,prodazna,ddv,kolicina,opis,opis2,mkproizvod,profit,proizvoditel) values ('" & TextBoxBarkod.Text & "','" & TextBoxNaziv.Text & "','" & kupovnacena & "','" & prodaznacena & "','" & ddv & "','" & kolicina & "','" & TextBoxOpis.Text & "','" & TextBoxOpis2.Text & "','" & mkpr & "','" & profit & "','" & TextBoxProizvoditel.Text & "')"
COMMAND = New MySqlCommand(Query, konekcija)
READER = COMMAND.ExecuteReader

score 1 · Answer 1 · edited May 15 '17 at 09:50

1

To prevent injection

Here's an example

Dim query As String

query = "INSERT INTO Users (user_name) VALUES(?);"
cmd = New MySqlCommand(query, con)
cmd.Parameters.AddWithValue("?", Me.User_nameTextBox.Text)
//Execute your command......

edited May 15 '17 at 09:50

Bugs

4,491
9
32
41

answered May 13 '17 at 12:23

Melchizedek

1,057
17
29

score 0 · Answer 2 · edited May 23 '17 at 12:26

0

Please check on Prepared Statements

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

Or refer Prepared Statements in VB.NET

edited May 23 '17 at 12:26

Community

1
1

answered May 13 '17 at 11:52

Deepak Tiwari

186
7

Bugs · Answer 3 · 2017-05-15T09:50:10.417

I strongly recommend you use SQL parameters. This is to reduce syntax issues but more importantly stops SQL injection. See Bobby Tables for more details on this.

I've also noted that you're using ExecuteReader. This is generally used for select statements. For inserts and updates you should use ExecuteNonQuery. Have a look at the documentation for more information.

If you haven't already I also suggest you implement Using:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

With these changes your code would look something like this:

Using con As New MySqlConnection(yourConnectionString),
      cmd As New MySqlCommand("INSERT INTO baza.artikli (kod, naziv, nabavna, prodazna, ddv, kolicina, opis, opis2, mkproizvod, profit, proizvoditel) VALUES (@kod, @naziv, @nabavna, @prodazna, @ddv, @kolicina, @opis, @opis2, @mkproizvod, @profit, @proizvoditel)", con)

    con.Open()

    cmd.Parameters.Add("@kod", MySqlDbType.[Type]).Value = TextBoxBarkod.Text
    cmd.Parameters.Add("@naziv", MySqlDbType.[Type]).Value = TextBoxNaziv.Text
    cmd.Parameters.Add("@nabavna", MySqlDbType.[Type]).Value = kupovnacena
    cmd.Parameters.Add("@prodazna", MySqlDbType.[Type]).Value = prodaznacena
    cmd.Parameters.Add("@ddv", MySqlDbType.[Type]).Value = ddv
    cmd.Parameters.Add("@kolicina", MySqlDbType.[Type]).Value = kolicina
    cmd.Parameters.Add("@opis", MySqlDbType.[Type]).Value = TextBoxOpis.Text
    cmd.Parameters.Add("@opis2", MySqlDbType.[Type]).Value = TextBoxOpis2.Text
    cmd.Parameters.Add("@mkproizvod", MySqlDbType.[Type]).Value = mkpr
    cmd.Parameters.Add("@profit", MySqlDbType.[Type]).Value = profit
    cmd.Parameters.Add("@proizvoditel", MySqlDbType.[Type]).Value = TextBoxProizvoditel.Text

    cmd.ExecuteNonQuery()

End Using

Note that I have used MySqlDbType.[Type]. You will want to replace [Type] with the data type you've used on your database.

score -1 · Answer 4 · edited May 23 '17 at 12:18

-1

To answer you first question: Set

TextBoxBarkod.Text = "(select password from mysql.user where user=’root’)"

Or in other words: Search the web for SQL-injection.

For an example how to use parameters see e.g. this post.

edited May 23 '17 at 12:18

Community

1
1

answered May 13 '17 at 11:51

LSA

404
7
11

This wont work, didnt supply all fields...the insert would fail and the select wont happen. If the insert only had one column, then yes it would work... – Trevor May 13 '17 at 12:00
Good point, at least it would have generated an error ;-). Made some changes. – LSA May 13 '17 at 12:06

Insert data into MySQL database using parameters

4 Answers4