Moving code to avoid SQL Injection using MySQL AES_ENCRYPT / AES_DECRYPT

Question

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

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. — Amigo, Jul 24 '15 at 18:17

score 1 · Accepted Answer · answered Jul 24 '15 at 10:18

1

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

answered Jul 24 '15 at 10:18

Saragis

1,782
6
21
30

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) – Amigo Jul 24 '15 at 12:16
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. – Saragis Jul 24 '15 at 12:17
Understend Saragis, is great and nice advice in your orientation, this greatly improve my coding, Hope the same to others beginers :) – Amigo Jul 24 '15 at 12:24

Moving code to avoid SQL Injection using MySQL AES_ENCRYPT / AES_DECRYPT

1 Answers1