PDO error: " SQLSTATE[HY000]: General error " When updating database

Question

I am getting an error when updating a database using PDO. I am new to PDO so maybe the problem is a small one and I just don't understand. Funny thing about the error, the command works fine and the database does actually get updated. But it still returns an error back at me.

Code:

try {
    $stmt = $pdo->prepare("UPDATE $page SET $section = :new_content WHERE $section = '$old_content'");
    $stmt->execute(array(
        'new_content' => $new_content
    ));
    $result = $stmt->fetchAll();
    echo "Database updated!";
}
catch(PDOException $e) {
    echo 'ERROR UPDATING CONTENT: ' . $e->getMessage();
}

Error: ERROR UPDATING CONTENT: SQLSTATE[HY000]: General error

I literally have no idea where the problem could be because its very vaque and I haven't been able to find anyone with the same problem.

You're vulnerable to SQL injection, even though you're (partially/incorrectly) using prepared statements and placeholders. Just because YOU'RE the one providing the data being insert doesn't mean you can't inject yourself. — Marc B, Oct 19 '12 at 17:38
Are you referring to the variables $page & $section? They are both taken from the fixed ID of html elements. so you are saying even they are potentially harmful? — jagershark, Oct 19 '12 at 17:39
`$old_content` would be the big red flag for me. **ANY** dynamic data going into a query string is potentially harmful. just because you pulled it out of a DB doesn't mean it's safe. e.g. consider something like `update users set name='Miles T. O\'Brien' where name='Miles O'Brien';`. you escape the newly updated name, but pulled the original name from the db to begin with, and now you've injected yourself a syntax error. — Marc B, Oct 19 '12 at 17:42
@AlxVallejo $pdo->prepare("SELECT.... not $pdo->prepare("UPDATE.... — Ruwantha, Mar 02 '16 at 17:00
In other words, there's no succinct way to return rows from an update query? — AlxVallejo, Mar 02 '16 at 17:38

score 113 · Accepted Answer · answered Oct 19 '12 at 18:10

113

You do not use fetchAll(),as in

$result = $stmt->fetchAll();

with update or insert queries. Removing this statement should rectify the problem.

answered Oct 19 '12 at 18:10

TranQ

1,381
1
9
10

So what is the correct method for executing update queries if not this? – sparecycle Sep 26 '14 at 18:21
@deeperDATA, Just the execute statement should be enough. Calling fetchAll() is needed only for select queries. – TranQ Sep 27 '14 at 07:27
1

I suggest that the correct method is update(). Try to use this for updating your tables – Rubinum Mar 17 '15 at 09:04

score 14 · Answer 2 · answered Apr 14 '16 at 18:39

14

Just to note, another possible reason for this error is if you make a second database call with the variable $stmt inside of an existing parent $stmt loop.

     $stmt = $conn->query($sql);

    while ($row = $stmt->fetch()) {  //second use of $stmt here inside loop

answered Apr 14 '16 at 18:39

Acyra

15,864
15
46
53

3

This was my issue, apparently PDO doesn't like the variable being reset. – Matt Cavanagh May 17 '16 at 20:42
@MattCavanagh do you mind sharing how you worked around this? I'm running into it now... – Michael Hommé Jun 16 '16 at 20:22
Silly of me, but just realized I was using the same variable name, `$stmt`, inside the loop. I've made it unique `$stmt_delete` and it works. – Michael Hommé Jun 16 '16 at 20:34
Yup, that was the issue I had too. – Matt Cavanagh Jun 16 '16 at 23:49
This is my situation, how to fix it? (how to reset the `$stmt`? – candlejack Jan 15 '18 at 04:34
1

@candlejack Use a different variable than $stmt inside of the loop – Acyra Jan 15 '18 at 09:39

PDO error: " SQLSTATE[HY000]: General error " When updating database

2 Answers2

Linked

Related