Once a day email notification

PHP
1

I’m working with this web script, which has internal chat/messaging between users. I’ve added (with help) the ability to successfully send an email notification, to a User when a chat/message is waiting for them (emails/message-alert).
But, sending an email upon every chat message, would probably be too many emails. So, I’m looking for a solution, that only would notify a User once a day that a message is waiting (unless you have a better idea). So, I guess I might need help with a cron job code? I look forward to any assistance. Here’s the current code:

<?php
if (IS_LOGGED == false) {
    $data = array(
        'status' => 400,
        'error' => 'Not logged in'
    );

    echo json_encode($data);
    exit();
}

function sendNotif($a)
{
try
{
$db = new PDO('mysql:host=localhost;dbname=real...1', 'real...11', 'password');
}
catch (Exception $e)
{
        die('Erreur : ' . $e->getMessage());
}

$query=$db->prepare("SELECT * FROM users   WHERE   id = $a");
 $query->execute();
 $data=$query->fetch();
 return $data['email'];
}

function sendNotifuser($a)
{
try
{
$db = new PDO('mysql:host=localhost;dbname=real...1', 'real...11', 'password');
}
catch (Exception $e)
{
        die('Erreur : ' . $e->getMessage());
}

$query=$db->prepare("SELECT * FROM users   WHERE   id = $a");
 $query->execute();
 $data=$query->fetch();
 return $data['username'];
}

if ($first == 'new') {
	if (!empty($_POST['id']) && !empty($_POST['new-message'])) {
		$link_regex = '/(http\:\/\/|https\:\/\/|www\.)([^\ ]+)/i';
	    $i          = 0;
	    preg_match_all($link_regex, PT_Secure($_POST['new-message']), $matches);
	    foreach ($matches[0] as $match) {
	        $match_url           = strip_tags($match);
	        $syntax              = '[a]' . urlencode($match_url) . '[/a]';
	        $_POST['new-message'] = str_replace($match, $syntax, $_POST['new-message']);
	    }

		$new_message = PT_Secure($_POST['new-message']);
		$id = PT_Secure($_POST['id']);
		if ($id != $pt->user->id) {

			$chat_exits = $db->where("user_one", $pt->user->id)->where("user_two", $id)->getValue(T_CHATS, 'count(*)');
			if (!empty($chat_exits)) {
				$db->where("user_two", $pt->user->id)->where("user_one", $id)->update(T_CHATS, array('time' => time()));
				$db->where("user_one", $pt->user->id)->where("user_two", $id)->update(T_CHATS, array('time' => time()));
				if ($db->where("user_two", $pt->user->id)->where("user_one", $id)->getValue(T_CHATS, 'count(*)') == 0) {
					$db->insert(T_CHATS, array('user_two' => $pt->user->id, 'user_one' => $id,'time' => time()));
				}
			} else {
				$db->insert(T_CHATS, array('user_one' => $pt->user->id, 'user_two' => $id,'time' => time()));
				if (empty($db->where("user_two", $pt->user->id)->where("user_one", $id)->getValue(T_CHATS, 'count(*)'))) {
					$db->insert(T_CHATS, array('user_two' => $pt->user->id, 'user_one' => $id,'time' => time()));
				}
			}

			$insert_message = array(
				'from_id' => $pt->user->id,
				'to_id' => $id,
				'text' => $new_message,
				'time' => time()
			);

			$insert = $db->insert(T_MESSAGES, $insert_message);
			if ($insert) {
				$pt->message = PT_GetMessageData($insert);
				$data = array(
					'status' => 200,
					'message_id' => $_POST['message_id'],
					'message' => PT_LoadPage('messages/ajax/outgoing', array(
						'ID' => $pt->message->id,
						'TEXT' => $pt->message->text,
						'USERNAME' => sendNotifuser($id)
					))
				);

				$link = $email_code . '/' . $email;
				$send_email_data = array(
				'from_email' => $pt->config->email,
				'to_email' => sendNotif($id),
				'to_name' => sendNotifuser($id),
				'subject' => 'Message Waiting',
				'charSet' => 'UTF-8',
				'message_body' => PT_LoadPage('emails/message-alert', array(
						'USERNAME' => sendNotifuser($id)
					)),

				'is_html' => true
				);
				$send_message = PT_SendMessage($send_email_data);
		}
	}
}
}

