FreeRadius + MySQL working, but I don't know how to customise SQL queries
This is the background:
- Up and running, production, server. Ubuntu 8.04.3
- Working like a charm for almost a year now as LAMP server, OpenLDAP server, samba PDC, DNS server, OpenVPN server, and a few other server tasks.
- ChilliSpot hotspot completely up and running well. FreeRADIUS with MySQL backend used for this purpose, working well. Captive portal customised and working just like it should.
This server hosts a set of PHP scripts called "PHP-Residence", which is a GNU/GPL-licensed booking system and reservation management for hotel and tourist apartments. The database is MySQL. The idea is to allow access to the hotspot to all clients who are staying at the apartments. For such, authorisation would have to check for the following:
- the username is in the clients database, AND
- the password matches the reservation code, AND
- the current date is within the time-frame where the guest is staying at the apartments,
Currently, if someone who stays at the aparments wants to connect to the hotspot, I have to create usernames and passwords manually. It is a tedious work to do, to create a new username and a new password everytime someone arrives. And in addition I find it particularly pointless, because I already have a MySQL database from which I can read the necessary information in order to accept or deny a connection.
The idea would be to have PHP-Residence to automatically populate the radcheck table with customer name, reservation code, arrival date, and departure date. At login time, the customer would be asked for his name and his reservation code as username and password. Then FreeRADIUS would check if those match, and also if the current date is equal or greater than the arrival date AND if the current date is equal or lower than the departure date.
That last bit is the tricky bit. I haven't got the smallest clue about MySQL syntax, nor FreeRADIUS operation. I have setup the hotspot according to Ubuntu documentation, and as I said it works great, only I do not know how to get it to check if the current date is valid for the specified username/password.
I suppose that I can figure out how to get PHP-Residence to populate the radcheck table (if I can't then I'll come back for help). But I really don't know how to check, in addition, for the current date being valid or not.
I have supposed that I have to add attributes to the radcheck table. As initially setup, radcheck contains UserName, Attribute (password), op (operand is == because we need an exact match), and Value (which is the password itself). I have added extra fields:
- Attribute2 (DateBegin)
- op2 (>= because current date must be greater or equal to the date when the customer arrives)
- Value2 (contains the date the reservation begins) - field type is date
- op3 (<= because current date must be lower or equal to the date when the customer departs)
- Value3 (contains de date the reservation ends) - field type is date
But how do I have to type the query in order to get this checked? How do I dump this into the sql.conf file?