I made PHP script to send report from MySQL database to users e-mails. Every user must recieve only their own data (with their id). Script tabela.php make html table with user content.
<?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>";
?>
Script sender.php send data to user:
<?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();
}
?>
Script works fine, but send one mail too many for user with last id. If there are 4 users, last receive two mails instead one - first with correct data and second with no data (empty table). The output of sender.php script is:
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
So I see that in last "Mail sent" there is no e-mail address, but I receive it on pr1@mail.pl. Why?
So I made changes in my code (simplified while, delete nested whiles and change order of queries in while loop which was reason for sending one mail too many) and now it works perfectly, but I still don't know how is SQL injection possible, because there is no POST or 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();
}
?>