if ($first == 'fetch') {
    if (empty($_POST['last_id'])) {
		$_POST['last_id'] = 0;
	}

	if (empty($_POST['id'])) {
		$_POST['id'] = 0;
	}

	if (empty($_POST['first_id'])) {
		$_POST['first_id'] = 0;
	}

	$messages_html = PT_GetMessages($_POST['id'], array('last_id' => $_POST['last_id'], 'first_id' => $_POST['first_id'], 'return_method' => 'html'));

	if (!empty($messages_html)) {
		$html = PT_LoadPage("messages/{$pt->config->server}/messages", array('MESSAGES' => $messages_html));
	} else {
		$html = PT_LoadPage("messages/ajax/no-messages");
	}

	$users_html = PT_GetMessagesUserList(array('return_method' => 'html'));

	if (!empty($messages_html) || !empty($users_html)) {
		$data = array('status' => 200, 'message' => $messages_html, 'users' => $users_html);
	}
}



if ($first == 'search') {
	$keyword = '';
	$users_html = '<p class="text-center">' . $lang->no_match_found . '</p>';
	if (isset($_POST['keyword'])) {
		$users_html = PT_GetMessagesUserList(array('return_method' => 'html', 'keyword' => $_POST['keyword']));
	}

	$data = array('status' => 200, 'users' => $users_html);
}

if ($first == 'delete_chat') {
	if (!empty($_POST['id'])) {
		$id = PT_Secure($_POST['id']);
		$messages = $db->where("(from_id = {$pt->user->id} AND to_id = {$id}) OR (from_id = {$id} AND to_id = {$pt->user->id})")->get(T_MESSAGES);
		$update1 = array();
		$update2 = array();
		$erase = array();
		foreach ($messages as $key => $message) {

			if ($message->from_deleted == 1 || $message->to_deleted == 1) {
				$erase[] = $message->id;
			} else {
				if ($message->to_id == $pt->user->id) {
					$update2[] = $message->id;
				} else {
					$update1[] = $message->id;
				}
			}
		}

		if (!empty($erase)) {
			$erase = implode(',', $erase);
			$final_query = "DELETE FROM " . T_MESSAGES . " WHERE id IN ($erase)";
			$db->rawQuery($final_query);
		}

		if (!empty($update1)) {
			$update1 = implode(',', $update1);
			$final_query = "UPDATE " . T_MESSAGES . " set `from_deleted` = '1' WHERE `id` IN({$update1}) ";
			$db->rawQuery($final_query);
		}

		if (!empty($update2)) {
			$update2 = implode(',', $update2);
			$final_query = "UPDATE " . T_MESSAGES . " set `to_deleted` = '1' WHERE `id` IN({$update2}) ";
			$db->rawQuery($final_query);
		}

		$delete_chats = $db->rawQuery("DELETE FROM " . T_CHATS . " WHERE user_one = {$pt->user->id} AND user_two = $id");
	}
}
?>
2

Before you think about doing anything next, do you think this is a proper way to use prepared statements? You might want to fix this before you move onto the next step. I wouldn’t worry about people not getting emails at this point. Highest priority is to fix this jaw dropping security hole.

2 Likes
3

Rather than “probably”, why not give a few options? eg.

  • Never
  • Weekly
  • Daily
  • Always
2 Likes
4

Thank you for your message. Greatly appreciated.
That is the “help” i received, and didn’t know it is a 'security hole". Thanks for letting me know. Can you explain how thta is a security hole, for my own education? And can you suggest/provide an alternative? Much thanks

5

Can you provide a code example of how to 'give a few ‘options’?

