Can I pass data from an email body to a batch file?

I would like to be able to pass two or three values from the body of an email into a batch file. Is it possible to pass variables through to a batch file?

I can control the email text so that it creates a clickable hyperlink that can be run by the user but ideally I would like the hyperlink to pass values through to the batch file.

i.e. in the email there's an entry which says StudentID=4444 how do I declare the 4444 as a variable that can then be used by the batch file?

Many thanks


Detail:
I work in a large school and whilst the registration process is pretty good in our MIS system I need to be able to quickly discover whether a child is actually at school, who hasn't registered. We have a process that checks against the SQL Registration table and emails all teachers of that student to ask if they have turned up to their lesson. It's a lot of manual work to then collate that information and manually enter it into the MIS system,

What I was hoping was to include in the email body a clickable link that will pass the students ID out to a batch file that will then run a new SQL update and directly update the table for that record. I have no problem calling the SQL cmdlet and updating the table but I don't know how to pass variables out to a batch file that can then be used. I've read its possible but can't find any good examples.
itmtsnAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You can't do that as a link. Usually you create an URL with a dynamic part which describes the parameter values, something like
   http://miscontrol/attendance.php?StudentID=4444
but that requires an Intranet website with code, and there is a security risk...

Of course you can use local VBA code if using Outlook to run code with parameters.
itmtsnAuthor Commented:
Thanks Qlemo :)

So I could achieve this by VBA in Outlook? Any pointers of where to start with VBA parameters in Outlook? I can see this snowballing into a project :)
Julian HansenCommented:
I see three options

1. AS Qlemo suggested an intranet server - not sure what the security risks are - it is a very common solution to update databases in this manner.

2. Allow for a mailback response - mail is sent to a mailbox which has a process watching it - a lot more work

3. VBA code that runs in Outlook - also more work than the intranet solution.


Instranet solution you mail out a page with

Was John Smith at school today?
YES   NO

Yes is linked to http://internalserver/studentattendance/XXX-ONE_TIME_KEY-XXX/YYYY/YES
No is linked to http://internalserver/studentattendance/XXX-ONE_TIME_KEY-XXX/YYYY/No

The one time key ensures that the link can only be clicked once and is required for the script to process the request. It also identifies the teacher.
YYYY identifies the student

The URL can vary based on your backend solution but the principle remains the same - call a specific function (studentattendance) with a one time key, a student ID and an action.

The rest is a simple server side script that runs a SQL query.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Of course the generated, "unpredictable" token is key. I agree with that it is probably the best and least effort approach to use an Internet server and links.
Bill PrewIT / Software Engineering ConsultantCommented:
Brainstorming here with the other experts, but another approach could be to attach a VBS script to the email, and in the script do a RUN to the BAT script desired, passing needed command line parms, right?

Or, another approach could be to use one of those free utilities that can package a BAT file as an EXE, and attach that to the email.  It could then run the desired BAT script passing it parms.

Both of these depend on the email client allowing VBS or EXE execution from the email, which is often blocked by default, but can be changed in settings.  In addition it requires the email viewer to open the attachment rather than just click a link.

~bp
NVITEnd-user supportCommented:
@itmtsn

Instead of email, how about pushing  the bat to the teacher's desktop?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Or sending back and forth an Excel file with the pupils and a flag for presence/absence?
Julian HansenCommented:
Brainstorming here with the other experts, but another approach could be to attach a VBS script to the email, and in the script do a RUN to the BAT script desired, passing needed command line parms, right
Smacks of a massive security hole - Outlook by default is going to block script and EXE -anything that can execute - and you don't really want to open up that - you are going to just be asking for trouble.

The idea here is KISS - least hassle greatest accuracy one click and forget that works and takes 0 time to do, relatively little time to setup and does create more problems than it solves.

When you start playing in the in Outlook scripting / executing space the landscape becomes very unstable.

In a school setup I would lock my network down to the nth degree (given the exploring and ingenious antics of the young mind) and will resist any action that opens even the slightest chink of a security breach.

Just my 2c worth /
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I agree about locking down as far as possible. A school is a hostile environment ;-).
I'm not certain Julian recommended a mail with a bunch of links (one for each pupil), or a link to a page containing links. Only the latter is useful, as the other would open 20 browser tabs...
frankhelkCommented:
About the approach of reading some values out of an email: That's really possible.

An email in its real form is just some kind of plain text file, which might be somewhat complicated, but the most common things could be circumvented easily.

Since I've done complex processing of plain text files in Excel, I could tell you that it's not that complicated to manage that in any VBA flavour ... and calling out to a batch file from Outlook is not complicated, either.

