PARAMETERIZED STRING AND WILDCARDS IN MYSQL

sandy taylor

Total Post:46

Points:324
Posted by  sandy taylor
.NET  MYSQL  String 
 1106  View(s)
Ratings:
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 ?
ORDER BY `id` ASC
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. Maria Susan

    Post:29

    Points:107
    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!