Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Installation/Configuration

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 23rd October 2009, 19:30
awe awe is offline
Junior Member
 
Join Date: May 2009
Location: Tossa de Mar (Girona) Spain
Posts: 17
Thanks: 0
Thanked 3 Times in 3 Posts
Default FreeRadius + MySQL working, but I don't know how to customise SQL queries

Hello,

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:

IF
- 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,
THEN AUTHORISE
ELSE DENY

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

-Attribute3 (DateEnd)
- 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?

Million thanks!
Reply With Quote
Sponsored Links
 

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Chilispot on dd-wrt + freeradius / phpmyprepaid on Centos 5 wrong user password treeman HOWTO-Related Questions 0 31st May 2009 06:49
phpmyadmin error lifeisboost Installation/Configuration 9 14th August 2008 19:19
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 09:11
LAMP System Fedora Core 6 MySQL start error gavino HOWTO-Related Questions 13 21st November 2007 19:13
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04


All times are GMT +2. The time now is 11:26.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.