Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

The % signs need to go in the variable that you assign to the parameter, instead of in the query.

I don't know if you're using mysqli or PDO, but with PDO it would be something like:

$st = $db->prepare("SELECT * FROM table WHERE name LIKE ?");
$st->execute(array('%'.$test_string.'%'));
For mysqli user the following.
$test_string = '%' . $test_string . '%';
$st->bind_param('s', $test_string);
$st->execute();
                Isn't it a kind of SQL injection? It is supposed to be prevented by the prepared statement am I wrong?
– JacopoStanchi
                Apr 16, 2018 at 20:41
SELECT * FROM table WHERE name LIKE '%' || :param || '%'
# mysql
SELECT * from table WHERE name LIKE CONCAT('%', :param, '%')
I'm not familar with other databases, but they probably have an equivalent function/operator.
                He's probably coming from an Oracle background, that's the Oracle concatenation operator, but in MySQL it's the OR operator. In MySQL you'd have to do CONCAT('%', ?, '%')
– Chad Birch
                Mar 19, 2009 at 17:47
                NEVER trust any value you put in your sql, even if you really think it is. This is the most obvious solution, because no preparing of the value outside the SQL, so principle of using parameters to prevent sql injection remains. You MUST trust your SQL, not your values. Thanks for the solution!
– Terradon
                Dec 31, 2019 at 9:01
in PHP using MYSQLI you need to define a new parameter which will be declared as:
$stmt = mysqli_prepare($con,"SELECT * FROM table WHERE name LIKE ?");
$newParameter='%'.$query.'%';
mysqli_stmt_bind_param($stmt, "s", $newParameter);
mysqli_stmt_execute($stmt);
this works for me..
    $connection = new mysqli($dbServer, $dbUserName, $dbPassword, $dbName);
    if($connection->connect_errno)
        exit("Database Connection Failed. Reason: ".$connection->connect_error);
        $tempFirstName = "reuel";
    $sql = "SELECT first_name, last_name, pen_name FROM authors WHERE first_name LIKE CONCAT(CONCAT('%',?),'%')";
    //echo $sql;
    $stateObj = $connection->prepare($sql);
    $stateObj->bind_param("s",$tempFirstName);
    $stateObj->execute();
    $stateObj->bind_result($first,$last,$pen);
    $stateObj->store_result();
    if($stateObj->num_rows > 0) {
        while($stateObj->fetch()){
            echo "$first, $last \"$pen\"";
            echo '<br>';
    $stateObj->close();
    $connection->close();
I will just adapt Chad Birch's answer for people like me who are used to utilize bindValue(...) for PDO:
$st = $db->prepare("SELECT * FROM table WHERE name LIKE :name");
$st->bindValue(':name','%'.$name.'%',PDO::PARAM_STR);
$st->execute();
The sprintf can do it. Remember to put another % in front of the original % to escape it.
$ret = sprintf("SELECT * FROM table WHERE name LIKE %%%s%%", $name);
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.