How to write MySQL query in prepare method when search?

Question

how can I secure my search query with the prepare method?

DB Connection

// connect to database
$con = new mysqli("localhost", "root", "", "chat");
// Check connection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

MySQL Query

if (isset($_POST['query'])) {
        
        $search_string = trim($_POST['query']);
        $query = mysqli_real_escape_string($con, $search_string);
        
        //MySQL order by best match
        $sql = "SELECT reply FROM question_answer WHERE question 
                LIKE '%$query%'
                ORDER BY CASE 
                      WHEN question = '$query' THEN 0  
                      WHEN question LIKE '$query%' THEN 1  
                      WHEN question LIKE '%$query%' THEN 2  
                      WHEN question LIKE '%$query' THEN 3  
                      ELSE 4
                END, question ASC";

        $res = mysqli_query($con, $sql);
        
        if (mysqli_num_rows($res) > 0) {
            $row = mysqli_fetch_assoc($res);
            $html = $row['reply'];
        } else {
            $html = "Sorry not be able to understand you";
            $added_on = date('Y-m-d h:i:s');
            mysqli_query($con, "INSERT INTO unknown_question(question,added_on,type) VALUES('$query','$added_on','user')");
        }
        echo $html;
        die();
    }

https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — id'7238, Jan 14 '21 at 05:42
`WHEN question LIKE '%$query' THEN 3` will fire never because the value matched this pattern matches previous pattern too. — Akina, Jan 14 '21 at 05:45

score 3 · Accepted Answer · edited Jan 14 '21 at 10:19

An example of using prepared queries with mysqli in your code could be like this:

if (isset($_POST['query'])) {
    $sql = "SELECT reply FROM question_answer WHERE question LIKE ?
        ORDER BY CASE 
            WHEN question = ? THEN 0  
            WHEN question LIKE ? THEN 1  
            WHEN question LIKE ? THEN 3
            ELSE 2
        END, question ASC";

    $query = trim($_POST['query']);
    $params = [
        '%' . $query . '%',
        $query,
        $query . '%',
        '%' . $query
    ];
    $stmt = $con->prepare($sql);
    $stmt->bind_param(str_repeat('s', count($params)), ...$params); 
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();

    if (!empty($row)) {
        $html = $row['reply'];
    } else {
        $html = "Sorry not be able to understand you";
        $added_on = date('Y-m-d h:i:s');
        $sql = "INSERT INTO unknown_question(question,added_on,type) VALUES(?,?,'user')";
        $stmt = $con->prepare($sql);
        $stmt->bind_param('ss', $query, $added_on); 
        $stmt->execute();
    }
    echo $html;
}

How to write MySQL query in prepare method when search?

1 Answers1