There is a page with a search form, the text of which is transmitted through the form.

I receive:

$where = ""; if(isset($_GET['search'])) $where = "WHERE `action` LIKE '%" . $_GET['search'] . "%'";

Next I connect to the database and:

 if ($result = $mysqli->prepare('SELECT * FROM `logs` ? ORDER BY `date` DESC LIMIT ?, 50')) { $result->bind_param("sd", $where, $limit); $limit = ($page - 1) * 50; $result->execute(); $result = $result->get_result(); while($row = $result->fetch_assoc()) { printf("<tr><td class='tg-3we0'>%s</td>", $row['date']); printf("<td class='tg-3we0'><font color='%s'>%s</font></td>", GetColor($row['class']), $row['action']); } $result->close(); }

And nothing. What's my mistake?

  • you cannot bind key phrases a la WHERE , FROM , etc. ...... only table fields ........ for example $where = "WHERE action LIKE '%?%'"; ...... SELECT * FROM logs '.$where.' ORDER BY SELECT * FROM logs '.$where.' ORDER BY .... $result->bind_param('s',$_GET['search']); .......... and the whole expression is impossible to bind - Alexey Shimansky
  • And with LIKE this is how WHERE action LIKE ? should be WHERE action LIKE ? ...... $result->bind_param('s','%'.$_GET['search'].'%'); - Alexey Shimansky

1 answer 1

Binding is not an end in itself. It is only a means. And the goal is to ensure that the request sent to the database is always registered by the programmer entirely in the script code . For this purpose, and serves as a binding, substituting question marks for variables.

Therefore, to solve this particular problem, we combine the binding with the request elements in the script.

 $where = ''; if(isset($_GET['search'])) $where = "WHERE `action` LIKE ?"; $serach = "%" . $_GET['search'] . "%"; } $sql = "SELECT * FROM `logs` $where ORDER BY `date` DESC LIMIT ?, 50"; $stmt = $mysqli->prepare($sql); if ($where) { $result->bind_param("si", $where, $limit); } else { $result->bind_param("i", $limit); } $limit = ($page - 1) * 50; $stmt->execute(); $result = $result->get_result();

Plus, this question clearly demonstrates why it is necessary to use a wrapper, and not to shuffle on mysqli bare. For example, using PDO, the code will be twice as short

 $params['limit'] = ($page - 1) * 50; $where = ''; if(isset($_GET['search'])) $where = "WHERE `action` LIKE :search"; $params['search'] = "%" . $_GET['search'] . "%"; } $sql = "SELECT * FROM `logs` $where ORDER BY `date` DESC LIMIT :limit, 50"; $stmt = $pdo->prepare($sql); $stmt->execute($params);

But the main thing is that in both cases the request is 100% hard-coded in the script code - that is, the injection simply has nowhere to cram - all data in the database is transmitted separately from the request *.

  • @ Alexey Shimansky thanks. - Ipatiev