acctfuncs.inc.php 42.3 KB
Newer Older
1001

1002
1003
1004
1005
1006
/**
 * Remove sessions from the database that have exceed the timeout
 *
 * @return void
 */
1007
function clear_expired_sessions() {
1008
	$dbh = DB::connect();
canyonknight's avatar
canyonknight committed
1009

1010
	$timeout = config_get_int('options', 'login_timeout');
1011
	$q = "DELETE FROM Sessions WHERE LastUpdateTS < (" . strval(time()) . " - " . $timeout . ")";
canyonknight's avatar
canyonknight committed
1012
	$dbh->query($q);
1013
1014
1015

	return;
}
1016

1017
1018
1019
1020
1021
1022
1023
1024
/**
 * Get account details for a specific user
 *
 * @param string $uid The User ID of account to get information for
 * @param string $username The username of the account to get for
 *
 * @return array Account details for the specified user
 */
1025
function account_details($uid, $username) {
1026
	$dbh = DB::connect();
canyonknight's avatar
canyonknight committed
1027
1028
1029
1030
1031
1032
	$q = "SELECT Users.*, AccountTypes.AccountType ";
	$q.= "FROM Users, AccountTypes ";
	$q.= "WHERE AccountTypes.ID = Users.AccountTypeID ";
	if (!empty($uid)) {
		$q.= "AND Users.ID = ".intval($uid);
	} else {
canyonknight's avatar
canyonknight committed
1033
		$q.= "AND Users.Username = " . $dbh->quote($username);
canyonknight's avatar
canyonknight committed
1034
	}
canyonknight's avatar
canyonknight committed
1035
	$result = $dbh->query($q);
canyonknight's avatar
canyonknight committed
1036
1037

	if ($result) {
canyonknight's avatar
canyonknight committed
1038
		$row = $result->fetch(PDO::FETCH_ASSOC);
canyonknight's avatar
canyonknight committed
1039
1040
1041
1042
1043
	}

	return $row;
}

1044
1045
1046
1047
1048
1049
1050
1051
/**
 * Determine if a user has already voted on a specific proposal
 *
 * @param string $voteid The ID of the Trusted User proposal
 * @param string $uid The ID to check if the user already voted
 *
 * @return bool True if the user has already voted, otherwise false
 */
1052
function tu_voted($voteid, $uid) {
1053
	$dbh = DB::connect();
1054

canyonknight's avatar
canyonknight committed
1055
1056
1057
1058
	$q = "SELECT COUNT(*) FROM TU_Votes ";
	$q.= "WHERE VoteID = " . intval($voteid) . " AND UserID = " . intval($uid);
	$result = $dbh->query($q);
	if ($result->fetchColumn() > 0) {
1059
1060
1061
1062
1063
1064
1065
		return true;
	}
	else {
		return false;
	}
}

1066
1067
1068
1069
1070
1071
1072
/**
 * Get all current Trusted User proposals from the database
 *
 * @param string $order Ascending or descending order for the proposal listing
 *
 * @return array The details for all current Trusted User proposals
 */
1073
function current_proposal_list($order) {
1074
	$dbh = DB::connect();
1075
1076

	$q = "SELECT * FROM TU_VoteInfo WHERE End > " . time() . " ORDER BY Submitted " . $order;
canyonknight's avatar
canyonknight committed
1077
	$result = $dbh->query($q);
1078

1079
	$details = array();
canyonknight's avatar
canyonknight committed
1080
	while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
1081
1082
1083
1084
1085
1086
		$details[] = $row;
	}

	return $details;
}

1087
1088
1089
1090
1091
1092
1093
1094
/**
 * Get a subset of all past Trusted User proposals from the database
 *
 * @param string $order Ascending or descending order for the proposal listing
 * @param string $lim The number of proposals to list with the offset
 *
 * @return array The details for the subset of past Trusted User proposals
 */
