Using "Like" Wildcard in Prepared Statement

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 + "%");

Running PreparedStatement with Like clause with wildcard

SQL Server accepts wild characters in the LIKE clause within the single quotation marks, like this ''.

A sample SQL query:

SELECT NAME FROM VERSIONS WHERE NAME LIKE 'Upd%'

The query above will yield you results on SQL Server. Applying the same logic to your Java code will retrieve results from your PreparedStatement as well.

PreparedStatement stmt = conn.prepareStatement("SELECT NAME FROM VERSIONS WHERE NAME LIKE ?");
stmt.setString(1, "Upd%");

I've tested this code on SQL Server 2012 and it works for me. You need to ensure that there are no trailing spaces in the search literal that you pass on to your JDBC code.

Though as a side note, you need to understand that a wildcard % used in the beginning, enforces a full table scan on the table which can deteriorate your query performance. A good article for your future reference.

Hope this helps!

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";
}

Using like statement with $wpdb- prepare showing hashes where wildcard characters are

Don't worry about the hashes, they'll get replaced with % in $wpdb when you execute your query.

These hashes were introduced with WP v4.8.3 as a fix for SQL injection attack.

They're placeholders for the % character. It prevents someone from using something other than %s, %d, and %f. If there's a % other than those approved uses, it'll replace the % with a hash. That hash will get replaced back to % when $wpdb executes the query.

If you want to remove the hashes yourself, you can use remove_placeholder_escape(), like so:

$query['conditions'][] = $wpdb->remove_placeholder_escape($wpdb->prepare($condition, $name));

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);

how to implement like operator in prepared statement?

Use

ps.setString(1, "%" + o.getOrgan() + "%");

Note the absence of quotes.

How to use SQL Wildcard in PreparedStatement with variable

Instead of using the concat operator you could use

like upper('%' || ? || '%')

slightly more readable.

Prepared statement LIKE wildcard

searchcolumn cannot be a bind variable. You can't bind table/column names

$sql = sprintf("SELECT * FROM `book` WHERE teacher_id = ? AND `%s` LIKE ?", $searchColumn);
$searchTerm = "%{$searchTerm}%";

$stmt = $link->stmt_init();
$stmt->prepare($sql);
$stmt->bind_param('is', $teacherid, $searchTerm);

It would also be a good idea to whitelist $searchColumn, validating that it really is a column in your book table before executing this

EDIT

And why bother using fetch_array(MYSQLI_BOTH) when you're only using associative values from the array? Using fetch_assoc() would be better, or you could be even cleverer, and use fetch_object(), and then you wouldn't need to populate your Book object property by property

Consider:

while ($book = $result->fetch_object('Book')) {
    array_push($books, $book);
}

---

Related Topics