Parameterized string and wildcards in MySQL

Total Post:46

.NET  MYSQL  String 
 1507  View(s)
Rate this:
I'm trying to build a MySQL query for a database search operation, where a user can specify a text string to match against a particular column. I figured that using the LIKE operator and surrounding the user input with % signs, to act as wildcards, would be best practice. I want the wildcards to be there on both the start and end so the user does not have to enter the whole string. Furthermore, I'd like to parameterize the query to avoid injection and whatnot. This leaves me with a query that looks something like this:

SELECT * FROM `sometable`
WHERE `name` LIKE ?
LIMIT 1,10
(Note that the name column is a VARCHAR(50) with collation utf8_general_ci.)

The parameter from the WHERE clause is added like so:

Using cmd As New OdbcCommand()
    cmd.Parameters.AddWithValue("name", "%" & strUserInput & "%")
However, what I now ended up with appears to be MySQL actually matching the name column against the concatenated string, treating the %'s as literals and not as wildcards as I had intended. I also tried LIKE CONCAT('%', ?, '%'), but this doesn't work either.

How would I glue a wildcard character to the start and end of a parameterized string? Or is there a much better way of doing this?
  1. Post:29

    Re: Parameterized string and wildcards in MySQL

    Your SqlParameter name is @name not name : cmd.Parameters.AddWithValue("@name", string.Format("%{0}%", strUserInput);

    And your sql should be:

    SELECT * FROM `sometable`
    WHERE `name` LIKE @name
    ORDER BY `id` ASC
    LIMIT 1,10

      Modified On Apr-07-2018 05:53:36 AM