Home > DeveloperSection > Forums > Parameterized string and wildcards in MySQL
sandy taylor
sandy taylor

Total Post:46

Points:324
Posted on    December-26-2014 10:20 PM

 .NET .NET  MYSQL  String 
Ratings:


 1 Reply(s)
 723  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 ?
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?


Maria Susan

Total Post:29

Points:107
Posted on    December-26-2014 10:37 PM

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


Don't want to miss updates? Please click the below button!

Follow MindStick