Cause
- A customer security report indicated that there was a link to an outdated website, and the outdated website became a shameful website~~
- This problem is caused by copying the content of other websites directly when they usually post, and not using the editor’s cleaning tool.
- Afterwards, the customer asked if he could check all the content and remove all links?
- Obviously, he takes it for granted~~
Thoughts
- One is to find all the links, and then tell the customer to dry up all the links according to his requirements. The customer may thank you at first, but he will come and tell you after a while, why this one is not available, and that one cannot be used, so don’t worry about it. looking for trouble
- One is to find all the information with links, and the system will automatically clear them after judging whether they are invalid links, but the question is, if this invalid link has become a valid shame website, how do you judge? Is it still necessary to judge whether the externally linked website contains shameful content or non-compliant content? For this little thing, am I sick? Hmm~~ Let’s talk about it later when I get sick
- One is, this is the only one left, manually delete it after manual judgment, after all, how do I know which links you want? Which ones are not, since not all external links must be removed, since the participation of “people” is needed, then this method is naturally used. And let customers delete by themselves, avoiding the disputes of accidental deletion, let customers participate in it, and experience the fun of manipulation. . . .
Of course, I searched the database in advance, and there are only more than 300 articles involving external links. Even if the customer is not willing to do it manually, we will do it manually, and it will not be tiring.
Structure
The basic data in the database is placed in the tableN table, and the content and other data are placed in the tableN_data table. The main fields are tableN.id, tableN.category, tableN.title, tableN.showtime, tableN_data.id, tableN_data.did, tableN_data.content, where id is the associated field of the two tables
For example, when searching directly in the database, use the following sql statement to search for content with http, and you can also look at www or specific characters.
select * from table1 as a join table1_data as b on b.id=a.id where content like "%http%"; select * from table2 as a join table2_data as b on b.id=a.id where content like "%http%"; select * from table3 as a join table3_data as b on b.id=a.id where content like "%http%"; select * from table4 as a join table4_data as b on b.id=a.id where content like "%http%"; select * from table5 as a join table5_data as b on b.id=a.id where content like "%http%"; //...
The search results written in this way will be divided into N tables for you to study carefully
But of course you can’t do this for customers, you must create a page for them to operate.
Well, in the end, people are lazy, and use the traditional php single page to directly complete this matter.
Code
I will not analyze it in sections, but integrate it into a section to see for yourself. Some content of the practical code cannot be leaked, so I made a slight modification when posting the article. If there is a bug during the period and cannot run, you can leave a message~~ If you can use it, please leave a message.
<!DOCTYPE html>
<html>
<head>
<title>Hyperlink cleaning</title>
<meta charset="utf-8">
</head>
<body>
<?php
// Connect to the database
$dsn = "mysql:host=127.0.0.1;dbname=testDb;charset=utf8mb4";
$username = "test";
$password = "test";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {<!-- -->
$pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {<!-- -->
echo "Failed to connect to the database:" . $e->getMessage();
exit;
}
// Query data in chunks
$perPage = 5;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
// The table structure is the same, put it in an array first, and adjust it later
$tables = array(
'table1',
'table2',
'table3',
'table4',
'table5',
'table6',
'table7'
);
// Choose which condition you want to use, note that mysql supports regular queries
$condition = array(
//"content like '%http:%'",
//"content like '%http:%'",
//"content like '%www%'",
//"content like '%<a%'",
//"content REGEXP '<a[^>] + href\s*=\s*["\'][^"\']*www[^ "\']*'",
"content REGEXP '<a[^>] + href\s*=\s*["\'][^"\']*http[^\ "\']*'"
);
$conditionString = implode(' or ', $condition);
// Generate sql by way of union joint query
$selects = array();
$countSql = '';
$selectSql = array();
//Rename the id field of the data table to id2 output, to prevent conflicts, output the current table name as the moudle field
foreach ($tables as $table) {<!-- -->
$selectSql[] = "(SELECT a.*,b.id as id2,b.content,b.did,'{<!-- -->$table}' as moudle FROM {<!-- -->$table} as a join {<!-- -->$table}_data as b ON b.id=a.id where (".$conditionString."))";
}
$selectSqlUnion = implode(' UNION ', $selectSql);
// Inquire
$selectSqlString = "SELECT * FROM (".$selectSqlUnion.") as alldata";
$sql = $selectSqlString . ' LIMIT ' . (($page - 1) * $perPage) . ',' . $perPage;
$result = $pdo->query($sql);
$selected_rows = array();
if ($result->rowCount() > 0) {<!-- -->
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {<!-- -->
$column_value = $row["content"];
$selected_rows[] = $row;
}
}
// total
$total = 0;
$countSqlString = "SELECT COUNT(*) FROM (".$selectSqlUnion.") as alldata WHERE (".$conditionString.")";
$countResult = $pdo->query($countSqlString);
if ($countResult->rowCount() > 0) {<!-- -->
while ($row = $countResult->fetch(PDO::FETCH_NUM)) {<!-- -->
$total = $row[0];
}
}
$totalPages = ceil($total / $perPage);
// List the selected row on the front-end page for the user to judge and check it
$n = 0;
echo "<form method='post' action=''>";
foreach ($selected_rows as $row) {<!-- -->
$n + + ;
echo "<div style='border:1px solid gray;padding:20px;margin:20px;'>
<!--Link to website preview-->
<h2>【{<!-- -->$n}】 <a href='http://127.0.0.1/{$row["id"]}.html' target='_blank '>{$row["title"]}</a></h2>
<p style='padding:5px;background-color:#F3F3F3;'>
Release time: {$row["showtime"]} & amp;nbsp; & amp;nbsp; & amp;nbsp;
The column you are in: {$row["category"]} & amp;nbsp; & amp;nbsp; & amp;nbsp;
Where model: {$row["moudle"]} & amp;nbsp; & amp;nbsp; & amp;nbsp;
Model ID: {$row["id"]} & amp;nbsp; & amp;nbsp; & amp;nbsp;
Model number: {$row["did"]}
</p>
<p>{$row["content"]}</p>
<label style='display:block;padding:5px;background-color:#CBC2EB;'>
<!--The table name and id number that store this information-->
<input type='checkbox' name='selected_rows[]' value='{$row["moudle"]}|{$row["id"]}'>selected
</label>
</div>";
}
echo "<div style='text-align:center;padding:28x;line-height:3;'><input type='submit' value='submit to remove hyperlink' style=\ 'font-size:18px;font-weight:bold;'/></div>";
echo "</form>";
// pagination
if ($totalPages > 1) {<!-- -->
echo "<div style='text-align:center;'>";
if ($page > 1) {<!-- -->
echo " & amp;nbsp; & amp;nbsp;<a href="?page=" . ($page - 1) . "">Previous</a> & amp ;nbsp; ";
}
for ($i = 1; $i <= $totalPages; $i ++ ) {<!-- -->
if ($i == $page) {<!-- -->
echo " & amp;nbsp; & amp;nbsp;<span>{<!-- -->$i}</span> & amp;nbsp; & amp;nbsp;";
} else {<!-- -->
echo " & amp;nbsp; & amp;nbsp;<a href="?page={<!-- -->$i}">{<!-- -->$i }</a> ";
}
}
if ($page < $totalPages) {<!-- -->
echo " & amp;nbsp; & amp;nbsp;<a href="?page=" . ($page + 1) . "">next page</a> & amp ;nbsp; ";
}
echo "</div>";
}
// process the data submitted by the user
if ($_SERVER["REQUEST_METHOD"] == "POST") {<!-- -->
if (!empty($_POST["selected_rows"])) {<!-- -->
foreach ($_POST["selected_rows"] as $selected_row) {<!-- -->
\t\t\t
// Read the data and judge whether the data exists in the table
$p_row = explode('|',$selected_row);
$p_data_sql = "SELECT * FROM {<!-- -->$p_row[0]}_data WHERE id='{<!-- -->$p_row[1]}'";
$p_result = $pdo->query($p_data_sql);
\t\t\t
$row = $p_result->fetch(PDO::FETCH_ASSOC);
\t\t\t
if(!empty($row)) {<!-- -->
// Replace hyperlink with link content
$text = preg_replace('/<a\s + [^>]*>(.*?)<\/a>/is', '$1', $row["content" ]);
\t\t\t\t
$stmt = $pdo->prepare("UPDATE {<!-- -->$p_row[0]}_data SET content = REPLACE(content, ?, ?) WHERE id = ?");
$stmt->execute(array($row["content"], $text, $row["id"]));
}
}
echo "Data updated successfully";
} else {<!-- -->
echo "Please select the data row to update";
}
}
// close the connection
$pdo = null;
?>
</body>
</html>