php prepared statement (INSERT not working)

Question

I'm trying to use prepared stmt to avoid sql injection and it is not working. I've surrounded my code with some alert statements to know where the problem is. It is at the bind_param statement. Everything after never executes. Hope I can find some help!

Been searching for an answer for the last 3 hours nothing have solved it so far. P.S the table has 6 columns. one id that auto increments, and the rest is as u can see form the code

$db = new PDO("mysql:port=3302;dbname=thedoctors", "root", ""); 
$Data = $_POST["post"];
$postData = $db->quote($_POST["post"]); //user's post
$user = $db->quote($_SESSION["user"]);    //user's email
$stmt = $db->prepare("INSERT INTO post (body,timee,likes,comments,userem) VALUES (?, NOW(), ?, ?, ?);");

// set parameters and execute

$first = ".$postData.";
$like = 0;
$comment = 0;

$stmt->bind_param("siis", $first, $like, $comment, $user);
echo "<script>alert('hello');</script>";
// $result = $stmt->get_result();
// header("location:activity.php");
if (strlen($Data) > 3000 || strlen($Data) < 1) {
    echo "<script>alert('Make sure your post is of correct length!');</script>";
} else {
    $stmt->execute();
    echo "<script>alert('Post uploaded! You can check it in your profile!');</script>";
}

Have you got the [exception mode](https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments/22662582#22662582) enabled? — Dharman, Apr 28 '19 at 10:00
$db = new PDO("mysql:port=3302;dbname=thedoctors", "root", ""); — Mohamad Hammoud, Apr 28 '19 at 10:24
the port thing is related to my xampp setup. Works fine on everything everytime. P.S everything was working on normal db->query but I want to make it prepared — Mohamad Hammoud, Apr 28 '19 at 10:25
Could you edit your post and include $db declaration in question? — Łukasz Gawron, Apr 28 '19 at 11:02

Łukasz Gawron · Accepted Answer · 2019-04-28T21:15:50.483

1

As you are using PDO not mysqli interface you need to use named parameters in SQL query like exlained here: https://www.php.net/manual/en/pdo.prepare.php

Currently you are relying on bind_param which is a method on mysqli interface.

Based on PDO docs:

<?php
/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();
$sth->execute(array(':calories' => 175, ':colour' => 'yellow'));
$yellow = $sth->fetchAll();
?>

Additionally I would recommend to use bindValue to make it more explicit that you are setting some values to placeholders like this:

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?>

edited Apr 28 '19 at 21:15

answered Apr 28 '19 at 10:27

Łukasz Gawron

897
10
20

OP is using MySQLi it seems. Why do you recommend to switch to PDO? That looks like a recommendation which should be in the comments, if at all. This is not the answer. – Dharman Apr 28 '19 at 10:41
In comment to question I've asked about $db declaration and author wrote he is using $db = new PDO("mysql:port=3302;dbname=thedoctors", "root", ""); So I recommend to use PDO methods. – Łukasz Gawron Apr 28 '19 at 11:00
:O `bind_param` param in the question threw me off. My guess then is that OP mixed the two extensions with each other. – Dharman Apr 28 '19 at 11:07
@MohamadHammoud could you mark it as an answer and edit question as I asked on comments? Information about declaration of $db is crucial to answer such questions even faster than we did here. – Łukasz Gawron Apr 28 '19 at 11:17
Marked now! One more question. Is this considered safe from sql injections? I mean is it as if I insert '?' sign instead of :calories or :colour as the code above? – Mohamad Hammoud Apr 28 '19 at 11:22
1

Both ways of specifying parameters in sql :varName or ? are equally safe from point of view of SQL injection. I just found it more readable to explicitly use varName in sql and than use bindValue for that placeholder. Additional benefit you don't care about order of variables in sql, so potentially less error-prone. – Łukasz Gawron Apr 28 '19 at 11:41

php prepared statement (INSERT not working)

1 Answers1