dongxie8856
2014-10-21 10:15
浏览 111
已采纳

PHP:在mySQL中插入引号时出错

I insert a text variable in a mySQL table. Everything works fine except in the text is a quotation mark. I thought that I can prevent an error by using "mysql_real_escape_string". But there is an error anyway.

My insert statement:

 $insertimage= "INSERT INTO image(filename,text,timestamp,countdown) VALUES ('$filename','$text','$timestamp','$countdown')";
 mysql_real_escape_string($insertimage);

The error message: MySQL Error: 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 '1413885955514','10')' at line 1

图片转代码服务由CSDN问答提供 功能建议

我在mySQL表中插入一个文本变量。 一切正常,除非文本是引号。 我以为我可以通过使用“mysql_real_escape_string”来防止错误。 但无论如何都有错误。

我的插入语句:

  $ insertimage =“INSERT INTO image(文件名,文本,时间戳, 倒计时)VALUES('$ filename','$ text','$ timestamp','$ countdown')“; 
 mysql_real_escape_string($ insertimage); 
   
 
 

错误消息: MySQL错误:您的SQL语法出错; 查看与您的MySQL服务器版本对应的手册,以便在第1行“1413885955514”,“10”)附近使用正确的语法

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

7条回答 默认 最新

  • doulong1987 2014-10-21 10:21
    已采纳

    The problem with your current code is that you have not correctly escaped the values you're trying to enter into the table.

    Better still is to avoid the mysql_* function family entirely. Those functions are now deprecated and bring security risks to the table (along with other concerns).

    You'd be better to use PDO and Prepared Statements, for example:

    $db = new PDO('param1', 'param2', 'param3');
    $sql = $db->prepare( 'INSERT INTO `image` (`filename`, `text`, `timestamp`, `countdown`) 
                                       VALUES (:filename, :text, :timestamp, :countdown)' );
    
    $sql->execute( array(':filename' => $filename, 
                         ':text' => $text, 
                         ':timestamp' => $timestamp, 
                         ':countdown' => $countdown )
    );
    
    点赞 打赏 评论
  • doudiejian5827 2014-10-21 10:18
    mysql_real_escape_string($insertimage);
    

    You will have to use this function to each variables before writing the query.

    $filename = mysql_real_escape_string($filename);
    $text = mysql_real_escape_string($text);
    $timestamp = mysql_real_escape_string($timestamp);
    $countdown = mysql_real_escape_string($countdown);
    
    $insertimage= "INSERT INTO image(filename,text,timestamp,countdown) VALUES ('$filename','$text','$timestamp','$countdown')";
    
    点赞 打赏 评论
  • duankeye2342 2014-10-21 10:18

    Try this ,

      $insertimage = sprintf("INSERT INTO image(filename,text,timestamp,countdown) VALUES    ('%s','%s','%s','%s')", mysql_real_escape_string($filename), mysql_real_escape_string($text),   $timestamp, $countdown);
    

    Why, because your inputs vars must be escaped before using them in sql

    then execute your sql.

    点赞 打赏 评论
  • douye4254 2014-10-21 10:18

    You need to escape data that you are putting into the SQL so that any special characters in it don't break the SQL.

    You are escaping all the special characters in the final string of SQL; even those that you want to have special meaning.

    If you want to use your current approach, you would do something like this:

     $filename = mysql_real_escape_string($filename);
     $text = mysql_real_escape_string($text);
     $timestamp = mysql_real_escape_string($timestamp);
     $countdown = mysql_real_escape_string($countdown);
     $insertimage= "INSERT INTO image(filename,text,timestamp,countdown) VALUES ('$filename','$text','$timestamp','$countdown')";
    

    … but the PHP mysql_ extension is obsolete and you shouldn't use it.

    Modern APIs, such as mysqli_ and PDO support prepared statements, which are a better way to handle user input. This answer covers that in more detail.

    点赞 打赏 评论
  • doukan3504 2014-10-21 10:20

    Escaping the entire query is not useful. In fact, right now, you are causing syntax errors by doing so.

    You should be escaping the individual variables that you inject into it.

    点赞 打赏 评论
  • donglin9717 2014-10-21 10:21

    Try this:

    $filename = mysql_real_escape_string($filename);
    $text = mysql_real_escape_string($text);
    $timestamp = mysql_real_escape_string($timestamp);
    $countdown = mysql_real_escape_string($countdown);
    
    $insertimage = "INSERT INTO image(filename,text,timestamp,countdown) VALUES ('$filename','$text','$timestamp','$countdown')";
    
    mysql_query($insertimage);
    
    点赞 打赏 评论
  • doukanxi4246 2014-10-21 10:22

    Concat the php variables like this:

    $insertimage= "INSERT INTO image(filename,text,timestamp,countdown) VALUES (" . $filenamec . "," . $text . ", " . $timestamp . ", " . $countdown . ")";
    
    with the respective single quotes in those that are text fields i.e: "... '" . $text . "' ..."
    
    点赞 打赏 评论

相关推荐 更多相似问题