How to concatenate single quote in MySQL query with VB.NET parameter?

Question

I am making a MySQL Select query using MySQLCommand object in VB.NET were I use parameters. I am facing an issue, in my where clause, I need to put the value for the criteria into single quote, I tried to use backslash ' (\') to escape the single quote, it does not work. I used double quotes as well, same issue. Can somebody help me? Is there something specific I need to do when using the MySQLCommand object in VB.NET with parameter and want my parameter value to be in single quote into a query?

Here is the Function in which I make the MySQL query:

Public Shared Function getGeographyUnits(critere As String, valeur As String) As List(Of geography_unit)
    Dim conn As MySqlConnection = DBUtils.GetDBConnection()
    Dim rdr As MySqlDataReader
    conn.Open()
    Dim cmd As MySqlCommand = New MySqlCommand("select ID,description from geography_unit where @critere = ''@valeur''", conn)
    cmd.Parameters.AddWithValue("@critere", critere)
    cmd.Parameters.AddWithValue("@valeur", valeur)
    rdr = cmd.ExecuteReader()
    Dim geography_units As New List(Of geography_unit)
    While rdr.Read
        Dim geography_unit As New geography_unit
        Try
            geography_unit.ID = CLng(rdr("Id"))
            geography_unit.description = rdr("description")
        Catch ex As Exception
        End Try
        geography_units.Add(geography_unit)
    End While
    rdr.Close()
    conn.Close()
    Return geography_units
End Function

Actually, I want the cmdText for my query to be something like this after rendering:

select ID,description from geography_unit where critere = 'valeur'

The issue comes mainly from the fact that I am using parameter, how can I solve it?

Do not post images of code. We can't copy paste them to try the code shown. — Steve, Jan 05 '21 at 16:07
Thanks for your feedback @Steve, I just edited my question and put the code snippet! — Cherlan-Miche PHILIPPE, Jan 05 '21 at 16:08
Said that do not enclose parameters placeholders in quotes. They are seen as string constants and are not associated to the parameter values. Finally if your value needs to have single quotes then add the quotes to the value (_valeur = $"'{valeur}'";_) — Steve, Jan 05 '21 at 16:09
That's better. Now I can see another error. You cannot have a field name represented by a parameter @critere is not valid in that point, you should have a string constant there — Steve, Jan 05 '21 at 16:10
From what I understand, I need to concatenate the quote with the value where I am passing the value to the function; consequently, where I call the function. Is that what you mean? — Cherlan-Miche PHILIPPE, Jan 05 '21 at 16:11
The string _valeur_ passed doesn't contain the quotes. If you are searching for a value that contains quotes then you could add to the string _valeur_ as I have explained before. If, instead, you think you need to add the quotes because you are querying a text field then this is a mistake. When using parameters quotes are not required. It is the type of the parameter that helps the database engine to properly execute the query — Steve, Jan 05 '21 at 16:40
Thanks for your comments @Steve, it works when I put the field directly as a string constant in my MySQLCommand as you suggested and when passing value to the parameter value where I call the function, I just put the value in double quote. But another issue emerges. I don't want to duplicate the function if I have to change the critere (which represents a field in the table), what do you think I can do? — Cherlan-Miche PHILIPPE, Jan 05 '21 at 16:47
Well, that's a problem that could only be resolved if you are absolutely sure that your user is not allowed to type the value for the _critere_ string. If you have a combobox or some other kind of control with a predefined set of valid input for _critere_ then you can concatente the critere value — Steve, Jan 05 '21 at 16:49
I understand, you are totally right @Steve from your last comment, I just put the value for valeur parameter into double quote and it works, and I use the field critere as a string constant — Cherlan-Miche PHILIPPE, Jan 05 '21 at 16:49

Steve · Accepted Answer · 2021-01-05T17:47:29.843

You need to fix your code with something like this. But please note a couple of things.

If the @valeur is enclosed in single quotes it is no more a parameter placeholder but a string constant and the parameter associated with the placeholder will not be used.

The connection should always enclosed in a using statement to avoid dangerous resources consuption on the server

If you want to have a variable list of field to which apply the valeur passed then you need to be absolutely sure that your user is not allowed to type the value for critere. You should provide some kind of control like combobox or dropdwonlist where the user could only choose between a prefixed set of values, then you can concatenate the critere variable to your sql command.

Public Shared Function getGeographyUnits(critere As String, valeur As String) As List(Of geography_unit)
    Using conn As MySqlConnection = DBUtils.GetDBConnection()

        Dim sqlText As String = "select ID,description from geography_unit"
        
        conn.Open()
        If Not String.IsNullOrEmpty(critere) Then
            sqlText = sqlText & " where " & critere & " = @valeur"
        End If
        Dim cmd As MySqlCommand = New MySqlCommand(sqlText, conn)
        cmd.Parameters.Add("@valeur", MySqlDbType.VarChar).Value = valeur
        Using rdr As MySqlDataReader = cmd.ExecuteReader()
            Dim geography_units As New List(Of geography_unit)
            While rdr.Read
                Dim geography_unit As New geography_unit
                Try
                    geography_unit.ID = CLng(rdr("Id"))
                    geography_unit.description = rdr("description")
                Catch ex As Exception
                End Try
                geography_units.Add(geography_unit)
            End While
        End Using
        ' rdr.Close() not needed when inside using
        ' conn.Close() not needed when inside using
        Return geography_units
    End Using
End Function

Also worth of note is the point in which I have used the Add method to add the parameter to the collection. The AddWithValue, while convenient, is the cause of a lot of bugs because it defines the type of the parameter looking at the argument received. This could end very badly when you pass dates or decimal numbers directly from a string.

Thanks a lot for your answer @Steve, it really helps me. I still use the valeur as parameter for my MySqlCommand object but when I assign value to this parameter, I just put it in double quote as a string constant as you suggested; the critere field is not used as a parameter at all for my MySqlCommand object, but simply a String constant concatenate with the cmdText query in sqlText variable; then everything is perfect! Thanks a lot! your answer was really helpful. — Cherlan-Miche PHILIPPE, Jan 05 '21 at 17:20
Thanks for your review @Steve, I will remove rdr.Close() and conn.Close() — Cherlan-Miche PHILIPPE, Jan 05 '21 at 17:53
Do we need conn.Open() @Steve? As our connection is inside a using statement! I guess we don't as for conn.Close() we get rid of! — Cherlan-Miche PHILIPPE, Jan 05 '21 at 20:35
No, the using statement doesn't open the connection. You still need to open it. The using statement is for disposing the object. And while disposing the connection and the reader will be closed as well — Steve, Jan 05 '21 at 20:47

score 0 · Answer 2 · answered Jan 05 '21 at 17:07

0

Quite simply, as valeur is a string then your query needs to be as follows

"select ID,description from geography_unit where critere = '" & valeur & "'"

If valeur was numeric then the format should be as follows "select ID,description from geography_unit where critere = " & valeur

Note the difference where single quotes are included within double quotes around the variable when it is a string.

answered Jan 05 '21 at 17:07

Zorlan

11
3

This is absolutely the worst way to do it. [Look at what Sql Injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) could do to your database – Steve Jan 05 '21 at 17:48

How to concatenate single quote in MySQL query with VB.NET parameter?

2 Answers2