Author: Kamil Tekiela (kamil-tekiela) Committer: Nikita Popov (nikic) Date: 2022-07-18T22:25:22+02:00
Commit: https://github.com/php/web-master/commit/4f98bb7878bc4cb8149c986ce782fe27d57d737e Raw diff: https://github.com/php/web-master/commit/4f98bb7878bc4cb8149c986ce782fe27d57d737e.diff Migrate mysql_* API Changed paths: M composer.json M docker/Dockerfile M include/functions.inc M public/entry/event.php M public/entry/svn-account.php M public/entry/user-note.php M public/fetch/cvsforwarding.php M public/fetch/events.php M public/fetch/user-notes-rss.php M public/fetch/user-notes.php M public/forgot.php M public/manage/event.php M public/manage/user-notes.php M public/manage/users.php M scripts/email-note-summaries M src/DB.php M src/Query.php Diff: diff --git a/composer.json b/composer.json index bbe039a..fc35a14 100644 --- a/composer.json +++ b/composer.json @@ -9,7 +9,6 @@ "require": { "php": ">=8.0", "ext-pdo": "*", - "ext-mysql": "*", "ext-json": "*", "ext-zlib": "*", "michelf/php-markdown": "^1.9", diff --git a/docker/Dockerfile b/docker/Dockerfile index 721ad4b..6f2a2b0 100644 --- a/docker/Dockerfile +++ b/docker/Dockerfile @@ -3,12 +3,5 @@ FROM php:8.0-cli RUN apt-get update && apt-get install -y \ unzip \ && docker-php-source extract \ - && php -r 'file_put_contents("mysql.zip", file_get_contents("https://github.com/php/pecl-database-mysql/archive/refs/heads/master.zip";));' \ - && unzip -q mysql.zip \ - && cd pecl-database-mysql-master \ - && phpize \ - && ./configure \ - && make -j$(nproc) install \ - && docker-php-ext-enable mysql \ && docker-php-ext-install pdo_mysql \ && docker-php-source delete \ No newline at end of file diff --git a/include/functions.inc b/include/functions.inc index c9c3046..b278a0f 100644 --- a/include/functions.inc +++ b/include/functions.inc @@ -92,53 +92,6 @@ function warn($message) { echo format_warn($message); } // ----------------------------------------------------------------------------------- -function db_connect($dieonerror = TRUE) -{ - if (!@mysql_connect(DB::getHost(), DB::getUser(), DB::getPassword())) { - if ($dieonerror) { - die(format_warn("Unable to connect to database!")); - } - return FALSE; - } - - if (!mysql_select_db(DB::getDatabase())) { - if ($dieonerror) { - die(format_warn("Unable to select database!")); - } - return FALSE; - } - - return TRUE; -} - -function db_query(App\Query $query) -{ - $query = $query->get(); - //var_dump($query); - $res = mysql_query($query); - if (!$res) { - $bt = debug_backtrace(); - die(format_warn("Query failed: " . hsc(mysql_error()) . "<tt>\n" . hsc($query) . "</tt><br />({$bt[0]['file']}:{$bt[0]['line']})")); - } - return $res; -} - -function db_query_safe($query, array $params = []) -{ - return db_query(new App\Query($query, $params)); -} - -function db_get_one($query) -{ - $res = mysql_query($query); - if ($res && mysql_num_rows($res)) { - return mysql_result($res, 0); - } - return FALSE; -} - -// ----------------------------------------------------------------------------------- - function array_to_url($array,$overlay=[]) { $params = []; foreach($array as $k => $v) { @@ -204,10 +157,10 @@ function show_prev_next($begin, $rows, $skip, $total, $extra = [], $table = true <?php } -function show_country_options($cc = "") +function show_country_options(DB $pdo, $cc = "") { - $res = db_query_safe("SELECT id, name FROM country ORDER BY name"); - while ($row = mysql_fetch_assoc($res)) { + $res = $pdo->safeQuery("SELECT id, name FROM country ORDER BY name"); + foreach ($res as $row) { echo "<option value=\"{$row['id']}\"", $cc == $row['id'] ? " selected" : "", ">{$row['name']}</option>"; } } @@ -231,12 +184,10 @@ function get_ssh_keys($string) { // ----------------------------------------------------------------------------------- // - -function find_group_address_from_notes_for($id) { - $res = db_query_safe("SELECT note FROM users_note WHERE userid = ? LIMIT 1", [$id]); - $row = mysql_fetch_assoc($res); +function find_group_address_from_notes_for(DB $pdo, $id) { + $note = $pdo->single("SELECT note FROM users_note WHERE userid = ? LIMIT 1", [$id]); $cc = ""; - if (preg_match("/\[group: (\w+)\]/", $row["note"], $matches)) { + if (preg_match("/\[group: (\w+)\]/", $note, $matches)) { switch($matches[1]) { case "php": $cc = "intern...@lists.php.net"; @@ -257,12 +208,12 @@ function find_group_address_from_notes_for($id) { define("MT_USER_APPROVE_MAIL", "gr...@php.net"); define("MT_USER_REMOVE_MAIL", "gr...@php.net"); -function user_approve($id) { - $res = db_query_safe("UPDATE users SET cvsaccess=1, enable=1 WHERE userid=?", [$id]); - if ($res && mysql_affected_rows()) { - $cc = find_group_address_from_notes_for($id); +function user_approve(DB $pdo, $id) { + $res = $pdo->safeQueryReturnsAffectedRows("UPDATE users SET cvsaccess=1, enable=1 WHERE userid=?", [$id]); + if ($res) { + $cc = find_group_address_from_notes_for($pdo, $id); $mailtext = $cc ? $cc : EMAIL_DEFAULT_CC; - $userinfo = fetch_user($id); + $userinfo = fetch_user($pdo, $id); $message = mt_approve_user($userinfo, $mailtext); /* Notify the user */ @@ -276,18 +227,17 @@ function user_approve($id) { mail($to, $subject, $message, $headers, "-fnore...@php.net"); warn("record $id ($userinfo[username]) approved"); return true; - } - else { + } else { warn("wasn't able to grant access to id $id."); return false; } } -function user_remove($id) { - $userinfo = fetch_user($id); - $res = db_query_safe("DELETE FROM users WHERE userid=?", [$id]); - if ($res && mysql_affected_rows()) { - $cc = find_group_address_from_notes_for($id); +function user_remove(DB $pdo, $id) { + $userinfo = fetch_user($pdo, $id); + $res = $pdo->safeQueryReturnsAffectedRows("DELETE FROM users WHERE userid=?", [$id]); + if ($res) { + $cc = find_group_address_from_notes_for($pdo, $id); $message = $userinfo['cvsaccess'] ? mt_remove_user($userinfo) : mt_deny_user($userinfo); @@ -302,12 +252,11 @@ function user_remove($id) { /* Notify public records */ mail($to, $subject, $message,"From: PHP Group <gr...@php.net>\nIn-Reply-To: <cvs-account-$i...@php.net>", "-fnore...@php.net"); - db_query_safe("DELETE FROM users_note WHERE userid=?", [$id]); - db_query_safe("DELETE FROM users_profile WHERE userid=?", [$id]); + $pdo->safeQuery("DELETE FROM users_note WHERE userid=?", [$id]); + $pdo->safeQuery("DELETE FROM users_profile WHERE userid=?", [$id]); warn("record $id ($userinfo[username]) removed"); return true; - } - else { + } else { warn("wasn't able to delete id $id."); return false; } @@ -386,27 +335,26 @@ function is_mirror_site_admin($user) { } # returns false if $user is not allowed to modify $userid -function can_modify($user,$userid) { +function can_modify(DB $pdo, $user,$userid) { if (is_admin($user)) return true; - $query = "SELECT userid FROM users WHERE userid = ? AND (email = ? OR username = ?)"; - $res = db_query_safe($query, [$userid, $user, $user]); - return $res ? mysql_num_rows($res) : false; + $query = "SELECT 1 FROM users WHERE userid = ? AND (email = ? OR username = ?)"; + $res = $pdo->single($query, [$userid, $user, $user]); + return (bool) $res; } -function fetch_user($user) { +function fetch_user(DB $pdo, $user): array { if ((int)$user) { - $res = db_query_safe( + return $pdo->row( "SELECT * FROM users LEFT JOIN users_note USING (userid) WHERE users.userid = ?", [$user]); - } else { - $res = db_query_safe( - "SELECT * FROM users LEFT JOIN users_note USING (userid) WHERE username = ? OR email = ?", - [$user, $user]); } - return mysql_fetch_array($res); + return $pdo->row( + "SELECT * FROM users LEFT JOIN users_note USING (userid) WHERE username = ? OR email = ?", + [$user, $user]); } + function invalid_input($in) { if (!empty($in['email']) && strlen($in['email']) && !is_emailable_address($in['email'])) { return "'". hsc($in['email']) ."' does not look like a valid email address"; @@ -436,11 +384,6 @@ function validateAction($k) { return false; } -function fetch_event($id) { - $res = db_query_safe("SELECT * FROM phpcal WHERE id = ?", [$id]); - return mysql_fetch_array($res,MYSQL_ASSOC); -} - function display_options($options,$current) { foreach ($options as $k => $v) { echo '<option value="', $k, '"', diff --git a/public/entry/event.php b/public/entry/event.php index bd153b6..aa3f096 100644 --- a/public/entry/event.php +++ b/public/entry/event.php @@ -1,6 +1,9 @@ <?php +use App\DB; + require_once __DIR__ . '/../../include/functions.inc'; +require_once __DIR__ . '/../../vendor/autoload.php'; $mailto = 'php-webmaster@lists.php.net'; #$mailto = 'j...@apache.org'; @@ -20,7 +23,7 @@ function day($in) { return strftime('%A',mktime(12,0,0,4,$in,2001)); } -db_connect(); +$pdo = DB::connect(); $valid_vars = ['sdesc','ldesc','email','country','category','type','url','sane','smonth','sday','syear','emonth','eday','eyear','recur','recur_day']; foreach($valid_vars as $k) { @@ -44,7 +47,7 @@ function day($in) { die("invalid start date"); $query = "INSERT INTO phpcal SET tipo=1, sdato=?, sdesc=?, url=?, email=?, ldesc=?, country=?, category=?"; - db_query_safe($query, ["$syear-$smonth-$sday", $sdesc, $url, $email, $ldesc, $country, $category]); + $pdo->safeQuery($query, ["$syear-$smonth-$sday", $sdesc, $url, $email, $ldesc, $country, $category]); $msg = "Date: $syear-$smonth-$sday\n"; break; case 'multi': @@ -63,7 +66,7 @@ function day($in) { $query = "INSERT INTO phpcal SET tipo=2," . "sdato=?, edato=?, sdesc=?, url=?, email=?, ldesc=?, country=?, category=?"; - db_query_safe($query, [ + $pdo->safeQuery($query, [ "$syear-$smonth-$sday", "$eyear-$emonth-$eday", $sdesc, $url, $email, $ldesc, $country, $category ]); @@ -76,7 +79,7 @@ function day($in) { $query = "INSERT INTO phpcal SET tipo=3," . "recur=?, sdesc=?, url=?, email=?, ldesc=?, country=?, category=?"; - db_query_safe($query, ["$recur:$recur_day", $sdesc, $url, $email, $ldesc, $country, $category]); + $pdo->safeQuery($query, ["$recur:$recur_day", $sdesc, $url, $email, $ldesc, $country, $category]); $msg = "Recurs Every: $re[$recur] ".day($recur_day)."\n"; @@ -85,7 +88,7 @@ function day($in) { die("invalid type"); } -$new_id = mysql_insert_id(); +$new_id = $pdo->lastInsertId(); $msg .= "Country: ".$country."\n" . "Category: ".$cat[$category]."\n" diff --git a/public/entry/svn-account.php b/public/entry/svn-account.php index 49b9e99..02a9c2b 100644 --- a/public/entry/svn-account.php +++ b/public/entry/svn-account.php @@ -1,5 +1,7 @@ <?php +use App\DB; + require __DIR__ . '/../../vendor/autoload.php'; require __DIR__ . '/../../include/email-validation.inc'; require __DIR__ . '/../../include/functions.inc'; @@ -61,47 +63,50 @@ die('Username is too long. It must have 1-16 characters.'); } -db_connect(); +$pdo = DB::connect(); if (!is_emailable_address($email)) die("that email address does not appear to be valid"); -$res = db_query_safe("SELECT userid FROM users WHERE username=?", [$username]); -if ($res && mysql_num_rows($res)) +$res = $pdo->safeQuery("SELECT userid FROM users WHERE username=?", [$username]); +if ($res) die("someone is already using that svn id"); $svnpasswd = gen_pass($passwd); $note = hsc($note); $query = "INSERT INTO users (name,email,svnpasswd,username) VALUES (?, ?, ?, ?)"; -if (db_query_safe($query, [$name, $email, $svnpasswd, $username])) { - $new_id = mysql_insert_id(); - - db_query_safe( - "INSERT INTO users_note (userid, note, entered) VALUES (?, ?, NOW())", - [$new_id, "$note [group: $group]"] - ); - - $msg = $note; - $from = "\"$name\" <$email>"; - - // The PEAR guys don't want these requests to their -dev@ list, only -group@ - if ($group != "pear") { - mail($mailto,"VCS Account Request: $username",$msg,"From: $from\r\nMessage-ID: <cvs-account-$new...@php.net>", "-fnore...@php.net"); - } - - $msg .= "\n-- \n"; - $msg .= "approve: https://main.php.net/manage/users.php?action=approve&id=$new_id\n";; - $msg .= "reject: https://main.php.net/manage/users.php?action=remove&id=$new_id\n";; - $msg .= "view: https://main.php.net/manage/users.php?id=$new_id\n";; - - mail($failto,"VCS Account Request: $username",$msg,"From: $from\r\nMessage-ID: <cvs-account-$new_id-ad...@php.net>", "-fnore...@php.net"); -} else { +try { + $pdo->safeQuery($query, [$name, $email, $svnpasswd, $username]); +} catch (\PDOException $e) { mail($failto,"VCS Account Request: $username", - "Failed to insert into database: ".mysql_error()."\n\n". + "Failed to insert into database: ".$e->getMessage()."\n\n". "Full name: $name\n". "Email: $email\n". "ID: $username\n". "Purpose: $note", "From: \"VCS Account Request\" <$email>"); + exit; } + +$new_id = $pdo->lastInsertId(); + +$pdo->safeQuery( + "INSERT INTO users_note (userid, note, entered) VALUES (?, ?, NOW())", + [$new_id, "$note [group: $group]"] +); + +$msg = $note; +$from = "\"$name\" <$email>"; + +// The PEAR guys don't want these requests to their -dev@ list, only -group@ +if ($group != "pear") { + mail($mailto,"VCS Account Request: $username",$msg,"From: $from\r\nMessage-ID: <cvs-account-$new...@php.net>", "-fnore...@php.net"); +} + +$msg .= "\n-- \n"; +$msg .= "approve: https://main.php.net/manage/users.php?action=approve&id=$new_id\n";; +$msg .= "reject: https://main.php.net/manage/users.php?action=remove&id=$new_id\n";; +$msg .= "view: https://main.php.net/manage/users.php?id=$new_id\n";; + +mail($failto,"VCS Account Request: $username",$msg,"From: $from\r\nMessage-ID: <cvs-account-$new_id-ad...@php.net>", "-fnore...@php.net"); diff --git a/public/entry/user-note.php b/public/entry/user-note.php index 3bfcc32..436f2c6 100644 --- a/public/entry/user-note.php +++ b/public/entry/user-note.php @@ -3,9 +3,12 @@ // service closed until we can filter spam //die ('[CLOSED]'); +use App\DB; + include_once 'note-reasons.inc'; include_once 'spam-lib.inc'; include_once 'functions.inc'; +require_once __DIR__ . '/../../vendor/autoload.php'; $mailto = 'php-no...@lists.php.net'; $failto = 'j...@php.net, alinde...@php.net, danbr...@php.net'; @@ -53,7 +56,7 @@ function validateUser($user) { unset($note_lc); /* End SPAM Checks ******************************************/ -db_connect(); +$pdo = DB::connect(); /* After a discussion in #php about the @@ -66,19 +69,7 @@ function validateUser($user) { a large flood of notes from coming in. */ $query = 'SELECT COUNT(*) FROM note WHERE ts >= (NOW() - INTERVAL 1 MINUTE)'; -$result = db_query_safe($query); - -if (!$result) { - mail ($failto, - 'failed manual note query', - "Query Failed: $query\nError: ".mysql_error(), - 'From: php-webmaster@lists.php.net', - '-fnore...@php.net' - ); - die("failed to query note db"); -} - -list ($count) = mysql_fetch_row ($result); +$count = $pdo->single($query); if ($count >= 3) { //Send error to myself. If this happens too many times, I'll increase @@ -102,55 +93,46 @@ function validateUser($user) { //This has been reverted until it has been discussed further. $query = "INSERT INTO note (user, note, sect, ts, status) VALUES (?, ?, ?,NOW(), NULL)"; -if (db_query_safe($query, [$user, $note, $sect])) { - $new_id = mysql_insert_id(); - $msg = $note; - - $msg .= "\n----\n"; - $msg .= "Server IP: {$_SERVER['REMOTE_ADDR']}"; - if (isset($_SERVER['HTTP_X_FORWARDED_FOR']) || isset($_SERVER['HTTP_VIA'])) { - $msg .= " (proxied:"; - if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) { - $msg .= " " . hsc($_SERVER['HTTP_X_FORWARDED_FOR']); - } - if (isset($_SERVER['HTTP_VIA'])) { - $msg .= " " . hsc($_SERVER['HTTP_VIA']); - } - $msg .= ")"; +$pdo->safeQuery($query, [$user, $note, $sect]); +$new_id = $pdo->lastInsertId(); +$msg = $note; + +$msg .= "\n----\n"; +$msg .= "Server IP: {$_SERVER['REMOTE_ADDR']}"; +if (isset($_SERVER['HTTP_X_FORWARDED_FOR']) || isset($_SERVER['HTTP_VIA'])) { + $msg .= " (proxied:"; + if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) { + $msg .= " " . hsc($_SERVER['HTTP_X_FORWARDED_FOR']); } - $msg .= "\nProbable Submitter: {$ip}" . ($redirip ? ' (proxied: '.htmlspecialchars($redirip).')' : ''); + if (isset($_SERVER['HTTP_VIA'])) { + $msg .= " " . hsc($_SERVER['HTTP_VIA']); + } + $msg .= ")"; +} +$msg .= "\nProbable Submitter: {$ip}" . ($redirip ? ' (proxied: '.htmlspecialchars($redirip).')' : ''); - $msg .= "\n----\n"; +$msg .= "\n----\n"; // $msg .= $spam_data; // $msg .= "\n----\n"; - $msg .= "Manual Page -- https://php.net/manual/en/$sect.php\n";; - $msg .= "Edit -- https://main.php.net/note/edit/$new_id\n";; - //$msg .= "Approve -- https://main.php.net/manage/user-notes.php?action=approve+$new_id&report=yes\n";; - foreach ($note_del_reasons AS $reason) { - $msg .= "Del: " - . str_pad($reason, $note_del_reasons_pad) - . "-- https://main.php.net/note/delete/$new_id/"; . urlencode($reason) ."\n"; - } - - // @phan-suppress-next-line PhanParamSuspiciousOrder - weird global padding count, but ok - $msg .= str_pad('Del: other reasons', $note_del_reasons_pad) . "-- https://main.php.net/note/delete/$new_id\n";; - $msg .= "Reject -- https://main.php.net/note/reject/$new_id\n";; - $msg .= "Search -- https://main.php.net/manage/user-notes.php\n";; - # make sure we have a return address. - if (!$user) $user = "php-gene...@lists.php.net"; - # strip spaces in email address, or will get a bad To: field - $user = str_replace(' ','',$user); - mail($mailto,"note $new_id added to $sect",$msg,"From: $user\r\nMessage-ID: <note-$new...@php.net>", "-fnore...@php.net"); -} else { - // mail it. - mail($failto, - 'failed manual note query', - "Query Failed: $query\nError: ".mysql_error(), - 'From: php-webmaster@lists.php.net', - "-fnore...@php.net"); - die("failed to insert record"); +$msg .= "Manual Page -- https://php.net/manual/en/$sect.php\n";; +$msg .= "Edit -- https://main.php.net/note/edit/$new_id\n";; +//$msg .= "Approve -- https://main.php.net/manage/user-notes.php?action=approve+$new_id&report=yes\n";; +foreach ($note_del_reasons AS $reason) { + $msg .= "Del: " + . str_pad($reason, $note_del_reasons_pad) + . "-- https://main.php.net/note/delete/$new_id/"; . urlencode($reason) ."\n"; } +// @phan-suppress-next-line PhanParamSuspiciousOrder - weird global padding count, but ok +$msg .= str_pad('Del: other reasons', $note_del_reasons_pad) . "-- https://main.php.net/note/delete/$new_id\n";; +$msg .= "Reject -- https://main.php.net/note/reject/$new_id\n";; +$msg .= "Search -- https://main.php.net/manage/user-notes.php\n";; +# make sure we have a return address. +if (!$user) $user = "php-gene...@lists.php.net"; +# strip spaces in email address, or will get a bad To: field +$user = str_replace(' ','',$user); +mail($mailto,"note $new_id added to $sect",$msg,"From: $user\r\nMessage-ID: <note-$new...@php.net>", "-fnore...@php.net"); + //var_dump(is_spammer('127.0.0.1')); // false //var_dump(is_spammer('127.0.0.2')); // true \ No newline at end of file diff --git a/public/fetch/cvsforwarding.php b/public/fetch/cvsforwarding.php index e8b67d0..e4fc67f 100644 --- a/public/fetch/cvsforwarding.php +++ b/public/fetch/cvsforwarding.php @@ -1,5 +1,7 @@ <?php +use App\DB; + require_once __DIR__ . '/../../include/functions.inc'; # token required, since this should only get accessed from php.net mx @@ -7,15 +9,13 @@ die("token not correct."); // Connect and generate the list from the DB -db_connect(); -$res = db_query_safe("SELECT username,email,spamprotect FROM users WHERE email != '' AND cvsaccess"); -if ($res) { - while ($row = @mysql_fetch_array($res)) { - echo "$row[username]@php.net: ", - ($row['spamprotect'] ? "|/local/bin/automoderate," : ""), - "$row[email];\n"; - echo "$row[username]@pair2.php.net: ", - ($row['spamprotect'] ? "|/local/bin/automoderate," : ""), - "$row[email];\n"; - } +$pdo = DB::connect(); +$res = $pdo->safeQuery("SELECT username,email,spamprotect FROM users WHERE email != '' AND cvsaccess"); +foreach ($res as $row) { + echo "$row[username]@php.net: ", + ($row['spamprotect'] ? "|/local/bin/automoderate," : ""), + "$row[email];\n"; + echo "$row[username]@pair2.php.net: ", + ($row['spamprotect'] ? "|/local/bin/automoderate," : ""), + "$row[email];\n"; } \ No newline at end of file diff --git a/public/fetch/events.php b/public/fetch/events.php index 0b3d074..71246b6 100644 --- a/public/fetch/events.php +++ b/public/fetch/events.php @@ -1,6 +1,9 @@ <?php +use App\DB; + require_once __DIR__ . '/../../include/functions.inc'; +require_once __DIR__ . '/../../vendor/autoload.php'; $valid_vars = ['token','cm','cy','cd','nm']; foreach($valid_vars as $k) { @@ -11,7 +14,7 @@ if (!isset($_REQUEST['token']) || md5($_REQUEST['token']) != "19a3ec370affe2d899755f005e5cd90e") die("token not correct."); -db_connect(); +$pdo = DB::connect(); // Set default values if (!isset($cm)) $cm = (int)strftime('%m'); @@ -28,7 +31,7 @@ // Collect events for $nm number of months while ($nm) { for($cat=1; $cat<=3; $cat++) { - $entries = load_month($cy, $cm, $cat); + $entries = load_month($pdo, $cy, $cm, $cat); $last = strftime('%e', mktime(12, 0, 0, $cm+1, 0, $cy)); for ($i = $cd; $i <= $last; $i++) { if (isset($entries[$i]) && is_array($entries[$i])) { @@ -85,14 +88,14 @@ function weekday($year, $month, $day, $which) } // Get events for one month in one year to be listed -function load_month($year, $month, $cat) +function load_month(DB $pdo, $year, $month, $cat) { // Empty events array $events = []; // Get approved events starting or ending in the // specified year/month, and all recurring events - $result = db_query_safe( + $result = $pdo->safeQuery( "SELECT * FROM phpcal WHERE ( ( (MONTH(sdato) = ? OR MONTH(edato) = ?) @@ -103,12 +106,8 @@ function load_month($year, $month, $cat) [$month, $month, $year, $year, $cat] ); - // Cannot get results, return with event's not found - if (!$result) { echo mysql_error(); return []; } - // Go through found events - while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { - + foreach ($result as $row) { switch($row['tipo']) { // One day event diff --git a/public/fetch/user-notes-rss.php b/public/fetch/user-notes-rss.php index 6bf03ae..ea62828 100644 --- a/public/fetch/user-notes-rss.php +++ b/public/fetch/user-notes-rss.php @@ -1,10 +1,12 @@ <?php +use App\DB; use App\Query; require_once __DIR__ . '/../../include/functions.inc'; +require_once __DIR__ . '/../../vendor/autoload.php'; -db_connect(); +$pdo = DB::connect(); if (isset($_GET['limit']) && is_numeric($_GET['limit']) && $_GET['limit'] <= 1000) { $limit = $_GET['limit']; @@ -26,10 +28,11 @@ $query->add(" ORDER BY sect,ts DESC"); $query->add(" LIMIT ?", [$limit]); -$res = db_query($query); +$res = $pdo->safeQuery($query->get(), $query->getParams()); $notes = []; -while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) { +// TODO: replace the loop with PDO fetch mode +foreach ($res as $row) { $notes[$row['id']] = $row; } diff --git a/public/fetch/user-notes.php b/public/fetch/user-notes.php index 06c4eac..7cc3576 100644 --- a/public/fetch/user-notes.php +++ b/public/fetch/user-notes.php @@ -8,35 +8,23 @@ if (!isset($_REQUEST['token']) || md5($_REQUEST['token']) != "19a3ec370affe2d899755f005e5cd90e") die("token not correct."); -// Changed old mysql_* stuff to PDO -try { - $dbh = DB::connect(); -} catch (PDOException $e) { - // Old error handling was to simply exit. Do we want to log anything here??? - exit; -} +$dbh = DB::connect(); -try { - $query = "SELECT DISTINCT note.id,note.sect,note.user,note.note,UNIX_TIMESTAMP(note.ts) AS ts,"; - $query .= "SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down,"; - $query .= "ROUND((SUM(votes.vote) / COUNT(votes.vote)) * 100) AS rate"; - $query .= " FROM note"; - $query .= " LEFT JOIN (votes) ON (note.id = votes.note_id)"; - //Only select notes that have been approved - $query .= " WHERE note.status is NULL"; - $query .= " GROUP BY note.id"; - $query .= " ORDER BY note.sect,ts DESC"; +$query = "SELECT DISTINCT note.id,note.sect,note.user,note.note,UNIX_TIMESTAMP(note.ts) AS ts,"; +$query .= "SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down,"; +$query .= "ROUND((SUM(votes.vote) / COUNT(votes.vote)) * 100) AS rate"; +$query .= " FROM note"; +$query .= " LEFT JOIN (votes) ON (note.id = votes.note_id)"; +//Only select notes that have been approved +$query .= " WHERE note.status is NULL"; +$query .= " GROUP BY note.id"; +$query .= " ORDER BY note.sect,ts DESC"; - $stmt = $dbh->prepare($query); - $stmt->execute(); -} catch (PDOException $e) { - // Old error handling was to simply exit. Do we want to log anything here??? - exit; -} +$result = $dbh->safeQuery($query); // Print out a row for all notes, obfuscating the // email addresses as needed -while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { +foreach ($result as $row) { $user = $row['user']; $row['rate'] = empty($row['rate']) ? 0 : $row['rate']; if ($user != "php-gene...@lists.php.net" && $user != "u...@example.com") { diff --git a/public/forgot.php b/public/forgot.php index 25912a7..ea7950a 100644 --- a/public/forgot.php +++ b/public/forgot.php @@ -1,5 +1,7 @@ <?php // vim: et ts=2 sw=2 +use App\DB; + require __DIR__ . '/../vendor/autoload.php'; require __DIR__ . '/../include/functions.inc'; require __DIR__ . "/../include/mailer.php"; @@ -18,14 +20,14 @@ function random_password() { head("forgotten password"); -db_connect(); +$pdo = DB::connect(); if ($id && $key) { if ($n1 && $n2) { if ($n1 === $n2) { $svnpasswd = gen_pass($n1); - $res = db_query_safe("UPDATE users SET forgot=NULL,svnpasswd=?,pchanged=? WHERE userid=? AND forgot=?", [$svnpasswd, $ts, $id, $key]); - if ($res && mysql_affected_rows()) { + $res = $pdo->safeQueryReturnsAffectedRows("UPDATE users SET forgot=NULL,svnpasswd=?,pchanged=? WHERE userid=? AND forgot=?", [$svnpasswd, $ts, $id, $key]); + if ($res) { echo '<p>Okay, your password has been changed. It could take as long as an hour before this change makes it to the VCS server and other services. To change your password again, you\'ll have to start this process over to get a new key.</p>'; foot(); exit; @@ -54,13 +56,12 @@ function random_password() { exit; } elseif ($user) { - $res = db_query_safe("SELECT * FROM users WHERE username = ?", [$user]); - if ($res && ($row = mysql_fetch_array($res,MYSQL_ASSOC))) { + $row = $pdo->row("SELECT * FROM users WHERE username = ?", [$user]); + if ($row) { $newpass = random_password(); $query = "UPDATE users SET forgot=? WHERE userid=?"; - $res = db_query_safe($query, [$newpass, $row['userid']]); - if ($res) { - $body = + $pdo->safeQuery($query, [$newpass, $row['userid']]); + $body = "Someone filled out the form that says you forgot your php.net VCS password. If it wasn't you, don't worry too much about it. Unless someone is reading your mail, there's not much they can do. (But you @@ -76,17 +77,13 @@ function random_password() { -- gr...@php.net "; - mailer( - $row['username'] . '@php.net', - "Password change instructions for $row[username]", $body, - new MailAddress('gr...@php.net', 'PHP Group')); - echo '<p>Okay, instructions on how to change your password have been sent to your email address. If you don\'t receive them, you\'ll have to contact gr...@php.net for help.</p>'; - foot(); - exit; - } - else { - echo '<p class="warning">Something strange happened. You\'ll have to contact gr...@php.net for help.</p>'; - } + mailer( + $row['username'] . '@php.net', + "Password change instructions for $row[username]", $body, + new MailAddress('gr...@php.net', 'PHP Group')); + echo '<p>Okay, instructions on how to change your password have been sent to your email address. If you don\'t receive them, you\'ll have to contact gr...@php.net for help.</p>'; + foot(); + exit; } else {?> <p class="warning">There's nobody named <?php echo hsc($user)?> around here. Perhaps you need to contact diff --git a/public/manage/event.php b/public/manage/event.php index d770c4b..f56af93 100644 --- a/public/manage/event.php +++ b/public/manage/event.php @@ -1,5 +1,6 @@ <?php +use App\DB; use App\Query; require __DIR__ . '/../../vendor/autoload.php'; @@ -27,7 +28,7 @@ $type = [1=>'single',2=>'multi',3=>'recur']; head("event administration"); -db_connect(); +$pdo = DB::connect(); $id = $_REQUEST['id'] ?? false; $action = $_REQUEST['action'] ?? false; @@ -44,9 +45,8 @@ if ($id && $action) { switch ($action) { case 'approve': - if (db_query_safe("UPDATE phpcal SET approved=1,app_by=? WHERE id=?", [$cuser, $id]) - && mysql_affected_rows()) { - $event = fetch_event($id); + if ($pdo->safeQueryReturnsAffectedRows("UPDATE phpcal SET approved=1,app_by=? WHERE id=?", [$cuser, $id])) { + $event = $pdo->row("SELECT * FROM phpcal WHERE id = ?", [$id]); $message = "This event has been approved. It will appear on the PHP website shortly."; if ($event['email']) mail($event['email'],"Event #$id Approved: $event[sdesc]",$message,"From: PHP Webmasters <php-webmaster@lists.php.net>", "-fnore...@php.net -O DeliveryMode=b"); @@ -57,9 +57,8 @@ } break; case 'reject': - $event = fetch_event($id); - if (db_query_safe("DELETE FROM phpcal WHERE id=?", [$id]) - && mysql_affected_rows()) { + $event = $pdo->row("SELECT * FROM phpcal WHERE id = ?", [$id]); + if ($pdo->safeQueryReturnsAffectedRows("DELETE FROM phpcal WHERE id=?", [$id])) { $message = $event['approved'] ? "This event has been deleted." : "This event has been rejected."; $did = $event['approved'] ? 'Deleted' : 'Rejected'; @@ -100,14 +99,14 @@ "tipo=?, ldesc=?, sdesc=?, email=?, url=?, country=?, category=? WHERE id=?", [$tipo, $in['ldesc'], $in['sdesc'], $in['email'], $in['url'], $in['country'], $in['category'], $id] ); - db_query($query); + $pdo->safeQuery($query->get(), $query->getParams()); warn("record $id updated"); unset($id); } if ($id && !$in) { - $in = fetch_event($id); + $in = $pdo->row("SELECT * FROM phpcal WHERE id = ?", [$id]); if (!$in) { unset($id); } @@ -167,7 +166,7 @@ <td> <select name="in[country]"> <option value="">- Select a country -</option> - <?php show_country_options($in['country']);?> + <?php show_country_options($pdo, $in['country']);?> </select> </td> </tr> @@ -253,8 +252,7 @@ $query = new Query("SELECT COUNT(id) FROM phpcal"); $query->addQuery($searchby); -$res = db_query($query); -$total = (int)mysql_result($res,0); +$total = $pdo->single($query->get(), $query->getParams()); $query = new Query("SELECT phpcal.*,country.name AS cname FROM phpcal LEFT JOIN country ON phpcal.country = country.id"); $query->addQuery($searchby); @@ -270,8 +268,8 @@ // Safe because we checked that $order is part of a fixed set. $query->add(" ORDER BY $order $ext"); } -$query->add(' LIMIT ?int, ?int', [$begin, $max]); -$res = db_query($query); +$query->add(' LIMIT ?, ?', [$begin, $max]); +$res = $pdo->safeQuery($query->get(), $query->getParams()); $extra = [ "search" => $search, @@ -283,7 +281,7 @@ "forward" => $forward, ]; -show_prev_next($begin,mysql_num_rows($res),$max,$total,$extra); +show_prev_next($begin,count($res),$max,$total,$extra); ?> <table class="useredit"> <tr> @@ -295,7 +293,7 @@ <th><a href="<?php echo PHP_SELF,'?',array_to_url($extra,["order"=>"category"]);?>">category</a></th> </tr> <?php -while ($row = mysql_fetch_array($res,MYSQL_ASSOC)) { +foreach ($res as $row) { ?> <tr> <td align="center"><a href="<?php echo PHP_SELF . "?id=$row[id]";?>">edit</a></td> @@ -316,6 +314,6 @@ ?> </table> <?php -show_prev_next($begin,mysql_num_rows($res),$max,$total,$extra); +show_prev_next($begin,count($res),$max,$total,$extra); foot(); diff --git a/public/manage/user-notes.php b/public/manage/user-notes.php index becd0e9..bdb2bac 100644 --- a/public/manage/user-notes.php +++ b/public/manage/user-notes.php @@ -1,5 +1,6 @@ <?php +use App\DB; use App\Query; require __DIR__ . '/../../vendor/autoload.php'; @@ -37,7 +38,7 @@ Your note has been removed from the online manual.'; -db_connect(); +$pdo = DB::connect(); $action = (isset($_REQUEST['action']) ? preg_replace('/[^\w\d\s_]/', '', $_REQUEST['action']) : ''); $id = (isset($_REQUEST['id']) ? intval($_REQUEST['id']) : ''); @@ -97,50 +98,50 @@ } elseif (substr($_REQUEST['keyword'], 0, 5) == 'sect:') { $search_heading = 'Search results for <em>' . hsc($_REQUEST['keyword']) . '</em>'; $section = str_replace('*', '%', substr($_REQUEST['keyword'], 5)); - $query->add("note.sect LIKE ? GROUP BY note.id ORDER BY note.sect, note.ts LIMIT ?int, 10", [$section, $limit]); + $query->add("note.sect LIKE ? GROUP BY note.id ORDER BY note.sect, note.ts LIMIT ?, 10", [$section, $limit]); } else { $search_heading = 'Search results for <em>' . hsc($_REQUEST['keyword']) . '</em>'; $query->add( - "note.note LIKE ? GROUP BY note.id LIMIT ?int, 10", + "note.note LIKE ? GROUP BY note.id LIMIT ?, 10", ['%' . $_REQUEST['keyword'] . '%', $limit]); } - $result = db_query_safe($query->get()); + $result = $pdo->safeQuery($query->get(), $query->getParams()); } else { /* Added new voting information to be included in note from votes table. */ /* First notes */ if ($type == 1) { $search_heading = 'First notes'; - $result = db_query_safe("SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, note.*, UNIX_TIMESTAMP(note.ts) AS ts ". + $result = $pdo->safeQuery("SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, note.*, UNIX_TIMESTAMP(note.ts) AS ts ". "FROM note ". "LEFT JOIN(votes) ON (note.id = votes.note_id) ". - "GROUP BY note.id ORDER BY note.id ASC LIMIT ?int, 10", [$limit]); + "GROUP BY note.id ORDER BY note.id ASC LIMIT ?, 10", [$limit]); /* Minor notes */ } else if ($type == 2) { $search_heading = 'Minor notes'; - $result = db_query_safe("SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, note.*, UNIX_TIMESTAMP(note.ts) AS ts ". + $result = $pdo->safeQuery("SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, note.*, UNIX_TIMESTAMP(note.ts) AS ts ". "FROM note ". "LEFT JOIN(votes) ON (note.id = votes.note_id) ". - "GROUP BY note.id ORDER BY LENGTH(note.note) ASC LIMIT ?int, 10", [$limit]); + "GROUP BY note.id ORDER BY LENGTH(note.note) ASC LIMIT ?, 10", [$limit]); /* Top rated notes */ } else if ($type == 3) { $search_heading = 'Top rated notes'; - $result = db_query_safe("SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, ". + $result = $pdo->safeQuery("SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, ". "ROUND((SUM(votes.vote) / COUNT(votes.vote)) * 100) AS rate, ". "(SUM(votes.vote) - (COUNT(votes.vote) - SUM(votes.vote))) AS arating, ". "note.id, note.sect, note.user, note.note, UNIX_TIMESTAMP(note.ts) AS ts ". "FROM note ". "JOIN(votes) ON (note.id = votes.note_id) ". - "GROUP BY note.id ORDER BY arating DESC, up DESC, rate DESC, down DESC LIMIT ?int, 10", [$limit]); + "GROUP BY note.id ORDER BY arating DESC, up DESC, rate DESC, down DESC LIMIT ?, 10", [$limit]); /* Bottom rated notes */ } else if ($type == 4) { $search_heading = 'Bottom rated notes'; - $result = db_query_safe("SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, ". + $result = $pdo->safeQuery("SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, ". "ROUND((SUM(votes.vote) / COUNT(votes.vote)) * 100) AS rate, ". "(SUM(votes.vote) - (COUNT(votes.vote) - SUM(votes.vote))) AS arating, ". "note.id, note.sect, note.user, note.note, UNIX_TIMESTAMP(note.ts) AS ts ". "FROM note ". "JOIN(votes) ON (note.id = votes.note_id) ". - "GROUP BY note.id ORDER BY arating ASC, up ASC, rate ASC, down DESC LIMIT ?int, 10", [$limit]); + "GROUP BY note.id ORDER BY arating ASC, up ASC, rate ASC, down DESC LIMIT ?, 10", [$limit]); /* Votes table view */ } else if ($type == 5) { $search_votes = true; // set this only to change the output between votes table and notes table @@ -149,58 +150,50 @@ $search = html_entity_decode($_GET['votessearch'], ENT_QUOTES, 'UTF-8'); $start = $iprange[0]; $end = $iprange[1]; - $resultCount = db_query_safe("SELECT count(votes.id) AS total_votes FROM votes JOIN (note) ON (votes.note_id = note.id) WHERE ". + $resultCount = $pdo->single("SELECT count(votes.id) AS total_votes FROM votes JOIN (note) ON (votes.note_id = note.id) WHERE ". "(hostip >= ? AND hostip <= ?) OR (ip >= ? AND ip <= ?)", [$start, $end, $start, $end]); - $resultCount = mysql_fetch_assoc($resultCount); - $resultCount = $resultCount['total_votes']; $isSearch = '&votessearch=' . hsc($search); - $result = db_query_safe( + $result = $pdo->safeQuery( 'SELECT votes.id, UNIX_TIMESTAMP(votes.ts) AS ts, votes.vote, votes.note_id, note.sect, votes.hostip, votes.ip '. 'FROM votes JOIN(note) ON (votes.note_id = note.id) '. 'WHERE (hostip >= ? AND hostip <= ?) OR (ip >= ? AND ip <= ?) '. - 'ORDER BY votes.id DESC LIMIT ?int, 25', + 'ORDER BY votes.id DESC LIMIT ?, 25', [$start, $end, $start, $end, $limitVotes]); } elseif (filter_var(html_entity_decode($_GET['votessearch'], ENT_QUOTES, 'UTF-8'), FILTER_VALIDATE_IP)) { $searchip = (int) ip2long(filter_var(html_entity_decode($_GET['votessearch'], ENT_QUOTES, 'UTF-8'), FILTER_VALIDATE_IP)); - $resultCount = db_query_safe("SELECT count(votes.id) AS total_votes FROM votes JOIN(note) ON (votes.note_id = note.id) WHERE hostip = ? OR ip = ?", [$searchip, $searchip]); - $resultCount = mysql_fetch_assoc($resultCount); - $resultCount = $resultCount['total_votes']; + $resultCount = $pdo->single("SELECT count(votes.id) AS total_votes FROM votes JOIN(note) ON (votes.note_id = note.id) WHERE hostip = ? OR ip = ?", [$searchip, $searchip]); $isSearch = '&votessearch=' . hsc(long2ip($searchip)); - $result = db_query_safe( + $result = $pdo->safeQuery( "SELECT votes.id, UNIX_TIMESTAMP(votes.ts) AS ts, votes.vote, votes.note_id, note.sect, votes.hostip, votes.ip ". "FROM votes JOIN(note) ON (votes.note_id = note.id) ". "WHERE hostip = ? OR ip = ? ". - "ORDER BY votes.id DESC LIMIT ?int, 25", + "ORDER BY votes.id DESC LIMIT ?, 25", [$searchip, $searchip, $limitVotes]); } else { $search = (int) html_entity_decode($_GET['votessearch'], ENT_QUOTES, 'UTF-8'); - $resultCount = db_query_safe("SELECT count(votes.id) AS total_votes FROM votes JOIN(note) ON (votes.note_id = note.id) WHERE votes.note_id = ?", [$search]); - $resultCount = mysql_fetch_assoc($resultCount); - $resultCount = $resultCount['total_votes']; + $resultCount = $pdo->single("SELECT count(votes.id) AS total_votes FROM votes JOIN(note) ON (votes.note_id = note.id) WHERE votes.note_id = ?", [$search]); $isSearch = '&votessearch=' . hsc($search); - $result = db_query_safe( + $result = $pdo->safeQuery( "SELECT votes.id, UNIX_TIMESTAMP(votes.ts) AS ts, votes.vote, votes.note_id, note.sect, votes.hostip, votes.ip ". "FROM votes JOIN(note) ON (votes.note_id = note.id) ". "WHERE votes.note_id = ? ". - "ORDER BY votes.id DESC LIMIT ?int, 25", + "ORDER BY votes.id DESC LIMIT ?, 25", [$search, $limitVotes]); } } else { $isSearch = null; - $resultCount = db_query_safe("SELECT COUNT(votes.id) AS total_votes FROM votes JOIN(note) ON (votes.note_id = note.id)"); - $resultCount = mysql_fetch_assoc($resultCount); - $resultCount = $resultCount['total_votes']; - $result = db_query_safe( + $resultCount = $pdo->single("SELECT COUNT(votes.id) AS total_votes FROM votes JOIN(note) ON (votes.note_id = note.id)"); + $result = $pdo->safeQuery( "SELECT votes.id, UNIX_TIMESTAMP(votes.ts) AS ts, votes.vote, votes.note_id, note.sect, votes.hostip, votes.ip ". "FROM votes JOIN(note) ON (votes.note_id = note.id) ". - "ORDER BY votes.id DESC LIMIT ?int, 25", + "ORDER BY votes.id DESC LIMIT ?, 25", [$limitVotes]); } /* IPs with the most votes -- aggregated data */ } elseif ($type == 6) { $votes_by_ip = true; // only set this get the table for top IPs with votes - $result = db_query_safe( + $result = $pdo->safeQuery( "SELECT DISTINCT(votes.ip), COUNT(votes.ip) as votes, COUNT(DISTINCT(votes.note_id)) as notes, ". "INET_NTOA(votes.ip) AS ip, MIN(UNIX_TIMESTAMP(votes.ts)) AS `from`, MAX(UNIX_TIMESTAMP(votes.ts)) AS `to` ". "FROM votes ". @@ -208,10 +201,10 @@ /* Last notes */ } else { $search_heading = 'Last notes'; - $result = db_query_safe( + $result = $pdo->safeQuery( "SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, note.*, UNIX_TIMESTAMP(note.ts) AS ts ". "FROM note LEFT JOIN(votes) ON (note.id = votes.note_id) ". - "GROUP BY note.id ORDER BY note.id DESC LIMIT ?int, 10", + "GROUP BY note.id ORDER BY note.id DESC LIMIT ?, 10", [$limit]); } } @@ -267,12 +260,13 @@ if (!empty($search_heading)) { echo "<h2>$search_heading</h2>"; } - while ($row = mysql_fetch_assoc($result)) { + $numberOfNotes = count($result); + foreach ($result as $row) { /* I had to do this because the JOIN queries will return a single row of NULL values even when no rows match. So the `if (mysql_num_rows($result))` check earlier becomes useless and as such I had to replace it with this. */ - if (mysql_num_rows($result) == 1 && !array_filter($row)) { + if ($numberOfNotes == 1 && !array_filter($row)) { echo "<p>No results found...</p>"; continue; } @@ -458,14 +452,14 @@ if ($step == 2) { $query = new Query('UPDATE note SET sect = ? WHERE ', [$_REQUEST["new_sect"]]); $query->addQuery($where); - db_query($query); + $pdo->safeQuery($query->get(), $query->getParams()); echo "<p>Mass change succeeded.</p>\n"; } elseif ($step == 1) { if (!empty($_REQUEST["new_sect"]) && $where) { $query = new Query('SELECT COUNT(*) FROM note WHERE '); $query->addQuery($where); - $result = db_query($query); - if (!($count = mysql_result($result, 0, 0))) { + $count = $pdo->single($query->get(), $query->getParams()); + if (!$count) { echo "<p>There are no such notes.</p>\n"; } else { $step = 2; @@ -525,13 +519,13 @@ exit; case 'approve': if ($id) { - if ($row = note_get_by_id($id)) { + if ($row = note_get_by_id($pdo, $id)) { if ($row['status'] != 'na') { die ("Note #$id has already been approved"); } - if ($row['id'] && db_query_safe("UPDATE note SET status=NULL WHERE id=?", [$id])) { + if ($row['id'] && $pdo->safeQuery("UPDATE note SET status=NULL WHERE id=?", [$id])) { note_mail_on_action( $cuser, $id, @@ -547,8 +541,8 @@ case 'reject': case 'delete': if ($id) { - if ($row = note_get_by_id($id)) { - if ($row['id'] && db_query_safe("DELETE note,votes FROM note LEFT JOIN (votes) ON (note.id = votes.note_id) WHERE note.id = ?", [$id])) { + if ($row = note_get_by_id($pdo, $id)) { + if ($row['id'] && $pdo->safeQuery("DELETE note,votes FROM note LEFT JOIN (votes) ON (note.id = votes.note_id) WHERE note.id = ?", [$id])) { $action_taken = ($action == "reject" ? "rejected" : "deleted"); note_mail_on_action( $cuser, @@ -582,13 +576,13 @@ head("user notes"); } - $row = note_get_by_id($id); + $row = note_get_by_id($pdo, $id); $email = (isset($_POST['email']) ? html_entity_decode($_POST['email'],ENT_QUOTES) : $row['user']); $sect = (isset($_POST['sect']) ? html_entity_decode($_POST['sect'],ENT_QUOTES) : $row['sect']); if (isset($note) && $action == "edit") { - if (db_query_safe('UPDATE note SET note=?,user=?,sect=?,updated=NOW() WHERE id=?', [html_entity_decode($note,ENT_QUOTES), $email, $sect, $id])) { + if ($pdo->safeQuery('UPDATE note SET note=?,user=?,sect=?,updated=NOW() WHERE id=?', [html_entity_decode($note,ENT_QUOTES), $email, $sect, $id])) { note_mail_on_action( $cuser, $id, @@ -651,19 +645,18 @@ if ($id) { if (strtoupper($_SERVER['REQUEST_METHOD']) == 'POST') { /* Make sure the note has votes before we attempt to delete them */ - $result = db_query_safe("SELECT COUNT(id) AS id FROM votes WHERE note_id = ?", [$id]); - $rows = mysql_fetch_assoc($result); - if (!$rows['id']) { + $result = $pdo->single("SELECT 1 FROM votes WHERE note_id = ?", [$id]); + if (!$result) { echo "<p>No votes exist for Note ID ". hsc($id) ."!</p>"; } else { if ($action == 'resetall' && isset($_POST['resetall'])) { - db_query_safe('DELETE FROM votes WHERE votes.note_id = ?', [$id]); + $pdo->safeQuery('DELETE FROM votes WHERE votes.note_id = ?', [$id]); /* 1 for up votes */ } elseif ($action == 'resetup' && isset($_POST['resetup'])) { - db_query_safe('DELETE FROM votes WHERE votes.note_id = ? AND votes.vote = 1', [$id]); + $pdo->safeQuery('DELETE FROM votes WHERE votes.note_id = ? AND votes.vote = 1', [$id]); /* 0 for down votes */ } elseif ($action == 'resetdown' && isset($_POST['resetdown'])) { - db_query_safe('DELETE FROM votes WHERE votes.note_id = ? AND votes.vote = 0', [$id]); + $pdo->safeQuery('DELETE FROM votes WHERE votes.note_id = ? AND votes.vote = 0', [$id]); } header('Location: user-notes.php?id=' . urlencode($id) . '&was=' . urlencode($action)); } @@ -672,9 +665,8 @@ 'FROM note '. 'JOIN(votes) ON (note.id = votes.note_id) '. 'WHERE note.id = ?'; - $result = db_query_safe($sql, [$id]); - if (mysql_num_rows($result)) { - $row = mysql_fetch_assoc($result); + $row = $pdo->row($sql, [$id]); + if ($row) { $out = "<p>\nAre you sure you want to reset all votes for <strong>Note #". hsc($row['id']) ."</strong>? "; if ($action == 'resetall') { $out .= "This will permanently delete all <em>". hsc($row['up']) ."</em> up votes and <em>". hsc($row['down']) ."</em> down votes for this note.\n</p>\n". @@ -717,7 +709,7 @@ } $ids = implode(',',$ids); // This is safe, because $ids is an array of integers. - if (db_query_safe("DELETE FROM votes WHERE id IN ($ids)")) { + if ($pdo->safeQuery("DELETE FROM votes WHERE id IN ($ids)")) { header('Location: user-notes.php?id=1&view=notes&was=' . urlencode($action) . (isset($_REQUEST['type']) ? ('&type=' . urlencode($_REQUEST['type'])) : null) . (isset($_REQUEST['votessearch']) ? '&votessearch=' . urlencode($_REQUEST['votessearch']) : null) @@ -771,9 +763,7 @@ $stats_sql['Last Week'] = new Query('SELECT COUNT(votes.id) AS total FROM votes WHERE UNIX_TIMESTAMP(votes.ts) >= ? AND UNIX_TIMESTAMP(votes.ts) < ?', [$lastweek, $week]); $stats_sql['Last Month'] = new Query('SELECT COUNT(votes.id) AS total FROM votes WHERE UNIX_TIMESTAMP(votes.ts) >= ? AND UNIX_TIMESTAMP(votes.ts) < ?', [$lastmonth, $month]); foreach ($stats_sql as $key => $query) { - $result = db_query($query); - $row = mysql_fetch_assoc($result); - $stats[$key] = $row['total']; + $stats[$key] = $pdo->single($query->get(), $query->getParams()); } ?> <h2>User contributed voting statistics</h2> @@ -873,15 +863,12 @@ function note_mail_user($mailto, $subject, $message) } // Return data about a note by its ID -function note_get_by_id($id) +function note_get_by_id(DB $pdo, $id) { - if ($result = db_query_safe('SELECT *, UNIX_TIMESTAMP(ts) AS ts FROM note WHERE id=?', [$id])) { - if (!mysql_num_rows($result)) { - die("Note #$id doesn't exist. It has probably been deleted/rejected already."); - } - return mysql_fetch_assoc($result); + if ($result = $pdo->row('SELECT *, UNIX_TIMESTAMP(ts) AS ts FROM note WHERE id=?', [$id])) { + return $result; } - return FALSE; + die("Note #$id doesn't exist. It has probably been deleted/rejected already."); } // Sends out a notification to the mailing list when diff --git a/public/manage/users.php b/public/manage/users.php index c426061..f40c584 100644 --- a/public/manage/users.php +++ b/public/manage/users.php @@ -4,6 +4,7 @@ # acls # handle flipping of the sort views +use App\DB; use App\Query; require __DIR__ . '/../../vendor/autoload.php'; @@ -60,22 +61,20 @@ function csrf_validate(&$mydata, $name) { head("user administration"); -db_connect(); +$pdo = DB::connect(); # ?username=whatever will look up 'whatever' by email or username if ($username) { - $query = "SELECT userid FROM users" - . " WHERE username=? OR email=?"; - $res = db_query_safe($query, [$username, $username]); + $query = "SELECT userid FROM users WHERE username=? OR email=?"; + $id = $pdo->single($query, [$username, $username]); - if (!($id = @mysql_result($res, 0))) { + if (!$id) { warn("wasn't able to find user matching '$username'"); } } if ($id) { $query = "SELECT * FROM users WHERE users.userid=?"; - $res = db_query_safe($query, [$id]); - $userdata = mysql_fetch_array($res); + $userdata = $pdo->row($query, [$id]); if (!$userdata) { warn("Can't find user#$id"); } @@ -91,11 +90,11 @@ function csrf_validate(&$mydata, $name) { switch ($action) { case 'approve': - user_approve((int)$id); + user_approve($pdo, (int)$id); break; case 'remove': - user_remove((int)$id); + user_remove($pdo, (int)$id); break; default: @@ -105,7 +104,7 @@ function csrf_validate(&$mydata, $name) { if ($in) { csrf_validate($_SESSION, "useredit"); - if (!can_modify($_SESSION["username"],$id)) { + if (!can_modify($pdo, $_SESSION["username"],$id)) { warn("you're not allowed to modify this user."); } else { @@ -114,7 +113,7 @@ function csrf_validate(&$mydata, $name) { } else { if (!empty($in['rawpasswd'])) { - $userinfo = fetch_user($id); + $userinfo = fetch_user($pdo, $id); $in['svnpasswd'] = gen_pass($in['rawpasswd']); } @@ -151,12 +150,12 @@ function csrf_validate(&$mydata, $name) { // Kill the session data after updates :) $_SERVER["credentials"] = []; } - db_query($query); + $pdo->safeQuery($query->get(), $query->getParams()); if(!empty($in['purpose'])) { $purpose = hsc($in['purpose']); $query = "INSERT INTO users_note (userid, note, entered) VALUES (?, ?, NOW())"; - db_query_safe($query, [$id, $purpose]); + $pdo->safeQuery($query, [$id, $purpose]); } if(!empty($in['profile_markdown'])) { @@ -164,7 +163,7 @@ function csrf_validate(&$mydata, $name) { $profile_html = \Michelf\MarkdownExtra::defaultTransform($profile_markdown); $query = "INSERT INTO users_profile (userid, markdown, html) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE markdown=?, html=?"; - db_query_safe($query, [$id, $profile_markdown, $profile_html, $profile_markdown, $profile_html]); + $pdo->safeQuery($query, [$id, $profile_markdown, $profile_html, $profile_markdown, $profile_html]); } } @@ -252,9 +251,9 @@ function csrf_validate(&$mydata, $name) { </tr> <?php if ($id) { - $res = db_query_safe("SELECT markdown FROM users_profile WHERE userid=?", [$id]); + $profile_row = $pdo->row("SELECT markdown FROM users_profile WHERE userid=?", [$id]); $userdata['profile_markdown'] = ''; - if ($profile_row = mysql_fetch_assoc($res)) { + if ($profile_row) { $userdata['profile_markdown'] = $profile_row['markdown']; } ?> @@ -304,8 +303,8 @@ function csrf_validate(&$mydata, $name) { ?> <h2 id="notes">Notes:</h2> <?php - $res = db_query_safe("SELECT note, UNIX_TIMESTAMP(entered) AS ts FROM users_note WHERE userid=?", [$id]); - while ($res && $userdata = mysql_fetch_assoc($res)) { + $res = $pdo->safeQuery("SELECT note, UNIX_TIMESTAMP(entered) AS ts FROM users_note WHERE userid=?", [$id]); + foreach ($res as $userdata){ echo "<div class='note'>", date("r",$userdata['ts']), "<br />".$userdata['note']."</div>"; } foot(); @@ -349,12 +348,10 @@ function csrf_validate(&$mydata, $name) { // Safe because we checked that $order is part of a fixed set. $query->add(" ORDER BY $order $ext"); } -$query->add(" LIMIT ?int, ?int ", [$begin, $max]); -$res = db_query($query); - -$res2 = db_query_safe("SELECT FOUND_ROWS()"); -$total = (int)mysql_result($res2,0); +$query->add(" LIMIT ?, ? ", [$begin, $max]); +$res = $pdo->safeQuery($query->get(), $query->getParams()); +$total = $pdo->single("SELECT FOUND_ROWS()"); $extra = [ "search" => $search, @@ -372,7 +369,7 @@ function csrf_validate(&$mydata, $name) { </ul></h1> <table id="users"> <thead> -<?php show_prev_next($begin,mysql_num_rows($res),$max,$total,$extra, false); ?> +<?php show_prev_next($begin,count($res),$max,$total,$extra, false); ?> </thead> <tbody> <tr> @@ -388,7 +385,7 @@ function csrf_validate(&$mydata, $name) { <th> </th> </tr> <?php -while ($userdata = mysql_fetch_array($res)) { +foreach ($res as $userdata) { ?> <tr class="<?php if (!$userdata["cvsaccess"]) { echo "noaccess"; }?>"> <td><a href="?username=<?php echo $userdata["username"];?>">edit</a></td> @@ -407,7 +404,7 @@ function csrf_validate(&$mydata, $name) { ?> </tbody> <tfoot> -<?php show_prev_next($begin,mysql_num_rows($res),$max,$total,$extra, false); ?> +<?php show_prev_next($begin,count($res),$max,$total,$extra, false); ?> </tfoot> </table> <?php diff --git a/scripts/email-note-summaries b/scripts/email-note-summaries index f15dca6..1690c75 100755 --- a/scripts/email-note-summaries +++ b/scripts/email-note-summaries @@ -1,32 +1,26 @@ #!/usr/local/bin/php -q <?php -mysql_connect("localhost","nobody","") - or die("unable to connect to server"); -mysql_select_db("phpmasterdb") - or die("unable to select database"); +use App\DB; -$query = "SELECT COUNT(*) AS count,sect FROM note" - . " GROUP BY sect ORDER BY count DESC LIMIT 20"; +require_once __DIR__ . '/../../vendor/autoload.php'; -$res = mysql_query($query) - or die("query to get top 20 pages failed"); +$pdo = DB::connect(); + +$query = "SELECT COUNT(*) AS count,sect FROM note GROUP BY sect ORDER BY count DESC LIMIT 20"; +$result = $pdo->safeQuery($query); $body = "Notes | Page\n" . "-------+---------------------------------------------------------\n"; $top20 = 0; -while ($row = mysql_fetch_array($res,MYSQL_ASSOC)) { +foreach ($result as $row) { $body .= sprintf("%5d | https://php.net/manual/en/%s.php\n";, $row['count'], $row['sect']); $top20 += $row['count']; } $query = "SELECT COUNT(*) FROM note"; - -$res = mysql_query($query) - or die("query to get total count of notes failed"); - -$total = mysql_result($res,0); +$total = $pdo->single($query); $body = "Following are the top 20 pages of the manual, sorted by the number\n" . "of user notes contributed. These sections could use a polish, those\n" diff --git a/src/DB.php b/src/DB.php index 9adf7c6..770c85f 100644 --- a/src/DB.php +++ b/src/DB.php @@ -12,6 +12,7 @@ public static function connect(): self $db = new self($connectionConfig, self::getUser(), self::getPassword()); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; } @@ -35,4 +36,31 @@ public static function getDatabase(): string { return \getenv("DATABASE_NAME") ?: "phpmasterdb"; } -} \ No newline at end of file + + public function safeQuery(string $sql, array $params = []): array + { + $stmt = $this->prepare($sql); + $stmt->execute($params); + return $stmt->fetchAll(); + } + + public function safeQueryReturnsAffectedRows(string $sql, array $params = []): int + { + $stmt = $this->prepare($sql); + $stmt->execute($params); + return $stmt->rowCount(); + } + + public function row(string $sql, array $params = []): array + { + $result = $this->safeQuery($sql, $params); + return array_shift($result) ?? []; + } + + public function single(string $sql, array $params = [], $column = 0) + { + $stmt = $this->prepare($sql); + $stmt->execute($params); + return $stmt->fetchColumn($column); + } +} diff --git a/src/Query.php b/src/Query.php index 72a6f5f..a34605d 100644 --- a/src/Query.php +++ b/src/Query.php @@ -4,31 +4,28 @@ class Query { private $query = ''; + /** @var array $params */ + private $params = []; public function __construct($str = '', $params = []) { $this->add($str, $params); } public function add($str, $params = []) { - if (substr_count($str, '?') !== count($params)) { - die("Incorrect number of parameters to query."); - } - - $i = 0; - $this->query .= preg_replace_callback('/\?(int)?/', function ($matches) use ($params, &$i) { - if (isset($matches[1]) && $matches[1] === 'int') { - return (int)$params[$i++]; - } else { - return "'" . mysql_real_escape_string($params[$i++]) . "'"; - } - }, $str); + $this->query .= $str; + $this->params = array_merge($this->params, $params); } public function addQuery(Query $q) { $this->query .= $q->get(); + $this->params = array_merge($this->params, $q->getParams()); } public function get() { return $this->query; } + + public function getParams(): array { + return $this->params; + } } \ No newline at end of file -- PHP Webmaster List Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
