Symfony Profiler

<?php
namespace App\Controller;
use App\Service\SycoreService;
use App\Utility\UserLanguageQueries;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Exception;
use Symfony\Component\Security\Core\Encoder\UserPasswordEncoderInterface;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;
use Psr\Log\LoggerInterface;
use Symfony\Component\Security\Http\Authentication\AuthenticationUtils;
use Symfony\Component\DependencyInjection\ParameterBag\ParameterBagInterface;
use Symfony\Component\Ldap\Ldap;
use Symfony\Component\Ldap\LdapInterface;
class SecurityController extends AbstractController
{
public function __construct(
private SycoreService $sycoreService,
private LdapInterface $ldap,
private string $baseDn,
private string $searchDn,
private string $searchPassword,
private LoggerInterface $logger) {
}
public function loginAction(AuthenticationUtils $authenticationUtils, LoggerInterface $logger)
{
$em=$this->getDoctrine()->getManager();
$sql = "select value::json from config where name_config = 'login_options'";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$value = $stmt->fetchAll()[0]["value"];
$loginOptions = json_decode($value, true);
//---------------------------------------
// Filtro solo quelli con active = "Y"
//---------------------------------------
$loginOptions = array_filter($loginOptions, function ($f) {
return $f['active'] === 'Y';
});
//---------------------------------------
// Ordina per "order"
//---------------------------------------
usort($loginOptions, function ($a, $b) {
return $a['order'] <=> $b['order'];
});
// get the login error if there is one
$error = $authenticationUtils->getLastAuthenticationError();
// last username entered by the user
$lastUsername = $authenticationUtils->getLastUsername();
$licenseAudit = $this->sycoreService->getLicenseAudit();
$status = $this->sycoreService->getStatus();
return $this->render(
'Security/login.html.twig',
array(
'available' => $status ? $status->available : true,
'maintenance_on' => $status ? $status->maintenance_on : false,
'maintenance_scheduled' => $status ? $status->maintenance_start != '' : false,
'maintenance_start' => $status ? $status->maintenance_start : '',
'is_licensed' => $licenseAudit ? $licenseAudit->is_licensed : false,
'service_tag' => $licenseAudit ? $licenseAudit->code : 'L-100-D00000',
// last username entered by the user
'last_username' => $lastUsername,
'error' => $error,
'cdate' => time(),
'login_options' => $loginOptions
)
);
}
public function checkAction(Request $request, LoggerInterface $logger)
{
$user = $this->getUser();
$logger->debug('CHECKACTION: check action to do');
if ( $user->getChangePassword()) {
$logger->debug('CHECKACTION: change password');
return $this->redirect($this->generateUrl('user_edit'));
}
return $this->redirect($this->generateUrl('home'));
}
//--------------------------------------------------------------------------
//Action intermedia tra login e caricamento dashboard per salvare i login
//--------------------------------------------------------------------------
public function userLoggingAction(Request $request, LoggerInterface $logger)
{
$em=$this->getDoctrine()->getManager();
$user = $this->getUser();
$userId = $user->getId();
$username = $user->getUsername();
$status = $this->sycoreService->getStatus();
if ($status && $status->maintenance_on) {
if ($user->getUserRole() != 'ROLE_SUPER_ADMIN')
{
return $this->redirect($this->generateUrl('logout'));
}
}
$userType = "local";
if ($user instanceof \App\Security\LdapUser)
{
$userType = "ldap";
$logger->debug('[userLoggingAction] $user is LDAP');
$this->ldap->bind($this->searchDn, $this->searchPassword);
$username = $user->getUsername();
$query = $this->ldap->query(
$this->baseDn,
sprintf('(uid=%s)', $username),
['filter' => ['*', 'isMemberOf']]
);
$results = $query->execute()->toArray();
$attributes = $results[0]->getAttributes();
if ($attributes["status"][0] != "Attivo" || $attributes["enable"][0] != "TRUE")
{
$logger->debug('[userLoggingAction] $user is disabled or inactive ');
return $this->redirect($this->generateUrl('logout'));
}
$localUser = $em->createQueryBuilder('u')
->select('u')
->from('App:User', 'u')
->where('u.username = :username')
->orderBy('u.lastName, u.firstName')
->setParameter('username', $username)
->getQuery()
->getResult();
if (empty($localUser)) {
$logger->debug('[userLoggingAction] user not found');
$sql = 'insert into user_acm(username, text1, email, first_name, last_name, user_role, status, change_text1 , flag_token) values('
. ':username, :password, :email, :first_name, :last_name, :user_role, :status, :change_text1, :flag_token)';
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('username', $username);
$stmt->bindValue('password', "");
$stmt->bindValue('email', $attributes["mail"][0]);
$stmt->bindValue('first_name', $attributes["cn"][0]);
$stmt->bindValue('last_name', $attributes["sn"][0]);
$stmt->bindValue('user_role', $user->getUserRole());
$stmt->bindValue('status', 'false');
$stmt->bindValue('change_text1', 0);
$stmt->bindValue('flag_token', 0);
$stmt->execute();
$sql = 'select id from user_acm where username = :username';
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('username', $username);
$stmt->execute();
$newIdUser = $stmt->fetchAll()[0]["id"];
$user->setId($newIdUser);
$randomString = substr(str_replace(['+', '/', '='], '', base64_encode(random_bytes(32))), 0, 30);
$sql = 'insert into user_token(id_user, token, date_start_validity, date_end_validity, check_validity)'
. "values( :id, :token, SYSDATE(), TO_DATE('31-12-2999 23:59:59', 'dd-mm-yyyy HH24:mi:ss'), 'Y')";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('id', $user->getId());
$stmt->bindValue('token', $randomString);
$stmt->execute();
$logger->debug('[userLoggingAction] user added');
} else {
$logger->debug('[userLoggingAction] user found');
$user->setId($localUser[0]->getId());
}
$user->setFirstName($attributes["cn"][0]);
$user->setLastName($attributes["sn"][0]);
$sql="select id_roles,description_roles from roles";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$resultRoles = $stmt->fetchAll();
//reset user roles
$sql="delete from user_profile_roles where id_user = :id";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('id', $user->getId());
$stmt->execute();
foreach ($resultRoles as $role) {
if (!empty(preg_grep("/^cn=".$role["description_roles"]."/i", $attributes["isMemberOf"]))) {
$profile = "Admin";
//admin, report, oper
$sql="select id_profile, type_profile from profile where type_profile = :profile";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('profile', $profile);
$stmt->execute();
$idProfile = $stmt->fetchAll()[0]["id_profile"];
//add nuovi
$sql="insert into user_profile_roles (id_user, id_profile, id_roles) values (:idUser, :idProfile, :idRuolo)";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('idUser', $user->getId());
$stmt->bindValue('idProfile', $idProfile);
$stmt->bindValue('idRuolo', $role["id_roles"]);
$stmt->execute();
} else {
//admin, report, oper
$sql="select id_profile, type_profile from profile where type_profile = :profile";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('profile', "None");
$stmt->execute();
$idProfile = $stmt->fetchAll()[0]["id_profile"];
//add nuovi
$sql="insert into user_profile_roles (id_user, id_profile, id_roles) values (:idUser, :idProfile, :idRuolo)";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('idUser', $user->getId());
$stmt->bindValue('idProfile', $idProfile);
$stmt->bindValue('idRuolo', $role["id_roles"]);
$stmt->execute();
}
}
$logger->debug('[userLoggingAction] roles refreshed');
} // end ldap user
$userId = $user->getId();
// recupero l'ip del client
$ip = $request->getClientIp();
if($ip == 'unknown'){
$ip = $_SERVER['REMOTE_ADDR'];
}
//-----------------------------------------------------------
// Inserisce un record di login nella tabella user_logging
//-----------------------------------------------------------
$sql = "INSERT INTO user_logging (
id_user, user_time_log, log_type, ip_address, username, user_type
) VALUES (
:id_user, SYSDATE(), :log_type, :ip_address, :username, :user_type
)";
$statement = $em->getConnection()->prepare($sql);
$statement->bindValue('id_user', $userId);
$statement->bindValue('log_type', 'login');
$statement->bindValue('ip_address', $ip);
$statement->bindValue('username', $username);
$statement->bindValue('user_type', $userType);
$statement->execute();
$sql="select description_roles
from user_profile_roles upr, profile p , roles r, user_acm u
where upr.id_profile = p.id_profile and
upr.id_roles = r.id_roles and
upr.id_user = u.id and
upr.id_user= :idUser";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('idUser', $userId);
$stmt->execute();
$DescriptionRole=$stmt->fetchAll();
$sql="select distinct string_agg(type_profile, ',') type_profile
from user_profile_roles upr, profile p , roles r, user_acm u
where upr.id_profile = p.id_profile and
upr.id_roles = r.id_roles and
upr.id_user = u.id and
upr.id_user= :idUser";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('idUser', $userId);
$stmt->execute();
$TypeProfile=$stmt->fetchAll();
$sql="select * from custom_dashboard_anag where level=0 and hidden='0' order by id";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$CustomDashboardLevel0 = $stmt->fetchAll();
$sql="select * from custom_dashboard_anag where level=1 and hidden='0' order by id";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$CustomDashboardLevel1 = $stmt->fetchAll();
$sql="select * from credits order by id";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$Credits = $stmt->fetchAll();
$session = $this->get('session');
$session->set('Ruolo', array(
'DescriptionRole' => $DescriptionRole,
'TypeProfile' => $TypeProfile
)
);
$session->set('CustomDashboard', array(
'CustomDashboardLevel0' => $CustomDashboardLevel0,
'CustomDashboardLevel1' => $CustomDashboardLevel1
)
);
$session->set('Credits', array(
'Credits' => $Credits,
)
);
return $this->redirect($this->generateUrl('home'));
}
public function logoutUserAction(Request $request, $from, LoggerInterface $logger)
{
$em=$this->getDoctrine()->getManager();
$user = $this->getUser();
$userId = $user->getId();
$username = $user->getUsername();
// recupero l'ip del client
$ip = $request->getClientIp();
if($ip == 'unknown'){
$ip = $_SERVER['REMOTE_ADDR'];
}
$logger->debug('[logoutUserAction] IP CLIENT: '.$ip);
$log_type_desc = '';
if($from == 1){
$log_type_desc = 'timeout';
} elseif ($from == 0){
$log_type_desc = 'user logout';
}
$userType = "local";
if ($user instanceof \App\Security\LdapUser)
{
$userType = "ldap";
}
//-----------------------------------------------------------
// Inserisce un record di logout nella tabella user_logging
//------------------------------------------------------------
$sql = "INSERT INTO user_logging (
id_user, user_time_log, log_type, log_type_desc, ip_address, username, user_type
) VALUES (
:id_user, SYSDATE(), :log_type, :log_type_desc, :ip_address, :username, :user_type
)";
$statement = $em->getConnection()->prepare($sql);
$statement->bindValue('id_user', $userId);
$statement->bindValue('log_type', 'logout');
$statement->bindValue('log_type_desc', $log_type_desc);
$statement->bindValue('ip_address', $ip);
$statement->bindValue('username', $username);
$statement->bindValue('user_type', $userType);
$statement->execute();
return $this->redirect($this->generateUrl('logout'));
}
public function loginFailureAction(Request $request, $username, LoggerInterface $logger)
{
//$logger = $this->get('logger');
$em=$this->getDoctrine()->getManager();
// recupero l'ip del client
$ip = $request->getClientIp();
if($ip == 'unknown'){
$ip = $_SERVER['REMOTE_ADDR'];
}
$logger->debug('[loginFailureAction] IP CLIENT: '.$ip);
$sfRedirect = $request->cookies->get('sf_redirect');
// $userType = "other";
if($sfRedirect){
$route = json_decode($sfRedirect, true);
$currentRoute = $route["route"];
if($currentRoute=="login_check")
{
$userType = "local";
} else {
$userType = "ldap";
}
}
//--------------------------------------------------------------
// Log fallimento login (bad credentials o username mancante)
//--------------------------------------------------------------
if ($username !== 'username') {
$sql = "
INSERT INTO user_logging (
user_time_log, log_type, log_type_desc, ip_address, username, user_type
) VALUES (
SYSDATE(), 'login failure', 'bad credentials', :ip, :username, :userType
)
";
} else {
$sql = "
INSERT INTO user_logging (
user_time_log, log_type, log_type_desc, ip_address, username, user_type
) VALUES (
SYSDATE(), 'login failure', 'username non inserito', :ip, '', :userType
)
";
}
$statement = $em->getConnection()->prepare($sql);
$statement->bindValue('ip', $ip);
if ($username !== 'username') {
$statement->bindValue('username', $username);
}
$statement->bindValue('userType', $userType);
$statement->execute();
return $this->redirect($this->generateUrl('login'));
}
//------------------------------------------------------------------
// Restituisce la lingua dell’utente loggato o la lingua di default
//------------------------------------------------------------------
public function getUserLanguage()
{
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$user = $this->getUser();
$idUser = $user->getId();
$defaultLang = $_ENV['DEFAULT_LANGUAGE'];
$userLang = new UserLanguageQueries($connection);
$lang = $userLang->getLanguageByUser($idUser);
return $lang ?? $defaultLang;
}
//------------------------------------------------------------------
// Aggiorna la lingua dell’utente loggato
//------------------------------------------------------------------
public function updateUserLanguage(Request $request, LoggerInterface $logger)
{
try {
$data = json_decode($request->getContent(), true);
$defaultLang = $_ENV['DEFAULT_LANGUAGE'];
$langCode = $data['lang'] ?? $defaultLang;
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$user = $this->getUser();
if (!$user) {
throw new \Exception('Utente non autenticato');
}
$idUser = $user->getId();
$userLang = new UserLanguageQueries($connection);
// Aggiorna la lingua
$updated = $userLang->updateLanguage($idUser, $langCode);
// Insert se non esiste
if ($updated === 0) {
$userLang->setLanguage($idUser, $langCode);
}
return new Response();
} catch (\Exception $e) {
$logger->error('Errore aggiornamento lingua utente: '.$e->getMessage());
return new Response("Errore nell'aggiornamento della lingua dell'utente!", 500);
}
}
//------------------------------------------------------------------
// Restituisce la lingua dell’utente loggato in formato JSON
//------------------------------------------------------------------
public function userLanguageResponse()
{
return new JsonResponse(['lang' => $this->getUserLanguage()]);
}
public function editUserAction(LoggerInterface $logger)
{
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$user = $this->getUser();
$idUser = $user->getId();
$logger->debug('[editUserAction] ID CHANGE PSW: '.$idUser);
//----------------------------------------------------------------------
// Recupera dati utente e token associato
//----------------------------------------------------------------------
$q = "
SELECT u.id, u.username, u.email, u.first_name, u.last_name, ut.token
FROM user_acm u
JOIN user_token ut ON u.id = ut.id_user
WHERE u.id = :idUser
";
$statement = $connection->prepare($q);
$statement->bindValue('idUser', $idUser);
$statement->execute();
$result = $statement->fetchAll();
$userLang = $this->getUserLanguage();
//----------------------------------------------------------------------
// Costruzione array informazioni utente
//----------------------------------------------------------------------
$ArrayUser=array();
foreach($result as $ar)
{
$ArrayUser_tmp = array('id' => $ar['id'],
'username' => $ar['username'],
'email' => $ar['email'],
'first_name' => $ar['first_name'],
'last_name' => $ar['last_name'],
'token' => $ar['token']);
array_push($ArrayUser, $ArrayUser_tmp);
}
//----------------------------------------------------------------------
// Recupera profili e ruoli associati all’utente
//----------------------------------------------------------------------
$sql = "
SELECT upr.id_user,
upr.id_profile,
upr.id_roles,
p.type_profile,
r.description_roles
FROM user_profile_roles upr
JOIN profile p ON upr.id_profile = p.id_profile
JOIN roles r ON upr.id_roles = r.id_roles
WHERE upr.id_user = :idUser
";
$statement = $connection->prepare($sql);
$statement->bindValue('idUser', $idUser);
$statement->execute();
$ProfileRole = $statement->fetchAll();
//----------------------------------------------------------------------
// Costruzione array profilo/ruolo
//----------------------------------------------------------------------
$ArrayUserProfile=array();
foreach($ProfileRole as $pr)
{
$ArrayUserProfile_tmp = array('profilo' => $pr['type_profile'],
'descrizione' => $pr['description_roles']);
array_push($ArrayUserProfile, $ArrayUserProfile_tmp);
}
$logger->debug('ArrayUserProfile: '. json_encode($ArrayUserProfile));
//----------------------------------------------------------------------
// Rendering pagina profilo utente
//----------------------------------------------------------------------
return $this->render(
'Security/profile.html.twig',
array( 'license_info' => $this->sycoreService->getLicenseInfo(),
'user' => $user,
'logged_user' => $user,
'InfoUser' => $ArrayUser,
'user_lang' => $userLang,
'profile_roles' => $ArrayUserProfile)
);
}
public function addUserAction()
{
$em = $this->getDoctrine()->getManager();
$user = $this->getUser();
$connection = $em->getConnection();
$statement = $connection->prepare(
'select id_profile ,type_profile, description_profile from profile' );
$statement->execute();
$listaProfili = $statement->fetchAll();
//$listaProfili = json_dencode($listaProfili);
$statement = $connection->prepare(
'select id_roles, description_roles, note FROM roles'
);
$statement->execute();
$listaRuoli = $statement->fetchAll();
//$listaRuoli = json_decode($listaRuoli);
return $this->render(
'Security/addUser.html.twig',
array(
'license_info' => $this->sycoreService->getLicenseInfo(),
'user' => $user,
'listaProfili' => $listaProfili,
'listaRuoli' => $listaRuoli,
'logged_user' => $user)
);
}
public function manageUserAction(ParameterBagInterface $params) {
$user = $this->getUser();
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$statement = $connection->prepare("
SELECT
user_acm.id,
username,
first_name,
last_name,
email,
user_role,
case user_role when 'ROLE_USER' then 'User'
when 'ROLE_ADMIN' then 'Admin'
when 'ROLE_SUPER_ADMIN' then 'SuperAdmin'
end tipo_utenza,
case when flag_token=1 then 'SI' else 'NO' end flag_token_string,
case when status=true then 'SI' else 'NO' end status_string,
string_agg(''||description_roles||''||': '||type_profile, ' ') ruoli_profili,
ut.token
FROM user_acm
LEFT JOIN user_profile_roles on user_acm.id = user_profile_roles.id_user
LEFT JOIN profile on user_profile_roles.id_profile = profile.id_profile
LEFT JOIN roles on user_profile_roles.id_roles = roles.id_roles
LEFT JOIN user_token ut on user_acm.id = ut.id_user
GROUP BY user_acm.id, username, first_name, last_name, email, user_role,
case when flag_token=1 then 'SI' else 'NO' end,
case when status=true then 'SI' else 'NO' end,
case user_role when 'ROLE_USER' then 'User'
when 'ROLE_ADMIN' then 'Admin'
when 'ROLE_SUPER_ADMIN' then 'SuperAdmin'
end,
ut.token
ORDER BY id DESC");
$statement->execute();
$listaUtenti = $statement->fetchAll();
$statement = $connection->prepare(
"SELECT id, username, first_name, last_name, email, roles.id_roles,
user_role, description_roles, type_profile
FROM user_acm , user_profile_roles, profile, roles
WHERE user_acm.id = user_profile_roles.id_user
and user_profile_roles.id_profile = profile.id_profile
and user_profile_roles.id_roles = roles.id_roles
order by id desc");
$statement->execute();
$listaRuoliUtenti = $statement->fetchAll();
$connection = $em->getConnection();
$statement = $connection->prepare(
'select id_profile ,type_profile, description_profile from profile' );
$statement->execute();
$listaProfili = $statement->fetchAll();
//$listaProfili = json_dencode($listaProfili);
$statement = $connection->prepare(
'select id_roles, description_roles, note FROM roles order by 2'
);
$statement->execute();
$listaRuoli = $statement->fetchAll();
$UserRole = $user->getUserRole();
return $this->render(
'Security/manageUser.html.twig',
array( 'user' => $user,
'user_role' =>$UserRole,
'listaUtenti' => $listaUtenti,
'listaProfili' => $listaProfili,
'listaRuoli' => $listaRuoli,
'listaRuoliUtenti' => $listaRuoliUtenti,
'logged_user' => $user)
);
}
public function savePasswordAction(Request $request, ParameterBagInterface $params, UserPasswordEncoderInterface $userPasswordEncoderInterface)
{
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('editpwd', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
try{
$id = $_POST["form_id_chpwd"];
$password= $_POST["password"];
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$user = $em->find('App:User', $id);
$user->setPassword($userPasswordEncoderInterface->encodePassword($user, $password));
$password = $userPasswordEncoderInterface->encodePassword($user, $password);
$q = 'update user_acm set text1 = :password where id = :id;';
$statement = $connection->prepare($q);
$statement->bindValue('password', $password);
$statement->bindValue('id', $id);
$statement->execute();
$ExitSavePwd=0;
$MsgSavePwd='Password Modificata con successo';
} catch (\Exception $ex) {
$ExitSavePwd=1;
$MsgSavePwd='Errore nel Salvataggio della password '.$ex;
}
$res_json = new JsonResponse();
$res_json->setData(array('ExitSavePwd' => $ExitSavePwd, 'MsgSavePwd' => $MsgSavePwd));
return $res_json;
}
public function changesaveEmailAction(Request $request)
{
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('changeuseremail', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$email = htmlEntities($request->request->get('email'), ENT_QUOTES);
$em = $this->getDoctrine()->getManager();
$user = $em->find('App:User', $this->getUser()->getId());
$user->setEmail($email);
$em->flush();
return $this->redirect($this->generateUrl('user_edit'));
}
public function changesavePasswordAction(Request $request, ParameterBagInterface $params, LoggerInterface $logger, UserPasswordEncoderInterface $userPasswordEncoderInterface)
{
$csrfToken = $request->request->get('csrftoken');
$res_json = new JsonResponse();
if (!$this->isCsrfTokenValid('changeuserpwd', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$password = $request->request->get('password');
$apassword = $request->request->get('apassword'); //password attuale
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$userId = $this->getUser()->getId();
$user = $em->find('App:User', $userId);
//verifico se la password attuale è corretta
if(!$userPasswordEncoderInterface->isPasswordValid($user, $apassword))
{
$ExitUpdate=1;
$MsgUpdate='Password attuale errata';
$res_json->setData(array('ExitUpdate' => $ExitUpdate, 'MsgUpdate' => $MsgUpdate));
return $res_json;
}
try{
$q = 'UPDATE user_acm SET text1 = :pwd, change_text1 = :ct1 WHERE id = :userId';
$password = $userPasswordEncoderInterface->encodePassword($user, $password);
$statement = $connection->prepare($q);
$statement->bindValue('pwd', $password);
$statement->bindValue('ct1', 0);
$statement->bindValue('userId', $userId);
$statement->execute();
$ExitUpdate=0;
$MsgUpdate='Password Modificata!';
} catch (\Exception $ex) {
$ExitUpdate=1;
$MsgUpdate='Errore nel Salvataggio';
}
$res_json->setData(array('ExitUpdate' => $ExitUpdate, 'MsgUpdate' => $MsgUpdate));
return $res_json;
}
//----------------------------------------------------------------------
// Crea un nuovo utente, gestisce password, token, ruoli e profili.
//----------------------------------------------------------------------
public function saveUserAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params, UserPasswordEncoderInterface $userPasswordEncoderInterface)
{
$res_json = new JsonResponse();
$csrfToken = $request->request->get('csrftoken');
//------------------------------------------------
// Controllo CSRF
//------------------------------------------------
if (!$this->isCsrfTokenValid('adduser', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$userLang = new UserLanguageQueries($connection);
try{
//------------------------------------------------
// Dati dal form
//------------------------------------------------
$username = $_POST["username"];
$email = $_POST["email"];
$nome = $_POST["nome"];
$cognome = $_POST["cognome"];
$password = $_POST["password"];
$tipoUtenza = $_POST["form_utenza"];
$lingua = $_ENV['DEFAULT_LANGUAGE'];
//------------------------------------------------
// Flag
//------------------------------------------------
$ChangeText = 1;
if ($request->request->has('chk_status')) {
$status = 1;
} else {
$status = 0;
}
if ($request->request->has('chk_token')) {
$FlagToken = 1;
} else {
$FlagToken = 0;
}
//------------------------------------------------
// Codifica password
//------------------------------------------------
$user = $em->find('App:User', $this->getUser()->getId());
$password = $userPasswordEncoderInterface->encodePassword($user, $password);
//------------------------------------------------
// Insert utente
//------------------------------------------------
$q = 'INSERT INTO "user"(username, text1, email, first_name, last_name, user_role, status, change_text1, flag_token) VALUES(:username, :password, :email, :first_name, :last_name, :user_role, :status, :change_text1, :flag_token);';
$statement = $connection->prepare($q);
$statement->bindValue('username', $username);
$statement->bindValue('password', $password);
$statement->bindValue('email', $email);
$statement->bindValue('first_name', $nome);
$statement->bindValue('last_name', $cognome);
$statement->bindValue('user_role', $tipoUtenza);
$statement->bindValue('status', $status);
$statement->bindValue('change_text1', $ChangeText);
$statement->bindValue('flag_token', $FlagToken);
$statement->execute();
//------------------------------------------------
// Recupero ID utente appena inserito
//------------------------------------------------
$sql = 'SELECT id FROM "user" WHERE username = :username AND email = :email';
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('username', $username);
$stmt->bindValue('email', $email);
$stmt->execute();
$ResulQuery = $stmt->fetch();
$IdUsername = $ResulQuery['id'];
$logger->debug("IdUsername ".$IdUsername);
// Insert lingua utente
$userLang->setLanguage($IdUsername, $lingua);
//------------------------------------------------
// Update flag token se richiesto
//------------------------------------------------
if ($request->request->has('chk_token')) {
$u = 'UPDATE "user" SET flag_token = 1 WHERE id = :id';
$stmt = $em->getConnection()->prepare($u);
$stmt->bindValue('id', $IdUsername);
$stmt->execute();
}
$logger->debug("[saveUserAction] --- Valori Utente --- ");
$logger->debug("[saveUserAction] $username ");
$logger->debug("[saveUserAction] $email");
$logger->debug("[saveUserAction] $nome ");
$logger->debug("[saveUserAction] $cognome");
$logger->debug("[saveUserAction] $status");
$logger->debug("[saveUserAction] $ChangeText");
//------------------------------------------------
// Insert token utente
//------------------------------------------------
$token = $this->generateToken($params);
$i = "INSERT INTO user_token(
id_user, token, date_start_validity, date_end_validity, check_validity
) VALUES (
:id_user, :token, SYSDATE(), TO_DATE('31-12-2999 23:59:59', 'dd-mm-yyyy HH24:mi:ss'), 'Y'
)";
$stmt = $em->getConnection()->prepare($i);
$stmt->bindValue('id_user', $IdUsername);
$stmt->bindValue('token', $token);
$stmt->execute();
//------------------------------------------------
// Ruoli e profili
//------------------------------------------------
$sql="select id_roles, description_roles from roles";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$ResultRoles = $stmt->fetchAll();
for ($i = 0; $i < count($ResultRoles); $i++) {
$ruolo = $ResultRoles[$i]['description_roles'];
$idRuolo = $ResultRoles[$i]['id_roles'];
$logger->debug("[saveUserAction] Valore Ruolo " . $ruolo);
$logger->debug("[saveUserAction] Id Ruolo " . $idRuolo);
$profile = $_POST["profile_" . $ruolo];
$logger->debug("[saveUserAction] Valore Profilo " . $profile);
if ($profile != "None") {
$sql = "SELECT id_profile FROM profile WHERE type_profile = :profile";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('profile', $profile);
$stmt->execute();
$ResulQuery = $stmt->fetch();
$IdProfile = $ResulQuery['id_profile'];
if ($IdProfile) {
$sql = "INSERT INTO user_profile_roles (id_user, id_profile, id_roles)
VALUES (:id_user, :id_profile, :id_roles)";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('id_user', $IdUsername);
$stmt->bindValue('id_profile', $IdProfile);
$stmt->bindValue('id_roles', $idRuolo);
$stmt->execute();
}
}
}
$ExitInsert = 0;
$MsgInsert = "Utente $username salvato con successo.";
} catch (\Exception $ex) {
$logger->debug("[saveUserAction] Errore nel Salvataggio dell'utente: " . $ex);
$ExitInsert = 1;
$MsgInsert = "Errore nel Salvataggio dell'utente $username";
}
$res_json->setData(array( 'ExitInsert' => $ExitInsert, 'MsgInsert' => $MsgInsert));
return $res_json;
}
public function saveMultipleUserAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params, UserPasswordEncoderInterface $userPasswordEncoderInterface)
{
$csrfToken = $request->request->get('csrftoken');
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
if (!$this->isCsrfTokenValid('addMultipleuser', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$File=$_FILES['form_lista_utenti'];
//$username = htmlEntities($_POST["username"], ENT_QUOTES);
//$email = htmlEntities($_POST["email"], ENT_QUOTES);
//$nome = htmlEntities($_POST["nome"], ENT_QUOTES);
//$cognome = htmlEntities($_POST["cognome"], ENT_QUOTES);
//$password = $_POST["password"];
//$tipoUtenza = htmlEntities($_POST["form_utenza"], ENT_QUOTES);
//$logger->debug("[saveMultipleUserAction] filename ".json_encode($filename));
foreach ($_FILES as $file) {
$fileName = basename($file['name']);
$logger->debug("[saveMultipleUserAction] filename ".json_encode($fileName));
}
$myfile = fopen($_FILES['form_lista_utenti']['tmp_name'], "r") or die("Unable to open file!");
//$myfile = file_get_contents($_FILES['form_lista_utenti']['tmp_name']);
$logger->debug("[saveMultipleUserAction] myfile ".json_encode($myfile));
$firstline = true;
$ContaUser=0;
$ContaNonUser=0;
$TotaliUtenti=0;
while (($data = fgetcsv($myfile, 10000, ';')) !== FALSE) {
try{
if (!$firstline)
{
$TotaliUtenti=$TotaliUtenti+1;
$logger->debug("[saveMultipleUserAction] data ".json_encode($data));
$username = $data[0];
$logger->debug("[saveMultipleUserAction] username ".json_encode($username));
$email = $data[1];
$logger->debug("[saveMultipleUserAction] email ".json_encode($email));
$nome = $data[2];
$logger->debug("[saveMultipleUserAction] nome ".json_encode($nome));
$cognome = $data[3];
$logger->debug("[saveMultipleUserAction] cognome ".json_encode($cognome));
$password = $data[4];
$logger->debug("[saveMultipleUserAction] password ".json_encode($password));
$ChangeText=1;
if ($request->request->has('chk_status')) {
$status = 1;
} else {
$status = 0;
}
if ($request->request->has('chk_token')) {
$FlagToken = 1;
} else {
$FlagToken = 0;
}
$tipoUtenza= 'ROLE_USER';
$user = $em->find('App:User', $this->getUser()->getId());
//$encoder = $this->getParameter('security.password_encoder');
$password = $userPasswordEncoderInterface->encodePassword($user, $password);//$encoder->encodePassword($user, $password);
$q = 'insert into user_acm(username, text1, email, first_name, last_name, user_role, status, change_text1 , flag_token) values('
. ':username, :password, :email, :first_name, :last_name, :user_role, :status, :change_text1, :flag_token);';
$statement = $connection->prepare($q);
$statement->bindValue('username', $username);
$statement->bindValue('password', $password);
$statement->bindValue('email', $email);
$statement->bindValue('first_name', $nome);
$statement->bindValue('last_name', $cognome);
$statement->bindValue('user_role', $tipoUtenza);
$statement->bindValue('status', $status);
$statement->bindValue('change_text1', $ChangeText);
$statement->bindValue('flag_token', $FlagToken);
$statement->execute();
$logger->debug("[saveMultipleUserAction] Prelevo id Utente appena inserito");
$sql="select id from user_acm where username = '$username' and email='$email'";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$ResulQuery = $stmt->fetchAll()[0];
$IdUsername=$ResulQuery['id'];
//FLAG TOKEN API
if ($request->request->has('chk_token')) {
//update user flag_token
$u = "update user set flag_token = 1 where id= '$IdUsername'";
$stmt = $em->getConnection()->prepare($u);
$stmt->execute();
}
//$encoded = $encoder->encodePassword($user, $password);
//$user->setChangePassword($encoded);
$logger->debug("[saveMultipleUserAction] --- Valori Utente --- ");
$logger->debug("[saveMultipleUserAction] $username ");
$logger->debug("[saveMultipleUserAction] $email");
$logger->debug("[saveMultipleUserAction] $nome ");
$logger->debug("[saveMultipleUserAction] $cognome");
$logger->debug("[saveMultipleUserAction] $password");
$logger->debug("[saveMultipleUserAction] $status");
$logger->debug("[saveMultipleUserAction] $ChangeText");
$token = $this->generateToken($params);
$i = 'insert into user_token(id_user, token, date_start_validity, date_end_validity, check_validity)'
."values( '$IdUsername', '$token', SYSDATE(), TO_DATE('31-12-2999 23:59:59', 'dd-mm-yyyy HH24:mi:ss'), 'Y')";
$stmt = $em->getConnection()->prepare($i);
$stmt->execute();
$sql="select id_roles,description_roles from roles";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$ResultRoles = $stmt->fetchAll();
for($i=0; $i<count($ResultRoles); $i++)
{
$ruolo=$ResultRoles[$i]['description_roles'];
$idRuolo=$ResultRoles[$i]['id_roles'];
$logger->debug("[saveMultipleUserAction] Valore Ruolo ".$ruolo);
$logger->debug("[saveMultipleUserAction] Id Ruolo ".$idRuolo);
$profile = htmlEntities($_POST["profile_".$ruolo], ENT_QUOTES);
$logger->debug("[saveMultipleUserAction] Valore Profilo ".$profile);
if($profile != "None")
{
$sql="select id_profile from profile where type_profile = '$profile'";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$ResulQuery = $stmt->fetchAll()[0];
$IdProfile=$ResulQuery['id_profile'];
$sql="insert into user_profile_roles (id_user, id_profile, id_roles) values ($IdUsername, $IdProfile, $idRuolo)";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
}
}
$ContaUser=$ContaUser+1;
}
} catch (\Throwable $th) {
$ContaNonUser=$ContaNonUser+1;
}
$firstline = false;
}
// return $this->redirect($this->generateUrl('user_manage'));
$res_json = new JsonResponse;
$res_json->setData(array('TotaliUtenti' => $TotaliUtenti,
'ContaUser' => $ContaUser,
'ContaNonUser' => $ContaNonUser));
return $res_json;
}
public function generateToken($params){
$em = $this->getDoctrine()->getManager();
$randomString = substr(str_replace(['+', '/', '='], '', base64_encode(random_bytes(32))), 0, 30); // 32 chars, without /=+
$q = "select count(*) cnt from user_token where token = '$randomString'";
$statement = $em->getConnection()->prepare($q);
$statement->execute();
$ResulQuery = $statement->fetchAll()[0];
$count = $ResulQuery['cnt'];
if ($count > 0) {
generateToken($params);
} else {
return $randomString;
}
}
//--------------------------------------------------------
// Aggiorna dati, ruoli e profili di un utente esistente
//--------------------------------------------------------
public function updateUserAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params)
{
//------------------------------------------
// Controllo CSRF
//------------------------------------------
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('edituser', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
try{
//------------------------------------------
// Dati dal form
//------------------------------------------
$id = $_POST["form_id"];
$email = $_POST["form_email"];
$nome = $_POST["form_nome"];
$cognome = $_POST["form_cognome"];
$tipoUtenza = $_POST["form_utenza"];
//------------------------------------------
// Flag
//------------------------------------------
if ($request->request->has('chk_token')){
$f_token = 1;
} else {
$f_token = 0;
}
if ($request->request->has('chk_utenza')){
$statusUtenza = 1;
} else {
$statusUtenza = 0;
}
$em = $this->getDoctrine()->getManager();
//------------------------------------------------
// Stato dell'utente
//------------------------------------------------
$sql = "SELECT CASE WHEN status = true THEN 1 ELSE 0 END AS status
FROM user_acm
WHERE id = :id";
$statement = $em->getConnection()->prepare($sql);
$statement->bindValue('id', $id);
$statement->execute();
$ResulQuery = $statement->fetch();
$status = $ResulQuery['status'];
if ($statusUtenza != $status) {
$q = "update user_acm set status = :statusUtenza where id = :id";
$statement = $em->getConnection()->prepare($q);
$statement->bindValue('statusUtenza', $statusUtenza);
$statement->bindValue('id', $id);
$statement->execute();
}
//------------------------------------------------
// Flag token dell'utente
//------------------------------------------------
$sql = "SELECT flag_token FROM user_acm WHERE id = :id";
$statement = $em->getConnection()->prepare($sql);
$statement->bindValue('id', $id);
$statement->execute();
$ResulQuery = $statement->fetch();
$flag_token = $ResulQuery['flag_token'];
if ($f_token != $flag_token) {
$q = 'update user_acm set flag_token = :flag_token where id = :id;';
$statement = $em->getConnection()->prepare($q);
$statement->bindValue('flag_token', $f_token);
$statement->bindValue('id', $id);
$statement->execute();
}
//------------------------------------------------
// Controllo se esiste un token per l'utente
//------------------------------------------------
$q = "SELECT COUNT(*) AS token FROM user_token WHERE id_user = :id";
$statement = $em->getConnection()->prepare($q);
$statement->bindValue('id', $id);
$statement->execute();
$ResulQuery = $statement->fetch();
$token = $ResulQuery['token'];
//---------------------------------------------------------
// Se token non esiste e flag attivo, creo un nuovo token
//---------------------------------------------------------
if ($f_token == 1 && $token == 0) {
$token = $this->generateToken($params);
$i = "INSERT INTO user_token(
id_user, token, date_start_validity, date_end_validity, check_validity
) VALUES (
:id_user, :token, SYSDATE(), TO_DATE('31-12-2999 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 'Y'
)";
$stmt = $em->getConnection()->prepare($i);
$stmt->bindValue('id_user', $id);
$stmt->bindValue('token', $token);
$stmt->execute();
}
//------------------------------------------------
// Update dei dati dell'utente
//------------------------------------------------
$q = 'UPDATE user_acm SET
email = :email,
first_name = :first_name,
last_name = :last_name,
user_role = :user_role
WHERE id = :id';
$statement = $em->getConnection()->prepare($q);
$statement->bindValue('email', $email);
$statement->bindValue('first_name', $nome);
$statement->bindValue('last_name', $cognome);
$statement->bindValue('user_role', $tipoUtenza);
$statement->bindValue('id', $id);
$statement->execute();
$sql="select id_roles, description_roles from roles order by 2";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$ResultRoles = $stmt->fetchAll();
$sql = "DELETE FROM user_profile_roles WHERE id_user = :id_user";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('id_user', $id);
$stmt->execute();
for ($i = 0; $i < count($ResultRoles); $i++) {
$ruolo = $ResultRoles[$i]['description_roles'];
$idRuolo = $ResultRoles[$i]['id_roles'];
$logger->debug("[updateUserAction] Valore Ruolo " . $ruolo);
$logger->debug("[updateUserAction] Id Ruolo " . $idRuolo);
$profile = $_POST["profile_" . $ruolo];
$logger->debug("[updateUserAction] Valore Profilo " . $profile);
if ($profile != "None") {
$logger->debug("[updateUserAction] Profilo diverso da None");
//------------------------------------------------
// Recupero l'ID del profilo selezionato
//------------------------------------------------
$sql = "SELECT id_profile FROM profile WHERE type_profile = :profile";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('profile', $profile);
$stmt->execute();
$ResulQuery = $stmt->fetch();
$IdProfile = $ResulQuery['id_profile'];
//------------------------------------------------
// Insert nuova associazione utente-profilo-ruolo
//------------------------------------------------
$sql = "INSERT INTO user_profile_roles (id_user, id_profile, id_roles)
VALUES (:id_user, :id_profile, :id_roles)";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('id_user', $id);
$stmt->bindValue('id_profile', $IdProfile);
$stmt->bindValue('id_roles', $idRuolo);
$stmt->execute();
}
}
$ExitUpdateUser = 0;
$MsgUpdateUser = 'Utente Modificato con Successo';
} catch (\Exception $ex) {
$logger->debug("[updateUserAction] Errore nella modifica dell'utente: " . $ex);
$ExitUpdateUser = 1;
$MsgUpdateUser = "Errore nella modifica dell'utente.";
}
$res_json = new JsonResponse;
$res_json->setData(array(
'ExitUpdateUser' => $ExitUpdateUser,
'MsgUpdateUser' => $MsgUpdateUser
));
return $res_json;
}
public function deleteUserAction(Request $request, ParameterBagInterface $params, LoggerInterface $logger)
{
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('deluser', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
try{
$id = $_POST["form_id_del"];
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$userLang = new UserLanguageQueries($connection);
$q0 = 'select username from user_acm where id = :id;';
$stmt = $connection->prepare($q0);
$stmt->bindValue('id', $id);
$stmt->execute();
$username = $stmt->fetchAll()[0]["username"];
$q = 'delete from user_acm where id = :id;';
$statement = $connection->prepare($q);
$statement->bindValue('id', $id);
$statement->execute();
$userLang->deleteByUser($id);
$ExitDeleteUser=0;
$MsgDeleteUser="Utente $username Cancellato con Successo ";
} catch (\Exception $ex) {
$logger->debug("[deleteUserAction] Errore nella Cancellazione dell'utente: " . $ex);
$ExitDeleteUser=1;
$MsgDeleteUser="Errore nella Cancellazione dell'utente $username";
}
$res_json = new JsonResponse;
$res_json->setData(array(
'ExitDeleteUser' => $ExitDeleteUser,
'MsgDeleteUser' => $MsgDeleteUser
));
return $res_json;
}
//-----------------------------------------------------------
// Reindirizza l'utente all'URL della documentazione
// configurato nel file .env
//-----------------------------------------------------------
public function redirectDocumentation(){
$url = $_ENV['PRODUCT_DOC_URL'];
return $this->redirect($url);
}
}

src/Controller/SecurityController.php line 31