Translating all my code into vb.Net to avoid SQL Injection. With the examples here, I make the new INSERT INTO portion of code and think is working. But I am stuck to get back and verify the data.
Please if can show me a example how to do properly and populate combobox, datagrigview and textboxes.
Aditionally some example to simulate a SQL Injection from vb.Net to testing and learn for beginners developers like me.
My old code to INSERT:
Dim MySQLQuery As String = "INSERT INTO `Agents` (`User_Name`, `User_Pic`) VALUES (AES_ENCRYPT('" & txtUserName.Text & "', '" & MyPass & "'),
AES_ENCRYPT('" & txtUserPic.Text & "', '" & MyPass & "'"
My new parametrized code to INSERT:
MySQLConn.Open()
Dim command As New MySqlCommand()
Dim SQLADD As String = "INSERT INTO `Agents` (`AG_Nom`, `AG_Pic`) VALUES (AES_ENCRYPT('" & "'@UserName'" & "', '" & MyPass & "'), AES_ENCRYPT('" & "'@UserPic'" & "', '" & MyPass & "'))"
command.CommandText = SQLADD
command.Parameters.AddWithValue("@UserName", txtName.Text)
command.Parameters.AddWithValue("@UserPic", txtPicPath.Text)
command.Connection = MySQLConn
command.ExecuteNonQuery()
MySQLConn.Close()
My old code to SELECT:
MySQLQuery = "SELECT AES_DECRYPT(`User_Name`, '" & MyPass & "') AS UName, AES_DECRYPT(`User_Pic`, '" & MyPass & "') AS UPic FROM `Agents`"
MsgBox(MySQLReader.GetString("UName") & vbCrLf & MySQLReader.GetString("UPic")
How build new parametrized SELECT?
I try with:
Dim command As New MySqlCommand()
MySQLConn.Open()
Dim SQLID As String = "SELECT AES_DECRYPT(`AG_Nom`, '" & MyPass & "') AS @UserName, AES_DECRYPT(`AG_Pic`, '" & MyPass & "') AS @UserPic FROM `Agents`"
command = New MySqlCommand(SQLID, MySQLConn)
Dim Reader As MySqlDataReader
Reader = command.ExecuteReader()
While Reader.Read
txtDcryName.Text = Reader.GetString("@UserName")
txtDcryPicPath.Text = Reader.GetString("@UserPic")
End While
MySQLConn.Close()
MySQLConn.Dispose()
The last code Not working, error at Reader = command.ExecuteReader()
TIA
Talk1:
Will you tell us what the error is or must we guess?
Talk2:
Not is about error. As the title says, it's about converting the code to prevent SQL injection. Doubt was completely resolved with Saragis´s help, which is confirmed in the comments. Thank you for you attention.
Solutions1
You don't have to add the @ to your column identifiers. Also your insert-statement is still vulnerable to SQL-injection through the MyPass variable. It can be changed according to this, I'll leave that up to you.
Const SelData As String =
"SELECT AES_DECRYPT(`AG_Nom`, @MyPass) AS UserName, AES_DECRYPT(`AG_Pic`, @MyPass) AS UserPic FROM `Agents`"
Using conn As New MySqlConnection()
conn.Open()
Using comm As New MySqlCommand(SelData, conn)
comm.Parameters.AddWithValue("@MyPass", MyPass)
Using r As MySqlDataReader = comm.ExecuteReader
While r.Read
txtDcryName.Text = r.GetString("UserName")
txtDcryPicPath.Text = r.GetString("UserPic")
End While
End Using
End Using
End Using
Talk1:
Saragis´s solution works like a charm!!. I Forgot to say about 'MyPass' is a global variable where the key value is stored. In this sense, It's enough to not be vulnerable or is still advisable use:? Using comm As New MySqlCommand (SelData, conn) comm.Parameters.AddWithValue ("@ MyPass" MyPass)
Talk2:
Imo it's advisable to always parametrize your input values. I use it for everything, including global constants. This way you create a consistency in your code, it lowers the chance for errors because of faulty string concatenation and it enables you to make the query a constant. It also allows you to make changes to the input values without the need to change the query itself.
Talk3:
Understend Saragis, is great and nice advice in your orientation, this greatly improve my coding, Hope the same to others beginers :)