PHP MySQL UPDATE statement not working

Question

I have a MySQL database with a table (opendpu) that has multiple columns including columns titled "ECRNUM" and "PE_REQUIRED".

I'm simply trying to test this update statement by specifying some values. I get this error:

Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DOE WHERE ECRNUM = 81308' at line 1 )

I cannot, for the life of me, figure out what is wrong here. Can anyone help?

<?php
  require ('config.php');
 $ecrno = '81308';
 $pe_required = 'JOHN DOE';

while (true) {
try {
    $db = new PDO($dsn, $uname, $pword);
    $db->exec( "SET CHARACTER SET utf8" );
    $db->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC ); 
    $db->setAttribute( PDO::ATTR_PERSISTENT, true );
    break;
}
    catch (Exception $e) {
        $db = null;
        $counter++;
        if ($counter == $limit)
            throw $e;
    }
}

$stmt = $db->prepare("UPDATE opendpu SET PE_REQUIRED = $pe_required WHERE ECRNUM = $ecrno");
$stmt->execute() or die(print_r($stmt->errorInfo(), true));

  ?>

.

It seems you are having a single quote in your data for $pe_required . You could escape it by using addslashes function . But as you are already using PDO , you should be passing the values a parameters . — Uours
– Uours, Commented Nov 13, 2013 at 5:45

hammus · Accepted Answer · 2013-11-13 05:38:46Z

3

+1 for using prepared statements... but (and its a big BUT):

You should never use prepared statements without bind_param as this leaves you wide open to SQL injection and negates the benefits of prepared statements.

$stmt = $db->prepare("UPDATE opendpu SET PE_REQUIRED=? WHERE ECRNUM=?");
$stmt->bind_param('si', $pe_required, $ecrno);
$stmt->execute() or die(print_r($stmt->errorInfo(), true));

answered Nov 13, 2013 at 5:38

hammus

2,6222 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bagofmilk Over a year ago

I agree. But this was only to quickly test my statement

Shankar Narayana Damodaran · Accepted Answer · 2013-11-13 05:37:53Z

1

Change your syntax like this [Enclosed quotes around the variable]

$stmt = $db->prepare("UPDATE `opendpu` SET PE_REQUIRED = '$pe_required' WHERE ECRNUM = '$ecrno'");

answered Nov 13, 2013 at 5:37

Shankar Narayana Damodaran

68.6k43 gold badges102 silver badges129 bronze badges

6 Comments

Hyperboreus Over a year ago

Always a good idea to pass data into statements via string functions and not via the driver. SQL injection is waving at you.

R R Over a year ago

i dont think single quote is a must for enclosing the php variable while using in sql query.

bagofmilk Over a year ago

Well, this works. But I agree @RishabhRaj I thought with php the quotes were not necessary.

Shankar Narayana Damodaran Over a year ago

@bagofmilk, You definitely need input sanitization. Go through the articles on SO.

bagofmilk Over a year ago

Again...guys..this was a quick test. It's not like I'm going to launch this. Of course I needed to bind parameters, but I'm not worried about someone hacking my test file.

|

Nikul · Accepted Answer · 2013-11-13 05:42:00Z

0

Please check with below query

$stmt = $db->prepare("UPDATE opendpu SET PE_REQUIRED = '.$pe_required.' WHERE ECRNUM = '.$ecrno.'");

answered Nov 13, 2013 at 5:42

Nikul

1,0251 gold badge13 silver badges33 bronze badges

Collectives™ on Stack Overflow

PHP MySQL UPDATE statement not working

3 Answers 3

1 Comment

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related