How do I use the like operator in SQL with php user input?

Question

$search_query = mysqli_real_escape_string($conn, $_POST['this']);
$sql = "SELECT this FROM that WHERE this LIKE ' '%' + " .$search_query. " + '%' '";

This is what I have so far, is there something wrong with this syntax?

You have misplaced single quotes after `LIKE` and at the end of your statement. Should be: `$sql = "SELECT this FROM that WHERE this LIKE '%' + '" .$search_query. "' + '%' "`. — GMB, Dec 18 '19 at 23:00
But bottom line: **[use prepared statements at all time](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)**! — GMB, Dec 18 '19 at 23:02
@GMB Maybe im not doing it correctly then, because I can't echo the results. If I do $result = mysqli_query($conn, $sql); $row = mysqli_fetch_assoc($result); echo mb_substr($row["this"]); — reallarz, Dec 18 '19 at 23:04
Echoing @GMB **please please please** use prepared statements! Writing SQL commands like you're doing is **extremely unsafe**. — DHerls, Dec 18 '19 at 23:10
MySQL doesn't use `+` for string concatenation, it uses the `CONCAT()` function. — Barmar, Dec 18 '19 at 23:55

score 2 · Accepted Answer · answered Dec 18 '19 at 23:38

If you rewrite your query using prepared statements you won't have this type of issue. For example:

$sql = 'SELECT this FROM that WHERE this LIKE ?';
$stmt = $conn->prepare($sql);
$search_query = '%' . $_POST['this'] . '%';
$stmt->bind_param('s', $search_query);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['this'];
}

Note get_result is only available with the mysqlnd driver, if you don't have that, replace the last 4 lines with

$stmt->bind_result($ths);
while ($stmt->fetch()) {
    echo $ths;
}

How do I use the like operator in SQL with php user input?

1 Answers1