how to skip mysql reserved words from (bulk text)

Question

I am having error: ( SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's a immoderate-immoderate-fine boy' WHERE id='4'' at line 1all )

I am actually trying to modify and save bulk text using the method bellow by looping but the problem is I get above error after process few text. As I found mysql reserved words getting matched while looping on bulk text. How can I modify my method to work on bulk text without mysql reserved word conflict problem. Whats the solution?

Text update method:

public function update_des($id,$des){
        try
        {
            $stmt = $this->conn->prepare("UPDATE products SET des='$des' WHERE id='$id'");
            $stmt->execute();
        }
        catch(PDOException $e)
        {
            echo $e->getMessage();
        }

    }

It's not a reserved word, it's a quoting issue. You're not using prepared statements the right way. You should be using parameter binding as well. — aynber, Feb 17 '17 at 15:49
Do not echo in functions. The caller should use a try/catch block to catch the exception. — cottton, Feb 17 '17 at 16:47

score 1 · Answer 1 · edited Feb 17 '17 at 16:04

Your question not seems related to reserved word but it seems you have problem with quotes (or special char) for avoid this you can use binding.

 public function update_des ($id, $des)
 {
      try
      {
           $stmt = $this->conn->prepare("UPDATE products SET des=:des WHERE id=:id");
           $stmt->bindParam(':des', $des, PDO::PARAM_STR);
           $stmt->bindParam(':id', $id, PDO::PARAM_INT);
           $stmt->execute();
      }
      catch (PDOException $e)
      {
           echo $e->getMessage();
      }
 }

score 0 · Answer 2 · answered Feb 17 '17 at 15:51

Use prepared statements:

public function update_des($id,$des){
   try {
       $stmt = $this->conn->prepare("UPDATE products SET des=:des WHERE id=:id");
       $stmt->bindValue(':des', $des);
       $stmt->bindValue(':id', $id, PDO::PARAM_INT);
       $stmt->execute();
   } catch(PDOException $e)  {
      echo $e->getMessage();
   }

}

score 0 · Answer 3 · answered Feb 17 '17 at 16:26

You can also use way of prepared statements with an array.

public function update_des($id,$des){
    try
    {
        $stmt = $this->conn->prepare("UPDATE products SET des=? WHERE id=?");
        $stmt->execute([$id, $des]);
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }

}

No extra lines of code. Just make sure to maintain the order to ensure the parameters are matching.

score 0 · Answer 4 · answered Feb 17 '17 at 17:00

I dont understand why all answers here do the same - echo in functions. Do not echo in functions. The caller should catch and handle problems.

/**
 * @param int|string $id
 * @param string     $des If you do not use meaningful var names you have to write
 *                        a lot of documentation.
 *
 *
 * @return bool
 * @throws PDOException
 */
public function update_des($id, $des)
{
    $stmt = $this->conn->prepare(
        "UPDATE `products` SET `des` = :des WHERE `id` = :id"
    );
    return $stmt->execute(['des' => $des, 'id' => $id]);
}

try {
    $myObject->update_des(1, 'foo');
} catch (PDOException $e) {
    echo $e->getMessage();
}

I guess you are using PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION so you will get true or a PDOException on call.

how to skip mysql reserved words from (bulk text)

4 Answers4