Cannot delete MySQL record using PHP

Question

Sorry for the badly phrased title, I don't know what else to call this situation.

Here's my dilemma, for fun I'm making a little doodad so people can submit the name, platform, and location of Battlefield 3 servers so they no longer need to rely on word of mouth.

This older code works fine, it deletes records and does it's job.

<?php

mysql_connect('localhost', 'root', 'pass');
mysql_select_db('database');

$server_secret = mysql_real_escape_string($_POST['secret_initial']);
$server_confirm = mysql_real_escape_string($_POST['secret_verify']);

if ($server_secret == $server_confirm) {
    echo "Both matched.";
    if ((strlen($server_secret) < 6) and (strlen($server_verify) < 6)) {
        echo "Password too short.";
    } else {
        echo "Password is of proper length.";
        $sql = "DELETE FROM `servers` WHERE secret='" . sha1($server_secret) . "'";
        mysql_query($sql) or die("Couldn't delete record. " . mysql_error());
    }
} else {
    echo "Mismatch.";
}

?>

However, if I use this:

<?php

mysql_connect('localhost', 'root', 'pass');
mysql_select_db('database');

// Create variables from form
$server_secret = mysql_real_escape_string($_POST['secret_initial']);
$server_confirm = mysql_real_escape_string($_POST['secret_verify']);

// Check for verification
if ($server_secret == $server_confirm) {
    // Delete from database
    $sql = "DELETE FROM `servers` WHERE secret='" . sha1($server_secret) . "'";
    if (mysql_query($sql) or die("Couldn't delete record. " . mysql_error()) {
        $success = true;
    } else {
        $success = false;
    }
}

?>

It always reports $success as true, even when it is false, and what also confuses me is even when both the secret and verification are correct the entry isn't removed from the MySQL database.

The older version works fine though. That's what baffles me.

The same method I used for checking success (if (mysql_query($sql)) worked fine for insertion and actually gives me feedback if it fails as it should, but for some reason it always reports success when I attempt to delete a record.

If anyone can help me out here I'd really appreciate it.

Please note: I'm very much a novice with PHP/MySQL, so please forgive me.

Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Sep 01 '12 at 17:08
Exactly. You should start using MySQLi or PDO, both ensure that your code is from SQL injection and other such headaches. — SexyBeast, Sep 01 '12 at 17:09
Side note : In your code you first escape string, and then do `sha1` on escaped value. I think order should be opposite : first `sha1`, and then escape result. — a1ex07, Sep 01 '12 at 17:11
@Truth Once I finish and make sure it all works I'll go about changing it. Thanks for the heads up. — Battleroid, Sep 01 '12 at 17:14
Also, if you check the length of the passwords, you only have to check **one** of them, since you've already checked if they are equal (like in your first code) — Dirk McQuickly, Sep 01 '12 at 17:16

score 2 · Answer 1 · edited May 23 '17 at 12:12

2

From This post

mysql_query will return TRUE even if the query did not actually remove anything.

So you will always get true from Delete query..

Credit goes to Paolo

edited May 23 '17 at 12:12

Community

1
1

answered Sep 01 '12 at 17:16

Moe Tsao

1,054
6
9

score 1 · Accepted Answer · answered Sep 01 '12 at 17:07

1

This is because the query is syntactically correct, so there'll never be an error. Instead, you want to find the number of rows that were actually affected:

<?php

mysql_connect('localhost', 'root', 'pass');
mysql_select_db('database');

// Create variables from form
$server_secret = mysql_real_escape_string($_POST['secret_initial']);
$server_confirm = mysql_real_escape_string($_POST['secret_verify']);

// Check for verification
if ($server_secret == $server_confirm) {
    // Delete from database
    $sql = "DELETE FROM `servers` WHERE secret='" . sha1($server_secret) . "'";
    $result = mysql_query($sql);
    if (mysql_affected_rows($result)) {
        $success = true;
    } else {
        $success = false;
    }
}

?>

answered Sep 01 '12 at 17:07

Christopher Armstrong

7,907
2
26
28

Ah! Let me try this really quick. – Battleroid Sep 01 '12 at 17:08
Also, please read the comment from Truth - he is absolutely correct. I was showing you how to correct this case to learn from, but you should move away from the mysql_* functions. – Christopher Armstrong Sep 01 '12 at 17:09
Thanks, that was the issue, I'm going to double check the insertion deal as well. Once everything is working I'll go about changing it. – Battleroid Sep 01 '12 at 17:12
Well, it no longer reports success, however it still is not removing records. Odd. – Battleroid Sep 01 '12 at 17:18

Cannot delete MySQL record using PHP

2 Answers2