Click here to Skip to main content
13,661,044 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I need to parameterized my queries for safety purposes.

What I have tried:

 Private Sub Save_Click(sender As Object, e As EventArgs) Handles Save.Click
       
        MyNonQuery(String.Format("insert into boyscout_pos.tblproducts (Purchase_Invoice,Product_Code,Product_Name,Category,Size,Product_Price,Selling_Price,Qty_Stock) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", txtpurchaseinvoice.Text, txtproductcode.Text, txtproductname.Text, txtproductcategory.Text, TextBoxSize.Text, txtproductprice.Text, txtsellingprice.Text, txtproductquantity.Text))

        MessageBox.Show("Successfully Saved")

        TxtSearchCode.Clear()
        cboproductsize.Items.Clear()
        txtproductcode.Clear()
        txtproductname.Clear()
        txtproductcategory.Clear()
        txtproductprice.Clear()
        txtsellingprice.Clear()
        TextBoxSize.Clear()
        cboproductsize.Text = ""
        txtproductquantity.Clear()
End Sub
    
Public Sub MyNonQuery(ByVal SQCommand As String)
       Dim conn As New 
       MySqlConnection("server=localhost;userid=root;password=;database=boyscout_pos")
       Dim SQLCMD As New MySqlCommand(SQCommand, conn)
       conn.Open()
       SQLCMD.ExecuteNonQuery()
       conn.Close()
End Sub
Posted 15-Aug-17 15:49pm
Updated 15-Aug-17 19:42pm
v2
Comments
ppolymorphe 15-Aug-17 23:35pm
   
A,d what says Google in your place ?
Dave Kreskowiak 15-Aug-17 23:46pm
   
Type "vb.net parameterize queries mysql" into Google and you'll see this has been documented many, many times.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You want to end up with code like this:
Using con As New MySqlConnection(strConnect)
	con.Open()
	Using com As New MySqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con)
		com.Parameters.AddWithValue("@C1", myValueForColumn1)
		com.Parameters.AddWithValue("@C2", myValueForColumn2)
		com.ExecuteNonQuery()
	End Using
End Using
Which means that the way you are doing it will not work - you can't just pass a string to a "Generic" function.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

If you're using Visual Studio 2015 or higher, and targeting .NET 4.6 or later, something like this will work:
Public Shared Function CreateCommand(ByVal connection As IDbConnection, ByVal commandText As FormattableString, Optional ByVal commandType As CommandType = CommandType.Text) As IDbCommand
    Dim result As IDbCommand = connection.CreateCommand()
    
    Dim parameterNames As New List(Of String)(commandText.ArgumentCount)
    For Each parameter As Object In commandText.GetArguments()
        Dim parameterName As String = "@p" & parameterNames.Count
        parameterNames.Add(parameterName)
        
        Dim p As IDbDataParameter = result.CreateParameter()
        p.ParameterName = parameterName
        p.Value = parameter
        result.Parameters.Add(p)
    Next
    
    result.CommandText = String.Format(commandText.Format, parameterNames.ToArray())
    result.CommandType = commandType
    Return result
End Function

Public Sub MyNonQuery(ByVal commandText As FormattableString, Optional ByVal commandType As CommandType = CommandType.Text)
    Using connection As New MySqlConnection("server=localhost;userid=root;password=;database=boyscout_pos")
        Using command As IDbCommand = CreateCommand(connection, commandText, commandType)
            connection.Open()
            command.ExecuteNonQuery()
        End Using
    End Using
End Sub

...

MyNonQuery($"insert into boyscout_pos.tblproducts (Purchase_Invoice, Product_Code, Product_Name, Category, Size, Product_Price, Selling_Price, Qty_Stock) values ({txtpurchaseinvoice.Text}, {txtproductcode.Text}, {txtproductname.Text}, {txtproductcategory.Text}, {TextBoxSize.Text}, {txtproductprice.Text}, {txtsellingprice.Text}, {txtproductquantity.Text})")

This uses string interpolation[^] to pass the command text and parameters as one object. By passing it as a FormattableString[^], it is able to extract the parameter values and pass them properly, rather than concatenating them into the command text.

(NB: I didn't invent this idea - I saw it in an article, which I think was somewhere on CodeProject. Unfortunately, I didn't keep the link, and I can't find it now. If the author spots this and wants me to add a link, let me know.)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180810.1 | Last Updated 17 Aug 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100