How to change from simple mysqli query to prepared statement?

Question

I did 3 queries (SELECT, INSERT, UPDATE) it works but at the current state looks ugly and not safe.

Is there any way to make these SELECT, INSERT, UPDATE queries more readable and safer than this with the prepared statement?

    $email = $_SESSION['email'];
    $query = "SELECT username FROM users WHERE email='$email'";
    $result = mysqli_query($connect, $query);
    $row = mysqli_fetch_assoc($result);
    $username = $row['username'];

    if(!empty($_POST["comment"])){
    $id = $_GET['id'];
    $sql = "INSERT INTO user_comments (parent_id, comment, username, custom_id) VALUES ('".$_POST["commentID"]."', '".$_POST["comment"]."', '$username', '$id')";
    mysqli_query($connect, $sql) or die("ERROR: ". mysqli_error($connect));

    /// I need this update query to make every inserted comment's ID +1 or can I do this more simple?
    $sql1 = "UPDATE user_comments SET id = id +1 WHERE custom_id = '$id'";
    mysqli_query($connect, $sql1) or die("ERROR: ". mysqli_error($connect));

Have you looked at the documentation for [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php)? — aynber, Jul 11 '19 at 19:07
Yes, I did. I can do simple SELECT or UPDATE or INSERT query with prepared statement but I don't have idea how to do these multiple queries in a simple way. Or I must do every query seperately just like now? — NaderG, Jul 11 '19 at 19:10
Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Dharman, Jul 11 '19 at 22:42

score -1 · Accepted Answer · answered Jul 11 '19 at 19:39

Give this a try. You can use $ex->insert_id to get the last entered ID. This may come in handy when mass inserting into a DB. I generally use PDO as I find the code looks cleaner but it's all preference I suppose. Keep in mind for the ->bind_param line that "isii" is referring to the type(s) of data which you are entering. So, in this case, its Integer, String, Integer, Integer (I may have got this wrong).

$email    = $_SESSION['email'];
$query    = "SELECT username FROM users WHERE email='$email'";
$result   = mysqli_query($connect, $query);
$row      = mysqli_fetch_assoc($result);
$username = $row['username'];

if(!empty($_POST["comment"])){
$id        = $_GET['id'];
$commentID = $_POST["commentID"];
$comment   = $_POST["comment"];

$sql = "INSERT INTO user_comments (parent_id, comment, username, custom_id) VALUES (?, ?, ?, ?)";
$ex  = $connect->prepare($sql);
$ex->bind_param("isii", $commentID, $comment, $username, $id);

if($ex->execute()){
    // query success
    // I need this update query to make every inserted comment's ID +1 or can I do this more simple?

    $lastInsertID = $ex->insert_id;

    $sql1 = "UPDATE user_comments SET id = id + 1 WHERE custom_id = ?";
    $ex1  = $connect->prepare($sql1);
    $ex1->bind_param("i",$lastInsertID);
    if($ex1->execute()){
        // query success
    }else{
        // query failed
        error_log($connect->error);
    }
}else{
    //query failed
    error_log($connect->error);
}

Yes you got the isii part wrong, but I changed to issi and everything works fine, thank you so much! So now these queries are safe from sql injections? — NaderG, Jul 11 '19 at 20:48
@NaderG It then comes down to your database driver and how well it escapes (potentially) harmful user input. Currently, these mechanisms are all pretty good at their jobs. — , Jul 12 '19 at 12:39

How to change from simple mysqli query to prepared statement?

1 Answers1