[HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step

dhipo · Jan 25, 2017, 5:31 PM

some issue with pfsense 2.3.2-p1

i did an clean install
the redirect page was show (perfect)
i put all parameters (email, name, surname) agree newsletter and terms

clicked connect

and a Blank Screen is presented
with this url http://192.168.1.1:8002/index.php?zone=&redirurl=&language=en
nothing is inserted on mysql tables
and log shows
php-fpm 58240 /index.php: Submission to captiveportal with unknown parameter zone:

any glue ?

sanketgroup · Jan 26, 2017, 10:56 AM

Hi deajan
Thanks for superb detail explanation.

One question: How can i integrate separate MySQL server. I have already MySQL running on different PC (in LAN).
I know about point IP to MySQL server, but

how do i create table structure in my separate sql server.

I downloaded http://netpower.fr/sites/default/files/soft/bin/pfSense-cp-auth-onestep.gz file from step 2.2.2.
But it is not useful to create structure.

Pls help me.

Thanks
Sanket

deajan · Jan 27, 2017, 8:43 AM

@dhipo Did you set up a correct zone name in pfSense GUI ?
Also, which version have you tried ? Can you try with latest git master tree ?

You asked for a "glue", I can't give you that, but the latest dev snapshot should stick toghether with 2.3.2 without problems :)

@sanketgroup
The archive contains some sql files (in sql directory) that you must run on your mysql server.
Btw, also use latest github sql files because of an error in previous versions.

sanketgroup · Jan 27, 2017, 10:08 AM

where can i find latest github sql files?

I got schema from https://wiki.freeradius.org/config/MySQL-DDL-script
However there were some mismatch in column names.

deajan · Jan 27, 2017, 11:02 AM

See https://github.com/deajan/pfSense-cp-auth-onestep

sanketgroup · Jan 27, 2017, 11:58 AM

perfect, this schema worked.
BUT
how can i add internet username and passwords in sql.

for e.g In current setup, user 'testu' has been created PFSense>Services>FreeRadius>Users

if i delete this user here, it does not go to my sql to check the user.
And also i do not know in which table and now i can create users in MySQL.
Pls let me know.

Thanks a lot

deajan · Jan 27, 2017, 12:57 PM

@sanketgroup:

how can i add internet username and passwords in sql.

for e.g In current setup, user 'testu' has been created PFSense>Services>FreeRadius>Users

if i delete this user here, it does not go to my sql to check the user.
And also i do not know in which table and now i can create users in MySQL.
Pls let me know.

Thanks a lot

That's not how this captive portal is supposed to work.
It creates the user accounts on the fly.
If you don't want that behavior, just check the code of ozy-captive.php that creates the user in order to disable it and create them manually in the same manner the code does.

sanketgroup · Jan 27, 2017, 5:30 PM

On ozy-captive.php page.
when i fill in Email, Family Name, Surname and click connect - I get following error.

Fatal error: Uncaught Error: Call to undefined function mysql_real_escape_string() in C:\xampp\htdocs\PFSense-cp-auth\ozy-captive.php:35 Stack trace: #0 C:\xampp\htdocs\PFSense-cp-auth\ozy-captive.php(65): cleanInput('en') #1 {main} thrown in C:\xampp\htdocs\PFSense-cp-auth\ozy-captive.php on line 35

=== Mainly i want to store username password generated from my CRM application to radius database.
I have own CRM application which i can modify to store username password of hotspot users into radius database.
And then captive portal will verify that user in radius mysql database.
But I do not know in which table and column i have to insert usertname password.
And may be their plan like 1gb per day, or 2 hours access etc

Thanks

dhipo · Jan 27, 2017, 5:50 PM

@deajan

i got the latest git files and works perfect ..

i did some mods on sql tables(more fields, to administrative purposes) and working too

i'm using pf 2.3.2p1

i used toghther Squid and LightSquid to do Reports and work too

great Job

sanketgroup · Jan 28, 2017, 7:27 AM

Ok, i found the table name. It is radcheck.
Captive Portal is successfully authenticating username password stored in this table.

Now one question: how do i set usage type to users.

for e.g I want some user to have
1GB per day or
2 HR access or
100MB for 2 Hrs
etc.

deajan · Feb 3, 2017, 8:49 AM

@sanketgroup This is out of the captive portal discussion but after a short google request I found this one:

INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES ( NULL , 'user', 'user-password', '==', 'user');
INSERT INTO radreply (username, attribute, op, value) VALUES ('user', 'Rate-Limit', '=', '1024k/1024k');

It may be adapted to fit actual radius table scheme, you may find info on how to configure it on freeradius documentation.

ardorin · Feb 3, 2017, 1:33 PM

First of all, amazing job, it works perfectly.
However I have a small problem: in french and in spanish accents dont work.
I use UTF-8 in HTML header.
It only works if for example I replace á with á in the config file
Im also using the files from your github

Is there anything that should be added?

deajan · Feb 3, 2017, 1:41 PM

@ardorin Strange, I actually developped this on french computers and did never get any trouble. What navigator do you use to check this ?

Also, can you try to add the following after line 327 ?

ardorin · Feb 3, 2017, 2:14 PM

I tried adding that line and then in both Chrome and Firefox without any luck.
If I type any input in the HTML file with accents, it works, so the HTML header seems fine. The problem are the accents in the strings of the config file, for some reason they are not being coded correctly when introduced in the main file. Characters with accents are being replaced by a question mark.
I did edit your code a bit, but I didnt touch anything of the HTML part.

deajan · Feb 3, 2017, 2:18 PM

@ardorin What platform are you running ?
I hope you didn't copy paste some of the code :)
Please check that all files are UTF-8 without BOM encoded.

ardorin · Feb 3, 2017, 4:24 PM

I edited the database part, so those changes I made should not be irrelevant (and that part is working perfectly)
Yes, every file is UTF without BOM encoded

Alexandre4sol · Feb 3, 2017, 4:39 PM

Dear, I have the problem "/index.php: Submission to captiveportal with unknown parameter zone:".
I got the newest files and still the error continues.
I looked over the correction post and I also could not solve it.
Home page opens to register the form and fell on a white page with the url: http://192.168.123.1:8002/index.php?zone=&redirurl=&language=en.
In the log the message "/index.php: Submission to captiveportal with unknown parameter zone:"
Can someone help me I'm out of ideas?

deajan · Feb 4, 2017, 11:52 AM

@ardorin: out of ideas here. Have tested it on linux / windows, using various languages and systems. Didn't happen to me. Maybe start with a fresh install and try again ?
@Alexandre4sol: Are you running on 2.2 ? Cause zone parameter was introduced in 2.3. Also, what's your exact zone name ?

Alexandre4sol · Feb 4, 2017, 12:31 PM

@deajan: Hi, thanks for helping me out.
I'm using version 2.3.2-RELEASE (i386)
the captive portal zone is "guest"
This is the link to open the registration form. http://192.168.123.1:8002/index.php?zone=guest&redirurl=http%3A%2F%2Fwww.gstatic.com%2Fgenerate_204

![Captura de Tela 2017-02-04 a?s 10.28.56.png](/public/imported_attachments/1/Captura de Tela 2017-02-04 a?s 10.28.56.png)
![Captura de Tela 2017-02-04 a?s 10.28.56.png_thumb](/public/imported_attachments/1/Captura de Tela 2017-02-04 a?s 10.28.56.png_thumb)

deajan · Feb 4, 2017, 12:53 PM

@Alexandre4sol: I think dhipo on this thread did have the same error. Maybe you should talk to him to see how he managed to solve this. As for me I'll have to make some tests because I never encountered that problem.