1095
function past_proposal_list($order, $lim) {
1096
	$dbh = DB::connect();
1097
1098

	$q = "SELECT * FROM TU_VoteInfo WHERE End < " . time() . " ORDER BY Submitted " . $order . $lim;
canyonknight's avatar
canyonknight committed
1099
	$result = $dbh->query($q);
1100

1101
	$details = array();
canyonknight's avatar
canyonknight committed
1102
	while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
1103
1104
1105
1106
1107
1108
		$details[] = $row;
	}

	return $details;
}

Lukas Fleischer's avatar
Lukas Fleischer committed
1109
1110
1111
1112
1113
1114
1115
1116
/**
 * Get the vote ID of the last vote of all Trusted Users
 *
 * @return array The vote ID of the last vote of each Trusted User
 */
function last_votes_list() {
	$dbh = DB::connect();

1117
	$q = "SELECT UserID, MAX(VoteID) AS LastVote FROM TU_Votes, ";
1118
	$q .= "TU_VoteInfo, Users WHERE TU_VoteInfo.ID = TU_Votes.VoteID AND ";
1119
	$q .= "TU_VoteInfo.End < " . strval(time()) . " AND ";
1120
	$q .= "Users.ID = TU_Votes.UserID AND (Users.AccountTypeID = 2 OR Users.AccountTypeID = 4) ";
1121
	$q .= "GROUP BY UserID ORDER BY LastVote DESC, UserName ASC";
Lukas Fleischer's avatar
Lukas Fleischer committed
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
	$result = $dbh->query($q);

	$details = array();
	while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
		$details[] = $row;
	}

	return $details;
}

1132
1133
1134
1135
1136
/**
 * Determine the total number of Trusted User proposals
 *
 * @return string The total number of Trusted User proposals
 */
1137
function proposal_count() {
1138
	$dbh = DB::connect();
1139
	$q = "SELECT COUNT(*) FROM TU_VoteInfo";
canyonknight's avatar
canyonknight committed
1140
1141
	$result = $dbh->query($q);
	$row = $result->fetch(PDO::FETCH_NUM);
1142
1143
1144
1145

	return $row[0];
}

1146
1147
1148
1149
1150
1151
1152
/**
 * Get all details related to a specific vote from the database
 *
 * @param string $voteid The ID of the Trusted User proposal
 *
 * @return array All stored details for a specific vote
 */
1153
function vote_details($voteid) {
1154
	$dbh = DB::connect();
1155
1156
1157
1158

	$q = "SELECT * FROM TU_VoteInfo ";
	$q.= "WHERE ID = " . intval($voteid);

canyonknight's avatar
canyonknight committed
1159
1160
	$result = $dbh->query($q);
	$row = $result->fetch(PDO::FETCH_ASSOC);
1161
1162
1163
1164

	return $row;
}

1165
1166
1167
1168
1169
/**
 * Get an alphabetical list of users who voted for a proposal with HTML links
 *
 * @param string $voteid The ID of the Trusted User proposal
 *
Lukas Fleischer's avatar
Lukas Fleischer committed
1170
 * @return array All users who voted for a specific proposal
1171
 */
1172
function voter_list($voteid) {
1173
	$dbh = DB::connect();
1174

Lukas Fleischer's avatar
Lukas Fleischer committed
1175
	$whovoted = array();
1176

1177
1178
1179
1180
1181
1182
	$q = "SELECT tv.UserID,U.Username ";
	$q.= "FROM TU_Votes tv, Users U ";
	$q.= "WHERE tv.VoteID = " . intval($voteid);
	$q.= " AND tv.UserID = U.ID ";
	$q.= "ORDER BY Username";

canyonknight's avatar
canyonknight committed
1183
	$result = $dbh->query($q);
1184
	if ($result) {
canyonknight's avatar
canyonknight committed
1185
		while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
Lukas Fleischer's avatar
Lukas Fleischer committed
1186
			$whovoted[] = $row['Username'];
1187
1188
1189
1190
1191
		}
	}
	return $whovoted;
}

