Array from Form Input - Select Statement MySQLi Parameterisation

Question

Turning phrases entered in a Form input into an array to pass into a MySQL select statement where clause using MySQLi. The php code I have achieves this, but I can't workout how to parameterise the query to prevent against sql injection attacks. I've had a look at a few questions on this site, but I'm struggling to relate it to my code.

if(!empty($_POST['Message']))
{
      $searchStr = get_post($con,'Message');
      $aKeyword = explode(" ", $searchStr);

      $query ="SELECT m.ID, m.MessageText FROM MessageMain m LEFT OUTER JOIN Likes l on m.ID = l.PostID WHERE MessageText LIKE '%" . $aKeyword[0] . "%'";

     for($i = 1; $i < count($aKeyword); $i++) {
        if(!empty($aKeyword[$i])) {
            $query .= " OR MessageText like '%" . $aKeyword[$i] . "%'";
        }
      }

      $query .= " GROUP BY m.ID, m.MessageText ORDER BY count(m.id) desc";

       $result = $con->query($query);
       $rowcount=mysqli_num_rows($result);

Dharman · Accepted Answer · 2019-11-10 20:20:42Z

If you would like to build the WHERE clause dynamically based on the number of keywords to match you could do it like this:

if (!empty($_POST['Message'])) {
    $searchStr = get_post($con, 'Message');
    $aKeyword = explode(" ", $searchStr);

    $whereClauseArr = [];
    foreach ($aKeyword as $keyword) {
        if ($keyword) {
            $whereClauseArr[] = "MessageText LIKE ?";
            $whereValues[] = '%'.$keyword.'%';
        }
    }

    $stmt = $con->prepare(
        'SELECT m.ID, m.MessageText 
        FROM MessageMain m 
        LEFT OUTER JOIN Likes l on m.ID = l.PostID 
        WHERE '.implode(' OR ', $whereClauseArr).'
        GROUP BY m.ID, m.MessageText ORDER BY count(m.id) desc'
    );

    $stmt->bind_param(str_repeat('s', count($whereValues)), ...$whereValues);
    $stmt->execute();
    $result = $stmt->get_result();
}

Although in your case, checking the same column against multiple values would probably be better done with regular expression. This would make your query simpler and potentially also faster depending on the number of keywords you have.

if (!empty($_POST['Message'])) {
    $searchStr = get_post($con, 'Message');
    $aKeyword = explode(" ", $searchStr);

    $aKeyword = array_filter($aKeyword); // Remove empty values

    $stmt = $con->prepare(
        'SELECT m.ID, m.MessageText 
        FROM MessageMain m 
        LEFT OUTER JOIN Likes l on m.ID = l.PostID 
        WHERE MessageText REGEXP ?
        GROUP BY m.ID, m.MessageText ORDER BY count(m.id) desc'
    );

    $regexString = implode('|', $aKeyword);
    $stmt->bind_param('s', $regexString);
    $stmt->execute();
    $result = $stmt->get_result();
}

Thank you for the above. You're code worked without amendment :) and I've learned something really cool and useful. Thanks again. And I used the REGEXP answer, just to say!

Collectives™ on Stack Overflow

Array from Form Input - Select Statement MySQLi Parameterisation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related