loginform.php
14 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
<?php
class LoginForm extends DbConn
{
public function checkLogin($myusername, $mypassword, $myentidad)
{
$conf = new GlobalConf;
$ip_address = $conf->ip_address;
$login_timeout = $conf->login_timeout;
$max_attempts = $conf->max_attempts;
$timeout_minutes = $conf->timeout_minutes;
$attcheck = checkAttempts($myusername);
$curr_attempts = $attcheck['attempts'];
$datetimeNow = date("Y-m-d H:i:s");
$oldTime = strtotime($attcheck['lastlogin']);
$newTime = strtotime($datetimeNow);
$timeDiff = $newTime - $oldTime;
// QUERY
try {
$db = new DbConn;
$tbl_members = $db->tbl_members;
$err = '';
} catch (PDOException $e) {
$err = "Error: " . $e->getMessage();
}
//Se setea el search_path al esquema correspondiente..
$stentidad = $db->conn->prepare("SET search_path TO sigem".$myentidad);
$stentidad->execute();
$anio = date('Y');
// ***********************************************************************
// Muestra del código el nuevo login de usuarios
// Los datos de la entidad se encuentran en la BD entidades del taperekaha
// ***********************************************************************
/*
// Conexión al taperekaha para traer los parámetros de conexión a la base de datos de la Municipalidad
sql_conexion := 'dbname=sigemweb host=192.168.1.254 port=5432 user=postgres password=x25yvaga2017' ;
sql_consulta := 'SELECT consulta.sigem_site, consulta.sigem_dbname FROM entidades AS consulta WHERE consulta.activo = TRUE AND consulta.entidad = ' || $myentidad || ' ;' ;
// Traer los parámetros de conexión a la base de datos de la Municipalidad
SELECT t1.sigem_site, t1.sigem_dbname
FROM public.dblink(
CAST( sql_conexion AS text ),CAST( sql_consulta AS TEXT )
)
AS t1 (
sigem_site text,
sigem_dbname text
) INTO val_sigem_site, val_sigem_dbname ;
// Hacer la consulta para validar credenciales del usuario
consulta_sql_conexion := ('dbname=' || val_sigem_dbname || ' ' || val_sigem_site) ;
consulta_sql_caso1 := ' SELECT caso1.ejer_fisca AS ejer_fisca, caso1.usu_alias AS usu_alias, ' ;
consulta_sql_caso1 := consulta_sql_caso1 || ' '|| '''LOGIN''' || ' AS tipo_dato ,' ;
consulta_sql_caso1 := consulta_sql_caso1 || ' caso1.usu_numero AS cod_dato, ' ;
consulta_sql_caso1 := consulta_sql_caso1 || ' CONCAT(' || val || ',' || '''-USUARIO EXISTE''' || ' ) AS descripcion_dato ';
consulta_sql_caso1 := consulta_sql_caso1 || ' FROM ( SELECT count(usua.usu_numero) AS cant_usuario, usua.ejer_fisca, usua.usu_alias, usua.usu_numero FROM usuarios usua WHERE ' ;
consulta_sql_caso1 := consulta_sql_caso1 || ' usua.activo = TRUE ';
consulta_sql_caso1 := consulta_sql_caso1 || ' AND usua.ejer_fisca = ' || val_ejer || ' AND usua.usu_alias = ' || val_alias ;
consulta_sql_caso1 := consulta_sql_caso1 || ' AND pgp_sym_decrypt(usua.usu_clave_a,' || '''510580''' || ', ' || '''compress-algo=0, cipher-algo=aes256''' || ' ) = ' || val_clave ;
consulta_sql_caso1 := consulta_sql_caso1 || ' GROUP BY usua.ejer_fisca, usua.usu_alias, usua.usu_numero ) AS caso1 ';
RETURN QUERY SELECT t1.tipo_dato as tipo_dato, t1.cod_dato AS cod_dato, t1.descripcion_dato AS descripcion_dato FROM
public.dblink(CAST(consulta_sql_conexion AS text),
format( 'SELECT consulta.ejer_fisca, consulta.usu_alias, consulta.tipo_dato, consulta.cod_dato, consulta.descripcion_dato FROM (' || consulta_sql || ') AS consulta WHERE consulta.ejer_fisca = %L and consulta.usu_alias = %L GROUP BY consulta.ejer_fisca, consulta.usu_alias, consulta.tipo_dato, consulta.cod_dato, consulta.descripcion_dato ORDER BY consulta.ejer_fisca, consulta.usu_alias, consulta.tipo_dato, consulta.cod_dato, consulta.descripcion_dato ;',usu_ejer_fisca, usu_usu_alias ))
AS t1(
ejer_fisca numeric(18,0),
usu_alias text,
tipo_dato text,
cod_dato numeric(18,0),
descripcion_dato text)
ORDER BY t1.tipo_dato, t1.cod_dato, t1.descripcion_dato
*/
// ***********************************************************************
//SI ENTIDAD es CDE, corre query de password encriptado
//if ($myentidad == 505) {
//Se corre el query directamente sin usar PDO ya que la funcion pgp_sym_decrypt no puede ser escapada desde PDO y no reconoce la funcion
$dbconn = pg_connect("host=$db->host dbname=$db->db_name user=$db->username password=$db->password") or die('Could not connect: ' . pg_last_error());
$query = "SELECT entidad, ejer_fisca, usu_numero, usu_nom, usu_ape, usu_nivel, usu_alias, activo,
pgp_sym_decrypt(usu_clave_a::bytea,'510580')::text as usu_clave
FROM sigem".$myentidad.".usuarios WHERE usu_alias ilike '%".$myusername."%' and ejer_fisca = date_part('year', now())";
$datos = pg_query($query) or die('Error message: ' . pg_last_error());
// Se traen los datos de la tabla usuario
$result = pg_fetch_assoc($datos);
pg_close($dbconn);
/*} else {
// query normal via PDO
$stmt = $db->conn->prepare("SET search_path TO sigem".$myentidad);
$stmt = $db->conn->prepare("SELECT * FROM sigem".$myentidad.".usuarios WHERE usu_alias = :myusername and ejer_fisca = date_part('year', now())");
$stmt->bindParam(':myusername', $myusername);
$stmt->execute();
// Se traen los datos de la tabla usuario
$result = $stmt->fetch(PDO::FETCH_ASSOC);
}*/
if ($curr_attempts >= $max_attempts && $timeDiff < $login_timeout) {
//Demasiados intentos fallidos
$success = "<div class=\"alert alert-danger alert-dismissable\"><button type=\"button\" class=\"close\" data-dismiss=\"alert\" aria-hidden=\"true\">×</button>Numero maximo de reintentos excedido... por favor espere ".$timeout_minutes." minutos e intente nuevamente</div>";
} else {
//Si no se excede el maximo de intentos fallidos, continua....
// TODO: AGREGAR validacion contra hash no password en plano -.-
//La funcion password_verify solo funciona con versiones de php 5.5++, como centos 7 solo llega
//a 5.4 se obvia por de pronto
//if (password_verify($mypassword, $result['usu_clave']) && $result['activo'] == true) {
//Se agrego validacion de credenciales contra la tabla usuarios del esquema correspondiente a la institucion
//donde mypassword sea igual a usu_clave y activo == true
if (($mypassword == $result['usu_clave']) && $result['activo'] == true) {
//if ( $result['activo'] == true) {
//Inicio de Sesion Exitoso
$success = 'true';
session_start();
//Variables tabla sigemxxx.usuarios
$_SESSION['username'] = $myusername;
$_SESSION['entidad'] = $myentidad; //id_entidad
$_SESSION['usu_nom'] = $result['usu_nom'];
$_SESSION['usu_ape'] = $result['usu_ape'];
//Variables tabla public.entidades
try {
$datos = new DbConn;
$err = '';
} catch (PDOException $e) {$err = "Error: " . $e->getMessage();}
// Query
$dtent = $datos->conn->prepare("SELECT * FROM public.entidades WHERE entidad = ".$myentidad);
$dtent->execute();
$datosent = $dtent->fetch(PDO::FETCH_ASSOC);
//TODO: HACER FUNCIONAR EN CASA, asi establecer año fiscal y parametros de version directo de BD
//Variables tabla sigemxxx.parametros
/*try {
$param = new DbConn;
$err = '';
} catch (PDOException $e) {$err = "Error: " . $e->getMessage();}
// Query
$pent = $param->conn->prepare("SELECT * FROM parametros WHERE entidad ilike '".$myentidad."'");
$pent->execute();
$parament = $pent->fetch(PDO::FETCH_ASSOC);
error_log('PARAMETROS ENTIDAD: '.$parament);
error_log('Resultado: '.print_r($parament, true));*/
//DATOS public.entidades
$_SESSION['n_entidad'] = $datosent['nombre'];
$_SESSION['niv_entidad'] = $datosent['niv_entidad'];
$_SESSION['sig_entidad'] = $datosent['sig_entidad'];
$_SESSION['sigem_site'] = $datosent['sigem_site'];
$_SESSION['sigem_dbname'] = $datosent['sigem_dbname'];
$_SESSION['direccion'] = $datosent['direccion'];
$_SESSION['telefono'] = $datosent['telefono'];
$_SESSION['fax'] = $datosent['fax'];
$_SESSION['responsable'] = $datosent['responsable'];
$_SESSION['eslogan'] = $datosent['eslogan'];
$_SESSION['imagen'] = $datosent['imagen'];
$_SESSION['site_url'] = $datosent['site_url'];
$_SESSION['latlong'] = $datosent['latlong'];
$_SESSION['lng'] = $datosent['lng'];
$_SESSION['lat'] = $datosent['lat'];
$_SESSION['zoom'] = $datosent['zoom'];
$_SESSION['maxzoom'] = $datosent['maxzoom'];
$_SESSION['minzoom'] = $datosent['minzoom'];
$_SESSION['mapa_base'] = $datosent['mapa_base'];
$_SESSION['boundno'] = $datosent['boundno'];
$_SESSION['boundse'] = $datosent['boundse'];
//} elseif (password_verify($mypassword, $result['usu_clave']) && $result['activo'] == true) {
// TODO: AGREGAR validacion contra hash no password en plano -.-
} elseif (($mypassword == $result['usu_clave']) && $result['activo'] == true) {
//Cuenta sin verificar
$success = "<div class=\"alert alert-danger alert-dismissable\"><button type=\"button\" class=\"close\" data-dismiss=\"alert\" aria-hidden=\"true\">×</button>La cuenta ha sido creada, pero no puedes iniciar sesion hasta que sea verificada</div>";
} else {
//Usuario o password invalido
$success = "<div class=\"alert alert-danger alert-dismissable\"><button type=\"button\" class=\"close\" data-dismiss=\"alert\" aria-hidden=\"true\">×</button>Usuario o Contraseña Incorrecto/s</div>";
}
return $success;
}
}
public function insertAttempt($username)
{
try {
$db = new DbConn;
$conf = new GlobalConf;
$tbl_attempts = $db->tbl_attempts;
$ip_address = $conf->ip_address;
$login_timeout = $conf->login_timeout;
$max_attempts = $conf->max_attempts;
$datetimeNow = date("Y-m-d H:i:s");
$attcheck = checkAttempts($username);
$curr_attempts = $attcheck['attempts'];
$stmt = $db->conn->prepare("INSERT INTO ".$tbl_attempts." (ip, attempts, lastlogin, username) values(:ip, 1, :lastlogin, :username)");
$stmt->bindParam(':ip', $ip_address);
$stmt->bindParam(':lastlogin', $datetimeNow);
$stmt->bindParam(':username', $username);
$stmt->execute();
$curr_attempts++;
$err = '';
} catch (PDOException $e) {
$err = "Error: " . $e->getMessage();
}
//Determines returned value ('true' or error code)
$resp = ($err == '') ? 'true' : $err;
return $resp;
}
public function updateAttempts($username)
{
try {
$db = new DbConn;
$conf = new GlobalConf;
$tbl_attempts = $db->tbl_attempts;
$ip_address = $conf->ip_address;
$login_timeout = $conf->login_timeout;
$max_attempts = $conf->max_attempts;
$timeout_minutes = $conf->timeout_minutes;
$att = new LoginForm;
$attcheck = checkAttempts($username);
$curr_attempts = $attcheck['attempts'];
$datetimeNow = date("Y-m-d H:i:s");
$oldTime = strtotime($attcheck['lastlogin']);
$newTime = strtotime($datetimeNow);
$timeDiff = $newTime - $oldTime;
$err = '';
$sql = '';
if ($curr_attempts >= $max_attempts && $timeDiff < $login_timeout) {
if ($timeDiff >= $login_timeout) {
$sql = "UPDATE ".$tbl_attempts." SET attempts = :attempts, lastlogin = :lastlogin where ip = :ip and username = :username";
$curr_attempts = 1;
}
} else {
if ($timeDiff < $login_timeout) {
$sql = "UPDATE ".$tbl_attempts." SET attempts = :attempts, lastlogin = :lastlogin where ip = :ip and username = :username";
$curr_attempts++;
} elseif ($timeDiff >= $login_timeout) {
$sql = "UPDATE ".$tbl_attempts." SET attempts = :attempts, lastlogin = :lastlogin where ip = :ip and username = :username";
$curr_attempts = 1;
}
$stmt2 = $db->conn->prepare($sql);
$stmt2->bindParam(':attempts', $curr_attempts);
$stmt2->bindParam(':ip', $ip_address);
$stmt2->bindParam(':lastlogin', $datetimeNow);
$stmt2->bindParam(':username', $username);
$stmt2->execute();
}
} catch (PDOException $e) {
$err = "Error: " . $e->getMessage();
}
//Determines returned value ('true' or error code) (ternary)
$resp = ($err == '') ? 'true' : $err;
return $resp;
}
}