3

im learning MySQLi in order to make my site not vulnerable to SQL injections (wich is now) but i get confuse when i was trying to "translate" my old querys to MySQLi statements, so i hope you can help me with some examples so i can get it. Thanks a lot!.

Updating my site counter

$sql = "UPDATE post SET counter = counter+1 WHERE id=".$tget;

Sorting my comments

$info=mysql_query("SELECT * FROM `comments` WHERE idpost=" . $tget . " AND active=1 ORDER BY datetime DESC");

Saving the comment

$sql = "INSERT INTO `comments` (`id`, `idpost`, `comment`, `datetime`, `author`, `active`) VALUES (NULL, '" . addslashes($_POST['idcomment']) . "', '" . addslashes($_POST['comment']) . "', NOW(), '" . addslashes($_POST['name']) . "', '1');";

If you can explain me how to go from here to MySQLi i can finish with the others querys.

And by way, if you (expert) consider that there is other way to protect me from sql injections better than MySQLi, please tell me about it.

DomingoSL
  • 14,920
  • 24
  • 99
  • 173
  • I'm not an expert, but there is another way, it's called PDO: http://www.php.net/manual/en/book.pdo.php – Ignacio Jan 28 '11 at 18:27

2 Answers2

4
$conn = new mysqli(…);
$sql = "UPDATE post SET counter = counter+ 1 WHERE id= ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $tget);
$stmt->execute();

In the first argument to bind_param, use a string of i, s, d and b to set the parameter types:

$stmt = $conn->prepare("INSERT INTO mytable (int_column, string_column, double_column, blob_column, another_int_column VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param("isdbi", $int_val, $string_val, $double_val, $blob_val, $another_int_val);
$stmt->execute();
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    Shouldn't `$stmt->execute;` be `$stmt->execute();`? And shouldn't you have `$stmt->close();` after each query? – gen_Eric Jan 28 '11 at 18:24
  • 1
    @Rocket: sure, updated. There should be also `$stmt->fetch()` for the `SELECT` queries, but I omitted it for the sake of brevity, since the @op wanted to know how to bind. – Quassnoi Jan 28 '11 at 18:41
0

my experiencia tell with you use stored procedure with bind_param option. you need read this post for more detail.

Use one bind_param() with variable number of input vars

Community
  • 1
  • 1
SoftwareARM
  • 1,115
  • 10
  • 5