View Full Version : Automated Updating of an Access Database

10-20-2003, 03:01 PM
I'm still working with my news managemenet script and this time I'd like to know what would be the best way to update the database automatically, for example in this situation I want to be able to set the Publish Date and Expiration Date of an news item.
And when the item has expired, I would like to change it's status to 'Archived' and possibly move it to a table called 'archives'.

So I am only asking for the best way to do this, not a complete script or anything :)

10-20-2003, 03:41 PM
The 'best way' depends on your requirement.
Do you want to archive them daily, monthly ? Anayway. To do this automatically, you need a CRON job (on a unix-server) or a scheduled taks (windows) that will execute a script at a specified interval. (run a search here or an google about them)
Inside the script, you then have an "update" or "select into" statement with the date in the condition. Like
sql="Select var1, var2, var3 into newtable from oldtable where expiartiondate <=" & Date

10-21-2003, 02:00 PM
I'm not sure I can use scheduled tasks because I'm using a free host and Scheduled tasks are pretty expensive (40/h on some host...). I'll probably move to premium hosting sometime this year... we'll see what I can do then. Thanks for the help raf!

10-21-2003, 02:37 PM
Hmm. A scheduled task can be fired of from any machine. Even from your own PC so maybe you can set it up there or ask someone to set up a cronjob that just request that one asp page...

An alternative is to add some code to your global.asa's Session_OnStart sub. Like this

if weekday(Now,2) = 1 then
if application("cleandate") <> date then
application("cleandate") = date
url = "thepagetorun.asp" & Request.ServerVariables("URL")
end if
end if

So each time a new session is started (each new client that request his first page), there is a quick check to see which day it is. If it's a sunday, a second check is performed to see if the page has already ran today. If not, the page is requested. If you would like to run this proces each day, you could use

if application("cleandate") <> date then
application("cleandate") = date
url = "thepagetorun.asp" & Request.ServerVariables("URL")
end if

