Some posts not updating in database

Question

I'm using a forum type system for users to ask questions or say whats on their minds and I'm having a problem related to updating database information. I have no idea what's wrong here but I do know what's happening.

Some posts cannot be edited. Everything will happen as usual but will not update the database. Some posts have the wrong body but the correct title.

It doesn't make sense and I'll do some tests to check if the mysql is working.. but until then, any thoughts?

UPDATE:

This query is passing... but the database isn't updating for this particular row. This one only...

$new_body = $_POST['new_body'];
$old_body = $_POST['old_body'];
mysql_query("UPDATE questions SET body='".htmlspecialchars($new_body, ENT_QUOTES)."' WHERE body='".htmlspecialchars($old_body, ENT_QUOTES)."'") or die(mysql_error());

Also, if someone could enlighten me on SQL Injections and how to prevent them, I'd greatly appreciate it.

The columns are id, pin, locked, body, date, numberofcomments (i know I can just use php to read the amount of comments but I did this prior to learning that) and views.

UPDATE: Works now. Replaced the WHERE body to WHERE id. Stupid mistake. I could still use some sql injection enlightening though!

**warning** your code is extremely vulnerable to sql injection attacks! — Daniel A. White, Sep 30 '13 at 23:52
What column(s) is primary key on the table? Don't you have an id column in your `questions` table? — peterm, Sep 30 '13 at 23:57
Oh my goodness that worked! Wow.. can't believe I was so dumb! — Anthony, Oct 01 '13 at 00:03

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

As I mentioned in comments first of all use a primary key in your WHERE clause to target specific record in your table instead of using body column. That being said your update statement should look something like this

UPDATE questions SET body = ? WHERE id = ?

Now to prevent sql injections use switch to mysqli_* or PDO extension and use prepared statements instead of interpolating query strings.

Your code using prepared statements with mysqli_* might look like

$id = $_POST['id'];
$new_body = $_POST['new_body'];
$old_body = $_POST['old_body'];

//Do validation, sanitation, and encoding if necessary here before you put into database
...

$db = new mysqli('localhost', 'user', 'password', 'dbname');
if ($db->connect_errno) {
    die('Connection failed: %s\n' . $db->connect_error); //TODO better error handling
}

$sql = 'UPDATE questions SET body = ? WHERE id = ?';
$stmt = $db->prepare($sql);
if (!$stmt) {
    die('Can\'t prepare: ' . $db->error); //TODO better error handling
}
$stmt->bind_param('si', $new_body, $id);
$stmt->execute();

$stmt->close();
$db-close();

Further reading:

How can I prevent SQL injection in PHP? It's the absolute must read

score 0 · Answer 2 · answered Oct 01 '13 at 00:16

Please use Mysqli or PDO. Mysql_* is deprecated and insecure.

Have you tried checking if the post exists? As it seems a problem that the post doesn't exist or it's not finding it.

Do you get any mysql_error's or any output from mysql?

Also have you tried updating using phpmyadmin - Seeing if it outputs any errors there?

$new_body = $_POST['new_body'];
$old_body = $_POST['old_body'];
mysql_query("UPDATE questions SET body='".htmlspecialchars($new_body, ENT_QUOTES)."' WHERE body='".htmlspecialchars($old_body, ENT_QUOTES)."'") or die(mysql_error());

I haven't used mysql_ in a while, in favour of PDO, so this syntax may be incorrect. But you could try this:*

    $new_body = htmlentities($_POST['new_body']);
    $old_body = htmlentities($_POST['old_body']);
    $sql1=mysql_query("SELECT * FROM questions WHERE body='$old_body'") or die(mysql_error());
    if(mysql_num_rows($sql1)>"0")
    {
        $res=mysql_query("UPDATE questions SET body='$new_body'") or die(mysql_error());
        echo 'Updated';
    }
    else
    {
        //Insert.
    }

Some posts not updating in database

2 Answers2