But there's no need for that ... Outlook could be forced by filters (or events) to process Email messages with VBA code. The access to any part of the message (header fields, body, attachments, ...) is more or less simple. And since VBA could access your database with ODBC, your VBA code could analyze the email message and stuff the results into your SQL database.

In a simple approach I would send out emails with "YES" and "NO" coded into mailto: links with precoded subject lines. The teachers answer by clicking the links. The answer's subject line would contain some kind of answer ticket along with the coded answer.

The receiving outlook would analyze the mail message and stuff the result into the database. The ticket would make fraudulent emails a bit more harder to generate.
Julian HansenCommented:
@frankelk,

As a matter of interest - how would you distribute the VBA code?
frankhelkCommented:
no need to distribute - the code is IMHO only needed when receiving the answer emails, and - maybe - you could knit something for sending the question emails out. Both could be run on one Qutlook instance, and probably be transferred als simple code modules to Import/Export.

The rest is a matter of mailto: links in the question emails.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
mailto: does not send the mail, just opens a new email window...
Julian HansenCommented:
The reason I ask is distribution is always key - how to get code to where it needs to be and more importantly - how to ensure that channel is not used by malicious parties to distribute unwanted code.

We are talking about a school and I speak from experience when I say you cannot take anything for granted. The kids know more than those administering the IT infrastructure and you open just a small window and they are in there faster than you can blink.

Hence distribution in my view is key. All sounds good on paper but when it comes down to getting it to the right parties running on a heterogeneous hardware set the problem space is far from simple.

An intranet link eliminates all of these issues.
frankhelkCommented:
@Qlemo:

Right - it just opens a new email, but usually it could be equipped with a preset subject.

Doing that in the right way, the subject could hold some encoded - unique - info which would be hard to reproduce ... when that mail is received by the server, it could extract the needed info.

Example ... just outline of an idea:

The server sends out a request to the teacher, and it contains YES/NO mailto-links with predefined subjects (maybe intranet links, too, same intention but same way, would be a bit more secure if a login is involved). The subject lines (or link-formdata) consist of (or contain) a request ID and a cryptic code. The server generates that code unique for that request, i.e. generated by concatenating request ID (an INT would do), kid's name and birthdate, member ID, date and time of request, answer (YES/NO) and a random number/string together and generating a MD5 checksum from it. The server stores both codes for yes and no along with the request data as dataset into a (internal and therefore secret) table.

When the teacher answers the request, request ID and checksum of the answer is transmitted to the server. The server extracts the answer (checks for ID and answer code) and takes its actions. Afterwards it clears the request from the table. When a request is not answered that day, the server presumes a "NO" and processes that ...

This is secure, because the answer codes could not easily be predicted, and they are only valid for one distinct request ... as long as each teacher's request contains other codes.

And by the way: No real code is to be circulated outside of the server ... anything outside is email with cryptic codes and probably logins (which are as secure as the persons who use them ...)
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I would hate having to send 20 emails, and prefer a single Intranet site...
Julian HansenCommented:
... essentially the same as proposed above - except using onetime key links instead of going through the process of creating a new mail and sending it.

Trying to see the benefit of using the mailto here - seems like a whole bunch of extra work but with no real added benefit.
frankhelkCommented:
@Qlemo:

You wouldn't have to send out 20 emails ... if done the right way, it would be mostly "hands-free".

The server keeps track of the kids registrations. Probably it knows in what courses a kid is and which of 'em are scheduled on that day. It could know who is already on the "rightful absent" list (sick, on a external learning event, etc.).

When the deadline is over, it generates a list of all unexplained missing registrations. With the knowledge of which teacher's courses the  kid should attend, it generates a list of teachers to ask and automagically generates the emails.

The "single intranet site" poses a risk of being hacked into and do false reporting regulary. With the email approach the procedure involves a additional hurdle.

BTW: With some extra effort, one could knit some kind of smartphone app or PC software. It could (secured by credentials) fetch the mails, process them, pop up the questions and call the intranet server (with background login measures). That would simply reduce the teacher's work to log in and tap some yes/no buttons. Since the app would hold no critical code and could communicate encrypted and login secured, there's little risk of having the interface hacked.

And since the intranet server that processes the anwers has no web GUI interface (REST only), that part would be secure, either. With a login procedure the questions could even be by the app via the REST interface (no need for email).

There's a lot of possibilities here ... but the most possible security leak would be the passwords of the teachers. For fixing that it would get complicated ... (handies with fingerprint sensors, keycode dongles, etc. ...)
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Frank,

