SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Error performing update: SQLSTATE[42000]

    I am getting this error in my PHP (using PDO) code:

    Error performing update: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ':size_rob, size_premium = :size_premium, brand = :brand, name' at line 2

    Can anyone give me a push in the right direction to solve this?

    Code:
    if (isset($_REQUEST['name']))
    {
    
    $data_adv_id = $_REQUEST['data_adv_id'];
    $data_adv_id = htmlspecialchars($data_adv_id, ENT_QUOTES, 'UTF-8');
    
    $size_rob = $_REQUEST['size_rob'];
    $size_rob = htmlspecialchars($size_rob, ENT_QUOTES, 'UTF-8');
    	
    $size_premium = $_REQUEST['size_premium'];
    $size_premium = htmlspecialchars($size_premium, ENT_QUOTES, 'UTF-8');
    
    $brand = $_REQUEST['brand'];
    $brand = htmlspecialchars($brand, ENT_QUOTES, 'UTF-8');
    
    	if ($brand == "TA1") { $adv_i = "ta";}
    
    $name = $_REQUEST['name'];
    $name = htmlspecialchars($name, ENT_QUOTES, 'UTF-8');
    
    $month = $_REQUEST['month'];
    $month = htmlspecialchars($month, ENT_QUOTES, 'UTF-8');
    	
    $year = $_REQUEST['year'];
    $year = htmlspecialchars($year, ENT_QUOTES, 'UTF-8');
    
    try
    {
    $sql = "UPDATE adv_" . $adv_i . " SET 
    size_rob = :size_rob,
    size_premium = :size_premium,
    brand = :brand,
    name = :name,
    month = :month,
    year = :year
    WHERE id = '$data_adv_id'
    LIMIT 1
    ";
    $affectedRows = $pdo->exec($sql);
    
    // bindValue prevents injection attacks
     $s = $pdo->prepare($sql);
     $s->bindValue(':size_rob', $_POST['size_rob']); // copied/pasted from insert
     $s->bindValue(':size_premium', $_POST['size_premium']);
     $s->bindValue(':brand', $_POST['brand']);
     $s->bindValue(':name', $_POST['name']);
     $s->bindValue(':month', $_POST['month']);
     $s->bindValue(':year', $_POST['year']);
     $s->execute();
    }
    Steve Husting

  2. #2
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,276
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StevenHu View Post
    Code:
    try
    {
    $sql = "UPDATE adv_" . $adv_i . " SET 
    size_rob = :size_rob,
    size_premium = :size_premium,
    brand = :brand,
    name = :name,
    month = :month,
    year = :year
    WHERE id = '$data_adv_id'
    LIMIT 1
    ";
    $affectedRows = $pdo->exec($sql);
    
    // bindValue prevents injection attacks
     $s = $pdo->prepare($sql);
     $s->bindValue(':size_rob', $_POST['size_rob']); // copied/pasted from insert
     $s->bindValue(':size_premium', $_POST['size_premium']);
     $s->bindValue(':brand', $_POST['brand']);
     $s->bindValue(':name', $_POST['name']);
     $s->bindValue(':month', $_POST['month']);
     $s->bindValue(':year', $_POST['year']);
     $s->execute();
    }
    This may not be the issue, but an issue is that you're executing the SQL before you do any preparing or binding.
    "First make it work. Then make it better."

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I removed that line and it executed without errors. However, it did not actually update the table! Strange...
    Steve Husting

  4. #4
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If I understand the error, it is supposed to be a problem before the notice, not after, right?
    Steve Husting

  5. #5
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,276
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StevenHu View Post
    I removed that line and it executed without errors. However, it did not actually update the table! Strange...
    To figure out the answer to this one, it would be useful if you coul echo or var_dump the value of $sql as well as all the values that you're binding.
    "First make it work. Then make it better."

  6. #6
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,276
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StevenHu View Post
    If I understand the error, it is supposed to be a problem before the notice, not after, right?
    Correct. It was the colon-placeholder that was causing the syntax error, because without preparing the statement, the server doesn't know that it's supposed to be a placeholder. Instead, it was trying to interpret the colon-placeholder as literal syntax.
    "First make it work. Then make it better."

  7. #7
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This colon?

    size_rob = :size_rob,

    I thought that was correct syntax.
    Steve Husting

  8. #8
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,276
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    When it's run through prepare(), then it is. But if you skip prepare and try to execute it as is, then it isn't.
    "First make it work. Then make it better."

  9. #9
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The OP shows it running before prepare, so that's not the issue then.
    Steve Husting

  10. #10
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,276
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StevenHu View Post
    The OP shows it running before prepare, so that's not the issue then.
    It was the cause of the syntax error issue. As I recall, you said the syntax error went away when you removed the exec() line.
    "First make it work. Then make it better."

  11. #11
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I need to update the post because of new errors. I'll do that later with a different post since it is now a different issue.

    Thanks for helping me out!
    Steve Husting

  12. #12
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,340
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oh, brother ... the problem was one too many commas in the UPDATE query, which was added later when I was adding and removing lines.
    Steve Husting


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •