如何在MySQL中保存和检索文本数据保留换行符？ [英] How to store and retrieve text data in MySQL preserving the line breaks?

问题描述

如何从 textarea 存储和检索MySQL数据库中的数据，但保留换行符？我怎样才能以最安全的方式在用户不能进行跨站脚本或SQL注入攻击？

c $ c> mysql_real_escape（）函数，然后 INSERT INTO 数据库，然后检索时使用 htmlspecialchars（） code> function？

我只想知道如何安全地存储数据并保留换行符。我希望有人可以这样做我的例子：

$ p $ <？php
$ con = mysql_connect（主机，用户名密码）;
mysql_select_db（contents_db）;

//过滤过程以防止SQL注入
$ content = mysql_real_escape（$ _ POST ['content']）;
$ b mysql_query（'INSERT INTO contents_db（content，time）VALUES（{$ content}，{time（）}'）;

if（mysql_insert_id（）> 1 ）{
$ query = mysql_query（'SELECT * FROM contents_db ORDER BY time DESC LIMIT 1'）;
$ text = mysql_fetch_object（$ query）;
$ b $ //输出过程保留换行符
echo htmlspecialchars（$ text-> content）;
}

mysql_close（$ con）;
？>


如果我的示例已经正确，任何人都可以告诉我如何使它更好更安全吗？ b $ b

解决方案 getDatabaseResult（$ query）这样的单一函数，以使查询异常检查变得更容易）。

  try {
 $ PDO = new PDO（mysql：host =。$ db_host。; dbname =。$ db_name，$ db_user，$ db_pass）; 
 } 
 catch（PDOException $ e）{
 die（'mysql connection error'）; 
 
 
如果发布数据被设置添加新行
 if（isset（$ _ POST ['content']））
 {
尝试{
 $ query = $ PDO-> prepare（'INSERT INTO contents_db（content，time）VALUES？，？'）; 
 $ res = $ query-> execute（array（$ content，time（）））; 
 
 catch（PDOException $ e）{
 die（'insert query failed'）; 
 
 
 $ b $ //如果最后的查询被执行 - 选择数据
 //或者你可以调用$ PDO-> lastInsertId（）; 
 if（$ res）{
 try {
 $ query = $ PDO-> prepare（'SELECT * FROM contents_db ORDER BY time DESC LIMIT 1'）; 
 $ res = $ query-> execute（）; 
 $ res = $ query-> fetchAll（PDO :: FETCH_ASSOC）; 
 
 catch（PDOException $ e）{
 die（'select query failed'）; 
} 
 
 //输出过程以保存换行符
 echo nl2br（$ text ['content']）; 
} 
  

How can I store and retrieve data in MySQL database from a textarea but preserving the line-breaks? How can I also do it in a safest way where users can not do Cross-Site Scripting or SQL Injection attacks?

Should I filter first the user's input data through mysql_real_escape() function then INSERT INTO the database and then when retrieving, use htmlspecialchars() function?

I just want to know how to store data safely and preserving the line-breaks. I hope someone could do me an example like this:

<?php
    $con = mysql_connect(host,username,password);
    mysql_select_db(contents_db);

    //Filtering process to prevent SQL-Injection
    $content = mysql_real_escape($_POST['content']);

    mysql_query('INSERT INTO contents_db (content, time) VALUES ({$content},{time()}');

    if(mysql_insert_id() > 1){
        $query = mysql_query('SELECT * FROM contents_db ORDER BY time DESC LIMIT 1');
        $text = mysql_fetch_object($query);

        //Outputting process to preserve line-breaks
        echo htmlspecialchars($text->content);
    }

    mysql_close($con);
?>

If my example is right already, can anyone show me how to make it even better and safer?

解决方案

Thats full example of using PDO. Just example, you can improve it in many ways (for example, create single function like getDatabaseResult($query) to make queries exceptions check easier).

try{
    $PDO = new PDO("mysql:host=".$db_host.";dbname=".$db_name, $db_user, $db_pass);
}
catch(PDOException $e){
    die('mysql connection error');
}

// if post data is set - add new row
if(isset($_POST['content']))
{
    try{
        $query = $PDO->prepare('INSERT INTO contents_db (content, time) VALUES ?,?');
        $res   = $query->execute(array($content,time()));
    }
    catch(PDOException $e){
        die('insert query failed');
    }
}

// if last query was executed - select data
// or you can call for "$PDO->lastInsertId();"
if($res){
    try{
        $query = $PDO->prepare('SELECT * FROM contents_db ORDER BY time DESC LIMIT 1');
        $res   = $query->execute();
        $res   = $query->fetchAll(PDO::FETCH_ASSOC);
    }
    catch(PDOException $e){
        die('select query failed');
    }

    //Outputting process to preserve line-breaks
    echo nl2br($text['content']);
}

这篇关于如何在MySQL中保存和检索文本数据保留换行符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！