1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
/**
 * Cast a vote for a specific user proposal
 *
 * @param string $voteid The ID of the proposal being voted on
 * @param string $uid The user ID of the individual voting
 * @param string $vote Vote position, either "Yes", "No", or "Abstain"
 * @param int $newtotal The total number of votes after the user has voted
 *
 * @return void
 */
1202
function cast_proposal_vote($voteid, $uid, $vote, $newtotal) {
1203
	$dbh = DB::connect();
1204

canyonknight's avatar
canyonknight committed
1205
1206
	$q = "UPDATE TU_VoteInfo SET " . $vote . " = (" . $newtotal . ") WHERE ID = " . $voteid;
	$result = $dbh->exec($q);
1207

canyonknight's avatar
canyonknight committed
1208
1209
	$q = "INSERT INTO TU_Votes (VoteID, UserID) VALUES (" . intval($voteid) . ", " . intval($uid) . ")";
	$result = $dbh->exec($q);
1210
}
1211
1212
1213
1214
1215
1216
1217
1218

/**
 * Verify a user has the proper permissions to edit an account
 *
 * @param array $acctinfo User account information for edited account
 *
 * @return bool True if permission to edit the account, otherwise false
 */
Lukas Fleischer's avatar
Lukas Fleischer committed
1219
function can_edit_account($acctinfo) {
1220
1221
	if ($acctinfo['AccountType'] == 'Developer' ||
	    $acctinfo['AccountType'] == 'Trusted User & Developer') {
Lukas Fleischer's avatar
Lukas Fleischer committed
1222
		return has_credential(CRED_ACCOUNT_EDIT_DEV);
1223
1224
	}

1225
	$uid = $acctinfo['ID'];
Lukas Fleischer's avatar
Lukas Fleischer committed
1226
	return has_credential(CRED_ACCOUNT_EDIT, array($uid));
1227
}
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258

/*
 * Compute the fingerprint of an SSH key.
 *
 * @param string $ssh_key The SSH public key to retrieve the fingerprint for
 *
 * @return string The SSH key fingerprint
 */
function ssh_key_fingerprint($ssh_key) {
	$tmpfile = tempnam(sys_get_temp_dir(), "aurweb");
	file_put_contents($tmpfile, $ssh_key);

	/*
	 * The -l option of ssh-keygen can be used to show the fingerprint of
	 * the specified public key file. Expected output format:
	 *
	 *     2048 SHA256:uBBTXmCNjI2CnLfkuz9sG8F+e9/T4C+qQQwLZWIODBY user@host (RSA)
	 *
	 * ... where 2048 is the key length, the second token is the actual
	 * fingerprint, followed by the key comment and the key type.
	 */

	$cmd = "/usr/bin/ssh-keygen -l -f " . escapeshellarg($tmpfile);
	exec($cmd, $out, $ret);
	if ($ret !== 0 || count($out) !== 1) {
		return false;
	}

	unlink($tmpfile);

	$tokens = explode(' ', $out[0]);
1259
	if (count($tokens) < 4) {
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
		return false;
	}

	$tokens = explode(':', $tokens[1]);
	if (count($tokens) != 2 || $tokens[0] != 'SHA256') {
		return false;
	}

	return $tokens[1];
}

/*
 * Get the SSH public keys associated with an account.
 *
 * @param int $uid The user ID of the account to retrieve the keys for.
 *
 * @return array An array representing the keys
 */
function account_get_ssh_keys($uid) {
	$dbh = DB::connect();
	$q = "SELECT PubKey FROM SSHPubKeys WHERE UserID = " . intval($uid);
	$result = $dbh->query($q);

	if ($result) {
		return $result->fetchAll(PDO::FETCH_COLUMN, 0);
	} else {
		return array();
	}
}

/*
 * Set the SSH public keys associated with an account.
 *
 * @param int $uid The user ID of the account to assign the keys to.
 * @param array $ssh_keys The SSH public keys.
 * @param array $ssh_fingerprints The corresponding SSH key fingerprints.
 *
 * @return bool Boolean flag indicating success or failure.
 */
