Re: PHP & MySQ + unique keys

From: Malcolm Dew-Jones (yf110_at_vtn1.victoria.tc.ca)
Date: 03/17/05 

Date: 16 Mar 2005 16:54:31 -0800

Tony Clarke (clarket@eircom.net) wrote:
: "Brent Palmer" <bapalmer@bigpond.net.au> wrote in message
: news:3H1_d.786$C7.723@news-server.bigpond.net.au...
: >
: > "Tony Clarke" <clarket@eircom.net> wrote in message
: > news:Ts1_d.49713$Z14.37838@news.indigo.ie...
: >> Hi,
: >>
: >> I'm trying to write a system thats used for about 50 clients that uses
: >> html forms and php to log details. The problem is that when a client
: >> loads the form page it's given a value which is the last record in a
: >> table +1 (i.e. so its the next record). The problem with that is that a
: >> client could sit on that page for 20 mins (or whatever length of time)
: >> and a different client could use that record number and there what be an
: >> error "duplicate key....".
: >> I could use an MySQL UPDATE to lock the record when its issued to each
: >> client and maybe use a timestamp and check if the timestamp is over 10
: >> mins and delete any records that are over 10mins but there is the
: >> possibility of redundant data (or really useless data, a unique record
: >> and a timestamp) in the table sitting there for 10mins at a time.
: >>
: >> Is there an easy way of 'gauranteeing' a client a unique key? So you
: >> don't have to lock the record when the page loads (this also means that
: >> when you refresh the page that the unique key is incremented as you
: >> locked the last record and didn't put anything into the table, so the
: >> refreshed page is issued the next key +1 again).
: >>
: >> Thanks,
: >>
: >> Tony.
: >>
: >
: > Why don't you retrieve the key once the form is submitted? This way the
: > client has the key for only a fraction of time.
: > You could also autoIncrement the field in the table.

: Its possible I could do it this way, but it would make life much more akward
: for the people using the system, ideally I would like to do it before its
: submitted as its used as a reference number that is quoted to people.

0) Have two tables, ONE with multiple rows, one per form, indexed by a
serial number, this is not an autoindex column, and not all possible
serial numbers are necessarily used. Table TWO is a table with just one
row used strictly to record the next available serial number.

1) use sessions

2) when a person requests a new, "blank", form, then allocate the next
number from the (one row) sequence table. i.e. update the number stored
in the only row in the table. Each request for a new blank form will get
a unique number.

3) use the session to store the number on the server, and also use and
display it for the user in the form.

4) when the user saves the form, check the serial number is the one the
session is using. Then try to update a row with that serial number. If
the update works then the user has updated the form. If the row is not
found then insert a new row with that serial number and save the form data
in that row.

Notice that it is the session variable stored on the server that double
checks the update/insert is legit - this is what prevents some other user
from updating or inserting some data with a stolen serial number.

For some applications that is enough - once the session expires then the
user cannot update the record.

If you don't want to use sessions, then implement a check sum to prove the
serial number is valid. I.e. on the server, where the users can't see
what is going on, your php script combines the serial number with a secret
key, and encrypts the result, and includes that with the form. A person
who doesn't know the secret key will not be able to fake the serial number
because they won't know how to generate the checksum (unless they were
able to monitor the earlier transmission). Each time your script gets a
form it regenerates the checksum from the serial number and checks that
the checksum in the form is correct.

For other applications the user will need to be able to update the record
later on. In that case you need to provide another mechanism, such as a
regular login, to control which record a person can update. The serial
number will be used in the sql to access the row, but it will be the login
that controls which rows they are allowed to access. 

--
This space not for rent.

Relevant Pages

  • Re: Navigate in popup window
    ... I use a unique key name to identify the item in the system cache, ... However, if the data is unique to each client accessing your application, ... I still use caching instead of session state due ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Navigate in popup window
    ... I use a unique key name to identify the item in the system cache, ... However, if the data is unique to each client accessing your application, ... I still use caching instead of session state due ...
    (microsoft.public.dotnet.languages.vb)
  • Re: [PHP] Re: a question on session ID and security
    ... hash key" to the client when it doesn't need it? ... But by doing that you're exposing how your app validates the authentication key, leaving it open to being transferred to another machine. ... tutorial on PHP session security is helpful. ...
    (php.general)
  • RE: ISA 2004 Firewall client
    ... The green arrow only shows up when the client needs to initiate a ... firewall session. ... Part 3: I want to explain How the logs and sessions work: ... Collect the ISA firewall client configuration information ...
    (microsoft.public.windows.server.sbs)
  • Re: Database design
    ... Is each session related to a client? ... Yes, each session is related to a single client, or so I anticipate. ... (fsubSession and fsubProduct) ...
    (microsoft.public.access.tablesdbdesign)