more info here ( http://www.codingforums.com/showthread.php?s=&threadid=25953&highlight=SCHEDULED )

Inside that page that runs the update, you can then grab the page that the user requested, and transfer him to that page after the update.
Of course, this one unlucky first user of the day client will have a slightly higher responsetime on the first asp-page he requested from your application ...

10-22-2003, 06:14 AM
Plus you really have to think and plan using this kind of idea... I was going to use Session_OnEnd to clean up a specific database a bit, until I realized that someone might browse to the website, go have a cup of coffee, eat breakfast, or whatever, and then fill out a form.

At that point (especially if they took a bathroom break or something), their session would have expired, and nothing would have been entered into the database (although they filled out the form) since my "cleanup" script would have deleted their record at that point.

Yeah there are ways to fix this and perhaps get it working, even using the global.asa, (by checking how much time has expired, etc.), or better yet a DTS package (which is what I'll probably end up doing) but it's just a pitfall to look out for, and I thought I'd mention it.

Also I'm looking for input, (especially from GlennGV, raf, etc.) on some security issues I've been thinking about - it's easy to use javascript to modify form fields, etc. (even hidden ones!), and I thought it would be easy to use something like onchange="this.value = this.defaultValue", for instance... my logic is if you're using javascript (which you'd have to) to modify the form fields, that should stop it. And you can check other stuff to (hopefully) ensure that the form is being posted from your website.

If anyone is interested in hacking these ideas or has some input, let's get together and figure out the most secure way to deal with some of these issues! :D

10-22-2003, 09:35 AM

as you recall, we had this thread about security based on sessionvariables <--> sessions controlled by a db-table. to answer some request i got about adition information, i'm writing a quite large tutorial about sessions and the disadvantages of using sesions for security-issues + the alternatives.

The central argument is that IIS's sessionmanagement has other goals then securing a client-server dialog. Session timeout are there for performance reasons, not security.

I've pretty much written it out, i just need to insert some examplecode and db-design (i'm going to write a small demo-app and bundle the lott). If you like, i'll send it to you. It might hold some answers or get you thinking in a new direction.

I'm interested in brainstorming about your other security issues.

For the forms, javascript wount cut it. You need something serversided. I think the best way is to concatinate all values of the hidden formfield and then run an encoding or hashing function on it (i could write you one up, i think --> using your own function is the safest way). Then store the result of that function in a db-field.
When the form is posted, then run the concatenated values of the hidden formfield through the same function. If the result is different from the one in your db, then they altered the values. But you need a db for this ...
Well, you could use a hack around it by first storing it in a sessionbvariale, and in the session_onend write the sessionID and the sessionvariables value to a applicationvariable (an array). This requires that you also send the (encoded ?) sessionID in the form + that you vacume this applicationvariable from time to time.

But i hope to demonstrate that all security and site-navigation issues should be covered by a combination of server side coding and a db.

10-22-2003, 01:21 PM
It seems that I should ask my brother to set up a cronjob for me (he's the guru when it comes to these things...) or I just might use the scheduled task on my own pc as you also suggested.
You've been a great help once again! :thumbsup:

10-22-2003, 01:39 PM
Using onchange="this.value = this.defaultValue" isn't secured either. Malicious users can bypass it by typing this in the address bar:

10-22-2003, 03:42 PM
Yeah, I figured that much. Raf, you gave me a great idea there, I could use md5 on this stuff... then check it each time the form is posted.

Great idea.

10-22-2003, 07:01 PM
md5 would be quite solid (no idea if there are sha1() or sha2() implementations for ASP --> md5() seems to be compromised, seeing Visa explicitly mention it as a no-no)

but now i rethink it; there is no real need to hash or encode them. You could just as well store the un-encode concatenated values ...
Unless you are planning on sending the hashed value as e hidden formfield, but that wouldn't work because md5 is irreversable so you would pull them out of the db.

Thinking another bit further; there is no need to include them as hidden formfields since they are stored in the db. Just sticking the values together wth a seperator, and then using split() is probably the simplest and safest sollution.

10-23-2003, 04:45 AM
Hmm, this whole discussion actually brings up a new problem with the way we do stuff where I work. :)

It's not so much of a problem with someone being able to modify EVERY form field, or SQL injection attacks, since I have that aspect covered. If they put in bad data, so be it, as long as they aren't able to break the site or database. ;)

It's more of how to keep track of the individual using ONE hidden form field (and something server-side?), and making sure somehow that the user isn't able to modify the key ("id") field that is what we use to update records. I am pretty sure I can do exactly that, which leads to some potential security issues in my mind, at least.

I'm leaning towards a combination field check - for instance comparing the "id" value along with email (required), and only updating by the combination of those two values, perhaps... that way if a malicious user tries to modify that "id" hidden field, then I'd create a new "id" and update by the combination again... the end result being that some malicious user isn't able to update someone ELSE's record, which I believe they _can_ do right now if they're smart enough.

It seems that my colleagues haven't given some security issues much thought (their excuse is it's never happened - but how can you be sure about that?), so I want to come up with some feasible solutions to the issues that I'm finding, at least. ;)

I'm going to try it on a test db tomorrow.

10-23-2003, 08:59 AM
If you keep a session-table, then you don't need to send anything as a hidden formfield.
The problem you try to work around, is how to identify the user. There are basically 2 traditional options: IP or cookies. (IIS sessions use cookies, but they hold a sessionID which gets overwritten if the session times out). If you send your own cookie, then you can specify an expiration-date, that lies way in the future, and store some identification inthere. Like the PK value of that session in the sessions-table.
It's a lot harder to change the cookievalue + it's a lot harder to gues another valid PK value to replace it with. Inside your sessiontable, you can keep track of the user by storing the last requested page inthere. So a user should be able to:
- crack the cookie
- replace the value with that of another active session that also requested that form as the last page.
- do all that within the timout you specified and without any second chances --> one wrong request can end his session, if you want.
I mean, quite a slim chance. If you want to even make it harder, then you can encode the PK value with a sessionspecific seed, that you randomly generate and store inside the session-table. No way that they will be able to break that within the timelimit.
A third option is to use cookie or IP + some sort of encoded ticket (like in a Kerberos system). When the page is requested, you create a value (based on user + datetimevalue + seed. You encode that value and include it in the form as a hidden formfield + store it in the db. If the form is submitted, you check if there is a ticket + compare it to the one in the db.