6

This is literal translation for SQL. This means that anyone can do something like

1'); DROP TABLES users;

Meaning that it will delete the table called users without giving a care in the world for what will happen.

So this is how it will read.

SELECT * FROM users WHERE id = 1'); DROP TABLES users;

And since DROP TABLES is an actual SQL command, it will delete the table users. Or even worse, what if they use DROP DATABASE? Now all of your databases will be deleted.

Alternative? There’s no alternative for this. The only thing you really need to do is learn how to properly use prepared statements. As the code suggests, you have not demonstrated this yet.

7

Thank you for your kind, informative reply.
The code is in a php file, how can someone change the code in that file to do those things?

8

They don’t need access to the PHP file itself. They only need access to a poorly implemented query. Any kind of user input command will take in whatever the user inputs. The $a in your SQL query does exactly just that. This isn’t strictly for SQL commands only. This can happen with any kind of PHP command as well. Take for example, the eval() command. People have found that the eval() command is unsafe. Since users can put anything into this command, it will execute via PHP. Here’s a great example of what I am talking about for user inputs using the common $_POST command.

$a = $_POST['text_field'];
print $a;

Do you think I need to have access to the physical file itself to change any content within it? No I don’t. Since $_POST takes in user input, this will no matter what, allow me (the user) the ability to do whatever I want and put whatever I want in that $a variable.

Hint: This is leading to the solution towards your security hole.

9

Thanks for your kind explanation. Much appreciated. However, obviously I will need to find more than a hint, to remedy this. Any additional guidance will be welcomed

10

Look up “Prepared Statements”.

11

I would probably use the method that seems to be popular in forum software - send them an email when they get a chat message, but don’t send any more until they’ve been back to the site. The wording in such emails often indicates that there has been activity, but that the user won’t get another email no matter how much more activity there is, until they visit the site. So basically the email indicates that there has been at least some activity, but not how much there was.

When the user gets a chat message, check to see whether their “already notified” flag in their members profile is set. If it is not, send them the email, and set the flag. If it is, do not send the email. When the user next visits the site to read their chat messages, clear the flag.

You could probably store the date that the notification was sent, and have something that runs periodically and sends a reminder to anyone who hasn’t responded within a certain amount of time.

1 Like
12

Is this better?

$query=$db->prepare("SELECT * FROM users WHERE id = :val1  ");
$query->bindValue(':val1', $a, PDO::PARAM_STR);
$query->execute();
$data=$query->fetch();
return $data['email'];
}
1 Like
13

Yes, that’s great. Can you provide some bit of a code example to get me started?

14

Yes, you are now properly using prepared statements.

15

Thank you for your positive reply.
However, now this part does not work:


function sendNotifuser($a)

{

try

{

$db = new PDO('mysql:host=localhost;dbname=real...1', 'real...11', 'password');

}

catch (Exception $e)

{

        die('Erreur : ' . $e->getMessage());

}

$query=$db->prepare("SELECT * FROM users   WHERE   id = $a");

 $query->execute();

 $data=$query->fetch();

 return $data['username'];

}

I tried this without success:

function sendNotifuser($a)

{

try

{

$db = new PDO('mysql:host=localhost;dbname=real...1', 'real...11', 'password');

}

catch (Exception $e)

{

        die('Erreur : ' . $e->getMessage());

}

$query=$db->prepare("SELECT * FROM users WHERE id = :val1  ");
$query->bindValue(':val1', $a, PDO::PARAM_STR);
$query->execute();
$data=$query->fetch();
return $data['username'];

}

Any suggestions?

16

I personally don’t like using bindValue and bindParam. Just create an associative array with their variables associated with them and pass that array into the execute() function.

17

Thanks again, what you’re suggesting is a bit over my skill level

18

Not really. If using associative arrays is above your skill level, then I don’t think you should be touching database API just yet.

19

Any additional help will be appreciated

20

You can learn up on PDO here https://phpdelusions.net/pdo