php - 用于发送电子邮件的 PHP/MySQL 脚本 - 一封邮件太多
问题描述
我制作了 PHP 脚本以将报告从 MySQL 数据库发送到用户的电子邮件。每个用户必须只接收他们自己的数据(带有他们的 id)。脚本 tabela.php 使用用户内容制作 html 表格。
<?php
//select data
$sql = "SELECT oports.id, oports.handlowiec, oports.data_rozp, oports.data_przed, oports.nazwa, oports.city, oports.nip, oports.inic, db_users.email FROM oports, db_users WHERE db_users.id = oports.user_id and db_users.id = '{$sqlid}'";
//execute query
$wynik = $polaczenie->query($sql);
//make table schema
echo "<p style=\"font-size:14px;\">There is your report:<br></p>";
echo "<p>";
echo "<table boder=\"1\"><tr>";
echo "<td bgcolor=\"#f4df8b\"><strong>ID</strong></td>";
echo "<td bgcolor=\"#f9d74d\"><strong>name</strong></td>";
echo "<td bgcolor=\"#f4df8b\"><strong>Date started</strong></td>";
echo "<td bgcolor=\"#f9d74d\"><strong>Date deadline</strong></td>";
echo "<td bgcolor=\"#f4df8b\"><strong>Company name</strong></td>";
echo "<td bgcolor=\"#f9d74d\"><strong>City</strong></td>";
echo "<td bgcolor=\"#f4df8b\"><strong>NIP</strong></td>";
echo "<td bgcolor=\"#f9d74d\"><strong>Initials</strong></td>";
echo "</tr>";
//loop for show data in table
while ( $row = mysqli_fetch_row($wynik) ) {
echo "</tr>";
echo "<td bgcolor=\"#f7e8ab\">" . $row[0] . "</td>";
echo "<td bgcolor=\"#fbe383\">" . $row[1] . "</td>";
echo "<td bgcolor=\"#f7e8ab\">" . $row[2] . "</td>";
echo "<td bgcolor=\"#fbe383\">" . $row[3] . "</td>";
echo "<td bgcolor=\"#f7e8ab\">" . $row[4] . "</td>";
echo "<td bgcolor=\"#fbe383\">" . $row[5] . "</td>";
echo "<td bgcolor=\"#f7e8ab\">" . $row[6] . "</td>";
echo "<td bgcolor=\"#fbe383\">" . $row[7] . "</td>";
echo "</tr>";
}
echo "</table>";
echo "<br>";
echo "<p style=\"font-size:10px;\">Jest to e-mail wygenerowany z systemu CRM. Prosimy na niego nie odpowiadać</p>";
?>
脚本 sender.php 向用户发送数据:
<?php
include 'connect.php';
//connect with database
$polaczenie = @new mysqli($host, $db_user, $db_password, $db_name);
//set charset to show polish letters
$polaczenie->set_charset("utf8");
//check connection
if ($polaczenie->connect_errno!=0)
{
echo "Error: ".$polaczenie->connect_errno." Opis: ". $polaczenie->connect_error;
}
else
{
//define id variable
$sqlid = 1;
//select emails for user with id = sqlid
$zap = "SELECT email from db_users where id = '{$sqlid}'";
//make query (for while loop)
$zapt = $polaczenie->query($zap);
//while there are some data, make instructions in loop
while (($zapt -> fetch_assoc()) !== null)
{
//there are results
//execute query again (without this loop do not work properly)
$zap = "SELECT email from db_users where id = '{$sqlid}'";
//show email and save to variable rowxx
$zapx = mysqli_query($polaczenie,$zap);
while ($rowx = mysqli_fetch_assoc($zapx)) {
print_r ($rowx);
$rowxx = $rowx["email"];
}
//include content of tabela.php
ob_start();
include "tabela.php";
$content = ob_get_clean();
//define mail headers, subject and message
$od = "From: itest@mail.pl \r\n";
$od .= 'MIME-Version: 1.0'."\r\n";
$od .= 'Content-type: text/html; charset=utf-8'."\r\n";
$to = $rowxx;
$subject = "Raport szans";
$message = $content;
if(mail($to, $subject, $message, $od))
{
echo "Mail sent!";
}
else
{
echo "Error with sending!";
}
$sqlid++;
$zapt = $polaczenie->query($zap);
}
//else
echo 'No results';
$polaczenie->close();
}
?>
脚本工作正常,但是对于具有最后一个 ID 的用户发送一封邮件太多。如果有 4 个用户,最后收到两封邮件而不是一个 - 第一个有正确的数据,第二个没有数据(空表)。sender.php 脚本的输出是:
Array ( [email] => ika1@mail.pl ) Mail sent!Array ( [email] => pb1@mail.pl ) Mail sent!Array ( [email] => rr1@mail.pl ) Mail sent!Array ( [email] => pr1@mail.pl ) Mail sent!Mail sent!No results
所以我看到在上次“发送的邮件”中没有电子邮件地址,但我在 pr1@mail.pl 上收到了它。为什么?
解决方案
所以我对我的代码进行了更改(简化了 while,删除了嵌套的 while,并在 while 循环中更改了查询的顺序,这是发送一封邮件过多的原因),现在它工作得很好,但我仍然不知道 SQL 注入是如何可能的,因为没有 POST 或 GET。
<?php
include 'connect.php';
//connect with database
$polaczenie = @new mysqli($host, $db_user, $db_password, $db_name);
//set charset to show polish letters
$polaczenie->set_charset("utf8");
//check connection
if ($polaczenie->connect_errno!=0)
{
echo "Error: ".$polaczenie->connect_errno." Opis: ". $polaczenie->connect_error;
}
else
{
//define id variable
$sqlid = 1;
//select emails for user with id = sqlid
$zap = "SELECT email from db_users where id = '{$sqlid}'";
//make query (for while loop)
$zapt = $polaczenie->query($zap);
//while there are some data, make instructions in loop
while ($rowx = $zapt -> fetch_assoc())
{
//show e-mail recipient (for debug only)
print_r ($rowx);
$rowxx = $rowx["email"];
//include content of tabela.php
ob_start();
include "tabela.php";
$content = ob_get_clean();
//define mail headers, subject and message
$od = "From: itest@mail.pl \r\n";
$od .= 'MIME-Version: 1.0'."\r\n";
$od .= 'Content-type: text/html; charset=utf-8'."\r\n";
$to = $rowxx;
$subject = "Raport szans";
$message = $content;
if(mail($to, $subject, $message, $od))
{
echo "Mail sent!";
}
else
{
echo "Error with sending!";
}
//increment sqlid
$sqlid++;
//execute query again
$zap = "SELECT email from db_users where id = '{$sqlid}'";
$zapt = $polaczenie->query($zap);
}
//else
echo 'No results';
$polaczenie->close();
}
?>
推荐阅读
- sql - 缩短长嵌套子查询 (Oracle)
- highcharts - 基于美国位置的纬度和经度的 Highmap 不支持 highmap.js 文件
- python - Jupyter 笔记本和脚本在同一个包中?
- ios - Swift 泛型和协议:从静态函数返回专用类型?
- c# - 如何从序列化类 C# 中获取数据?
- python-3.x - 找不到满足要求的版本torch>=1.1.0(来自torchvision==0.3.0)
- css - 如何确保 HighStocks 图表 x 轴跨越整个图表宽度?
- reactjs - 为什么在 Electron 应用程序中 React Context API 行为异常?
- readline - can't read logger message thru socat -u UNIX-RECV:/dev/log,ignoreeof - 因为没有新行
- react-native - Expo Firestore上传base64不起作用