current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 3 down vote favorite

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));

  ?>

.

share|improve this question
4  
Missing quotes maybe? –  Hyperboreus Nov 13 '13 at 5:38
    
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 Nov 13 '13 at 5:45

3 Answers 3

up vote 1 down vote accepted

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

$stmt = $db->prepare("UPDATE `opendpu` SET PE_REQUIRED = '$pe_required' WHERE ECRNUM = '$ecrno'");
share|improve this answer
    
Always a good idea to pass data into statements via string functions and not via the driver. SQL injection is waving at you. –  Hyperboreus Nov 13 '13 at 5:38
    
i dont think single quote is a must for enclosing the php variable while using in sql query. –  R R Nov 13 '13 at 5:39
    
Well, this works. But I agree @RishabhRaj I thought with php the quotes were not necessary. –  bagofmilk Nov 13 '13 at 5:41
    
@bagofmilk, You definitely need input sanitization. Go through the articles on SO. –  Shankar Damodaran Nov 13 '13 at 5:42
    
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. –  bagofmilk Nov 13 '13 at 5:45
up vote 3 down vote

+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));
share|improve this answer
    
I agree. But this was only to quickly test my statement –  bagofmilk Nov 13 '13 at 5:42
up vote 0 down vote

Please check with below query 

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.