Deal with queued email messages after send from Azure Function

[jeffputz]

When you're running Functions, a queued message isn't deleted after it's sent. There should be some decision about what do with that: Either delete the message or flag it as failed. Maybe even periodically delete them after a certain age.

[senadburak]

Hi Jeff, I spent many hours trying to fix the problem wich is related to this I believe. At the and I found that the following modification gave a solution: QueuedEmailMessageRepository:

	public async Task DeleteMessage(int messageID)
	{
		await _sqlObjectFactory.GetConnection().UsingAsync(connection =>
			connection.ExecuteAsync($"DELETE FROM pf_QueuedEmailMessage WHERE MessageID = {messageID}"));
			//connection.ExecuteAsync("DELETE FROM pf_QueuedEmailMessage WHERE MessageID = @MessageID", new { MessageID = messageID }));
	}

	public async Task<QueuedEmailMessage> GetMessage(int messageID)
    {
		Task<QueuedEmailMessage> message = null;
		await _sqlObjectFactory.GetConnection().UsingAsync(connection =>
			message = connection.QuerySingleOrDefaultAsync<QueuedEmailMessage>($"SELECT MessageID, FromEmail, FromName, ToEmail, ToName, Subject, Body, HtmlBody, QueueTime FROM pf_QueuedEmailMessage WHERE MessageID = {messageID}"));
			//message = connection.QuerySingleOrDefaultAsync<QueuedEmailMessage>("SELECT MessageID, FromEmail, FromName, ToEmail, ToName, Subject, Body, HtmlBody, QueueTime FROM pf_QueuedEmailMessage WHERE MessageID = @MessageID", new {messageID}));
		return await message;
	}

Conclusion: for some reason the original solution didn't work on a production server. The problem was that the parameter new { MessageID = messageID } in th eoriginal solution produced an error "... must declare @MessageID...."). After I implemented the above changes, all went OK, ie. e-mails were sent, the queued messages were deleted.

[jeffputz]

@senadburak Your problem isn't related, but I also don't see what your problem was. You absolutely want to avoid doing non-parameterized queries whenever possible because ad-hoc queries don't have cached execution plans and are vulnerable to injection attacks.

The original issue here is that functions just never call delete.

[senadburak]

I understand, you are right with the possible injection etc.

However, I must repeat that before my changes, the function was called, but it produced an error and therefore didn't delete the queue.

After the changes it did executed without error and the records WHERE deleted. This was happening on the production server. On my local machine – no. Perhaps it is something with cashing (?)

From: Jeff Putz Sent: petak, 15. novembar 2019. 15:25 To: POPWorldMedia/POPForums Cc: senadburak; Mention Subject: Re: [POPWorldMedia/POPForums] Deal with queued email messages aftersend from Azure Function (#145)

@senadburak Your problem isn't related, but I also don't see what your problem was. You absolutely want to avoid doing non-parameterized queries whenever possible because ad-hoc queries don't have cached execution plans and are vulnerable to injection attacks. The original issue here is that functions just never call delete. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[jeffputz]

If it says there's a missing parameter, the error is the parameter is missing. That's not the fault of the SQL code, it has to be a problem elsewhere in the stack, or you're mistaken. I have the code running in production on an Azure function and it dequeues and sends the messages a hundred times a day.