How to go from php MySQL sql injection vulnerable query to a MySQLi not vulnerable query

Question

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.

I'm not an expert, but there is another way, it's called PDO: php.net/manual/en/book.pdo.php — Ignacio
– Ignacio, Commented Jan 28, 2011 at 18:27

Quassnoi · Accepted Answer · 2011-01-28 18:42:01Z

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();

edited Jan 28, 2011 at 18:42

answered Jan 28, 2011 at 18:16

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gen_Eric Over a year ago

Shouldn't $stmt->execute; be $stmt->execute();? And shouldn't you have $stmt->close(); after each query?

Quassnoi Over a year ago

@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.

Community · Accepted Answer · 2017-05-23 11:58:05Z

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

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Jan 28, 2011 at 18:26

SoftwareARM

1,10510 silver badges5 bronze badges

Collectives™ on Stack Overflow

How to go from php MySQL sql injection vulnerable query to a MySQLi not vulnerable query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related