Using Wildcards in Prepared Statement - MySQLi

Using wildcards in prepared statement - MySQLi

You have to pass parameters to bind_param() by reference, which means you have to pass a single variable (not a concatenated string). There's no reason you can't construct such a variable specifically to pass in, though:

$className = '%' . $this->className . '%';
$query->bind_param('s', $className);

php mysqli prepared statement using wildcards

I could not get it to work with a prepared statement. I moved to to a mysqli->query() and escaped the user string. Carlos, thanks for the help in fixing my query.

Implement LIKE in PHP prepared Statements with % wildcards

I want to thank everyone for their help with this. ArtisticPhoenix got me headed in the right direction.

This post hit the mark of what I was looking for to bring it all together:

Adding a wildcard character to a string in PHP

Here's the "slightly" updated code:

    $search = $_POST['search'].'%';

    //echo($search);

    $stmt = $link->prepare("SELECT lname, fname FROM planner WHERE lname LIKE ?");
    $stmt->bind_param('s', $search);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
    echo "<table><tr><th>Last Name</th><th>First Name</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["lname"]."</td><td>".$row["fname"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

Mysqli Prepared Statement with % wildcard

You need to bind the complete value, not just a portion of it. This means doing:

$where = "First_Name LIKE ?"

And then binding:

$vals = array('%Mike%');

Wildcard in prepared MySQLi returning bad values

Please note that the details on this answer are unlikely to resolve the question on their own. With my own further testing I established that appending % or _ wildcards to strings before they are bound does not effect the way they are bound to the query.

What you are currently trying to do is Concatenate the data ($whatName) with an SQL instruction % each side of it, and the Prepared Statement parser is simply not having this, as it defeats the security purposes of prepard statements.

So, the solution is that you need to manually concatenate the variable into the LIKE statement only at the point of insertion, and not before, as you are doing at the moment.

The example below will do as you intend:

$selectStr = WHERE `Name` LIKE CONCAT('%',?,'%') ORDER BY `Name`
$whatToBind = $whatName;
$stmt = $mysqli->prepare($selectStr);
$stmt->bind_param('s', $whatToBind);
$stmt->execute();

Note that the data and the query never mix, until the prepared statement is executed.

---

A note on UTF-8 and character sets on MySQL:

Do not use the utf8_ MySQL character sets as they are an incomplete subset of true UTF-8 and so can still raise serious issues with character recognition. Instead you want to be using utf8mb4_ character sets/collations/etc.

Character encoding may be a related issue to your problem, and I highly recommend reading up on the excellent answers given on this Stack Overflow Question as well as using the PHP mb_ multibyte string functions.

---

Some further things to check:

• Check the MySQL / PHP interaction character set is set correctly, typically with: $mysqli->set_charset("utf8mb4"); right after database connection is established.

• Can you show any output from $mysqli->error ?

• Can you show us your whole SQL query?

• Can you show the Collation / MySQL structure of the Names column?

• Can you show what the value of $whatName is right before binding? (while your question makes sense, having a specific example of a specific situation and a specific result as well as an intended result is very useful for us to debug.

• Silly thing but ensure you don't have a LIMIT on your results! :-D

Using like wildcard in prepared statement

You need to set it in the value itself, not in the prepared statement SQL string.

So, this should do for a prefix-match:

notes = notes
    .replace("!", "!!")
    .replace("%", "!%")
    .replace("_", "!_")
    .replace("[", "![");
PreparedStatement pstmt = con.prepareStatement(
        "SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");
pstmt.setString(1, notes + "%");

or a suffix-match:

pstmt.setString(1, "%" + notes);

or a global match:

pstmt.setString(1, "%" + notes + "%");

Why Mysqli prepared statement does'nt escape wildcards (% and _) and Backslash ( \ )?

Prepared statements do escape the \ properly. Otherwise they wouldn’t create properly formatted string literals, which is the main purpose of prepared statements.

Besides that, % and _ are only special characters to the LIKE comparison but not to strings in general. So there is no need to escape them in general. If you need to escape them for a LIKE comparison, do it explicitly:

$what = addcslashes($what, '%_');

Besides that, LIKE does also support the use of an escape character other than \:

mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1

---

Related Topics