you need a definitive confirmation. "Lack of" cannot be used. And you usually check at the beginning of a class, so there is no "deadline". Also, each mailto: creates a new mail, as said - if you code the attendance info for one kid into the subject, you need 20 mails. Nothing automated there, the "send" button still needs to get pressed. Anything else needs VBA code, which we wanted to avoid, IIRC.

If I were in that shoes, I would like to either
a) have an Excel table posted with the email, which I put a mark on presence or absence,
b) have a coded, one-time link posted directing me to interactive HTML code I can do the same as in the Excel table.
You need a visual feedback to see which kids you checked already, so "not checked", "absent", "present" should be the valid options visible immediately.

Of course we could also just have a mail containing all kids expected to be there, and the teacher answers by removing all present kids, or move their "links" to a different location in the mail, or something like that.
frankhelkCommented:
OK ... after having meditated a bit about the subject, I agree to not using email ... at least not in the sense of using a regular mail client for that.

But the more I think about that, I'd prefer some network based but not HTML solution.

Thinking about that environment I'd think about some smartphone/tablet app that communicates with the server, basically on the mechanism I've outlined above. With checking every class when starting up, the server knows the course the teacher gives at that time (or the teacher chooses from a list) an the server sends out a list of enlisted kids (and the cryptic tokens for the answers) to the app.

The app then displays the names one by one to the teacher, along with buttons, like

Doe, John, *2001/12/24
[present]
[absent, with excuse]
[absent, unexplained]
[not checked]
[postpone check]

Open in new window


The teacher checks the presence one by one (with one tap for each kid) and the app returns the answer codes back to the server. When the check is postponed (for latecomers with a hangover), the codes could stay in the list for another x minutes - while the teacher is able to record their presence - and will be reported absent after the hangover period.

Opening the app would require a password, which is to be checked by the server over secure sockets, and after the hangover period and subsequent processing the app closes automatically.

Communicating with the server would require a socket based, encrypted REST interface.
Julian HansenCommented:
but the most possible security leak would be the passwords of the teachers
Passwords won't work - that is why the suggestion of a one time key is the right answer.

The server generates a one time key that corresponds to a teacher and student. A mail is auto generated and sent to the teacher with a YES and NO link

http://server/restful/service/yes/onetimekey
http://server/restful/service/no/onetimekey

When either of those is clicked it records the click and disables the one time key (which would probably just be a server generated GUID). When the server creates the key it stores it along with the teacher and student it is for.

Receiving end can be a very simple RESTful end point that accepts the incoming GET and processes as per the above.

You don't need an app for this - you just work of the two links.
frankhelkCommented:
The one time key would be right for email, because the key reaches the teacher on that way (more or less secure). On the other side, if somebody hacks the email account, the one-time keys are disclosed and could be used by that culprit (I know that they're obsolted afterwards, but that's a risk either way). But it would break the system nontheless if i.e. the culprit just fetches all emails and reports everyone as present ... or absent.

When the app I proposed logs into the server with name/password, the server could issue a token that could be used to secure the subsequent communication for a class check. Afterwards the token expires. And there are ways to obfuscate the credentials in an nondecryptable way (i.e. the server sends a random text, the app conactenates that with name and password hash and makes an MD5 of that ... that would be the token, which is secure and non-decodable).

And - by the way - the "request ID and a cryptic code" thing I mentioned above would resemble the one-time-code you refer to ...

Overall, the app thing would implement more security due to the fact that fetching the codes doesn't go thru a standard protocol that could be intercepted by a standard client with credentials.
itmtsnAuthor Commented:
Guys, thanks so much. I really appreciate all the feedback and its very interesting watching the discussion from the sidelines.

Security is critical as you've all suggested and we do have issues here with passive hackers (little darlings) so I'm very aware that whatever solution we use is as hack free as possible. We do have a pretty secure system but they obviously love to try and find holes.

I like the sound of the intranet server and to be honest I'm thinking that I don't even want the teacher to click the Yes link but just reply Yes to the Registrations secretary and the secretary can then click the Yes link. We currently only allow her and the DB admin the ability to update certain tables in SQL based on their AD credentials and even then the routines are hidden away on PCs not accessible to students.

I also only really need the YES as the email goes out to 10+ different teachers, has this student been in your lesson, we don't really care about the NOs.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
AFAIK the common practise is that the students are in the class, so you will have a majority of "Yes" :D. And what about if someone is missing from the list? Should not happen, but ...
frankhelkCommented:
Well ... I only lineout some concepts, the final solution should be well discussed with experts anyhow.

The concept could be adapted, twisted and bent to fit  as you like or need it.

