HotSpot login database
Posted: Wed Oct 28, 2009 6:39 pm
Dear Marco,
I have set up a HotSpot with captive portal in the company. The authorisation backend is MySQL. My company has PHP-Residence running on our own LAMP server. I now have to link PHP-Residence to the database of the FreeRadius server that will authorise or deny the connection to clients.
For such, the radius server does a query. There is a new table in the database called radcheck, which contains the following fields:
UserName, Attribute, op, Value, ClientNr, StartDay, EndDay.
UserName must contain the family name (cognome) of the client
Attribute must contain the value 'password' -it's a VarChar(32)-
op must contain the value '==' -it's a VarChar(2)-
Value must contain the reservation code (the code with just four letters as in the database, it's used as the password)
ClientNr must contain the client number (idclienti)
StartDay must contain the day the reservation begins
EndDay must contain the day the reservation ends
When a client connects to the hotspot he is presented with a form asking for the family name and the password. When the form is submitted, the radius server checks the radcheck table to see if username (client family name) and password (reservation code) match, and it will also check that curdate() (current date) is valid, ie between StartDay and EndDay. All of this is already setup and running.
However, I would need a bit of your help to automate the procedure of populating the radcheck table. What I would need to do is:
1) When a new reservation is inserted, PHP-Residence must insert a line in radcheck containing the familiy name of the client, the text strings 'password' and '==' as values for Attribute and op fields, the client number, arrival day and departure day in the corresponding fields. This would also be a good moment to delete from the table all entries that correspond to reservation that are already finished, so there would be a regular cleanup of the table.
2) When a client data is updated with a change in the family name, the field UserName of the radcheck table must be updated too with the new value.
3) When a reservation is deleted, its corresponding line in the radcheck table must be deleted too.
4) When the reservation code is regenerated, the field Value in the radcheck table must be updated with the new value.
5) When the client of a reservation is changed for another client, the fields UserName and ClientNr must be updated in the radcheck table. I have included a the field ClientNr containing the "idclienti" in order to make this task easier.
My knowledge of PHP is barely basic. My knowledge in MySQL is sufficient (or so I believe). My request is if you could point to me where in the PHP-Residence scripts whould I add the extra mysql_query(xxx); in order to keep the radcheck up to date.
Thank you!
I have set up a HotSpot with captive portal in the company. The authorisation backend is MySQL. My company has PHP-Residence running on our own LAMP server. I now have to link PHP-Residence to the database of the FreeRadius server that will authorise or deny the connection to clients.
For such, the radius server does a query. There is a new table in the database called radcheck, which contains the following fields:
UserName, Attribute, op, Value, ClientNr, StartDay, EndDay.
UserName must contain the family name (cognome) of the client
Attribute must contain the value 'password' -it's a VarChar(32)-
op must contain the value '==' -it's a VarChar(2)-
Value must contain the reservation code (the code with just four letters as in the database, it's used as the password)
ClientNr must contain the client number (idclienti)
StartDay must contain the day the reservation begins
EndDay must contain the day the reservation ends
When a client connects to the hotspot he is presented with a form asking for the family name and the password. When the form is submitted, the radius server checks the radcheck table to see if username (client family name) and password (reservation code) match, and it will also check that curdate() (current date) is valid, ie between StartDay and EndDay. All of this is already setup and running.
However, I would need a bit of your help to automate the procedure of populating the radcheck table. What I would need to do is:
1) When a new reservation is inserted, PHP-Residence must insert a line in radcheck containing the familiy name of the client, the text strings 'password' and '==' as values for Attribute and op fields, the client number, arrival day and departure day in the corresponding fields. This would also be a good moment to delete from the table all entries that correspond to reservation that are already finished, so there would be a regular cleanup of the table.
2) When a client data is updated with a change in the family name, the field UserName of the radcheck table must be updated too with the new value.
3) When a reservation is deleted, its corresponding line in the radcheck table must be deleted too.
4) When the reservation code is regenerated, the field Value in the radcheck table must be updated with the new value.
5) When the client of a reservation is changed for another client, the fields UserName and ClientNr must be updated in the radcheck table. I have included a the field ClientNr containing the "idclienti" in order to make this task easier.
My knowledge of PHP is barely basic. My knowledge in MySQL is sufficient (or so I believe). My request is if you could point to me where in the PHP-Residence scripts whould I add the extra mysql_query(xxx); in order to keep the radcheck up to date.
Thank you!