function account_set_ssh_keys($uid, $ssh_keys, $ssh_fingerprints) {
	$dbh = DB::connect();

	$q = sprintf("DELETE FROM SSHPubKeys WHERE UserID = %d", $uid);
	$dbh->exec($q);

	$ssh_fingerprint = reset($ssh_fingerprints);
	foreach ($ssh_keys as $ssh_key) {
		$q = sprintf(
			"INSERT INTO SSHPubKeys (UserID, Fingerprint, PubKey) " .
			"VALUES (%d, %s, %s)", $uid,
			$dbh->quote($ssh_fingerprint), $dbh->quote($ssh_key)
		);
		$dbh->exec($q);
		$ssh_fingerprint = next($ssh_fingerprints);
	}

	return true;
}
1318
1319
1320
1321
1322
1323
1324
1325

/*
 * Invoke the email notification script.
 *
 * @param string $params Command line parameters for the script.
 *
 * @return void
 */
1326
function notify($params) {
1327
	$cmd = config_get('notifications', 'notify-cmd');
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
	foreach ($params as $param) {
		$cmd .= ' ' . escapeshellarg($param);
	}

	$descspec = array(
		0 => array('pipe', 'r'),
		1 => array('pipe', 'w'),
	);

	$p = proc_open($cmd, $descspec, $pipes);

	if (!is_resource($p)) {
		return false;
	}

	fclose($pipes[0]);
	fclose($pipes[1]);

	return proc_close($p);
}
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421

/*
 * Obtain a list of terms a given user has not yet accepted.
 *
 * @param int $uid The ID of the user to obtain terms for.
 *
 * @return array A list of terms the user has not yet accepted.
 */
function fetch_updated_terms($uid) {
	$dbh = DB::connect();

	$q = "SELECT ID, Terms.Revision, Description, URL ";
	$q .= "FROM Terms LEFT JOIN AcceptedTerms ";
	$q .= "ON AcceptedTerms.TermsID = Terms.ID ";
	$q .= "AND AcceptedTerms.UsersID = " . intval($uid) . " ";
	$q .= "WHERE AcceptedTerms.Revision IS NULL OR ";
	$q .= "AcceptedTerms.Revision < Terms.Revision";

	$result = $dbh->query($q);

	if ($result) {
		return $result->fetchAll();
	} else {
		return array();
	}
}

/*
 * Accept a list of given terms.
 *
 * @param int $uid The ID of the user to accept the terms.
 * @param array $termrev An array mapping each term to the accepted revision.
 *
 * @return void
 */
function accept_terms($uid, $termrev) {
	$dbh = DB::connect();

	$q = "SELECT TermsID, Revision FROM AcceptedTerms ";
	$q .= "WHERE UsersID = " . intval($uid);

	$result = $dbh->query($q);

	if (!$result) {
		return;
	}

	$termrev_update = array();
	while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
		$id = $row['TermsID'];
		if (!array_key_exists($id, $termrev)) {
			continue;
		}
		if ($row['Revision'] < $termrev[$id]) {
			$termrev_update[$id] = $termrev[$id];
		}
	}
	$termrev_add = array_diff_key($termrev, $termrev_update);

	foreach ($termrev_add as $id => $rev) {
		$q = "INSERT INTO AcceptedTerms (TermsID, UsersID, Revision) ";
		$q .= "VALUES (" . intval($id) . ", " . intval($uid) . ", ";
		$q .= intval($rev) . ")";
		$dbh->exec($q);
	}

	foreach ($termrev_update as $id => $rev) {
		$q = "UPDATE AcceptedTerms ";
		$q .= "SET Revision = " . intval($rev) . " ";
		$q .= "WHERE TermsID = " . intval($id) . " AND ";
		$q .= "UsersID = " . intval($uid);
		$dbh->exec($q);
	}
}
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463

