0

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?

Improve this question
9
  • Do not post images of code. We can't copy paste them to try the code shown. Commented Jan 5, 2021 at 16:07
  • Thanks for your feedback @Steve, I just edited my question and put the code snippet! Commented Jan 5, 2021 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}'";) Commented Jan 5, 2021 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 Commented Jan 5, 2021 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? Commented Jan 5, 2021 at 16:11

2 Answers 2

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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.
I will test the Add method!
Thanks for your review @Steve, I will remove rdr.Close() and conn.Close()
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!
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
|
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.

Improve this answer

1 Comment

This is absolutely the worst way to do it. Look at what Sql Injection could do to your database

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.