1

I am trying to delete data from MySQL using PHP

<?php

if (isset($_POST['delete'])) {
    $queryDelete = "Delete FROM info WHERE userID={$_POST['delete']}";

    if (!($database = mysqli_connect("localhost", "root", ""))) {
        die("Could not connect to database. </body></html>");
    }
    if (!mysqli_select_db($database, "project2")) {
        die("Could not open books database. </body></html>");
    }
    if (!(mysqli_query($database, $queryDelete))) {
        echo "<p>Could not execute query!</p>";
        die(mysqli_error($database) . "</body></html>");
    }
    mysqli_close($database);
}

this is my delete.php using it on this page

<!DOCTYPE html>
<html>
<head>
    <link rel="stylesheet" href="Style.css">
</head>
<header>
    <div>
        <p id="page">Users List</p>
        <img id="title_pic" src="images/title_pic.jpg" alt="#">
    </div>
</header>
<body>
    <?php include 'bar.php' ?>
    <?php include 'delete.php' ?>

    <br><br><br><br>
    <h1 style="color:yellow;"> List of all Users: </h1>
    <br>

    <?php

    $query = "SELECT userID, fName, email  FROM info";

    if (!($database = mysqli_connect("localhost", "root", ""))) {
        die("Could not connect to database. </body></html>");
    }
    if (!mysqli_select_db($database, "project2")) {
        die("Could not open project database. </body></html>");
    }
    if (!($result = mysqli_query($database, $query))) {
        echo "<p>Could not execute query!</p>";
        die(mysqli_error($database) . "</body></html>");
    }
    mysqli_close($database);

    while ($row = mysqli_fetch_row($result)) {
        foreach ($row as $value) {
            echo "<span style='color:white;'> $value </span>";
        }
        echo ' <form action = "delete.php" method = "POST">';
        echo '<input type="submit"  name= "delete" value="delete" class="btn">';
        echo '</form>';
        echo "<br>";
    }

    ?>
</html>

It's redirecting me to delete.php page but when I go back to the second one (Displayuser.php) all info are there and nothing is deleted I used the same technique to add info but I am having trouble to delete them from the table.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Anthony
  • 17
  • 4
  • 2
    **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman May 26 '21 at 19:12
  • 1
    `name= "delete" value="delete"` which means that your code will generate the query `userID=delete`, which is invalid for a few reasons (sending the string `delete` instead of the id, strings need to be quoted, you should use prepared statements instead of injecting user data into your queries (not invalid, but very insecure)) – M. Eriksson May 26 '21 at 19:16
  • Also, why do you have a delete form for every single field in your database table? – Dharman May 26 '21 at 19:19
  • okay I am using POST now and now I am having "Could not execute query!". – Anthony May 26 '21 at 19:19
  • I am new to php and I have my first project for university – Anthony May 26 '21 at 19:20
  • You are getting this because: 1. You are vulnerable to SQL injection. 2. You have not enabled mysqli error reporting. See [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) 3. Your SQL does not make sense as pointed by Magnus. Your userId is unlikely to ever be `delete` – Dharman May 26 '21 at 19:21
  • 1
    Then I'll recommend you to read up on prepared statements, as others have mentioned so you learn the correct way from the start. I would also suggest using PDO instead of MySQLi. It has an easier API and comes with other benefits as well. – M. Eriksson May 26 '21 at 19:21
  • If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo & https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection – Dharman May 26 '21 at 19:21
  • we have to use our dr. method he said we need to paste his code and just change name values etc.. – Anthony May 26 '21 at 19:23
  • 1
    If your school is teaching you to write code like this then you should RUN away. Don't ever learn to write code like this. You are only learning bad practices and how to write buggy code. Please listen to our advise and learn PHP properly – Dharman May 26 '21 at 19:24
  • Yes I will thank you for pointing this out. For now I need to stick to my dr. method or I will fail my class. I will learn the correct way by myself. Thank you guys. – Anthony May 26 '21 at 19:26
  • I have told you already what you need to do to solve the problem. 1. Use prepared statements. 2. Change the value in the form to something meaningful like the user ID – Dharman May 26 '21 at 19:27
  • You can't fail class for writing proper code, but if you showed me this code, I would fail you immediately. – Dharman May 26 '21 at 19:28
  • 1
    I would happily flunk my class if it required me to write code like this. But I don't see how they could do that if you follow our advice. Just say that you noticed it was insecure and that you fixed that. If that doesn't work, talk to the principle and get him fired immediately. We're saying this for your and your class mates sake. – M. Eriksson May 26 '21 at 19:29
  • Yeah using the dr. method is a must we can't use any other. I found a lot of easier ways to write it but he won't accept it. – Anthony May 26 '21 at 19:32

1 Answers1

1

Here is how your code should look like. First in your form, provide the ID of the user you want to delete. Make sure to enable mysqli error reporting and select the right database when connecting.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$database = mysqli_connect("localhost", "root", "", 'project2');
$database->set_charset('utf8mb4'); // always set the charset

$users = $database->query("SELECT userID, fName, email  FROM info");

?>
<!DOCTYPE html>
<html>
<head>
    <link rel="stylesheet" href="Style.css">
</head>
<body>
    <header>
        <div>
            <p id="page">Users List</p>
            <img id="title_pic" src="images/title_pic.jpg" alt="#">
        </div>
    </header>
    <?php include 'bar.php' ?>
    <?php include 'delete.php' ?>
    <br><br><br><br>
    <h1 style="color:yellow;"> List of all Users: </h1>
    <br>
    <?php

    foreach ($users as $user) {
        foreach ($user as $value) {
            echo "<span style='color:white;'>'.htmlspecialchars($value).'</span>";
        }
        echo ' <form action = "delete.php" method = "POST">';
        echo '<button type="submit" name="delete" value="'.htmlspecialchars($user['userID']).'" class="btn">Delete</button>';
        echo '</form>';
        echo "<br>";
    }

    ?>
</html>

Then in your delete.php, read the POST value and delete the row with that ID using prepared statement.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$database = mysqli_connect("localhost", "root", "", 'project2');
$database->set_charset('utf8mb4'); // always set the charset

if (isset($_POST['delete'])) {
    $stmt = $database->prepare("DELETE FROM info WHERE userID=?");
    $stmt->bind_param('s', $_POST['delete']);
    $stmt->execute();
}
Dharman
  • 30,962
  • 25
  • 85
  • 135