Parameterize SQL Queries

Question

I want parameterize some SQL Statements so my code isn't vunerable to SQL Injections any longer But i have actually no plan how to parameterize for example a where clause.

Dim accID As String = DatabaseConnecter.readField("SELECT ID FROM accounts WHERE accountname ='" & user & "' AND password='" & pw & "';")

The Problem is if you type in a given username, for example test and extend the username with. You can log in without entering the password into the Application.

Edit:

Public Function readField(ByVal sql As String) As String
        Dim output As String = "ERROR"
        Using cn = New MySqlConnection(connString.ToString())
            Using cmd = New MySqlCommand(sql, cn)
                cn.Open()
                Using rd = cmd.ExecuteReader()
                    Try
                        rd.Read()
                        output = rd.GetString(0)
                        rd.Close()
                    Catch ex As Exception
                    End Try
                End Using
                cn.Close()
            End Using
        End Using
        Return output
    End Function
´´´

You need to change the _readField_ method if you want to parameterize that query. Could you post the code of _readField_? — Steve, Aug 20 '19 at 09:52

score 1 · Answer 1 · answered Aug 20 '19 at 09:58

Private Function GetID(User As String, pw As String) As String
    Using cmd As New SqlCommand("SELECT ID FROM accounts WHERE accountname =@user AND password=@password", New SqlConnection(SQLConnString))
        cmd.Parameters.AddWithValue("@user", User)
        cmd.Parameters.Add("@password", SqlDbType.NVarChar)
        cmd.Parameters("@password").Value = pw
        Try
            cmd.Connection.Open()
            Return cmd.ExecuteScalar()
        Catch ex As Exception
            'handle error
            Return Nothing
        Finally
            cmd.Connection.Close()
        End Try
    End Using

End Function

I've demostrated two methods of setting the parameters. Search for more info or comparison.

I am using Mysql.data because i connect to a MariaDB not to a MS SQL DB — unicursal7, Aug 20 '19 at 10:05
I believe the syntax is similar for MySql. This link may help https://dev.mysql.com/doc/dev/connector-net/8.0/html/P_MySql_Data_MySqlClient_MySqlCommand_Parameters.htm — Jon Roberts, Aug 20 '19 at 10:32

Steve · Accepted Answer · 2019-08-20T11:29:33.740

To have a parameterized query you need to create parameters and write a proper SQL text where, in place of values typed directly from your user, you have parameter placeholders.

So, for example, you sql text should be something like this

Dim sqlText = "SELECT ID FROM accounts WHERE accountname =@name AND password=@pwd"

Now you have a parameterized text, but stil we need to create the parameters that will be sent to the database engine together with your sql command.

You can create the parameter (two in this case) in this way before calling the method that executes the query

Dim p1 as MySqlParameter = new MySqlParameter("@name", MySqlDbType.VarChar)
p1.Value = user  

Dim p2 as MySqlParameter = new MySqlParameter("@pwd", MySqlDbType.VarChar)
p2.Value = password

Dim pms As List(Of MySqlParameter) = new List(Of MySqlParameter)()
pms.Add(p1)
pms.Add(p2)

Now we need to pass this list to your method (and this requires changes to your method signature)

DatabaseConnecter.readField(sqlText, pms)

The method itself should change to something like

Public Function readField(ByVal sql As String, Optional pms As List(Of MySqlParameter) = Nothing) As String
    Dim output As String = "ERROR"
    Using cn = New MySqlConnection(connString.ToString())
        Using cmd = New MySqlCommand(sql, cn)
            cn.Open()

            ' This block adds the parameter defined by the caller to the command
            ' The parameters are optional so we need to check if we have really received the list or not
            if pms IsNot Nothing Then
                cmd.Parameters.AddRange(pms.ToArray())
            End If
            Using rd = cmd.ExecuteReader()
                Try
                    rd.Read()
                    output = rd.GetString(0)
                    rd.Close()
                Catch ex As Exception
                End Try
            End Using
            ' no need to close when inside a using block
            ' cn.Close()
        End Using
    End Using
    Return output
End Function

The method now has an optional parameter that will contain the list of the parameters required by the query (or nothing if your query doesn't require parameters). This list is added to the command parameters collection and the query is now executed.

Final Note: Storing passwords in clear text into a database is a well known security problem. I suggest you to search about how to store passwords in a database.

Parameterize SQL Queries

2 Answers2