acctfuncs.inc.php 42.2 KB
Newer Older
1001
1002
1003
1004
1005

	$q = "DELETE FROM Sessions WHERE UsersID = " . intval($uid);
	$dbh->exec($q);
}

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

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

	return;
}
1020

1021
1022
1023
1024
1025
1026
1027
1028
/**
 * 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
 */
1029
function account_details($uid, $username) {
1030
	$dbh = DB::connect();
canyonknight's avatar
canyonknight committed
1031
1032
1033
1034
1035
1036
	$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
1037
		$q.= "AND Users.Username = " . $dbh->quote($username);
canyonknight's avatar
canyonknight committed
1038
	}
canyonknight's avatar
canyonknight committed
1039
	$result = $dbh->query($q);
canyonknight's avatar
canyonknight committed
1040
1041

	if ($result) {
canyonknight's avatar
canyonknight committed
1042
		$row = $result->fetch(PDO::FETCH_ASSOC);
canyonknight's avatar
canyonknight committed
1043
1044
1045
1046
1047
	}

	return $row;
}

1048
1049
1050
1051
1052
1053
1054
1055
/**
 * 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
 */
1056
function tu_voted($voteid, $uid) {
1057
	$dbh = DB::connect();
1058

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

1070
1071
1072
1073
1074
1075
1076
/**
 * 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
 */
1077
function current_proposal_list($order) {
1078
	$dbh = DB::connect();
1079
1080

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

1083
	$details = array();
canyonknight's avatar
canyonknight committed
1084
	while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
1085
1086
1087
1088
1089
1090
		$details[] = $row;
	}

	return $details;
}

1091
1092
1093
1094
1095
1096
1097
1098
/**
 * 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
 */
1099
function past_proposal_list($order, $lim) {
1100
	$dbh = DB::connect();
1101
1102

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

1105
	$details = array();
canyonknight's avatar
canyonknight committed
1106
	while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
1107
1108
1109
1110
1111
1112
		$details[] = $row;
	}

	return $details;
}

Lukas Fleischer's avatar
Lukas Fleischer committed
1113
1114
1115
1116
1117
1118
1119
1120
/**
 * 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();

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

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

	return $details;
}

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

	return $row[0];
}

1150
1151
1152
1153
1154
1155
1156
/**
 * 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
 */
1157
function vote_details($voteid) {
1158
	$dbh = DB::connect();
1159
1160
1161
1162

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

canyonknight's avatar
canyonknight committed
1163
1164
	$result = $dbh->query($q);
	$row = $result->fetch(PDO::FETCH_ASSOC);
1165
1166
1167
1168

	return $row;
}

1169
1170
1171
1172
1173
/**
 * 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
1174
 * @return array All users who voted for a specific proposal
1175
 */
1176
function voter_list($voteid) {
1177
	$dbh = DB::connect();
1178

Lukas Fleischer's avatar
Lukas Fleischer committed
1179
	$whovoted = array();
1180

1181
1182
1183
1184
1185
1186
	$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
1187
	$result = $dbh->query($q);
1188
	if ($result) {
canyonknight's avatar
canyonknight committed
1189
		while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
Lukas Fleischer's avatar
Lukas Fleischer committed
1190
			$whovoted[] = $row['Username'];
1191
1192
1193
1194
1195
		}
	}
	return $whovoted;
}

1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
/**
 * 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
 */
1206
function cast_proposal_vote($voteid, $uid, $vote, $newtotal) {
1207
	$dbh = DB::connect();
1208

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

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

/**
 * 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
1223
function can_edit_account($acctinfo) {
1224
1225
	if ($acctinfo['AccountType'] == 'Developer' ||
	    $acctinfo['AccountType'] == 'Trusted User & Developer') {
Lukas Fleischer's avatar
Lukas Fleischer committed
1226
		return has_credential(CRED_ACCOUNT_EDIT_DEV);
1227
1228
	}

1229
	$uid = $acctinfo['ID'];
Lukas Fleischer's avatar
Lukas Fleischer committed
1230
	return has_credential(CRED_ACCOUNT_EDIT, array($uid));
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
1259
1260
1261
1262

/*
 * 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]);
1263
	if (count($tokens) < 4) {
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
1318
1319
1320
1321
		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;
}
1322
1323
1324
1325
1326
1327
1328
1329

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

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

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

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

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

	return proc_close($p);
}
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
1422
1423
1424
1425
1426
1427

/*
 * 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);
	}
}
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
1464
1465
1466
1467
1468
1469

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();
}
1470
1471

/*
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
 * 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.
1485
 */
1486
function get_captcha_salts() {
1487
1488
1489
1490
	$dbh = DB::connect();
	$q = "SELECT count(*) FROM Users";
	$result = $dbh->query($q);
	$user_count = $result->fetchColumn();
1491
1492
1493
1494
1495
1496

	$ret = array();
	for ($i = 0; $i <= 5; $i++) {
		array_push($ret, 'aurweb-' . ($user_count - $i));
	}
	return $ret;
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
1526
1527
1528
1529
1530
1531
}

/*
 * 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