Back End Architecture

Making sure there are no future problems

[brotherhood of LAN]

Hope this is the right forum- and couldnt think of a good title :)

Anyways, I'm talking about maintenance on the back end of a website....in this instance I have a database of which there are database editors written in ASP to manipulate the data.

I remember reading a while back in here somewhere about using a javascript to prevent the same record being submitted twice upon clicking submit. First and foremost if anyone could post such a neat little script that would be great.

The main question is, are there any issues I should be aware of before I plump this piece of info on my site. I already have a password protecting the directory where all the editors are....and of course, the above little nifty script will help maintain the integrity of the information held within the database.

But is there anything else that should be noted when making such a setup? It is a bit of a general question but nonetheless - I am sure many people in here have a nice little setup on their sites that no one else can see - bespoke to their maintenance needs.

...would love to hear what you all are bearing in mind in such cases....

[theboyduck]

I hope this is the sort of advice you're looking for -

Check that no sensitive information can be obtained from any logs you have set up (I'm thinking passwords and query strings that could be used to manipulate your database).

[Nick_W]

Also, look into whatever the ASP equivalent of 'sessions' is. JS is toooooo easily compromized for something like this: set a cookie (or session on host) when the data is posted and check for the existence of it before posting. Or something like that.

Also, if the 'editors' use strings in the url make sure you don't do anytthing like site.com/editors/index.asp?sort=ASC

easily compromized and messed with, assign numbers to stuff like that otherwise some smartarse will start appending delete queries to the url ;)

Nick

[ergophobe]

One additional thing I do is "clear the cache" in PHP. I assume you can do the same thing in ASP, though I don't know how.

Before I get too far down the page, I submit the data to the DB and then immediately redirect and clear the GET and POST data instead of just going on to render the page. This takes almost no time since it occurs right at the top and then immediately renders the page again:

// if data already submitted, reload to clear GET and POST

if ($clear_vars) {
header("Location: " . $page_url);
exit();
}

This will help eliminate some data duplication, though I don't know about someone double-clicking the submit button. I can't do it, but I would somehow need to set up the server to be slow enough to let me hit twice to really test.

Tom

[madcat]

I don't mean to change the subject but I'm trying to understand database organization and I thought this might fit.

If you have two tables both with 10 rows of records that relate to each other, and you took records 8 out of each table, MySql would number them like ... 5, 6, 7, 9, 10.

What is the best method to control sequential numbers, or is it pointless to create a function to do so. Without proper experience it seems to me like I would want the records to automatically re-number themeselves so they are in sequence 1-9.

I haven't worked with databases so any common method insight would be helpful.

Thanks-

[joshie76]

Here's one solution to prevent duplicate inserts/updates etc from the same form (refresh/double click)... All the code would be ASP on the server, there's no need for JavaScript.

When the session starts, initialize a session variable something like:

session("actioncount") = 0

Whenever there is a form on a page (that can postback to the database) you need to add a hidden form field containing the value of the session variable:

<input type="hidden" value="<%=session("actioncount")%>" name="actioncount">

When a form postback happens you need to check that the value of the

Request.Form("actioncount")

is equal to the value of

session("actioncount")

. If so, you can go ahead and run the update on the database. You should also increment the

session("actioncount")

variable by one.

If the same form is then reposted the value of

session("actioncount") = 0

and

Request.Form("actioncount")

will not match and you should return an error (or whatever you think appropriate) to the user.

Let me know if I've not been clear.

Josh

[ergophobe]

OT RE Madcat's inquiry.

Basically, you're referring to auto-increment columns, which are usually used as a primary key. There's generally no reason to have/want sequential numbers in a primary key. It might help if you (i.e. without the help of a computer) were accessing the data in a printout, but mostly what's important is that the number stay the same. Consider the following:

+--- tableNames ----+

pk firstname lastname
1 John Carranzo
2 Achmed Smith
3 Billy-Bob Dupont

+---- tablePosition ---+

pk position
1 catcher
2 pitcher
3 firstbase

+------- tableWho'sOnFirst -----+

pk position name
1 1 2
2 3 3
3 2 1

Now, we fire John Carranzo and we can either

1. delete his record (tableNames where pk=1) and the corresponding record in tableWho'sOnFirst (where name=1). This is simple, fast and everything is working okay. If you're using a DBMS that protext referential integrity, this is no problem at all. In MySQL you'll have to double check.

2. delete his record and then loop through tableNames so that everyone above him has his number (which is the primary key) reduced by one and then find the corresponding record in tableWho'sOnFirst and reduce "name" by one. This is slow and complex, you run the risk of messing up your primary keys and so forth.

If you have even minimal data abstraction, the users should not need to know anything about how or why these numbers are generated and should not need to know whether or not they are sequential. I don't know where you work, but do you know who has the exact next employee number after you (I work for a state government, and I have no idea).

Tom

[madcat]

Thanks for that explanation Tom, that really clears things up for me. No, I guess it doesn't make a difference if they're sequential or not as long as everything matches from table to table.