如何从数据库中删除htmlentities()值? [英] How to remove htmlentities() values from the database?

问题描述

很久以前我什么都不知道-甚至现在我都不知道-我在php中设计了一个Web应用程序，该应用程序通过htmlentities()运行值后在mysql数据库中插入了数据.我最终意识到了这一点，并删除了这一步，并将其停留在输出而不是输入中，并以自己快乐的方式继续前进.

但是，从那时起，我不得不重新访问一些旧数据，但是不幸的是，我遇到了一个问题，当它在屏幕上显示时，我会看到显示的值，这些值实际上是htmlentit两次的.

那么，是否有一种mysql或phpmyadmin方法将所有受影响的较旧的行改回其相关字符，还是我必须编写脚本来读取每一行，解码和更新12个表中的所有1700万行? /p>

感谢大家的帮助，我在下面用一些代码写下了我自己的答案，虽然不漂亮，但是可以在较早的测试数据上使用，因此禁止有人在我躺在床上的时候指出我的代码中存在明显错误明天将在备份数据库上运行它，如果可以的话，将在实时数据库上运行它.

解决方案

我最终使用了它，虽然不漂亮，但是我很累，现在是凌晨2点，它已经完成了工作！ (关于测试数据)

$tables = array('users', 'users_more', 'users_extra', 'forum_posts', 'posts_edits', 'forum_threads', 'orders', 'product_comments', 'products', 'favourites', 'blocked', 'notes');
foreach($tables as $table)
    {       
        $sql = "SELECT * FROM {$table} WHERE data_date_ts < '{$encode_cutoff}'";
        $rows = $database->query($sql);
        while($row = mysql_fetch_assoc($rows))
            {
                $new = array();
                foreach($row as $key => $data)
                    {
                        $new[$key] = $database->escape_value(html_entity_decode($data, ENT_QUOTES, 'UTF-8'));
                    }
                array_shift($new);
                $new_string = "";
                $i = 0;
                foreach($new as $new_key => $new_data)
                    {
                        if($i > 0) { $new_string.= ", "; }
                        $new_string.= $new_key . "='" . $new_data . "'";
                        $i++;
                    }
                $sql = "UPDATE {$table} SET " . $new_string . " WHERE id='" . $row['id'] . "'";
                $database->query($sql);
                // plus some code to check that all out
            }
    }

Long before I knew anything - not that I know much even now - I desgined a web app in php which inserted data in my mysql database after running the values through htmlentities(). I eventually came to my senses and removed this step and stuck it in the output rather than input and went on my merry way.

However I've since had to revisit some of this old data and unfortunately I have an issue, when it's displayed on the screen I'm getting values displayed which are effectively htmlentitied twice.

So, is there a mysql or phpmyadmin way of changing all the older, affected rows back into their relevant characters or will I have to write a script to read each row, decode and update all 17 million rows in 12 tables?

EDIT:

Thanks for the help everyone, I wrote my own answer down below with some code in, it's not pretty but it worked on the test data earlier so barring someone pointing out a glaring error in my code while I'm in bed I'll be running it on a backup DB tomorrow and then on the live one if that works out alright.

解决方案

I ended up using this, not pretty, but I'm tired, it's 2am and it did its job! (Edit: on test data)

$tables = array('users', 'users_more', 'users_extra', 'forum_posts', 'posts_edits', 'forum_threads', 'orders', 'product_comments', 'products', 'favourites', 'blocked', 'notes');
foreach($tables as $table)
    {       
        $sql = "SELECT * FROM {$table} WHERE data_date_ts < '{$encode_cutoff}'";
        $rows = $database->query($sql);
        while($row = mysql_fetch_assoc($rows))
            {
                $new = array();
                foreach($row as $key => $data)
                    {
                        $new[$key] = $database->escape_value(html_entity_decode($data, ENT_QUOTES, 'UTF-8'));
                    }
                array_shift($new);
                $new_string = "";
                $i = 0;
                foreach($new as $new_key => $new_data)
                    {
                        if($i > 0) { $new_string.= ", "; }
                        $new_string.= $new_key . "='" . $new_data . "'";
                        $i++;
                    }
                $sql = "UPDATE {$table} SET " . $new_string . " WHERE id='" . $row['id'] . "'";
                $database->query($sql);
                // plus some code to check that all out
            }
    }

这篇关于如何从数据库中删除htmlentities()值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！