php - 在php中使用json_encode的Mysql更新查询问题

Question

当我使用 php mysql 更新数据时，遇到一些问题，我的代码 php 代码在这里

$query = "UPDATE `wp_experience` SET 
                     `exp_from` ='". $exp_from."' ,
                     `exp_to` = '". $exp_to."' ,
                     `exp_title` = '". json_encode($exp_title)."',
                     `exp_desc` = '". json_encode($exp_desc)."' ,                               
                     `exp_cat` = '". $exp_cat."'                                
                     WHERE `id` =".$oldid;

它将产生类似的数据，

UPDATE wp_experience SET exp_from ='2016-01-22 00:00:00' , exp_to = '2002-11-14 00:00:00' , exp_title = '{"en":" PSA Peugeot Citroën Automobiles, Mulhouse (F-68)","fr":"Technical Directué - FRENCH","de":"Responsable d'unité de maintenance"}', exp_desc = '{"en":" Test</p>","fr":" Test</p>","de":" H</p>"}' , exp_cat = '18' WHERE id =28

我遇到了这个问题，

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 'unité de maintenance"}', exp_desc = '{"en":" Test</p>","fr":" Test</p' at line 1

如何解决这个问题？

Answer 1

某些嵌入的字符串会破坏您的查询，因此请使用 mysqli_real_escape_string() 或准备好的 SQL 语句:

$query = "
    UPDATE 
        wp_experience 
    SET 
        exp_from    = '" . $exp_from . "' ,  
        exp_to      = '" . $exp_to . "' , 
        exp_title   = '" . mysqli_real_escape_string($con, json_encode($exp_title)) . "',  
        exp_desc    = '" . mysqli_real_escape_string($con, json_encode($exp_desc)) . "' ,
        exp_cat     = '" . mysqli_real_escape_string($con, $exp_cat) . "'
    WHERE 
        id = " . $oldid;