function account_comments($uid, $limit, $offset=0) {
	$dbh = DB::connect();
	$q = "SELECT PackageComments.ID, Comments, UsersID, ";
	$q.= "PackageBaseId, CommentTS, DelTS, EditedTS, B.UserName AS EditUserName, ";
	$q.= "PinnedTS, ";
	$q.= "C.UserName as DelUserName, RenderedComment, ";
	$q.= "PB.ID as PackageBaseID, PB.Name as PackageBaseName ";
	$q.= "FROM PackageComments ";
	$q.= "LEFT JOIN PackageBases PB ON PackageComments.PackageBaseID = PB.ID ";
	$q.= "LEFT JOIN Users A ON PackageComments.UsersID = A.ID ";
	$q.= "LEFT JOIN Users B ON PackageComments.EditedUsersID = B.ID ";
	$q.= "LEFT JOIN Users C ON PackageComments.DelUsersID = C.ID ";
	$q.= "WHERE A.ID = " . $dbh->quote($uid) . " ";
	$q.= "ORDER BY CommentTS DESC";

	if ($limit > 0) {
		$q.=" LIMIT " . intval($limit);
	}

	if ($offset > 0) {
		$q.=" OFFSET " . intval($offset);
	}

	$result = $dbh->query($q);
	if (!$result) {
		return null;
	}

	return $result->fetchAll();
}

function account_comments_count($uid) {
	$dbh = DB::connect();
	$q = "SELECT COUNT(*) ";
	$q.= "FROM PackageComments ";
	$q.= "LEFT JOIN Users A ON PackageComments.UsersID = A.ID ";
	$q.= "WHERE A.ID = " . $dbh->quote($uid);

	$result = $dbh->query($q);
	return $result->fetchColumn();
}
1464
1465

/*
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
 * Compute the list of active CAPTCHA salts. The salt changes based on the
 * number of registered users. This ensures that new users always use a
 * different salt and protects against hardcoding the CAPTCHA response.
 *
 * The first CAPTCHA in the list is the most recent one and should be used for
 * new CAPTCHA challenges. The other ones are slightly outdated but may still
 * be valid for recent challenges that were created before the number of users
 * increased. The current implementation ensures that we can still use our
 * CAPTCHA salt, even if five new users registered since the CAPTCHA challenge
 * was created.
 *
 * @return string The list of active salts, the first being the most recent
 * one.
1479
 */
1480
function get_captcha_salts() {
1481
1482
1483
1484
	$dbh = DB::connect();
	$q = "SELECT count(*) FROM Users";
	$result = $dbh->query($q);
	$user_count = $result->fetchColumn();
1485
1486
1487
1488
1489
1490

	$ret = array();
	for ($i = 0; $i <= 5; $i++) {
		array_push($ret, 'aurweb-' . ($user_count - $i));
	}
	return $ret;
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
}

/*
 * Return the CAPTCHA challenge for a given salt.
 *
 * @param string $salt The salt to be used for the CAPTCHA computation.
 *
 * @return string The challenge as a string.
 */
function get_captcha_challenge($salt) {
	$token = substr(md5($salt), 0, 3);
	return "LC_ALL=C pacman -V|sed -r 's#[0-9]+#" . $token . "#g'|md5sum|cut -c1-6";
}

/*
 * Compute CAPTCHA answer for a given salt.
 *
 * @param string $salt The salt to be used for the CAPTCHA computation.
 *
 * @return string The correct answer as a string.
 */
function get_captcha_answer($salt) {
	$token = substr(md5($salt), 0, 3);
	$text = <<<EOD

 .--.                  Pacman v$token.$token.$token - libalpm v$token.$token.$token
/ _.-' .-.  .-.  .-.   Copyright (C) $token-$token Pacman Development Team
\  '-. '-'  '-'  '-'   Copyright (C) $token-$token Judd Vinet
 '--'
                       This program may be freely redistributed under
                       the terms of the GNU General Public License.

EOD;
	return substr(md5($text . "\n"), 0, 6);
}
For faster browsing, not all history is shown. View entire blame