HotSpot login database

Discuss here how to configure hoteldruid and better use its features.

Moderator: marco

Post Reply
awe
Posts: 4
Joined: Tue Jan 20, 2009 10:43 pm
Location: Tossa de Mar, Spain

HotSpot login database

Post by awe »

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!
marco
Posts: 1332
Joined: Tue Jul 05, 2005 6:00 pm
Location: Roma, Italia

Re: HotSpot login database

Post by marco »

Hello,
I would be interested on how you have set up the radius server, if you can explain a little or point to some documentation. To update your table I would suggest using a cron job to be run once or twice a day, you should be able to do it with the mysql command. If you choose to modify the php-residence code you'll have to port the changes each time you want to upgrade to a new version, but if you still want to do it probably the best way is to update the table each time a reservation is checked in or out, check-in is updated only in modifica_prenota.php, look for the block delimited by:

Code: Select all

if ($attiva_checkin == "SI" and $priv_mod_checkin == "s" and...
Regards,
Marco
Problems installing, configuring, upgrading?
Try the easiest way to use HotelDruid:
https://www.digitaldruid.net/hosted/index.php
awe
Posts: 4
Joined: Tue Jan 20, 2009 10:43 pm
Location: Tossa de Mar, Spain

SOLVED: HotSpot login database

Post by awe »

Hello,

I did not realise about your answer, sorry. Now this post is really old, but just in case it is still of interest, this is how it has worked for me.

Server is Ubuntu 8.04. I use FreeRadius for the radius server. It is in the repositories. FreeRadius and ChilliSpot have been successfully setup according to Ubuntu documentation, see here: https://help.ubuntu.com/community/WifiD ... tspot/8.04

Once working, all the tables in the radius database have been moved into the database of PHP-Residence, for simplicity. The file /etc/freeradius/sql.conf is the key to the setup. radiusdb must contain the name of the database that has the data of PHP-Residence. Then I have added an extra variable:

Code: Select all

# Table containing details of reservations
tableprenota = "prenota2010"
The query that will be used for retreiving usernames is:

Code: Select all

authorize_check_query = "SELECT idprenota AS id,idclienti AS UserName,Attribute,codice AS Value,op FROM ${tableprenota} INNER JOIN ${authcheck_table} WHERE idclienti=%{SQL-User-Name} AND dayofyear(curdate())>=Iddatainizio AND dayofyear(curdate())<=Iddatafine ORDER BY idprenota"
I have the same with authorize_check_reply on the next line.

Make sure that the table radcheck has only one line, the following:

Code: Select all

mysql> select * from radcheck;
+----+----------+-----------+----+-------+
| id | UserName | Attribute | op | Value |
+----+----------+-----------+----+-------+
|  1 |          | Password  | == |       | 
+----+----------+-----------+----+-------+
1 row in set (0.00 sec)
There you go. Username is the client id as in the table idclienti. Password is the reservation code, the four-letter code as found in the field codice in the prenotaxxxx table (obviously, the current table, ie prenota2010). So, if my name is John Doe, I am client number 33, and I have a reservation with its code being "abcd", arriving on August 15th and departing on August 25th, when Chillispot asks me to login, I type 33 for username and "abcd" for password. If the current date is any moment between August 15th and August 25th I will be able to connect. Otherwise, not.

By the way, I have commented out a line or two in PHP-Residence so that when viewing a reservation it will display the four-letter code, without addind the numbers. Also, I always see the client id in addition to his/her details. This makes my life easy when it comes to giving the connection details.

Like this I run the hotspot almost effortlessly, and very important, without having to pay anything to anyone. For those with the interest, this can be expanded with things like phpmyprepaid, so customers will have to pay in order to connect (I want to provide free access, so I have not done this and I cannot give precise instructions). There is one little thing that must be done manually, though. Once a year, when the new year is created in php-residence, the file sql.conf has to be updated. As of now, it has

Code: Select all

# Table containing details of reservations
tableprenota = "prenota2010"
and when the year 2011 will be created this line will be

Code: Select all

# Table containing details of reservations
tableprenota = "prenota2011"
Then restart freeradius for the new config to take effect, and you're set for one more year.

Regards,

Eduard
gonesouth
Posts: 105
Joined: Fri Apr 25, 2008 12:59 am
Location: Canada

Re: HotSpot login database

Post by gonesouth »

Thanks, Eduard, that is very clear and helpful. now, I would like to hijack your thread and ask if there are other applications that could use this approach. For example, in another thread I am looking at running two instances of PHP-residence under different customer facing interfaces to sell the same rooms.
Niko18
Posts: 35
Joined: Wed Jun 11, 2014 8:15 am

Re: HotSpot login database

Post by Niko18 »

Interesting feature, do you know if it possible to active and deactivate a user using the check - in and check- out button that is different from "initial date - end date"

And possible to add a delay or bonus of X minutes to a guest when they make the check out? This way the clients don't lose the wi-fi connection so abruptly in case they need to wait for their taxi or take the last pictures of their trip
Post Reply