mysql aes 支持特殊字符_使用MySQL AES_ENCRYPT / AES_DECRYPT移动代码以避免SQL注入

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 :)