Re: Receiving email into pl/sql
Date: Thu, 08 Nov 2007 08:30:17 -0800
Message-ID: <1194539413.603457_at_bubbleator.drizzle.com>
Lee wrote:
> DA Morgan wrote:
>> Lee wrote: >> >>> I've sent email from pl/sql with utl_smtp and with 10g's utl_mail; >>> but now I want to do the inverse, i.e. I want to READ email from pl/sql. >>> >>> The idea is to set up a dedicated email account. Users could send >>> stereotyped messages to that account, and the pl/sql routine would >>> read the mail, parse the messages and do the needful. >>> >>> As far as I can tell, utl_mail will send, but not receive email. >>> >>> I can think of some Rube Golberg workarounds but can anyone point the >>> way to a "no fuss" way to read simple text emails? >>> >>> >>> Thanks in advance >> >> >> If you think it is a good idea to send emails, across the web, from >> some Microsoft Outlook client directly into an Oracle database I am sure >> we can recommend a good 12 step program for you. >>
> As you yourself point out, the email winds up as data somwhere; so why
> would it be worse to send email to an automated agent than sending email
> to a human agent or just reading an input file as data?
Mail systems, not the Oracle database, contain filters that trap spam and a wide variety of diseases. In the database you would have to write your own: Reinvent the wheel.
> Why would reading email to drive a script from inside a stored procedure
> be more dangerous than running a static batch script or a script that
> takes input from a human or from a data file?
Were the only issue SQL Injection your point would be valid. But there is far more that could happen here than just that. Wouldn't be hard to create one heck of a good denial of service attack with email. I don't think you, and even 100 of your friends can type that fast.
> I assume you're concerned about sql injection attacks or maybe some sort
> of spam and/or spoofing, or even an attempt to "flood" the system a la
> DDOS attacks?
Exactly.
> Maybe I'm not being sufficiently imaginative or paranoid, but I cant see
> how the sort of scheme I'm thinking of is more dangerous than crossing
> the street. Everybody and his uncle has a listserv that runs on commands
> sent in by email, so why is that setting off alarm bells?
When walking across the street you, perhaps, have to watch out for one or two people trying to hit you. When you get on the internet you have every kid with a keyboard trying to take you out. The odds are not in your favor.
> I can use an http callout to get data from anywhere on the planet. There
> are "rest"-full web services, and SOAP interfaces and all sorts of ways
> to have all kinds of heaven-knows-what get presented as input. A routine
> that parses stereotyped email messages and deliveres canned reports in
> response seems pretty benign.
Not in 11g you can't. Oracle stuffed that security breach with the new DBMS_NETWORK_ACL_ADMIN package (ACL = Access Control List).
> Or am I living in a fools paradise?
You know the answer to that. <g>
>> Incoming emails are stored somewhere. Find the location. Read them using >> anything from UTL_FILE to whatever.
> My Oracle server is running on a different box from my email server, so
> the trick is to get the data from the mail server to the oracle box.
Routers, hubs, switches, networks, cat5 cable. You can get there is you are authorized to do so.
> Of course there is that proverb about the relative velocity of fools and
> fearful angels, so tell me more about why I could be stepping off a
> cliff here.
Look at the amount of effort put into database security by those companies that intentionally expose their databases to the public. Amazon.com, Ebay, etc. You want to duplicate that effort? Because clicking on an email client is too much work?
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Nov 08 2007 - 17:30:17 CET