To come back to the email at its most simple flavour, you could simple send out an email with a one-time-key in the subject, like

Subject: [1A1234XYZ] Please reply if John Doe, *2001/12/14 im your class !

The body could be empty.

The teacher simply replies and lets the subject untouched, only if YES.

On the recipient side you could set up a minimalistic (.NET) email client program that reads thru the email headers, finds the subject line, and extracts the key. Then it acts accordingly. Most of the functions needed are part of the .NET runtime.

That would be hands-free on the receiving side and very simple for the teacher. Anyhow that could be easily intercepted if somebody breaches into your mail server and answers and deletes one of the emails to get an easy day off ;-)
Julian HansenCommented:
The problem with the above suggestions - sending an email and mailing the secretary is that email is the easiest thing in the world to hack. To send an email looking like it came from someone other than you is a case of just knowing the email address.

Having an application out there that does a secure logon to the DB in my mind is just crazy - exposing your DB to outside access like that - just better know what you are doing in terms of locking it down - but I certainly would not do it that way.

Mailing the secretary - that is going to be a lot of work for the person in question 20 teachers 30 kids in a class - 600 emails / links for secretary to process.

The way I see it you want to know the NO's - so as suggested in an earlier post - assume all present and only send out one email with each child's name in it - the name is a link back to the server - if clicked it is a NO - link has a onetime key.

As for a a man in the middle attack sniffing the one time key - here is the thing - if a no is a click and a yes is not then the only thing to be concerned about is that the link is clicked and man in the middle blocks the link - snatching the OTK does nothing for him/her in this scenario.

The above results in  0-5 clicks per teacher per day for the No's

At the end of the day we are talking about an attendance register not state secrets - the key thing with the system is to keep the integrity of the network / database intact and not expose to much.
itmtsnAuthor Commented:
Sorry just to be clear this is to deal with any boys that haven't registered.

The boys should be using biometric/pincode devices to register both AM and PM but there's always a number that forget/have gone home ill/have stayed home sick etc etc

The Registration secretary has to account for every boy so there's always 20-80 students unaccounted for. We've simplified the process of her having to manually email all the teachers to find out whether boys are in their lesson by automating the process with a nice SQL cmdlet that emails the teachers. That's why its only the YES's that we're interested in.

If nobody answers YES in a timely manner then the student needs to be located more urgently. We're just trying to alleviate a great deal of the manual inputting of the YES by automating it through an internal process.
Julian HansenCommented:
Then same as NO solution - YES / NO does not really matter - link is clicked and it means something - you can define what that is.

So as I understand it system scans db for students that have not "checked in". It produces a list and sends off a "Do you see him" email.

Teacher clicks I see him or ignores email.

Live reporting shows number of students who have not checked in - should get smaller over time after email is sent - leaving you (after specified time period) of those unaccounted for.
frankhelkCommented:
@Julian Hansen
The problem with the above suggestions - sending an email and mailing the secretary is that email is the easiest thing in the world to hack. To send an email looking like it came from someone other than you is a case of just knowing the email address.

I agree to that. Part of that would be addressed by the email subject (which is even in the reply) contains a one-time key, so just sending an email to the secretary won't work - the key is missing.

The email with key thing is insecure as soon as somebody breaches into a teacher's account on the mail server.

Having an application out there that does a secure logon to the DB in my mind is just crazy - exposing your DB to outside access like that - just better know what you are doing in terms of locking it down - but I certainly would not do it that way.

I didn't speak of a direct login into the DB ... I would set up some PC box in the DMZ with a web server (i.e. ASP) that services the app requests (creating keys, etc.) and does the final DB stuff by connecting to the (secure) server. The app would communicate with that server thru secure sockets.

A pro of that would be that the communication is non-standard. Another pro: The handling of the replies would be fully automatic. The app could be even poll regulary for status ("are there requests ?"), ring if any and open with a password only for D/L the requests.

So the secretary would just mark the kids in question on the list and trigger the requests, and automagically the phones of the teachers ring. When one answers "Yes", the name goes off the list.

The precise algorithm needs to be lined out as needed, but such an app for the teachers would be (IMHO) the most versatile and safe measure.

BTW: The NO's might be useless on first sight, but when tracking a kid it might be important if all questioned teachers have answered (if all have replied, the search could go on w/o loosing time ...)
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
My opinion: All confirmations should be three-state (no, yes, no reply yet). That reveals much more than only getting one kind of answer.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itmtsnAuthor Commented:
Thanks guys, have split the points three ways as I think that's the fairest way. I it was quite a detailed conversation that you all contributed to.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.