Creating a parameterised MySQL query from multiple concatenated strings

Question

What I'm aiming to do is create a few functions which will assist me in the creation of a multitude of MySqlCommand. But I'm going wrong (information provided below).

Background

I have achieved this by defining constants in a class, such as:

Public Const Create_Table = "CREATE TABLE IF NOT EXISTS `@arg0` (@arg1)"

Strings such as this form the basis for my parameterised query. Now, these arguments aren't single values, they are actually concatenated strings. So I know I'm edging towards dangerous waters here, so bear with me here.

I use the following function to concatenate arguments:

Public Shared Function ListToQuery(values As List(Of String),
                                   Optional ByVal separator As String = ", ") As String
    Dim queryBuilder As New StringBuilder

    For Each value As String In values
        queryBuilder.Append((value) & separator)
    Next
    Dim query As String = queryBuilder.ToString
    Return query.Remove(query.Length - 2)
End Function

This works as per normal; when I call it with a List:

Dim myValues As New List(Of String)
myValues.AddRange({"Int Primary", "Text Name"})
MsgBox(ListToQuery(myValues))

It returns "Int Primary, Text Name" - perfectly normal. However, this is where things start to go wrong.

My next part is to create the MySqlCommand based off several arguments, an Array of String. This is achieved by calling the following function:

Public Shared Function BuildCommand(args() As String, Command As String, connection As MySqlConnection) As MySqlCommand

    Dim cmd As New MySqlCommand(Command, connection)
    For i As Integer = 0 To args.Length - 1
        'cmd.CommandText = cmd.CommandText.Replace("@arg" & i, args(i))
        cmd.Parameters.AddWithValue("@arg" & i, MySqlHelper.EscapeString(args(i)))
    Next
    Return cmd

End Function

Issue

I call this function by doing this:

Dim myCommand as MySqlCommand = BuildCommand({"MyTableName", ListToQuery(myValues)}, Create_Table, myConnection)
Dim dr = myCommand.ExecuteReader()

What seems to happen, here is that an error occurs, and I picked up something which seems to occur within the parameterised query:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''Int Primary, Text Name')' at line 1

The argument seems to have been encapsulated within ', if I'm interpreting this error correctly. Now note the comment in my code for BuildCommand(). If I were to use pure concatenation, that query would run just fine.

Edit: The resulting query should have been:

CREATE TABLE IF NOT EXISTS `MyTableName` (Int Primary, Text Name)

Question

I have a couple, in fact. Firstly, I would like to ask if this is a safe way (i.e. even with concatenating my arguments and using parameters in the query) to run a command, and secondly, how can I avoid getting this error?

I'm not quite sure if I can parse each argument individually without causing the query to become 'unsafe' or without modifying the general MySQL statement.

I've been discussing this with another user and he managed to find this question: Parameterized dynamic sql query. This is asking a similar question. However, it is not the same issue I'm having.

Thanks in advance.

Answer 1

This code is not the same as the one you showed me before, but now that I see what changes you've made and in what context you use this things just got much easier.

When adding SQL parameters you do not need to escape their value since that is done automatically. This is probably why you experience it getting escaped twice.

Change:

cmd.Parameters.AddWithValue("@arg" & i, MySqlHelper.EscapeString(args(i)))

to:

cmd.Parameters.AddWithValue("@arg" & i, args(i))

...and it should work (and be safe)!

Answer 2

This function generates a CREATE TABLE command. I got the same problem several times, but until now I did not find a suitable solution. Instead I create my query my own:

    Public Shared Function BuildCommand(args() As String, connection As MySqlConnection) As MySqlCommand
    Dim sb As New StringBuilder
    Dim cmd As New MySqlCommand
    cmd.Connection = connection
    sb.Append("CREATE TABLE IF NOT EXISTS`")
    sb.Append(args(0))
    sb.Append("`(")
    For i As Integer = 1 To args.Length - 1
        sb.Append("`")
        sb.Append(args(i))
        sb.Append("`,")
    Next
    sb.Remove(sb.Length - 1, 1)
    sb.Append(")")
    cmd.CommandText = sb.ToString
    Return cmd
End Function