dongliao6777
dongliao6777
2011-07-29 00:09
浏览 141
已采纳

如何在MySQL数据库中保存引号

Whenever I add a single quote (') or a double quote (") in my PHP formfield, it will be saved in my MySQL DB as " / '. How can save the 'real' "quotes" in my DB?

I tried to prevent this by making a secure Mysql connection thru PDO, but it doesn't seem to work properly.

So here's the important part of my code:

    $insert_hello = filter_var($_POST['hello'], FILTER_SANITIZE_STRING);
    $dbh->query("SET NAMES 'utf8'");
    $stmt = $dbh->prepare("INSERT INTO testtable (data) VALUES (:hello)");
    $stmt->bindParam(':hello', $insert_hello, PDO::PARAM_STR);      
    $stmt->execute();

Some background information:

The server runs on PHP v5.2.12-0.

The DBStorage engine is InnoDB and has its client-, connection-, results- and system charset are set to utf8.

The DB field has its collation set to utf8_unicode_ci.

Magic quotes are disabled thru .htaccess.

Thanks in advance!

Kind regards, Jroen

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

3条回答 默认 最新

  • douwei8911
    douwei8911 2011-07-29 01:04
    已采纳

    Ok, just to formalize the correct answer:

    The problem is caused by filter_var() that converts some characters into HTML entities. There is no need to manually sanitize the data since PDO does that for you.

    You can just write something like this, that should work just fine:

    $dbh->query("SET NAMES 'utf8'");
    $stmt = $dbh->prepare("INSERT INTO testtable (data) VALUES (:hello)");
    $stmt->bindParam(':hello', $_POST['hello'], PDO::PARAM_STR);      
    $stmt->execute();
    
    点赞 评论
  • doutan2228
    doutan2228 2011-07-29 00:12

    The best idea is to leave this as is and html decode on reading.

    点赞 评论
  • douweng7083
    douweng7083 2011-07-29 00:14

    You can use the php function $decoded_insert_hello = html_entity_decode($insert_hello, ENT_QUOTES) to do this.

    http://www.php.net/manual/en/function.html-entity-decode.php

    点赞 评论

相关推荐