MySQLi prepare statement good practice

Question

I want to see if my practice is good enough to protect from sql injection.

$mysqli = new mysqli($host,$username,$password,$database);
$query = $mysqli->prepare('SELECT * FROM users WHERE id = ? AND check = ?');
$query->bind_param('ii', $_GET['id'], $_POST['check']);
$query->execute();

$result = $mysqli->query($query);
while ($row = $result->fetch_assoc()) {
...
}

I've seen that in some examples have this line before the while:

$result = $query->get_result();

And others that use trim(), intval() etc in $_GET/$_POST for safety. Which is the best practice and safest way to avoid sql injections?

Side note: It should be `$_GET/$_POST` with the `_`. – Albzi Oct 07 '16 at 10:21 — Albzi, Oct 07 '16 at 10:21

score 0 · Accepted Answer · answered Oct 07 '16 at 10:54

The best MySQLi prepared statement practice is apparently a PDO prepared statement

the code you posted above just makes no sense. To make it work, indeed you have to use get_result() function, which - alas! - is not guaranteed to be available:

$query = $mysqli->prepare('SELECT * FROM users WHERE id = ? AND check = ?');
$query->bind_param('ii', $_GET['id'], $_POST['check']);
$query->execute();
$result = $query->get_result();
while ($row = $result->fetch_assoc()) {
...
}

while with PDO you need two times less code which is always guaranteed to work

$query = $pdo->prepare('SELECT * FROM users WHERE id = ? AND check = ?');
$query->execute([$_GET['id'], $_POST['check']]);
while ($row = $query->fetch()) {
...
}

not to mention other PDO's wonderful features

And others that use trim(), intval() etc

these things are just irrelevant it SQL, you may use them for whatever else reason.

MySQLi prepare statement good practice

1 Answers1